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.