Pipelined functions

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

Yannick Jaquier on LinkedinYannick Jaquier on RssYannick Jaquier on Twitter
Yannick Jaquier
Find more about me on social media.
This entry was posted in Oracle and tagged . Bookmark the permalink.

2 thoughts on “Pipelined functions

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>