调用从Java返回Oracle类型的PL / SQL函数
发布时间:2020-12-15 04:18:21 所属栏目:Java 来源:网络整理
导读:我有一个PL / SQL函数,如下所示返回Oracle类型(PROCESSEXCLEFILEARGS) FUNCTION PROCESS_FILE_INTERNAL ( i_Filename VARCHAR2,i_EventType NUMBER ) RETURN PROCESSEXCELFILEARGS 我必须从Java调用此函数,我的Java方法如下所示 OracleCallableStatement cst
我有一个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类型 谢谢 解决方法
你的情况下使用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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |