从0jdbc6 JDBCthin驱动程序使用自定义对象返回类型调用Oracle PL/SQL过程 [英] Calling an Oracle PL/SQL procedure with Custom Object return types from 0jdbc6 JDBCthin drivers

问题描述

我正在编写一些JDBC代码，该代码调用具有Custom Object返回类型的Oracle 11g PL/SQL过程.每当我尝试使用返回类型的寄存器时，根据我设置的类型执行该语句时，都会收到ORA-03115或PLS-00306错误.下面是一个示例:

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代码:

Procedure GetDataSummary (p_my_key    IN    KEYS.MY_KEY%TYPE,
                          p_recordset OUT   data_summary_tab,
                          p_status    OUT   VARCHAR2);

更多PLSQL代码(自定义对象详细信息):

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代码:

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.

谁能给我一个例子，说明我该怎么做?我想这是可能的.但是我看不到行集OracleType. CURSOR，REF，DATALINK等失败.

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.

很抱歉，这是一个愚蠢的问题.我不是PL/SQL专家，并且在某些问题上可能使用了错误的术语. (如果是这样，请编辑我.)

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).

谢谢.

Regs，安德鲁

推荐答案

我终于(在别人的帮助下)找到了答案.它分为三个部分:

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

首先是我需要使用:

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

不是我一直尝试使用的简单JDBC CallableStatement.

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

第二部分是我必须按如下方式注册"out"参数:

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

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

第三部分(在上面的第二部分中是隐含的)是"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

oracle.jdbc.oracore.OracleTypeADT.initMetadata上的

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

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)

就是这样.

此外，请注意，我们的自定义对象类型不在任何程序包中.如果是这样，您可能需要修改一下第三个参数.

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.

这篇关于从0jdbc6 JDBCthin驱动程序使用自定义对象返回类型调用Oracle PL/SQL过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！