ORACALE PIPELINED CONCEPTS

These are used to return more than one value from a table.

Ex:-

Step 1:
-----------
CREATE TABLE EMP(DEPTNO INT,DNAME VARCHAR2(10),LOC VARCHAR2(10));

Step 2:
-----------
Insert sample data.

Step 3:
-----------
Create an object for the row type casting.
CREATE OR REPLACE TYPE dept_row
AS OBJECT
(
  deptno INT,
dname VARCHAR2(20),
  loc VARCHAR2(20)
);

Step 4:
-----------
Create a Return Type for the pipelined function.
CREATE OR REPLACE TYPE dept_table_type
AS TABLE OF dept_row;

Step 5:
-----------
CREATE OR REPLACE FUNCTION dept_pipe_function RETURN dept_table_type PIPELINED IS
BEGIN
FOR rec in (select * from dept)
LOOP
PIPE ROW (dept_row(rec.deptno,rec.dname,rec.loc));
END LOOP;
RETURN;
END;


Step 6:
----------
SQL> select * from table(dept_pipe_function);




SQL> select *from table(dept_pipe_function);

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON