Calling an Oracle PL/SQL procedure with Custom Object return types from 0jdbc6 JDBCthin drivers

Posted by Andrew Harmel-Law on Stack Overflow See other posts from Stack Overflow or by Andrew Harmel-Law
Published on 2010-04-19T10:53:24Z Indexed on 2010/04/20 11:13 UTC
Read the original article Hit count: 195

Filed under:
|
|
|
|

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

© Stack Overflow or respective owner

Related posts about plsql

Related posts about java