Pipelined functions
Table of contents
Preamble
Have you ever wanted to display in PL/SQL before procedure complete ? As you know doing this using DBMS_OUTPUT.PUT_LINE does not work, I even thought it was simply not possible at all until I discovered the examples of references section…
Putting it in action is not really complex but it is changing a bit the way you will develop your PL/SQL blocks. Notice it is similar to what Oracle is doing in DBMS_XPLAN package.
Pipelined functions creation
Let first create our test package which contains only one function, creating a package allow you to embed an output type in it:
create or replace package pipe_test
as
type output is table of varchar2(50);
FUNCTION function1(interval int default 10) RETURN output pipelined;
end;
/
show error
create or replace package body pipe_test
as
function function1(interval int default 10) RETURN output pipelined
is
date1 varchar2(19);
begin
loop
date1:=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
pipe row(date1);
pipe row(' ');
dbms_lock.sleep(interval);
end loop;
return;
end;
end pipe_test;
/
show err
To use it, similarly to DBMS_XPLAN, use something like:
SQL> set lines 50 pages 0 arraysize 1 SQL> select * from table(pipe_test.function1(10)); 2012-05-21 17:41:21 2012-05-21 17:41:31 . . .
And the magic is there !!
Remark:
Notice the arraysize value before selecting the table(function).






Comments