Useful scripts of Oracle

Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data, instead of a table ,you would use a pipelined function.

Oracle Database then returns rows as they are produced by the function.

If you specify the keyword PIPELINED alone ( PIPELINED IS ...), then the PL/SQL function body should use the PIPE keyword. This keyword instructs the database to return single elements of the collection out of the function, instead of returning the whole collection as a single value.
You can specify thePIPELINED USING implementation_type clause if you want to predefine an interface containing the start, fetch, and close operations. The implementation type must implement the ODCITable interface and must exist at the time the table function is created. This clause is useful for table functions that will be implemented in external languages such as C++ and Java.
If the return type of the function is ANYDATASET, then you must also define a describe method(ODCITableDescribe)as part of the implementation type of the function.

Syntax:

CREATE OR REPLACE FUNCTION <pipelined_function_name>[(<function arguments>)]
RETURN <collection type> PIPELINED

IS
[(<variables declaration>)];

BEGIN

<.... LOOP>

[<some_actions>]

PIPE ROW (<cast to collection data_type>);

<.... END LOOP;>

RETURN;

END;

PIPELINED functions will operate like a table:

SELECT * FROM TABLE(<pipelined_function_name>[(<function arguments>)]);

Example - Generating Some Random employees from hr.employees table:

CREATE OR REPLACE TYPE o_EName AS OBJECT (
FirstName VARCHAR2(20),
LastName VARCHAR2(20)
);
/

CREATE OR REPLACE TYPE t_ENames AS TABLE OF o_EName;
/

CREATE OR REPLACE FUNCTION GetRandEmployees (pEmpCount NUMBER)
RETURN t_ENames PIPELINED
IS
CURSOR c_Emps IS

SELECT First_Name,Last_Name FROM
( SELECT E.First_Name,E.Last_Name
FROM HR.Employees E
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM <=pEmpCount;
BEGIN

FOR c IN c_Emps LOOP

PIPE ROW (o_EName(c.First_Name,c.Last_Name));

END LOOP;

RETURN;

END;
/
SELECT * FROM TABLE(GetRandEmployees(6));

Result:

FIRSTNAME	LASTNAME
Alexis	Bull
Guy	Himuro
Tayler	Fox
Mozhe	Atkinson
Jean	Fleaur
Clara	Vishney

Using PIPELINED in PL/SQL.