create table ref_cur_tx_test (data varchar2(100), numb number(20));
create table ref_cur_tx_test_log (src varchar2(10), datetime timestamp, data varchar2(100), numb number(20));


create or replace package body pkg_ref_cur_tx_test is

procedure log_it1 is
  pragma autonomous_transaction;
begin
  insert into ref_cur_tx_test_log (src, datetime, data, numb)
  select 'log1', systimestamp, data, numb from ref_cur_tx_test;
  commit;
end;

procedure log_trick(p_rc in sys_refcursor) is
  pragma autonomous_transaction;
  rec ref_cur_tx_test%rowtype;
begin
  loop
    fetch p_rc into rec;
    exit when p_rc%notfound;
    insert into ref_cur_tx_test_log (src, datetime, data, numb)
    values ('log2', systimestamp, rec.data, rec.numb);
  end loop;
  commit;
end;

procedure log_it2 is
  rc sys_refcursor;
begin
  open rc for select * from ref_cur_tx_test;
  log_trick(rc);
  close rc;
end;

procedure run is
begin
  execute immediate 'truncate table ref_cur_tx_test';

  for i in 1..10 loop
    insert into ref_cur_tx_test(data, numb) values (dbms_random.string('L', 20), dbms_random.value(1,100500));
  end loop;
  
  log_it1;
  log_it2;
 
  commit;
end;

END;
/

create or replace package pkg_ref_cur_tx_test is
  procedure run;
end pkg_ref_cur_tx_test;
/


begin
  pkg_ref_cur_tx_test.run;
end;
/

select * from ref_cur_tx_test;
select * from ref_cur_tx_test_log where src!='log2'; -- results are not here
select * from ref_cur_tx_test_log; -- results are here

drop table ref_cur_tx_test purge;
drop table ref_cur_tx_test_log purge;
drop package pkg_ref_cur_tx_test;

create global temporary table ref_cur_tx_test (data varchar2(100), numb number(20));