帮看一个jdbc获取oracle存储过程自定义类型的有关问题

SQL code

--初始化数据
create table student
(
    id int,
    name varchar(20),
    age int,
    sex varchar(2),
    birth date
);
insert into student values(1,'张三',23,'男',sysdate);
insert into student values(2,'李四',24,'男',sysdate);
insert into student values(3,'王五',25,'男',sysdate);



--创建包
create or replace package TestPackage as
    type TestRecord1 is record(id int,name varchar(20));
    type TestTable1 is table of TestRecord1 index by binary_integer;
    procedure TestProcedure1(result out TestTable1);
end TestPackage;


--建存储过程
create or replace package body TestPackage as
       procedure TestProcedure1(result out TestTable1)
       as
            record1 TestRecord1;
         v_id int;
         v_name varchar(20);
         cursor1 sys_refcursor;
       begin
               open cursor1 for select id,name from student;
            loop
                fetch cursor1 into v_id,v_name;
                if cursor1%notfound then
                       exit;
                else
                    record1.id := v_id;
                    record1.name := v_name;
                    result(result.count) := record1;
                end if;
            end loop;
       end TestProcedure1;
end TestPackage;


--调用存储过程正确
declare 
        table1 TestPackage.TestTable1;
        i int := 0;
begin
     TestPackage.TestProcedure1(table1);
     while i < table1.count loop        
            dbms_output.put_line(table1(i).id);
            i := i + 1;
     end loop;
end;

Java code

//这个就有错了
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test","xxx","yyy");
OracleCallableStatement cs = (OracleCallableStatement) con.prepareCall("{call TestPackage.TestProcedure1(?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.ARRAY,"TestPackage.testTable1".toUpperCase());
cs.execute();
ARRAY array = cs.getARRAY(1);
Datum[] data = array.getOracleArray();
for(Datum d:data)
{
    STRUCT struct = (STRUCT)d;  
    Datum[] stringValue = struct.getOracleAttributes();  
    Object[] noStringValue = struct.getAttributes();  
            
    System.out.print((BigDecimal)noStringValue[0]);//id
    System.out.println(new String(stringValue[1].getBytes()));//name
}
cs.close();
con.close();

------解决方案--------------------------------------------------------

Java code

//java调用oracle存储过程时数组参数
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor; 
 

 

 public ReturnType BatchAddTerminals(TerminalUsersType[] tus) {
  ReturnType ret = new ReturnType();
  ret.setCode(0);
  //Connection conn = C3P0Pool.getInstance().getConn();
  GeneralConnect gc = new GeneralConnect();
  Connection conn = gc.getConnection();
  oracle.jdbc.OracleConnection oracleConn = (oracle.jdbc.OracleConnection) conn;
  CallableStatement stat = null;
  if (oracleConn != null) {
   try {
    StructDescriptor sd = new StructDescriptor(
      "TYPE_TERMINAL_USER", oracleConn);
    STRUCT[] results = new STRUCT[tus.length];
    for (int i = 0; i < tus.length; i++) {
     Object[] o = new Object[7];
     o[0] = new Integer(tus[i].getEpid());
     o[1] = new String(tus[i].getUserName());
     o[2] = new String(tus[i].getMobile());
     o[3] = new String(tus[i].getTermCode());
     o[4] = new String(tus[i].getUserDesc());
     o[5] = new String(tus[i].getSmsMobile());
     o[6] = new String(tus[i].getPassword());
     results[i] = new STRUCT(sd, oracleConn, o);
    }
    String sql = "{call PACK_USER_TERMINALS.BatchAddTerminals(?,?,?)}";
    stat = oracleConn.prepareCall(sql);
    stat.registerOutParameter(2, java.sql.Types.INTEGER);
    stat.registerOutParameter(3, java.sql.Types.VARCHAR);
    oracle.sql.ArrayDescriptor des_TABLE_TERMINAL_USER = oracle.sql.ArrayDescriptor
      .createDescriptor("TABLE_TERMINAL_USER", oracleConn);
    oracle.sql.ARRAY ora_array1 = new oracle.sql.ARRAY(
      des_TABLE_TERMINAL_USER, oracleConn, results);
    stat.setArray(1, ora_array1);

    stat.execute();
    ret.setCode(stat.getInt(2));
    ret.setDesc(stat.getString(3));

   } catch (java.sql.SQLException ex) {
    Log.getInstance().outLog("访问数据库异常" + ex.getMessage());
    ret.setDesc("访问数据库异常" + ex.getMessage());
   } catch (Exception ex) {
    Log.getInstance().outLog("其他异常" + ex.getMessage());
    ret.setDesc("其他异常" + ex.getMessage());
   } finally {
    CloseDB.attemptClose(stat);
    CloseDB.attemptClose(conn);
   }
  }
  return ret;
 }

 

ArrayDescriptor.createDescriptor(type, conn);schema级别对象类型是可以的只是单独的名字，默认前面是schema,但是不能加package,应该是oracle jdbc里面代码的问题解决的办法只有对package.type建立一个同义词了（尝试下同义词）要么你就用schema级别的对象(自定义类型放在包外定义)，jdbc这点不是很好这是一个长久以来的缺陷To my knowledge, the Oracle JDBC driver does not support using the ArrayDescriptor for array data types (varray or nested table) that are defined inside of a package. The same is true for StructDescriptor as well. If you want to use array and object data types, you must define them outside of a package. Then you'll be able to use the descriptors in your JDBC programs.As far as I know, you can only create an "ArrayDescriptor" and a "StructDescriptor" for database types.In other words, types that were created using the CREATE TYPE (DDL) statement.You cannot create an "ArrayDescriptor" or a "StructDescriptor" for types created in PL/SQL packages.