Java - Arrays and [Oracle] JDBC

Site hosted by Angelfire.com: Build your free website today!

http://www.princeton.edu/~storacle/jdbc8_doc/oracle.sql.ArrayDescriptor.html

Arrays and Oracle's JDBC drivers don't seem to play well together. I can't speak for other JDBC drivers, but it appears at first glance that it is impossible to pass arrays to and from Oracle via their JDBC drivers. Actually, it is possible but not intuitive.

Environment :

BEA Weblogic 6.x with Oracle JDBC Thin-Client Drivers (Type 4)
Oracle DBMS 8.1.6 (Oracle 7.x won't work)

Originally, I got all this to work using the older Oracle JDBC drivers (Type 2). I changed the ArrayDescriptor... Blah, blah. This really didn't help much because our environment uses the Type 4 drivers (which didn't work with the ArrayDescriptor change).

Solution :

Implement custom OracleArray class
Create Oracle "object" type [array] in the database
Code against the custom OracleArray and "object array(s)"

Source :	Listing1 - custom OracleArray implemention
Author :	Jason Vogel
References Links :	http://www.poboxes.com/jasonvogel

Source	package com.<companyname>.baseclass.service.data; import oracle.sql.Datum; import oracle.sql.CustomDatum; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.jdbc.driver.OracleConnection; import java.util.; import java.sql.SQLException; public class OracleArray implements CustomDatum { private List data; private String sqlType; // pass in the sql name of the array* public OracleArray(String sqlType, List data) { this.data = data; this.sqlType = sqlType; } public Datum toDatum(OracleConnection conn) throws SQLException { ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(sqlType, conn); ARRAY array = new ARRAY(arrayDesc, conn, data.toArray()); return array; } }

Source :	Listing2 - Oracle "array object"
Author :	Jason Vogel
Notes :	We created an "array" object for string, number, and datetime. Additionally, this must match your Java source. Oracle Gotchas : Synonyms do NOT work against OBJECT Types. Our workaround was to create a public user "PROD_TYPES" to own these objects in all of our schemas (because the user has to be hard-coded). Index-By tables will NOT work, you must use Nested Tables.
References Links :	http://www.poboxes.com/jasonvogel

Source	Object Definitions // Object Type : TYPE_STRING_ARRAY CREATE OR REPLACE TYPE TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000); // Object Type : TYPE_NUMBER_ARRAY CREATE OR REPLACE TYPE TYPE_NUMBER_ARRAY AS TABLE OF NUMBER; ... Test Package CREATE OR REPLACE PACKAGE BODY java_test IS FUNCTION f_get_value_test ( s_arg_in IN VARCHAR2, s_arg_inout IN OUT VARCHAR2, s_arg_out OUT VARCHAR2, s_error_msg_out OUT common_func.T_ERROR_MSG ) RETURN NUMBER IS ls_routine_name VARCHAR2(50) := 'f_get_value_test'; BEGIN s_arg_out := TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'); INSERT INTO CLIPBOARD (clipboard_id,owner,misc_text_1,misc_text_2,misc_text_3) VALUES (clipboard_id_seq.nextval,'JVOGEL',s_arg_in,s_arg_inout,s_arg_out); s_arg_inout := '2+2'; commit; RETURN common_func.SUCCESS; EXCEPTION WHEN OTHERS THEN s_error_msg_out := 'SQLERRM : '\|\|SQLERRM; rollback; RETURN common_func.ERROR; END; FUNCTION f_get_value_test_array ( s_arg_out OUT VARCHAR2, st_arg_array_inout OUT PROD_TYPES.TYPE_STRING_ARRAY, s_error_msg_out OUT common_func.T_ERROR_MSG ) RETURN NUMBER IS ls_routine_name VARCHAR2(50) := 'f_get_value_test_array'; ln_count number := -1; BEGIN s_arg_out := TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'); s_error_msg_out := 'Looking good!!!'; -- Constructor st_arg_array_inout := PROD_TYPES.TYPE_STRING_ARRAY(); -- Allocate room for element(s) st_arg_array_inout.EXTEND(5); -- Set element value(s) st_arg_array_inout(1) := 'success a11'; st_arg_array_inout(2) := 'success a12'; st_arg_array_inout(3) := 'success a13'; st_arg_array_inout(4) := '@#$@ success a14 !!!'; st_arg_array_inout(5) := '!!! success A15 ***'; RETURN common_func.SUCCESS; EXCEPTION WHEN OTHERS THEN s_error_msg_out := '[Package Error] SQLERRM : '\|\|SQLERRM; RETURN common_func.ERROR; END; BEGIN null; END java_test;

Source :	Listing3 - Example Usage
Author :	Jason Vogel
References Links :	http://www.poboxes.com/jasonvogel

Source	package com.<companyname>.ejb.session; import javax.ejb.; import java.util.; import javax.naming.; import java.sql.; import javax.sql.; import javax.naming.; import javax.ejb.RemoveException; import javax.naming.Context; import javax.naming.NamingException; import com.<companyname>.ejb.entity.; import oracle.sql.; import oracle.jdbc.driver.; /* * @stereotype SessionBean * @remoteInterface com.<companyname>.ejb.session.testBean * @homeInterface com.<companyname>.ejb.session.testBeanHome * @statemode Stateless / public* class testBeanEJB implements javax.ejb.SessionBean { public testBeanEJB() { } // --------------------------------------------------------------- // SessionBean interface implementation public void ejbActivate() { } public void ejbPassivate() { } public void ejbRemove() { } public void setSessionContext(SessionContext ctx) { this.ctx = ctx; } // --------------------------------------------------------------- // create methods public void ejbCreate() { } public void getValueTest(String argumentOne, String argumentTwo) throws FinderException, EJBException , SQLException { Connection connection = null; ResultSet resultSet = null; CallableStatement callableStatement = null; Exception savedException = null; long serviceItemIdLong = 0; String inOutArgument = null; String outArgument = null; String dataBaseErrorMessage = null; long returnNumber = 2; String[] stringArray1Obj = {}; ARRAY arrayArray1Obj = null; ArrayDescriptor descriptor = null; List myList = null; /////////////////////////////////////////////////////////////////////////////////////////////// // java_test.f_get_value_test_array /////////////////////////////////////////////////////////////////////////////////////////////// try { // Get the connection connection = EntityHelper.getDataSourceConnection(getDataSourceJndiName()); if (connection == null) { throw new Exception("Unable to get valid connection"); } else { System.out.println("connection.getClass() : " +connection.getClass()); } OracleArray myArray = new OracleArray("TYPE_STRING_ARRAY", myList); // Create SQL callableStatement = connection.prepareCall("{? = call java_test.f_get_value_test_array(?,?,?)}"); *Example IN arguments [NOT USED HERE]* `// Register the SQL arg IN parameters callableStatement.setInt(1,accountNumber); callableStatement.setARRAY(2, myArray.toARRAY(con)); callableStatement.setInt(3,locationCode); callableStatement.setString(4,sessionId); callableStatement.setString(5,userName);` // Register the SQL arg parameters callableStatement.registerOutParameter(1,Types.NUMERIC); // Return Code callableStatement.registerOutParameter(2,Types.VARCHAR); // callableStatement.registerOutParameter(3,OracleTypes.ARRAY,"TYPE_STRING_ARRAY"); callableStatement.registerOutParameter(3,OracleTypes.ARRAY,"TYPE_STRING_ARRAY"); callableStatement.registerOutParameter(4,Types.VARCHAR); // Execute the pl/sql package function. try { callableStatement.execute(); } catch (Exception exception) { System.out.println("!!! Execute failed !!!"); throw new Exception(exception.toString()); } arrayArray1Obj = (ARRAY)callableStatement.getArray(3); if (arrayArray1Obj != null) { stringArray1Obj = (String[])arrayArray1Obj.getArray(); if (stringArray1Obj != null) { // Print out the forth element System.out.println("stringArray1Obj[3] : " + stringArray1Obj[3]); } } // Get the return value from function returnNumber = callableStatement.getLong(1); System.out.println("[java_test.f_get_value_test_array] ReturnCode : " + returnNumber); // String s1 = callableStatement.getString(2); // Both work String s1 = (String)callableStatement.getObject(2); // Both work // Get the return error message dataBaseErrorMessage = callableStatement.getString(4); System.out.println("dataBaseErrorMessage : " + dataBaseErrorMessage); if (returnNumber == 0) { // No data found throw new FinderException("Unable to find the values."); } else if (returnNumber < 0) { // Some other database error throw new Exception(dataBaseErrorMessage); } } catch (FinderException exception) { savedException = exception; throw exception; } catch (SQLException exception) { savedException = exception; throw exception; } catch (Exception exception) { savedException = exception; throw new EJBException(exception.toString()); } finally { try { //Since CallableSatement is descendant of PreparedStatement //cleanupDataSource will work even if callableStatement is passed as Argument. EntityHelper.cleanupDataSource(connection, callableStatement); } catch (Exception exception) { if (savedException != null) { throw new EJBException(savedException.toString() + exception.toString()); } else { throw new EJBException (exception.toString()); } } } System.out.println("Finished call to java_test.f_get_value_test_array"); // RETURN COLLECTION OF PK // return serviceItemKeyArrayList; } private String getDataSourceJndiName() throws NamingException, Exception { if(rootContext == null) { rootContext = (Context)EntityHelper.getContext().lookup(ENVIRONMENT_JNDINAME); } return (String)rootContext.lookup("DATA_SOURCE_JNDINAME"); } // --------------------------------------------------------------- // private fields private SessionContext ctx; private Context rootContext; static final String ENVIRONMENT_JNDINAME = "java:comp/env"; }