sys_refcursor

sys_refcursor can be used to pass cursors from and to a stored precedure.

The following procedure proc_ref_cursor accepts a sys_refcursor and loops on that cursor and prints out (dbms_output) what it fetches:

create or replace procedure 
  proc_ref_cursor (rc in sys_refcursor) as

  v_a number;
  v_b varchar2(10);
  
begin
  loop
    fetch rc into v_a, v_b;
    exit when rc%notfound;
    dbms_output.put_line(v_a || ' ' || v_b);
  end loop;
end;
/

Here's a table that we will use to select from:

create table table_ref_cursor (
  a number,
  b varchar2(10)
);

insert into table_ref_cursor values(1, 'one');
insert into table_ref_cursor values(2, 'two');
insert into table_ref_cursor values(3, 'three');
insert into table_ref_cursor values(4, 'four');

commit;

Here's another stored procedure (call_proc_ref_cursor) that opens a cursor (select * from table_ref_cursor) and passes that cursor to proc_ref_cursor.

create or replace procedure 
  call_proc_ref_cursor as

  v_rc sys_refcursor;

begin

  open v_rc for 
    select * from table_ref_cursor;

  proc_ref_cursor(v_rc);

  close v_rc;
end;
/

See also the keyword cursor and ref cursors in PL/SQL.

Using ref cursors with perl

See this example.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback