Search notes:

Oracle: SYS_REFCURSOR

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

insert into tq84_ref_cursor values (5, 'five' );
insert into tq84_ref_cursor values (4, 'four' );
insert into tq84_ref_cursor values (8, 'eight');

declare

  stmt   varchar2(3999);
  cur    sys_refcursor;

  a      number;
  b      varchar2(10);

begin

  stmt := 'select a,b from tq84_ref_cursor';

  open cur for stmt;

  loop
    fetch cur into a,b;
    exit when cur%notfound;
    dbms_output.put_line('a: ' || a || ', b: ' || b);
  end loop;

  close cur;


end;
/


drop table tq84_ref_cursor;
Github repository Oracle-Patterns, path: /SQL/cursors/ref_cursor_from_string.plsql

Passing ref cursors to procedures

sys_refcursor can be used to pass cursors from and to a stored precedures
In the following example, the procedure proc_ref_cursor accepts a sys_refcursor and loops over that cursor and prints (dbms_output) what it fetches:
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;
And this is the definition of the procedure.
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 the stored procedure (call_proc_ref_cursor) that opens a cursor for a select statement (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;
/
Execute call_proc_ref_cursor:
begin
   call_proc_ref_cursor;
end;
/

See also

sys_refcursor is defined in $ORACLE_HOME/rdbms/admin/stdspec.sql.
Ref cursors in PL/SQL

Index