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

About Post Author

Share the knowledge!
This entry was posted in Oracle and tagged . Bookmark the permalink.

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>