Printable View
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.
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
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.
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
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;