create global temporary table temp_table ( 
  id number(38,0) 
) on commit preserve rows

create table data_table ( 
  id number(38,0), 
  val varchar2(10) 
)

insert into data_table  
select 1, 'a' from dual union all 
select 2, 'b' from dual union all 
select 3, 'c' from dual union all 
select 4, 'd' from dual

declare 
 
type ref_cursor is ref cursor; 
 
procedure storeToTempTable 
is 
  cmd varchar2(32767); 
  pragma autonomous_transaction; 
begin 
  cmd := q'[ 
    insert into temp_table (id) 
    select 1 from dual union all 
    select 2 from dual union all 
    select 3 from dual union all 
    select 4 from dual 
  ]'; 
  execute immediate cmd; 
  commit; 
end; 
 
procedure truncateTempTable 
is 
  pragma autonomous_transaction; 
begin 
  execute immediate 'truncate table temp_table'; 
end; 
 
function getCursor return ref_cursor 
is 
  cur      ref_cursor; 
  q  varchar2(32767); 
begin 
  truncateTempTable; 
  storeToTempTable; 
  q := 'select r.* from data_table r, temp_table t where r.id = t.id'; 
  open cur for q; 
  return cur; 
end; 
 
-------------------------------------------------------------------------------- 
 
begin -- main 
  
declare 
  cursor1 ref_cursor; 
  cursor2 ref_cursor; 
  dto data_table%rowtype; 
  i1 pls_integer := 0; 
  i2 pls_integer := 0; 
begin 
  cursor1 := getCursor; 
  loop 
    exit when i1 = 2; 
    i1 := i1 + 1; 
    fetch cursor1 into dto; 
    exit when cursor1%notfound; 
    dbms_output.put_line('i1 = ' || i1 || ', dto.id = ' || dto.id); 
  end loop; 
  dbms_output.put_line('not closing 1'); 
  --dont close cursor1; 
 
  cursor2 := getCursor; 
  loop 
    i2 := i2 + 1; 
    fetch cursor2 into dto; 
    exit when cursor2%notfound; 
    dbms_output.put_line('i2 = ' || i2 || ', dto.id = ' || dto.id); 
  end loop; 
  dbms_output.put_line('closing 2'); 
  close cursor2; 
 
  loop 
    i1 := i1 + 1; 
    fetch cursor1 into dto; 
    exit when cursor1%notfound; 
    dbms_output.put_line('i1 = ' || i1 || ', dto.id = ' || dto.id); 
  end loop; 
  dbms_output.put_line('closing 1'); 
  close cursor1; 
end; 
 
end;