Create a pipelined function

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

Syntax

The syntax of a pipelined 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 a pipelined function.

• If a pipelined function runs a DML statement, use PRAGMA AUTONOMOUS_TRANSACTION; to make it autonomous. After that, each instance of the function creates an independent transaction during parallel execution.

• If you call a pipelined 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 pipelined function has one or more cursor variable parameters. For more information about cursor variables, see Cursor variables.

• In a pipelined 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 point to a RETURN statement that returns control to the caller. However, in a pipelined 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 pipelined 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 descriptions, data instances, and data instance sets 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