从 Java 调用返回 Oracle 类型的 PL/SQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22201330/
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
Call PL/SQL function returning Oracle type from Java
提问by harindya
I have a PL/SQL function as follows which returns an Oracle type (PROCESSEXCLEFILEARGS)
我有一个 PL/SQL 函数，如下所示，它返回一个 Oracle 类型（PROCESSEXCLEFILEARGS）
FUNCTION PROCESS_FILE_INTERNAL
(
i_Filename VARCHAR2,
i_EventType NUMBER
)
RETURN PROCESSEXCELFILEARGS
I have to call this function from Java and my Java Method looks as follows
我必须从 Java 调用这个函数，我的 Java 方法如下所示
OracleCallableStatement cstmt = null;
try{
OracleDriver ora = new OracleDriver();
DriverManager.registerDriver(ora);
Connection connection = ora.defaultConnection();
String call = "{ ? = call NEUTRINO_META.PKG_EXCEL.PROCESS_FILE_INTERNAL(?, ?) }";
cstmt = (OracleCallableStatement)connection.prepareCall(call);
cstmt.setQueryTimeout(1800);
cstmt.registerOutParameter(1, OracleTypes.OTHER, "NEUTRINO_META.PROCESSEXCELFILEARGS");
cstmt.setString(2, filename);
cstmt.setDouble(3, eventType);
cstmt.execute();
OracleObjects.ProcessExcelFileArgsobj = (OracleObjects.ProcessExcelFileArgs)cstmt.getObject(1);
connection.commit();
}
catch (SQLException e){
WriteEventToDb(e.getMessage());
}
finally{
if (cstmt != null){
cstmt.close();
}
}
OracleObject.ProcessExcelFileArgs is implementing SQLData, and the readSQl(..) and writeSQL(..) method are implemented properly to read and write the types fields.
OracleObject.ProcessExcelFileArgs 正在实现 SQLData，并且正确实现了 readSQl(..) 和 writeSQL(..) 方法来读取和写入类型字段。
But when i run this java method I get a SQLException with message 'Invalid column type: 1111'
但是当我运行这个 java 方法时，我得到一个带有消息“无效列类型：1111”的 SQLException
Can anyone let me know if there is anything wrong in the approach I took, or if there is any other way to retrieve the return oracle type as a java object.
任何人都可以让我知道我采用的方法是否有任何问题，或者是否有任何其他方法可以将返回的 oracle 类型检索为 java 对象。
EDIT:
编辑：
create or replace
TYPE PROCESSEXCELFILEARGS FORCE AS OBJECT
(
FullFilePath VARCHAR2(700),
Filename VARCHAR2(200),
Graph TYPEGRAPHDATA
)
please not that TYPEGRAPHDATA is another user defined Oracle type at schema level
请不要说 TYPEGRAPHDATA 是另一个用户定义的架构级别的 Oracle 类型
thanks
谢谢
采纳答案by harindya
ok I managed to get the returned oracle type as a java object by using the following code.
好的，我设法使用以下代码将返回的 oracle 类型作为 java 对象获取。
try{
Map rtn = connection.getTypeMap();
rtn.put("NEUTRINO_META.PROCESSEXCELFILEARGS", Class.forName("OracleObjects.ProcessExcelFileArgs"));
String call = "{ ? = call NEUTRINO_META.PKG_EXCEL.PROCESS_FILE_INTERNAL(?, ?) }";
cstmt = (OracleCallableStatement)connection.prepareCall(call);
cstmt.setQueryTimeout(1800);
cstmt.registerOutParameter(1, OracleTypes.STRUCT, "NEUTRINO_META.PROCESSEXCELFILEARGS");
cstmt.setString(2, filename);
cstmt.setDouble(3, eventType);
cstmt.execute();
ProcessExcelFileArgs args = (ProcessExcelFileArgs)cstmt.getObject(1, rtn);
}
catch (SQLException e){
WriteEventToDb(e.getMessage());
}
finally{
if (cstmt != null){
cstmt.close();
}
}
This worked by my ProcessExcelFileArgs class having implemented java.sql.SQLData, and by adding to Oracletype to java class mapping to the connection type map.
这通过我的 ProcessExcelFileArgs 类实现了 java.sql.SQLData，并通过添加到 Oracletype 到 java 类映射到连接类型映射来实现。
回答by Dmitry Nikiforov
You case use oracle.sql.STRUCT class. Easiest example:
你的情况下使用 oracle.sql.STRUCT 类。最简单的例子：
In Oracle:
在甲骨文中：
create type type_dummy is object (
id int,
name varchar2(10)
)
/
create or replace function get_type_dummy
return type_dummy
is
begin
return type_dummy(1,'ABCDe');
end;
/
In Java:
在 Java 中：
class TypeDummy {
public Long id;
public String name;
}
try {
DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ods.fors.ru:1521:test","odh","odh");
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("{ ? = call get_type_dummy }"); ;
cstmt.registerOutParameter(1, OracleTypes.JAVA_STRUCT, "TYPE_DUMMY");
cstmt.execute();
oracle.sql.STRUCT td = (oracle.sql.STRUCT)cstmt.getObject(1);
Object[] x = td.getAttributes();
TypeDummy ntd = new TypeDummy();
ntd.id = ((BigDecimal)x[0]).longValue();
ntd.name = (String)x[1];
System.out.println(ntd.id);
System.out.println(ntd.name);
cstmt.close();
}
...
Output:
输出：
1
ABCDe
回答by zzp
Here, I can't see any particular need to get back table data from a package function, using a CallableStatement object. Instead, we can use a normal sql query to wrap up the results and fetch them into a normal java resultset. Doing so, we avoid trying to find a painful solution adjusting the package function (which uses package-level types), leaving the function and package types intact, as well as continue benefiting from using the unknowing internal functionality and speed of the pipelined plsql function. Panos Zafiropoulos.
在这里，我看不到使用 CallableStatement 对象从包函数取回表数据的任何特殊需要。相反，我们可以使用普通的 sql 查询来包装结果并将它们提取到普通的 java 结果集中。这样做，我们避免试图找到一个痛苦的解决方案来调整包函数（使用包级类型），保持函数和包类型不变，并继续受益于使用流水线 plsql 函数的未知内部功能和速度. 帕诺斯·扎菲罗普洛斯。
回答by Jose Boretto Blengino
You have an Oracle type is RECORD:
你有一个 Oracle 类型是RECORD：
TYPE my_type IS RECORD (
foo VARCHAR2 (12)
);
In oracle your function return a PIPELINE:
在 oracle 中，您的函数返回一个PIPELINE：
FUNCTION my_funtion (
foo VARCHAR2,
bar VARCHAR2
)
RETURN my_type PIPELINED
In java, you can use the table funtion, and then Retrieve values from the ResultSet
在java中，你可以使用表函数，然后从ResultSet中检索值
select * from table (URGP.PKG_AG_SIR_MEW.RECUPERARPERSONAPORNRODOC(?,?))
