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

java, jdbc, oracle11g, plsql, rdbms

Solution

I finally (with a little help from others) found out the answer to this. It came in three parts:

The first was that I needed to use an:

OracleCallableStatement stmt = (OracleCallableStatement) conn.prepareCall(query);

rather than the simple JDBC CallableStatement I had been trying to use.

The second part was that I had to register my "out" parameter as follows:

stmt.registerOutParameter(2, OracleTypes.STRUCT, "DATA_SUMMARY_TAB");

The third part, and it is implicit in part 2 above, was that "DATA_SUMMARY_TAB" had to be in UPPER CASE. If you put it in lower case, then you get a cryptic error message as follows:

java.sql.SQLException: invalid name pattern: MYTEST.data_summary_tab

at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:553) at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:469) at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:390) at oracle.sql.StructDescriptor.(StructDescriptor.java:320)

That's it.

Also, please note our custom object type was not in any packages. If it is, you might need to hack the third parameter around a little.

Problem

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); ``` More PLSQL Code (Custom Object Details): ``` CREATE OR REPLACE TYPE data_summary_obj AS OBJECT (data_key NUMBER, data_category VARCHAR2 (100), sensitive_flag VARCHAR2 (1), date_created DATE, date_rep_received DATE, date_first_offering DATE, agency_data_ref VARCHAR2 (13), change_code VARCHAR2 (120), data_ref VARCHAR2 (50), data_status VARCHAR2 (100), data_count NUMBER) / CREATE OR REPLACE TYPE data_summary_tab AS TABLE OF data_summary_obj / ``` Java Code: ``` String query = "begin manageroleviewdata.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

Original source