Java调用带有记录类型出参的存储过程

目的：

当过程包中定义了 Type( record ), 并作为包内存储过程的出参，如何通过Java进行存储过程调用？查看了网上的案例，没有找到最终答案。本次测试中，此问题仍未解决。先记录下来，以持续跟踪。

实验过程：

存储过程包：

create or replace package pkg_test is  type TYPEINFO is record(    id    number,    name  varchar2(10)  );  prcedure test(    i_sno   in pls_integer,     o_inf  out TYPEINFO,    o_flag out varchar2,    o_msg  out varchar2  );end pkg_test;/create or replace package body pkg_test is  procedure test(    i_sno   in pls_integer,     o_inf  out TYPEINFO,    o_flag out varchar2,    o_msg  out varchar2  ) is  begin    select 111 id, 'James' name into o_inf from dual;    o_flag := '1';    o_msg := 'ok!';  end test;begin  null;end pkg_test;

Java代码：

import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import oracle.jdbc.OracleTypes;import oracle.jdbc.driver.OracleCallableStatement;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor;import oracle.sql.STRUCT;import oracle.sql.StructDescriptor;import org.junit.Before;import org.junit.Test;public class TypeTest {    String driver = "oracle.jdbc.driver.OracleDriver";      String strUrl = "jdbc:oracle:thin:@10.0.0.1:1521:orcl";      Statement stmt = null;      ResultSet rs = null;      Connection conn = null;      //CallableStatement cstmt = null;      OracleCallableStatement cs = null;      @Before      public void init() {          try {              Class.forName(driver);              conn = DriverManager.getConnection(strUrl, "dbusr", "****");          } catch (Exception e) {              e.printStackTrace();          }      }      /**      * 返回 Record类型的包内存储过程，测试失败     */      @Test      public void procOutTypeOfRecord() {          try {            System.out.println("start");            ArrayDescriptor ad = ArrayDescriptor.createDescriptor("PKG_TEST.TYPEINFO", conn);            System.out.println("ad = "+ad.toString());            cs = (OracleCallableStatement)conn.prepareCall("{call pkg_test.test(?,?,?,?)}");            cs.setInt(1, 50);              cs.registerOutParameter(2, OracleTypes.ARRAY);            cs.registerOutParameter(3, OracleTypes.VARCHAR);            cs.registerOutParameter(4, OracleTypes.VARCHAR);            cs.execute();            System.out.println("executed");            ARRAY t1 = cs.getARRAY(2);            String fg = cs.getString(3);            System.out.println("out flag=" + fg);        } catch (SQLException ex2) {              ex2.printStackTrace();          } catch (Exception ex2) {              ex2.printStackTrace();          } finally {              try {                  if (rs != null) {                    rs.close();                }            } catch(SQLException e){}            try{                if (cs != null) {                      cs.close();                  }            } catch(SQLException e){}            try{                if (conn != null) {                      conn.close();                  }            } catch(SQLException e){}        }      }    /*测试脚本*/    public static void main(String[] args){        TypeTest tt = new TypeTest();        tt.init();        tt.procOutTypeOfRecord(); //Failed, java.sql.SQLException: 无效的名称模式: PKG_TEST.TYPEINFO    }}

报错信息：java.sql.SQLException: 无效的名称模式，应该是无法识别在package中定义的record type。
如果将此type调整为 ref cursor，则可以通过OracleTypes.CURSOR来解决问题，java代码见下：

    /**      * 返回 Cursor类型的包内存储过程，测试成功     */      @Test      public void procOutTypeOfCursor() {          try {            System.out.println("start");            cs = (OracleCallableStatement)conn.prepareCall("{call pkg_test.test(?,?,?,?)}");            cs.setInt(1, 0);            cs.registerOutParameter(2, OracleTypes.CURSOR);            cs.registerOutParameter(3, OracleTypes.VARCHAR);            cs.registerOutParameter(4, OracleTypes.VARCHAR);            cs.execute();            System.out.println("executed");            //STRUCT t1 = cs.getSTRUCT(2);            String fg = cs.getString(3);            String mg = cs.getString(4);            System.out.println("out flag=" + fg+"; msg="+mg);            if ("1".equals(fg)){                rs = (ResultSet)cs.getObject(2);                while (rs.next()){                    System.out.println(rs.getString("ATYPE")+" "+rs.getString("ID"));                }            }        } catch (SQLException ex2) {              ex2.printStackTrace();          } catch (Exception ex2) {              ex2.printStackTrace();          } finally {              try {                  if (rs != null) {                    rs.close();                }            } catch(SQLException e){}            try{                if (cs != null) {                      cs.close();                  }            } catch(SQLException e){}            try{                if (conn != null) {                      conn.close();                  }            } catch(SQLException e){}        }      }

结论：

暂无

后记：

问题仍在分析中，欢迎大家提出建议。