java 使用来自 0jdbc6 JDBCthin 驱动程序的自定义对象返回类型调用 Oracle PL/SQL 过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2666754/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling an Oracle PL/SQL procedure with Custom Object return types from 0jdbc6 JDBCthin drivers
提问by Andrew Harmel-Law
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:
我正在编写一些 JDBC 代码，它调用具有自定义对象返回类型的 Oracle 11g PL/SQL 过程。每当我尝试注册我的返回类型时，根据我设置的类型执行语句时，我都会收到 ORA-03115 或 PLS-00306 作为错误。一个例子如下：
PLSQL Code:
PLSQL代码：
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):
更多 PLSQL 代码（自定义对象详细信息）：
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:
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.
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.
谁能给我提供一个例子来展示我如何做到这一点？我想这是可能的。但是我看不到行集 OracleType。CURSOR、REF、DATALINK 等失败。
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).
如果这是一个愚蠢的问题，请道歉。我不是 PL/SQL 专家，可能在我的问题的某些领域使用了错误的术语。（如果是这样，请编辑我）。
Thanks in advance.
提前致谢。
Regs, Andrew
雷格斯，安德鲁
回答by Andrew Harmel-Law
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.
而不是我一直在尝试使用的简单 JDBC CallableStatement。
The second part was that I had to register my "out" parameter as follows:
第二部分是我必须按如下方式注册我的“out”参数：
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:
第三部分，在上面的第 2 部分中是隐含的，“DATA_SUMMARY_TAB”必须是大写的。如果你把它放在小写，那么你会得到一个神秘的错误消息，如下所示：
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)
在 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)
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.
另外，请注意我们的自定义对象类型不在任何包中。如果是，您可能需要稍微修改第三个参数。
回答by APC
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.CURSORexpects a REF CURSOR, which is equivalent to a JDBC ResultSet. Whereas data_summary_tabsounds like it might be a varray or nested table.
用户定义类型的描述是data_summary_tab什么？ OracleTypes.CURSOR需要一个 REF CURSOR，它相当于一个 JDBC ResultSet。而data_summary_tab听起来它可能是一个 varray 或嵌套表。
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.
这表明您使用的是比数据库服务器旧版本的客户端（比如 10g 甚至 9i）。通常我们可以摆脱它，但有时它会导致我们正在做一些不寻常的事情的错误。我不确定通过 JDBC 调用用户定义的类型是否应该算作“不常见的事情”，但我怀疑它可能。
