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