pipelined function dynamic sql

Pipelined function удобнее создавать в пакете.

select * from TABLE(PIPELINED_TEST.PIPE_LINED);

create or replace package PIPELINED_TEST is

TYPE ttt_type IS RECORD(
name0 varchar2(200),
name1 varchar2(200),
name2 varchar2(200),
name3 varchar2(200),
name4 varchar2(200),
name5 varchar2(200));

TYPE ttt_tab IS TABLE OF ttt_type;

function PIPE_LINED return ttt_tab
PIPELINED;

end PIPELINED_TEST;
/
create or replace package body PIPELINED_TEST is

function PIPE_LINED return ttt_tab
PIPELINED is
buf ttt_tab;
c sys_refcursor;
sSQL varchar2(2000);
begin
sSQL := 'select ''LINK'', ''NAME'', ''PERCENT'', ''FOUND'', ''ERR'', ''DDATE'' from dual';

open c for sSQL;
loop
fetch c bulk collect
into buf;

for i in 1 .. buf.Count loop
pipe row(buf(i));
end loop;

exit when c%NotFound;
end loop;
close c;
end PIPE_LINED;

begin
null;
end PIPELINED_TEST;