Create a pipelined table function

A pipelined table function must be a standalone function or a package function. This topic describes how to create a pipelined table function.

Main syntaxes

For more information, see CREATE FUNCTION. The syntax of pipelined_clause is as follows:

PIPELINED
{ [ USING [schema.] implementation_type ]
| { ROW | TABLE } POLYMORPHIC [ USING [schema.] implementation_package ]
}

The syntax of PIPE ROW is as follows:

PIPE ROW ( row ) ;

The syntax of parallel_enable_clause is as follows:

PARALLEL_ENABLE
[ (PARTITION argument BY
    { ANY
    | { HASH | RANGE } (column [, column ]...) [ streaming_clause ]
    | VALUE (column)
    }
  )

The syntax of return_statement is as follows:

RETURN [ expression ] ;

Syntax description

• If the pipelined table function is a standalone function, specify the PIPELINED option in the CREATE FUNCTION statement. If the pipelined table function is a package function, specify the PIPELINED option in both the function declaration and the function definition.

• We recommend that you specify the PARALLEL_ENABLE option to enable the pipelined table function for parallel execution.

• If the pipelined table function executes DML statements, specify the PRAGMA AUTONOMOUS_TRANSACTION; option to make the function autonomous. Then each instance of the function creates a separate transaction during parallel execution.

• Calling a pipelined table function multiple times in one query or in separate queries results in multiple executions of the underlying implementation. If the function is deterministic, specify the DETERMINISTIC option.

• A pipelined table function usually has one or more cursor variables. For more information about cursor variables, see Cursor variables.

• In a pipelined table function, the PIPE ROW statement returns collection elements to the caller without returning the right of control.

• Each execution path in the function must point to a RETURN statement that returns the right of control to the caller. However, the RETURN statement does not need to return values to the caller.

Data types of return values

The return value of a pipelined table function must be a collection defined in the schema or package, and cannot be an associative array. Elements of a collection must be of SQL data types, but not data types supported only by PL, such as the PLS_INTEGER and BOOLEAN data types.

You can use SQL data types ANYTYPE and ANYDATA to dynamically encapsulate and access descriptions, data instances, and data instance sets of any other SQL data types (including objects and collections).

Examples

obclient> CREATE OR REPLACE PACKAGE pkg_pi AUTHID DEFINER AS
        TYPE numset_tbl IS TABLE OF NUMBER;
        FUNCTION fuc(n NUMBER) RETURN numset_tbl PIPELINED;
      END pkg_pi;
      /
Query OK, 0 rows affected

obclient> CREATE OR REPLACE PACKAGE BODY pkg_pi AS
        FUNCTION fuc(n NUMBER) RETURN numset_tbl PIPELINED IS
        BEGIN
          FOR i IN 1..n LOOP
            PIPE ROW(i);
          END LOOP;
          RETURN;
        END fuc;
      END pkg_pi;
      /
Query OK, 0 rows affected

obclient> SELECT * FROM TABLE(pkg_pi.fuc(6));
+--------------+
| COLUMN_VALUE |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
|            5 |
|            6 |
+--------------+
5 rows in set

obclient> SELECT * FROM TABLE(pkg_pi.fuc(3));
+--------------+
| COLUMN_VALUE |
+--------------+
|            1 |
|            2 |
|            3 |
+--------------+
2 rows in set