Skip to main content

ArrayDescriptor and Oracle 11g JDBC driver

This month I have a lot of things to post about GAE, jOOQ, GWT, MVPP, but so little time to write them. In the meantime I want to share a little tip about a strange error message we had last year during the migration of a large number of J2EE web applications from Oracle 9i to 11g (well, the project context was bigger than a mere database migration, but the error occurred was due to the migration only). Part of these applications used to invoke PL/SQL functions and stored procedures on Oracle databases.

Oracle JDBC for 11g introduced an important difference from the 9i release in the oracle.sql.ArrayDescriptor usage. Every time you need to pass an Array of data from a Java web application to an Oracle stored procedure you have to use an oracle.sql.ARRAY object. In order to create it, before you have to create an instance of oracle.sql.ArrayDescriptor this way:

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("CUSTOM_TYPE_ARRAY", conn);

were CUSTOM_TYPE_ARRAY is a custom type defined in the Oracle schema used by the web application and conn is an instance of oracle.jdbc.driver.T4CConnection. In a web application this connection underlies in a pooling data source and so you need to get it previously through reflection using the getInnermostDelegate method:


Method delegateMethod = 
             conn.getClass().getSuperclass().getMethod("getInnermostDelegate", new Class[0]);
if(delegateMethod != null) {
conn = (Connection) delegateMethod.invoke(conn, new Object[0]);
} 

But simply moving to JDBC for 11g (no change in the application code and the stored procedures) this doesn’t work anymore. Trying to create the new instance of ArrayDescriptor you obtain the following exception:


java.sql.SQLException: Fail to construct descriptor: Invalid arguments
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:147)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:144)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115)

The exception thrown doesn't explain the real cause and is misleading: so people could waste a lot of time going in a wrong direction.
This error happens because the internal implementation of the oracle.sql.ArrayDescriptor class is almost totally different from JDBC 9i to JDBC 11g and Oracle introduced a further access restriction in the new release.
The solution is the following: you have to simply set the accessToUnderlyingConnectionAllowed property of the application data source to true (if not specified the default is false) in the application context.xml file. Example:


<Resource name="dsDataSourceNT" auth="Container"
     type="javax.sql.DataSource" username="UserName" password="XXXXXXX"
     driverClassName="oracle.jdbc.driver.OracleDriver"
     url="jdbc:oracle:thin:@DBServer:1612:SID5"
     maxActive="16" maxIdle="8" maxWait="60000"
     validationQuery="SELECT 1 FROM DUAL"
    accessToUnderlyingConnectionAllowed="true"
/>



Labels:

Comments

Post a Comment

Popular posts from this blog

Streamsets Data Collector log shipping and analysis using ElasticSearch, Kibana and... the Streamsets Data Collector

One common use case scenario for the Streamsets Data Collector (SDC) is the log shipping to some system, like ElasticSearch, for real-time analysis. To build a pipeline for this particular purpose in SDC is really simple and fast and doesn't require coding at all. For this quick tutorial I will use the SDC logs as example. The log data will be shipped to Elasticsearch and then visualized through a Kibana dashboard. Basic knowledge of SDC, Elasticsearch and Kibana is required for a better understanding of this post. These are the releases I am referring to for each system involved in this tutorial: JDK 8 Streamsets Data Collector 1.4.0 ElasticSearch 2.3.3 Kibana 4.5.1 Elasticsearch and Kibana installation You should have your Elasticsearch cluster installed and configured and a Kibana instance pointing to that cluster in order to go on with this tutorial. Please refer to the official documentation for these two products in order to complete their installation (if you do
Post a Comment
Read more

Exporting InfluxDB data to a CVS file

Sometimes you would need to export a sample of the data from an InfluxDB table to a CSV file (for example to allow a data scientist to do some offline analysis using a tool like Jupyter, Zeppelin or Spark Notebook). It is possible to perform this operation through the influx command line client. This is the general syntax: sudo /usr/bin/influx -database '<database_name>' -host '<hostname>' -username '<username>'  -password '<password>' -execute 'select_statement' -format '<format>' > <file_path>/<file_name>.csv where the format could be csv , json or column . Example: sudo /usr/bin/influx -database 'telegraf' -host 'localhost' -username 'admin'  -password '123456789' -execute 'select * from mem' -format 'csv' > /home/googlielmo/influxdb-export/mem-export.csv
6 comments
Read more

Using Rapids cuDF in a Colab notebook

During last Spark+AI Summit Europe 2019 I had a chance to attend a talk from Miguel Martinez  who was presenting Rapids , the new Open Source framework from NVIDIA for GPU accelerated end-to-end Data Science and Analytics. Fig. 1 - Overview of the Rapids eco-system Rapids is a suite of Open Source libraries: cuDF cuML cuGraph cuXFilter I enjoied the presentation and liked the idea of this initiative, so I wanted to start playing with the Rapids libraries in Python on Colab , starting from cuDF, but the first attempt came with an issue that I eventually solved. So in this post I am going to share how I fixed it, with the hope it would be useful to someone else running into the same blocker. I am assuming here you are already familiar with Google Colab. I am using Python 3.x as Python 2 isn't supported by Rapids. Once you have created a new notebook in Colab, you need to check if the runtime for it is set to use Python 3 and uses a GPU as hardware accelerator. You
5 comments
Read more