Accessing REFCURSOR in another PL/SQL program

[gopi]

Typically a REFCURSOR is read by java and the set of data is processed within java. In our case we want to use that same REFCURSOR in another PL/SQL procedure.

How can we assign a REFCURSOR that resulted from package-1 to a cursor(expicit or implied) in a second package-2 ?

I hope i make sense.
thanks for your help.

[slash]

create or replace package pk1 as
type t1 is ref cursor;
procedure p1( x out t1);
end pk1;
/

create or replace package body pk1 as

procedure p1 (x out t1) as

begin

open x for select * from emp;

end p1;

end pk1;

set serverout on
declare
a emp%rowtype;
c pk1.t1;

begin
pk1.p1(c);
loop
fetch c into a;
exit when c%NOTFOUND;
dbms_output.put_line( a.ename||' '||a.sal||' '||a.empno);
end loop;

end;
/

Cheers
Slash

[gopi]

In your refcursor you demonstrate:
open x for select * from emp;

what happens if we had a refcursor such as:
open x for select .... from emp, dept, address where ....;

how do we define:
a emp%rowtype; ????

Thank you for your time and help.

[slash]

Hi,
You can declare local variables or a record type.

create or replace package pk1 as
type t1 is ref cursor;

procedure p1( x out t1);

type r is record
(
vn_empno emp.empno%type,
vs_ename emp.ename%type,
vn_sal emp.sal%type
);

end pk1;
/

create or replace package body pk1 as

procedure p1 (x out t1) as

begin

open x for select empno,ename,sal from emp;

end p1;

end pk1;

declare
type s is ref cursor;
c s;
r1 pk1.r;
begin
pk1.p1(c);
loop
fetch c into r1;
exit when c%NOTFOUND;
dbms_output.put_line( r1.vs_ename||' '||r1.vn_sal||' '||r1.vn_empno);
end loop;

end;

Cheers
Slash

[gopi]

Thanks a lot - it worked! You might wonder why we do this? It's becuase we want to reuse a ref cursor that has exised in a proc.
Here was my simplified experiment:
______________________________________________________________


create or replace package frank_getrid1
AS
type cust_cursor is ref cursor;
type r is record
(
customer_nbr customer_information.customer_nbr%type,
customer_name customer_information.customer_name%type,
warehouse_nbr customer_profile.warehouse_nbr%type
);

procedure get_records ( p_cust_ref OUT cust_CURSOR );
procedure get_it;
END;


create or replace package body frank_getrid1
AS

procedure get_records (p_cust_ref OUT cust_cursor )
IS
BEGIN
OPEN p_cust_ref FOR
select a.customer_nbr, a.customer_name, b.warehouse_nbr
from customer_information a, customer_profile b
where a.customer_nbr = b.customer_nbr
and a.customer_nbr < 200;
END get_records;


procedure get_it IS
r1 frank_getrid1.r;
cust_cursor1 cust_cursor;

BEGIN
get_records(cust_cursor1);
LOOP
fetch cust_cursor1 into r1;
exit when cust_cursor1%notfound;
dbms_output.put_line(r1.customer_nbr || ' '|| r1.customer_name ||
' ' || r1.warehouse_nbr);
END LOOP;

END get_it;
END frank_getrid1;