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).
- How to identify table fragmentation and remove it ? - December 18, 2018
- How to non intrusively find index rebuild or shrink candidates ? - November 23, 2018
- Simple Oracle Document Access (SODA) installation and usage - November 1, 2018
- Oracle REST Data Services (ORDS) installation and usage - October 8, 2018
- Application Continuity (AC) for Java – JDBC HA – part 6 - September 13, 2018
- Transaction Guard (TG) for Java – JDBC HA – part 5 - August 27, 2018