Pipelined Table Functions

Table functions are used to return collections that mimic tables.

They can be queried like a regular table by using the TABLE function in the FROM clause Regular table functions require collections to be complete before they are returned causing bottlenecks during large Extraction Transformation Load (ETL) operations.

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, allowing subsequent processing to start before the entire collection is fully populated.

CREATE TYPE integer_nt_type is table of integer
/
CREATE OR REPLACE FUNCTION get_tab_nos(n IN INTEGER)
RETURN integer_nt_type
IS
num_tab integer_nt_type:=integer_nt_type();
BEGIN
FOR i IN 1..n LOOP
num_tab.extend;
num_tab(num_tab.last):=i;
END LOOP;
RETURN num_tab;
END;
/
CREATE OR REPLACE FUNCTION get_nos(n in integer)
RETURN integer_nt_type PIPELINED
IS
BEGIN
FOR i IN 1..n LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
/
SELECT * FROM TABLE(get_nos(5));

PIPELINED Clause

This option tells Oracle to return the results of the function as they are processed, and not wait for a complete execution or completion of the result set. This pipelining of the result set to one row at a time has the immediate advantage of not requiring excessive memory or disk staging resources.

PIPE ROW(out_rec)
The PIPE ROW statement is the interface or mechanism to send a piped row through the PIPELINED option through to the caller of the function.