Dienstag, 20. Januar 2009

pipelined function vs. dbms_output

I don't like dbms_output.
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:17
and after 15 sec.
20-JAN-2009 14:24:32
It might not be a big advantage for anyone, just a thing I like.
Kommentar veröffentlichen