create table testq(x number);

insert into testq values (1);
insert into testq values (2);
insert into testq values (3);
insert into testq values (4);

commit;

create or replace procedure p_testq
is

i number:=1;

type t_trc is ref cursor;
trc t_trc;

v_sql varchar2(2000);

-- record to which data goes into
type t_prec is record(x testq.x %type);
prec t_prec;


-- plsql table to store data
type t_frec is table of t_prec index by binary_integer;
frec t_frec;

-- flow of data, is from v_sql --> plsql record --> plsql table

begin
  v_sql := 'select x from testq order by x';
  
  
  open trc for v_sql; -- SQL STMT TO ref cursor
  
   LOOP
                    FETCH trc INTO prec ; -- ref cursor to plsql record
                exit when trc%notfound;
  
     frec(i).x := prec.x; -- plsql record to plsql table
     dbms_output.put_line('frec(i).x: '||frec(i).x);
  END LOOP;

end;