Oracle PL / SQL procedure call using custom object return types from 0jdbc6 JDBCthin drivers

Question

I am writing some JDBC code that invokes Oracle 11g PL / SQL processing, which has the type of the returned user object. Whenever I try to register my return types, I get either ORA-03115 or PLS-00306 as an error when the statement is executed depending on the type set. The following is an example:

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 information):

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 someone provide me an example showing how I can do this? I think it's possible. However, I do not see the OracleType rowset. CURSOR, REF, DATALINK, etc.

, . PL/SQL , , . ( , , ).

.

Regs, Andrew

Answer 1

( ) . :

-, :

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

JDBC CallableStatement, .

, "" :

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

, 2 , , "DATA_SUMMARY_TAB" . , :

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

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

.

, , - . , .

Answer 2

, , .

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

data_summary_tab? OracleTypes.CURSOR REF CURSOR, JDBC ResultSet. data_summary_tab , .

ORA-03115: unsupported network datatype or representation

, , (, 10g 9i). , , . , JDBC "", , .