ADF/BC JDBC to retrieve structured data from packaged function

Got a request for code to show how to retrieve a collection of structured data (Oracle Type Objects) using JDBC in an ADF/BC scenario. The first parameter is the name of the Oracle Type that is the collection of your structured data type.

public Object callStoredFunctionReturningArrayOfRecords(String pfunctionReturnType, String stmt,Object[] bindVars) throws SQLException {
oracle.sql.STRUCT [] returnArray = null;
String [] recordArray = null;
Connection conn = getDBTransaction().createStatement(1).getConnection();
//Connection conn = getConnection();
// Now, declare a descriptor to associate the host array type with the
// array type in the database.


ArrayDescriptor arrayDescriptor=ArrayDescriptor.createDescriptor(pfunctionReturnType, conn);
// example:  StructDescriptor structDescriptor = StructDescriptor.createDescriptor("TEST_ARR_OF_REC_TYPE", conn);


// Create the ARRAY objects to associate the host array
// with the database array.


oracle.sql.ARRAY returnARRAY = new oracle.sql.ARRAY(arrayDescriptor,conn,returnArray);

OracleCallableStatement st = null;
try {
// 1. Create a JDBC CallabledStatement
st = (OracleCallableStatement)getDBTransaction().createCallableStatement(
"begin ? := " + stmt + ";end;", 0);


// 2. Register the first bind variable for the return value
st.registerOutParameter(1, OracleTypes.ARRAY, pfunctionReturnType);
if (bindVars != null) {
// 3. Loop over values for the bind variables passed in, if any
for (int z = 0; z < bindVars.length; z++) {
// 4. Set the value of user-supplied bind vars in the stmt
st.setObject(z + 2, bindVars[z]);
}
}
// 5. Set the value of user-supplied bind vars in the stmt
st.executeUpdate();
// Associate the returned arrays with the ARRAY objects.


returnARRAY = (oracle.sql.ARRAY)st.getARRAY(1);
// OracleResultSet mainRS = (OracleResultSet)st.getResultSet();
// ARRAY anotherARRAY = mainRS.getARRAY(1);


// Get the data back into the data arrays.
// NOTE: I got an NPE on the following line at one point...not sure why...
// I saw this error in opmn log; not sure if returnARRAY was null or
// if there are special rules about casting null into an array.
Object[] oarray = (Object[])returnARRAY.getArray();
// Object[] oarray = (Object[])anotherARRAY.getArray();


for (int i = 0; i < oarray.length; i++) {
oracle.sql.STRUCT struct = (oracle.sql.STRUCT)oarray[i];//new STRUCT(structDescriptor, conn, recordArray);
StructDescriptor structDescriptor = struct.getDescriptor();
ResultSetMetaData rsmd = structDescriptor.getMetaData();
Object [] attrs = struct.getAttributes();
if (rsmd != null && attrs != null && attrs[0] != null && attrs[1] != null) {
getLogger().fine("nested row [" + i + "]: " + rsmd.getColumnLabel(1) +
" " + attrs[0].toString() + " " + rsmd.getColumnLabel(2) +
" " + attrs[1].toString());
}
}
// 6. Return the value of the first bind variable
return oarray;
} catch (SQLException e) {
throw new JboException(e);
} finally {
if (st != null) {
try {
// 7. Close the statement
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}