Calling an Oracle PL/SQL procedure with Custom Object return types from 0jdbc6 JDBCthin drivers
- by Andrew Harmel-Law
I'm writing some JDBC code which calls a Oracle 11g PL/SQL procdedure which has a Custom Object return type. Whenever I try an register my return types, I get either ORA-03115 or PLS-00306 as an error when the statement is executed depending on the type I set. An example is below:
PLSQL Code:
Procedure GetDataSummary (p_my_key IN KEYS.MY_KEY%TYPE,
p_recordset OUT data_summary_tab,
p_status OUT VARCHAR2);
Java Code:
String query = "beginmanageroleviewdata.getdatasummary(?, ?, ?); end;");
CallableStatement stmt = conn.prepareCall(query);
stmt.setInt(1, 83);
stmt.registerOutParameter(2, OracleTypes.CURSOR); // Causes error: PLS-00306
stmt.registerOutParameter(3, OracleTypes.VARCHAR);
stmt.execute(stmt); // Error mentioned above thrown here.
Can anyone provide me with an example showing how I can do this? I guess it's possible. However I can't see a rowset OracleType. CURSOR, REF, DATALINK, and more fail.
Apologies if this is a dumb question. I'm not a PL/SQL expert and may have used the wrong terminology in some areas of my question. (If so, please edit me).
Thanks in advance.
Regs, Andrew
