• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
Bartenders:
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

registerOutParameter Problem when the type is ARRAY  RSS feed

 
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.
 
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...
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!