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

Question

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

Solution 1

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.

Solution 2

You two different and perhaps contradictory error messages there.

PLS-00306: wrong number or types of arguments in call to 'string' 

What is the dexcription of user defined type data_summary_tab? OracleTypes.CURSOR expects a REF CURSOR, which is equivalent to a JDBC ResultSet. Whereas data_summary_tab sounds like it might be a varray or nested table.

ORA-03115: unsupported network datatype or representation

This suggests you are using an older version of the client than the database server (say 10g or even 9i). Normally we can get away with it, but sometime it can cause bugs where we're doing uncommon things. I'm not sure whether calling user-defined types over JDBC ought to count as an "uncommon thing" but I suspect it may.

Comments

[Andrew Harmel-Law]

Hi @APC. Thanks for this. The last error was a typo. I've edited my question to rectify this. I think the tips on PLS-00306 are the right track (I'm pretty certain we're using the correct JDBC driver). I'll take a look and report back.

[Aleksejs Mjaliks]

And how to hack around, if parameters is in package?

[Planky]

Got me with the capitalization