pipelined table function

[segmentfault]

i have a table function (pipelined) which take as an argument a cursor. for each row in the cursor i go and execute another query which produce more than 5 rows.
for example, for one row in the cursor, my query can produce 5 rows. for another row in the cursor the query can produce 7 rows. when i execute this query i bulk bind into a pl/sql table.

so basically i open a cursor and for each row in the cursor i execute another query that produce 1 or more rows. what i want to be able to do is pipeline my entired pl/sql table which might contain 1 or more row. is this possible?

i tried and it doesn't work. i don't know if table function can only pipeline one row at a time. i try to use parallel enable keyword but i don't know the # of rows return by my second query. can you please help? thanks

 

[Dragonsoft]

Yes you can,
A pipelined function only send one row at a time.
So do a inner loop of your plSql-table
and pipe each row.

Example code does not syntax:
Function PipeIT( pcCursor ...)return TYPE
begin
open pcCursor;
loop
fetch pcCursor into r;
exit when pcCursor%Notfound;

Select ... bulk collect into ....;
if plsqltable.count > 0 then
for i in plsqltable.first..plsqltable.last loop
pipe row plsqltable(i).data;
end loop;

end loop;
end PipeIT;
hmm something like this(sloppy typing)?
The returned data from the plsqltable ha to have the same rowtype as the returning type of the outer function.

Best regards
Olle


"Some thoughts has a certain sound..."