To return objects like collections in function we use table function. But with regular table function there is a limitation that collection needs to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for 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, saving memory and allowing subsequent processing to start before all the rows are generated. Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection.
CREATE TYPE num_square_tab AS OBJECT (
CREATE TYPE obj IS TABLE OF num_square_tab;
— Build a pipelined table function.
CREATE OR REPLACE FUNCTION table_square(range IN NUMBER) RETURN OBJ PIPELINED AS
FOR i IN 1 .. range LOOP
PIPE ROW(num_square_tab (i,i*i));
— Test it.
ORDER BY id ;
Once you start working with large warehousing ETL operations the performance improvements can be massive, allowing data loads from external tables via table functions directly into the warehouse tables, rather than loading via a staging area. Another advantage of using pipelining is , suppose you query :
Select * from table(TABLE_SQUARE(5))
Then function will execute 3 rows only and it will terminate with exception NO_DATA_NEEDED. Now it’s up to you, that you want to handle it or not. But important point, function will run for 3 rows only.