Create and use reference cursor : ref cursor « Cursor « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Cursor
3. ref cursor

SQL>
SQL> create table big_table as select * from all_objects;

Table created.

SQL>
SQL> declare
  2      c sys_refcursor;
  3      type array is table of big_table%rowtype index by binary_integer;
  4      l_rec array;
  5  begin
  6      for i in 1 .. 5
  7      loop
  8          open c for select * from big_table ORDER BY OWNER;
  9          fetch c bulk collect into l_rec limit i*1000;
 10          close c;
 11      end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop table big_table;

Table dropped.

SQL>