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

Turning Python Scripts into Working Web Apps Quickly with Streamlit

 I just realized that I am using Streamlit since almost one year now, posted about in Twitter or LinkedIn several times, but never wrote a blog post about it before. Communication in Data Science and Machine Learning is the key. Being able to showcase work in progress and share results with the business makes the difference. Verbal and non-verbal communication skills are important. Having some tool that could support you in this kind of conversation with a mixed audience that couldn't have a technical background or would like to hear in terms of results and business value would be of great help. I found that Streamlit fits well this scenario. Streamlit is an Open Source (Apache License 2.0) Python framework that turns data or ML scripts into shareable web apps in minutes (no kidding). Python only: no front‑end experience required. To start with Streamlit, just install it through pip (it is available in Anaconda too): pip install streamlit and you are ready to execute the working de...
12 comments
Read more

jOOQ: code generation in Eclipse

jOOQ allows code generation from a database schema through ANT tasks, Maven and shell command tools. But if you're working with Eclipse it's easier to create a new Run Configuration to perform this operation. First of all you have to write the usual XML configuration file for the code generation starting from the database: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-2.0.4.xsd">   <jdbc>     <driver>oracle.jdbc.driver.OracleDriver</driver>     <url>jdbc:oracle:thin:@dbhost:1700:DBSID</url>     <user>DB_FTRS</user>     <password>password</password>   </jdbc>   <generator>     <name>org.jooq.util.DefaultGenerator</name>     <database>       <name>org.jooq.util.oracle.OracleDatabase</name>     ...
3 comments
Read more

Load testing MongoDB using JMeter

Apache JMeter ( http://jmeter.apache.org/ ) added support for MongoDB since its 2.10 release. In this post I am referring to the latest JMeter release (2.13). A preliminary JMeter setup is needed before starting your first test plan for MongoDB. It uses Groovy as scripting reference language, so Groovy needs to be set up for our favorite load testing tool. Follow these steps to complete the set up: Download Groovy from the official website ( http://www.groovy-lang.org/download.html ). In this post I am referring to the Groovy release 2.4.4, but using later versions is fine. Copy the groovy-all-2.4.4.jar to the $JMETER_HOME/lib folder. Restart JMeter if it was running while adding the Groovy JAR file. Now you can start creating a test plan for MongoDB load testing. From the UI select the MongoDB template ( File -> Templates... ). The new test plan has a MongoDB Source Config element. Here you have to setup the connection details for the database to be tested: The Threa...
39 comments
Read more