Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE 11.2.0.2.0 Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production


create table tab (id, code) as 
    select  rownum, 'code-'||to_char(rownum,'FM099') 
    from dual connect by level<=10
10 rows affected

create or replace procedure proc as
    type rowarr is table of tab%rowtype;
    rows rowarr; 
    rc sys_refcursor;
begin 
    execute immediate q'[
        create table tmptab as
        select * from tab where 1=0]';     
    execute immediate q'[
        insert into tmptab select * from tab where rownum<=3 order by id]';
    dbms_output.put_line ('inserted into tmptab '||sql%rowcount||' row(s)');     
    execute immediate q'[
        insert into tmptab values (123, 'code-123')]';
    dbms_output.put_line ('inserted into tmptab '||sql%rowcount||' row(s)');     
    
    execute immediate q'[select * from tmptab]' bulk collect into rows;    
    dbms_output.put_line ('fetched from tmptab '||rows.count||' row(s)');
    --или
    open rc for q'[select * from tmptab]';
    fetch rc bulk collect into rows;    
    dbms_output.put_line ('fetched from tmptab '||rows.count||' row(s)');
    for i in 1..rows.count loop
        dbms_output.put_line ('tmptab row('||i||')='||rows(1).id||' '||rows(i).code);
    end loop;    
    
    execute immediate q'[drop table tmptab purge]';
end proc;
/
begin 
    dbms_output.enable;
    proc; 
end;
/
1 rows affected

dbms_output:
inserted into tmptab 3 row(s)
inserted into tmptab 1 row(s)
fetched from tmptab 4 row(s)
fetched from tmptab 4 row(s)
tmptab row(1)=1 code-001
tmptab row(2)=1 code-002
tmptab row(3)=1 code-003
tmptab row(4)=1 code-123