帮看一个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.