使用来自 0jdbc6 JDBCthin 驱动程序的自定义对象返回类型调用 Oracle PL/SQL 过程 [英] Calling an Oracle PL/SQL procedure with Custom Object return types from 0jdbc6 JDBCthin drivers
问题描述
我正在编写一些 JDBC 代码,它调用具有自定义对象返回类型的 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).
提前致谢.
注册,安德鲁
推荐答案
我终于(在别人的帮助下)找到了这个问题的答案.它分为三个部分:
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);
而不是我一直在尝试使用的简单 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屋!