Win a copy of Kotlin for Android App Development this week in the Kotlin forum!
  • 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
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

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...
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!