http://technology.amis.nl/2008/07/16/oracle-jdbc-passing-a-table-of-custom-object-type-to-a-stored-procedure-implementing-efficient-single-round-trip-data-exchange-part-two-of-an-adf-application-on-a-plsql-api/
public String getCount(String[] parmArray, String sqlIn) {
CallableStatement cs = null;
Connection conn;
try {
cs =
getDBTransaction().createCallableStatement("begin SP_CREATESQL(?,?,?,?,?);end;",
0);
conn = getDBTransaction().createStatement(1).getConnection();
Object[]
paramArray = new Object[2];
paramArray[0] = parmArray[0];
paramArray[1] = parmArray[1];
ArrayDescriptor arrayDesc =
ArrayDescriptor.createDescriptor("T_STRINGARRAY", conn);
// T_STRINGARRAY is a Collection Type defined in the database schema
ARRAY sqlArray = new ARRAY(arrayDesc, conn, paramArray);
cs.setArray(1, sqlArray);
cs.setString(2, sqlIn);
cs.registerOutParameter(3, Types.VARCHAR);
cs.registerOutParameter(4, Types.INTEGER);
cs.registerOutParameter(5, Types.VARCHAR);
cs.executeUpdate();
System.out.println("Count " + cs.getString(3));
System.out.println("Error Code: " + cs.getInt(4));
System.out.println("Error Message: " + cs.getString(5));
return cs.getString(3);
} catch (SQLException e) {
e.printStackTrace();
throw new JboException(e);
} finally {
if (cs != null) {
try {
cs.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println(e.getSQLState());
}
}
}
}
