Pipelined Table functions

A pipelined table function will come up with a single row for the result set and pipe it out of the function when it’s available.

Since the function gets called from SQL the SQL engine needs to be aware of the result it produces. So I need to create types that define a row of the result. And also the function returns more than one row so i need a collection to store it. In this tutorial i prefer nested table , Associative array will also be used.

Syntax 

FUNCTION function_name RETURN collection PIPELINED
IS

declaration section goes here

BEGIN

execution or any processing goes here
PIPE ROW(collection variable name); --for pipelining of values to send the processed rows

RETURN;  --to return the control

END;

EXAMPLE

1. First I create a type for the records I return from my function:

CREATE OR REPLACE TYPE OBJ_VAL IS OBJECT(COLIUMN_NAME_IN VARCHAR2(100), DATA_TYPE_IN VARCHAR2(100));

2. Then I created a nested table of object type to hold the records 

    that function will return.

CREATE OR REPLACE TYPE NEST_OBJ_VAL IS TABLE OF OBJ_VAL;

3. Next i will create the PIPELINED TABLE function 

CREATE OR REPLACE FUNCTION PRINT_COLS(TABLE_NAMES VARCHAR2,

                                      OWNER_NAME  VARCHAR2 DEFAULT NULL)

  RETURN NEST_OBJ_VAL

  PIPELINED IS

  CURSOR C1 IS

    SELECT COLUMN_NAME, DATA_TYPE

      FROM ALL_TAB_COLUMNS

     WHERE TABLE_NAME = upper(TABLE_NAMES)

       AND (OWNER = upper(OWNER_NAME) OR OWNER IS NULL);

  TEMP_C1      C1%ROWTYPE;

  TEMP_OBJ_VAL OBJ_VAL;

BEGIN

  OPEN C1;

  LOOP

    FETCH C1

      INTO TEMP_C1;

    TEMP_OBJ_VAL := OBJ_VAL(TEMP_C1.COLUMN_NAME, TEMP_C1.DATA_TYPE);

    EXIT WHEN C1%NOTFOUND;

    PIPE ROW(TEMP_OBJ_VAL); -- Send the processed row

  END LOOP;

  RETURN;   --Return control back

END;

4 .Then execute the table function 

select * from table(print_cols('table_name','owner_name'));

This function is used to extract all columns and their datatype for the table that is passed as an argument.