Thursday, November 17, 2011

Oracle jdbc 4.0 driver and createArrayOf issue

Problem description:

Passing an Array object to database stored procedure you can acheive as shown in oracle jdbc 4.0 documentation, eg:

Array aArray = con.createArrayOf("VARCHAR", northEastRegionnewYork);


but ... this don't work with oracle database, as I noticed today. After some googling I found following information:

The SQL standard array type is anonymous, that is the type "array of foo" does not have a name. Only the element type is named. In Oracle SQL the array type is named. In fact anonymous array types are not supported. So, the JDBC 4.0 standard factory method takes the element type as its argument and creates an instance of an anomyous array type. The Oracle JDBC drivers define an Oracle proprietary method, createArray, which takes the name of an array type and returns an instance of that named array type. This is required by the way Oracle SQL is defined. At present the Oracle database cannot support the JDBC 4.0 standard createArrayOf method.

Hmm... how to code this? This is my solution:

Connection conn;
....
ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("NUMARRAY", conn);
ARRAY ar = new ARRAY(arraydesc, conn, di);
....
statement.setArray(1, ar);
statement.setString(2, "ble");
statement.execute();
ar.free();
...

No comments:

Post a Comment