调用从Java返回Oracle类型的PLSQL函数(Call PLSQL function returning Oracle type from Java)

调用从Java返回Oracle类型的PL / SQL函数(Call PL/SQL function returning Oracle type from Java)

我有一个PL / SQL函数如下，它返回一个Oracle类型（PROCESSEXCLEFILEARGS）

FUNCTION PROCESS_FILE_INTERNAL ( i_Filename VARCHAR2, i_EventType NUMBER ) RETURN PROCESSEXCELFILEARGS

我必须从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正在实现SQLData，并且正确实现了readSQl（..）和writeSQL（..）方法来读取和写入类型字段。

但是当我运行这个java方法时，我得到一个带有'无效列类型：1111'消息的SQLException

任何人都可以让我知道我采取的方法是否有任何错误，或者是否有任何其他方法来检索返回oracle类型作为java对象。

编辑：

create or replace TYPE PROCESSEXCELFILEARGS FORCE AS OBJECT ( FullFilePath VARCHAR2(700), Filename VARCHAR2(200), Graph TYPEGRAPHDATA )

请注意，TYPEGRAPHDATA是架构级别的另一个用户定义的Oracle类型

谢谢

I have a PL/SQL function as follows which returns an Oracle type (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

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.

But when i run this java method I get a SQLException with message 'Invalid column type: 1111'

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.

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

thanks

最满意答案

你的情况下使用oracle.sql.STRUCT类。 最简单的例子：

在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; /

在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(); } ...

输出：

1 ABCDe

ok I managed to get the returned oracle type as a java object by using the following code.

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.

调用从Java返回Oracle类型的PL / SQL函数(Call PL/SQL function returning Oracle type from Java)

我有一个PL / SQL函数如下，它返回一个Oracle类型（PROCESSEXCLEFILEARGS）

FUNCTION PROCESS_FILE_INTERNAL ( i_Filename VARCHAR2, i_EventType NUMBER ) RETURN PROCESSEXCELFILEARGS

我必须从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正在实现SQLData，并且正确实现了readSQl（..）和writeSQL（..）方法来读取和写入类型字段。

但是当我运行这个java方法时，我得到一个带有'无效列类型：1111'消息的SQLException

任何人都可以让我知道我采取的方法是否有任何错误，或者是否有任何其他方法来检索返回oracle类型作为java对象。

编辑：

create or replace TYPE PROCESSEXCELFILEARGS FORCE AS OBJECT ( FullFilePath VARCHAR2(700), Filename VARCHAR2(200), Graph TYPEGRAPHDATA )

请注意，TYPEGRAPHDATA是架构级别的另一个用户定义的Oracle类型

谢谢

I have a PL/SQL function as follows which returns an Oracle type (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

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.

But when i run this java method I get a SQLException with message 'Invalid column type: 1111'

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.

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

thanks

最满意答案

你的情况下使用oracle.sql.STRUCT类。 最简单的例子：

在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; /

在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(); } ...

输出：

1 ABCDe

ok I managed to get the returned oracle type as a java object by using the following code.

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.