Returning multiple rows from oracle stored procedure

[amolso]

Hi

I have got a problem. I have java program which calls a oracle stored procedure and it works fine if output is single row. We can use cursor in procedures to display output on SQL> but this does not work for Java program.

In Java program I am using PrepareCall() to execute the stored procedure but the output is still single row where as I am expecting the multiple row output. This may be happening becaues the cursor variables in stored procedures keep only last row values.

What I want is my stored procedure returns a output which can be stored into the resultset in java. Do I have to use Types for this.

If anyone can give me a simple program in stored procedure (e.g. even on SCOTT user EMP table like 'SELECT EMPNO, ENAME FROM EMP' which will return result set that can be stored into the java resultset)
I ll be very much thankful.

create or replace procedure jdbc_3 (var1 out number,var2 out varchar2) is
cursor c1 is select empno, ename from emp;
begin
for c2 in c1 loop
var1:=c2.empno;
var2:=c2.ename;
end loop;
end;
/

The above procedure called from java just invokes last record's empno and name. Can anyone help me out in this regard

Amol New to dba world, so please ......

 

[Kenrae]

You can have your procedure to return only one row, and call it multiple times.
The simplest aproach would be to have a package with a cursor variable in it, and three functions. One for opening the cursor, one for returning a row and advancing the cursor, and one for closing it.

 

[amolso]

Hi Ken

Thanks . I got the answer for my problem. What you have said is true but there is no need for 3 functions. This can be done using ref cursor.

Please go thru

http://govt.oracle.com/~tkyte/ResultSets/index.html

Amol
New to dba world, so please ......