• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

registerOutParameter Problem when the type is ARRAY

 
Felix Liu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, friends,
I meet a problem when I call a SP which return is ARRAY.
Here is the Stored Procedure SQL:
----------------------------------
CREATE OR REPLACE package eaitest is
TYPE SomeRecord IS RECORD (
orders_oid varchar2(100)
);
type all_user is table of varchar2(60)
index by binary_integer;

PROCEDURE EAITest5(
pOid out all_user)
IS
cursor c_orders is select * from orders order by oid;
nCount number;
pDesc varchar2(20);
BEGIN
nCount := 1;
for r in c_orders loop
pOid(nCount) := r.oid;
-- pDesc(nCount) := r.description;
nCount := nCount + 1;
end loop;
pDesc := 'desc';
END;
----------------------------------
My Java Code is here:
----------------------------------
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection dbconn = DriverManager.getConnection("...","...","...");
CallableStatement stmt = dbconn.prepareCall("{call EAITEST.EAITest5(?)}");
stmt.registerOutParameter(1, Types.ARRAY, "EAITEST.all_user");
stmt.execute();
----------------------------------
When I run my code, I got exception: Can't find EAITEST.all_user.
I don't why. Any one can help me ? Thanks a lot.
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Liu,
Allow me to suggest that you return a "ref cursor" instead. Here is a good example of how to do that.
However, if you insist on returning an array, then perhaps this other discussion (from the Ask Tom Web site): return resultset as an array from function may be helpful.
Also (for your information), there are code samples at Oracle's OTN Web site.
Good Luck,
Avi.
 
Felix Liu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,Avi
Thanks for your reply.
But because the stored procedure has a legacy program, I can't change it. Also, it work on Oracle 7. So, Could you tell me if I call such stored procedure, how to set in java program? Thanks.
 
Felix Liu
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, All,
I found the answer.
http://dbis.informatik.uni-freiburg.de/doc901/java.901/a90211/oci_func.htm#1017512
And test on Oracle 9i. It is OK.

But my system is Oracle V7.3.4, which does not support this function.
So pity...
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic