Oracle Stored Procedure Array Parameters
We are passing custom Oracle datatypes (in our case, arrays of basic types such as Varchar) as parameters to stored procedures using JDBC. Everything is working fine, but we have about 12 custom Oracle data types, and each one takes just over 1/2 second to register (using ArrayDescriptor). This gives us about a 7 second hit for all of them - seems a bit much for a simple operation.
Is there a way to speed this up - config settings, tricks, etc?
Alternatively, can I define these data types at compile time, or save the registered ArrayDescriptor as a persistent object?
Any ideas would be greatly appreciated
Here's some notes I made for myself - hope they help.
>>>>>>>>>>>>>>>>>>>>>>
Brief summary of passing arrays to/from Oracle stored procedure:
- in Oracle, create oracle data type (NOT pl/sql type - at least not in version 8.1.5 of classes12.zip -- 8.1.7 might support some, like VTable)
create type TYPE_CHAR1 as char (? not sure of syntax)
- create data type definition in java - ArrayDescriptor
ArrayDescriptor TYPE_DESC_CHAR1 = ArrayDescriptor.createDescriptor(TYPE_CHAR1, moConn);
- to get output array
CallableStatement oSP.registerOutParameter(7, java.sql.Types.ARRAY, TYPE_CHAR1);
oSP.execute();
ResultSet rs = oSP.getArray(7).getResultSet(); //note: there are other ways to do this
rsName.getString(2); // pos 1 = index into array, pos 2 = returned value
- to set input array
CallableStatement oSP.setArray(iIdx++, new ARRAY(TYPE_DESC_CHAR1, conn, myArray) );
- To save ArrayDescriptors to .jar file (performance enhancement)
// Place object definition in file - do once in development.
FileOutputStream ostream = new FileOutputStream(sFile);
ObjectOutputStream p = new ObjectOutputStream(ostream);
ArrayDescriptor TYPE_DESC_CHAR1 = ArrayDescriptor.createDescriptor(TYPE_CHAR1, conn);
P.writeObject(TYPE_DESC_CHAR1);
p.flush();
ostream.close();
-- use jarsigner tool to place file into .jar
-- ex: jar cf myjar.jar myarraydesc.txt
// Get object definitions from jar file sSource
ClassLoader oCL = this.getClass().getClassLoader();
InputStream istream = oCL.getResourceAsStream("myarraydesc.txt"); //Note: remember to import .jar file
ObjectInputStream p = new ObjectInputStream(istream);
TYPE_DESC_CHAR1 = (ArrayDescriptor) p.readObject();
TYPE_DESC_CHAR1.setConnection(moConn); //must set connection for each object
istream.close();
