File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes registerOutParameter Problem when the type is ARRAY Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "registerOutParameter Problem when the type is ARRAY" Watch "registerOutParameter Problem when the type is ARRAY" New topic
Author

registerOutParameter Problem when the type is ARRAY

Felix Liu
Greenhorn

Joined: May 12, 2004
Posts: 3
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

Joined: Oct 11, 2000
Posts: 1121

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

Joined: May 12, 2004
Posts: 3
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

Joined: May 12, 2004
Posts: 3
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...
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: registerOutParameter Problem when the type is ARRAY
 
Similar Threads
Calling stored procedure problems
Fetching Data from PL-SQL Table as OUT parameter in Java
Fetching Data from PL-SQL Table as OUT parameter in Java
Stored procedure which returns array
java.sql.SQLException: invalid name pattern