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 !!
Notice the arraysize value before selecting the table(function).
- Enhanced password strength in MySQL with validate_password plugin - April 12, 2017
- Automatic extended statistics to improve performance - April 6, 2017
- OGG-03517 when moving to Unicode from any other character set - March 10, 2017
- Audit creation and deletion of users for SOX evidences - February 17, 2017
- PL/SQL performance tuning with bulk SQL/binding and parallelism - February 14, 2017
- GoldenGate 12c (12.2) installation and configuration - January 16, 2017