Create a pipeline function

A pipeline function must be a standalone function or a packaged function. This topic describes how to create a pipeline function.

Syntax

The syntax of a pipeline function is as follows. For more information about the complete syntax of the CREATE FUNCTION statement, see CREATE FUNCTION. The syntax of the pipelined_clause is as follows:

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

The syntax of the PIPE ROW clause is as follows:

PIPE ROW ( row ) ;

The syntax of the parallel_enable_clause is as follows:

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

The syntax of the return_statement is as follows:

RETURN [ expression ] ;

Syntax

• For a standalone function, specify the PIPELINED option in the CREATE FUNCTION statement. For a packaged 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 parallel execution of the pipeline function.

• If a pipeline function runs DML statements, use PRAGMA AUTONOMOUS_TRANSACTION; to make it autonomous. After this statement is executed, an independent transaction is created for each instance of the function during parallel execution.

• If you call a pipeline function multiple times in the same query or in separate queries, the underlying implementation is executed multiple times. If the function is deterministic, specify the DETERMINISTIC option.

• Generally, a pipeline function has one or more cursor variable parameters. For more information about cursor variables, see Cursor variables.

• In a pipeline function, use the PIPE ROW statement to return a collection element to the caller without returning control to the caller.

• Each execution path in a function must end with a RETURN statement that returns control to the caller. However, in a pipeline function, the RETURN statement does not need to return a value to the caller.

Data types

The data type of the return value of a pipeline function must be a collection type defined at the schema level or in a package. It cannot be an associative array. The elements of a collection must be SQL data types, not PL/SQL data types such as PLS_INTEGER and BOOLEAN.

You can use the SQL data types ANYTYPE and ANYDATA to dynamically encapsulate and access the type description, data instance, and data instance set of any other SQL type, including object and collection types.

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