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

• pipelined function vs. dbms_output
• Pipelined Functions: Better Than DBMS_OUTPUT

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts