Oracle Pipelined Table Functions

PIPELINED functions are useful if there is a need for a data source other than a table in a select statement.PIPELINED functions will operate like a table.

Pipelined functions are simply "code you can pretend is a database table"

Pipelined functions give you the ability to : "select * from PLSQL_FUNCTION "

Steps to perform :

1.The producer function must use the PIPELINED keyword in its declaration.

2.The producer function must use an OUT parameter that is a record, corresponding to a row in the result set. 

3.Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword. 

4.The producer function must end with a RETURN statement that does not specify any return value. 

5.The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

Example:

CREATE OR REPLACE TYPE DateListTab AS TABLE OF date;

Create or replace FUNCTION TestPipelinedFunctions RETURN  DateListTab  PIPELINED IS

   fromdate date:= sysdate;

   Todate date:=sysdate+365;

begin

   for datecounter in to_number(to_char(fromdate, 'J')) .. to_number(to_char(Todate, 'J')) 

   loop

          PIPE ROW ( to_date(datecounter, 'J') );

   end loop;          

    RETURN;

end TestPipelinedFunctions;

SELECT * FROM TABLE(TestPipelinedFunctions);