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){}

        }  
    }

结论：

暂无

后记：

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