• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

registerOutParameter Problem when the type is ARRAY

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Get meta with me! What pursues us is our own obsessions! But not this tiny ad:
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic