So I came to pipelined functions.
here a small example:
create or replace function dummy return DBMS_DEBUG_VC2COLL PIPELINED -- NOTE the pipelined keyword is begin pipe row (to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' )); dbms_lock.sleep(15); pipe row (to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' )); return; end; /Make sure you set the arraysize of SQL*PLUS (or whatever) small enough, otherwise you will get a bunch of results at once, not when they occure.
set arraysize 1
select * from table(dummy());gives
COLUMN_VALUE -------------------- 20-JAN-2009 14:24:17and after 15 sec.
20-JAN-2009 14:24:32It might not be a big advantage for anyone, just a thing I like.