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).
- 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
- Fast Connection Failover (FCF) – JDBC HA – part 4 - August 7, 2018
- Fast Application Notification (FAN) – JDBC HA – part 3 - July 27, 2018
- Transparent Application Failover (TAF) – JDBC HA – part 2 - July 16, 2018