Skip to content

Pipelined functions

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).

References

About Post Author

Share the knowledge!

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *