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

不关橙猫猫事的哦 2011-11-28 07:31:40

java代码调用存储过程报这个错,代码如下:
Exception in thread "main" java.sql.SQLException: 无效的名称模式: TESTPACKAGE.TESTTABLE1


--初始化数据
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();
...全文
252 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
AldisZhan 2011-11-30
  • 打赏
  • 举报
回复

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.


https://forums.oracle.com/forums/thread.jspa?threadID=843161
我嘞个去 2011-11-29
  • 打赏
  • 举报
回复

//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;
}

  • 打赏
  • 举报
回复
[Quote=引用 4 楼 gavin_ts 的回复:]

提供参考
[/Quote]
真悲剧,公司把所有的blog地址全屏蔽了,麻烦贴出来一下。。
我嘞个去 2011-11-29
  • 打赏
  • 举报
回复
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 kindazrael 的回复:]

Java code
什么错 能看看log吗
[/Quote]


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)
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 gavin_ts 的回复:]

Java code

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




public ReturnType BatchAddTerminals(TerminalUsersType[] tus) {
ReturnType ret = new ReturnT……
[/Quote]

我仿照你的代码改成这样了,还是有错啊。。。。


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)

AldisZhan 2011-11-28
  • 打赏
  • 举报
回复
什么错 能看看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();

67,512

社区成员

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器,保护我们的业务核心组件(中间件),以延续它的生命力,而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧