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();
}
}
}
}

jdbc calling procedures and DBTransaction isDirty()

Did you know that even if you create a callableStatement from the DBTransaction in your ADF/BC layer and call a database stored procedure, function, package, etc...that; and if that stored program unit that you called does DML and dirty's the transaction, the DBTransaction's isDirty() method will not go to true (unless it was true before the call)?

I did not; but now I do. And now: so do you, dear reader.