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:

SELECT * FROM TABLE(table_function_name(parameter_list))

(For more information about the TABLE clause of the SELECT statement, see Oracle Database SQL Language Reference.)

A table function can take a collection of rows as input (that is, it can have an input parameter that is a nested table, varray, or cursor variable). Therefore, output from table function tf1 can be input to table function tf2, and output from tf2 can be input to table function tf3, and so on. For more information, see"Chaining Pipelined Table Functions".

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 Streams.
  Streaming eliminates intermediate staging between processes. For information about Oracle Streams, see Oracle Streams Concepts and Administration.
• Pipeline the function results, with the PIPELINED option.
  
  
  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: Creating and Invoking Pipelined Table Function

  CREATE OR REPLACE PACKAGE pkg1 AS
    TYPE numset_t IS TABLE OF NUMBER;
    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
  END pkg1;
  /
  
  CREATE PACKAGE BODY pkg1 AS
    -- FUNCTION f1 returns a collection of elements (1,2,3,... x)
    FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
    BEGIN
      FOR i IN 1..x LOOP
        PIPE ROW(i);
      END LOOP;
      RETURN;
    END f1;
  END pkg1;
  /
  
  SELECT * FROM TABLE(pkg1.f1(5));
  

  Result:

  COLUMN_VALUE
  ------------
             1
             2
             3
             4
             5
   
  5 rows selected.