Oracle Pipelined Table Functions

PIPELINED functions will operate like a table.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradation in both cases.

Overview of Table Functions:
A table function is a user-defined PL/SQL function that returns a collection of rows (a nested table or varray). You can select from this collection as if it were a database table by invoking the table function inside the TABLE clause in a SELECT statement. For example:


To improve the performance of a table function, you can:

• Enable the function for parallel execution, with the PARALLEL_ENABLE option.
• Functions enabled for parallel execution can run concurrently.
• Stream the function results directly to the next process, with Oracle .

A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)

Example 1:

We can write a PL/SQL function that will operate like a table

sql>create type array as table of number;

The PIPELINED keyword on line 4 allows this function to work as if it were a table:

create function

  gen_numbers(n in number default null)

  return array

  PIPELINED

  as

  begin

     for i in 1 .. nvl(n,999999999)

     loop

         pipe row(i);

     end loop;

     return;

  end;

/

Function created.

Suppose we needed three rows for something. We can now do that in one of two ways:

select * from TABLE(gen_numbers(3));

 COLUMN_VALUE

 ------------

           1

           2

           3

or

select * from TABLE(gen_numbers)

 where rownum <= 3;

 COLUMN_VALUE

 ------------

           1

           2

           3

Example 2:

CREATE TYPE t_tf_row AS OBJECT (

  id           NUMBER,

  description  VARCHAR2(50)

);

/

CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;

CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS

BEGIN

  FOR i IN 1 .. p_rows LOOP

    PIPE ROW(t_tf_row(i, 'Description for ' || i));   

  END LOOP;

  RETURN;

END;

/

SELECT *

FROM   TABLE(get_tab_ptf(10))

ORDER BY id DESC;