Oracle 18c

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


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table tst (v1 varchar2(100), v2 varchar2(100))
insert all 
    into tst values ('XXX3', 'YYY1')
    into tst values ('XXX2', 'YYY2')
    into tst values ('XXX1', 'YYY3')
select * from dual
3 rows affected

create or replace package pkg_tst as
    type t_recs is table of tst%rowtype;
    function get_recstbl return t_recs;
    function get_recsppl return t_recs pipelined;
end;
/
create or replace package body pkg_tst as
    function get_recstbl return t_recs is
        ret t_recs;
    begin
        select * 
        bulk collect into ret from tst;
        return ret;
    end;
    function get_recsppl return t_recs pipelined is
    begin
        for r in (select * from tst) loop
            pipe row (r);
        end loop;
        return;
    end;
end;
/
select * from table (pkg_tst.get_recstbl)
ORA-00902: invalid datatype

select * from table (pkg_tst.get_recsppl)


V1
V2


XXX3
YYY1

XXX2
YYY2

XXX1
YYY3


declare
    recs pkg_tst.t_recs := pkg_tst.get_recstbl;
begin 
    for r in (select * from table (recs)) loop
        dbms_output.put_line(r.v1||chr(9)||r.v2);
    end loop;
end;
/
1 rows affected

dbms_output:
XXX3	YYY1
XXX2	YYY2
XXX1	YYY3