67,512
社区成员
发帖
与我相关
我的任务
分享
--初始化数据
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;
//这个就有错了
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();
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.
//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;
}
Exception in thread "main" java.sql.SQLException: 无效的名称模式: TESTPACKAGE.TESTTABLE1
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:527)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:407)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1952)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:199)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:118)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:169)
at test1.test9(test1.java:139)
at test1.main(test1.java:156)
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:test", "xxx", "yyy");
oracle.jdbc.OracleConnection oracleConn = (oracle.jdbc.OracleConnection) conn;
String sql = "{call TestPackage.TestProcedure1(?)}";
CallableStatement stat = oracleConn.prepareCall(sql);
ArrayDescriptor.createDescriptor("TESTPACKAGE.TESTTABLE1", oracleConn);
Array array = stat.getArray(1);
stat.execute();
stat.close();
conn.close();
Exception in thread "main" java.sql.SQLException: 无效的名称模式: TESTPACKAGE.TESTTABLE1
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:527)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:407)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1952)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:199)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:118)
at test1.test10(test1.java:164)
at test1.main(test1.java:172)
什么错 能看看log吗
--自定义record
create or replace type TestRecord is object(id int,name varchar(20));
--自定义table
create or replace type TestTable is table of testRecord;
--存储过程返回table
CREATE OR REPLACE procedure TestProcedure(result out TestTable)
as
begin
select TestRecord(id,name) bulk collect into result from student;
end TestProcedure;
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 TestProcedure(?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.ARRAY,"testTable".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();