I have a pl/sql parallel_enable pipelined function to write files. The select statement with the parallel hint runs fine in parallel if running separately. But the function doesn't run in parallel. Anyone knows how to fix it?
select *
from table(parallel_x(cursor (select /*+ parallel(c, 5) */
c.cid
from customers c)));
create or replace type x_t as object
(
line_count number,
session_id number
);
create or replace type x_tab as table of x_t;
function parallel_x(s in sys_refcursor) return x_tab
pipelined
parallel_enable(partition s by any) is
session_id number;
f utl_file.file_type;
line varchar2(32767);
line_count number := 0;
begin
select sid into session_id from v$mystat where rownum = 1;
file_name := 'file_' || session_id;
f := utl_file.fopen(location => 'directory',
filename => file_name,
open_mode => 'w',
max_linesize => 32767);
loop
fetch s
into line;
utl_file.put_line(file => f, buffer => line);
line_count := line_count + 1;
exit when s%notfound;
end loop;
utl_file.fclose(f);
pipe row(x_t(line_count, session_id));
return;
end;