Oracle Pipelined Functions

Let me give you a typical scenario which we encounter everday.

Say we have a table called employee and the fields are (or rather the most significant fields for our discussion) are

ename and dno .

SQL> select substr(ename,1,25),dno from employee;

SUBSTR(ENAME,1,25)               DNO

------------------------- ----------

JACK                              10

JILL                              10

MASON                             20

MARK                              20

you are being asked to write a small pl/sql code to return the enames of a particular department, the normal way we would

do is to write a procedure like

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)

/

CREATE or replace procedure p1(dno_p IN number,ename_p OUT vcarray)

IS

CURSOR c1 is select ename from employee where dno=dno_p;

i number:=1;

BEGIN

ename_p := vcarray();

for c2 in c1

loop

 ename_p.extend;

 ename_p(ename_p.count) :=c2.ename ;

 i:=i+1;

end loop;

END;

/

To call the above procedure , we are forced to write a pl/sql block like

SQL> declare

  2  ename vcarray ;

  3  begin

  4  p1(10,ename);

  5  for i in 1..ename.COUNT

  6  LOOP

  7  dbms_output.put_line(ename(i));

  8  end loop;

  9  end;

 10  /

JACK

JILL

PL/SQL procedure successfully completed.

So in the above example we are forced to write a pl/sql block to read from the procedure.

The below example uses pipelined functions which will make it possibel for the same

job to be done via a function (eventhough we think of function as only returning

a single value) and the icing on the cake is the function can be referenced as a simple select

from sqlplus prompt itself....

CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(500)

/

CREATE OR REPLACE FUNCTION get_employees (dno_p IN number)

RETURN vcarray

PIPELINED

AS

CURSOR c1 is select ename from employee where dno=dno_p;

BEGIN

FOR c2 in c1

LOOP

   pipe row (c2.ename);

END LOOP;

RETURN;

END;

/

SQL> select * from TABLE (get_employees(10) );

COLUMN_VALUE

----------------------------------------------------------------------------------------------------

JACK

JILL