Java Database Connectivity (JDBC)

I found in these forums and on AskTom several entries about passing a Java array as parameter to PL-SQL code, an elegant solution for my web-application I thought. That was about 2 weeks ago, and here's my WARNING WHY YOU SHOULDN'T USE IT..

Here's how I did it (jump to the line 'It works..' if the technical details are not of your interest but you just want to know why not).

On the db side, define tp_string_array_table as:
. create or replace type tp_string_array_table as table of varchar2(48)

In the java code, the String array productIdArr[] contains the list of product id's to insert:
. ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("TP_STRING_ARRAY_TABLE", oracleConnection );

. ARRAY arrayToPass = new ARRAY( descriptor, oracleConnection, productIdArr );

. OraclePreparedStatement ps = (OraclePreparedStatement)
.. oracleConnection.prepareStatement( "{ call pkg_product.insert_product_id_array( ? ) }" );
. ps.setARRAY( 1, arrayToPass );
. ps.execute();
. ps.close();


And on the plsql side:
. procedure insert_product_id_array( a_product_id_array tp_string_array_table )
. is
. begin
.. for i in 1 .. a_product_id_array.count
.. loop
... insert into t_product(product_id)
... values ( a_product_id_array(i));
.. end loop;
. end insert_product;

It works. But my advice is.. better shy away from it, unless you want to get stung.

(1) First of all it breaks your architectural pattern: if the rest of your code makes use of the standard JDBC java.sql.Connection, java.sql.PreparedStatement's classes, etc. why break that pattern and bolt on some oracle.jdbc.OracleConnection, oracle.jdbc.OraclePreparedStatement classes? It feels like replacing one of your ferrari's wheels by a tractor wheel...

(2) Then you'll have trouble when using a connection pool, since the OracleConnection can only be established from an 'underlying' connection (Apache dbcp parlando), not a wrapped poolconnection (another a break from your architectural pattern).
It's not too much trouble, eg. in Tomcat (6.x) simply add accessToUnderlyingConnectionAllowed="true" to your <Resource.. > definition, and get your Oracle connection like this:
Connection delegatingConn = ((DelegatingConnection)pooledConnection).getInnermostDelegate();
oracleConnection= (oracle.jdbc.OracleConnection)delegatingConn;
This will be different for other webcontainers / connection pools (so migrate away from Tomcat, and your app won't work anymore).

(3) It's buggy. I had it working fine on Oracle 10 Express on linux, the same for Oracle 11g-32 bit on linux, but on Oracle 11g-64 bit on linux it didn't ! What happened on the latter platform? !t didn't error out, but just passed an array of null values (the number of elements was correct, but they were all null). So exactly the same java/plsql code but with a different Oracle 11 database produces different results!

Number 3 was the killer argument to remove it again from my code. Wasted days.

Bottom line: I'm still waiting for Oracle to come up with a simple, elegant and above all standard java solution for passing an array as a parameter to a PL-SQL procedure....

Thanks for listening to my rant.

Willem

Keywords: java plsql array argument parameter OracleConnection accessToUnderlyingConnectionAllowed oracle11g