Pipelined Functions

Pipelined function..

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

Pipelining negates the need to build huge collections by piping rows out of the function as they are created, saving memory and allowing subsequent processing to start before all the rows are generated.

Pipelined table functions include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, rather than building up a table collection. Notice the empty RETURN call, since there is no collection to return from the function.

PIPE ROW :
PIPE ROW statement causes a table function to pipe a row and continue processing.
The statement enables a PL/SQL table function to return rows as soon as they are produced. (For
performance, the PL/SQL runtime system provides the rows to the consumer in batches.


Example :

Non-Piplined function :

First, create a table object as the return value for the table function.
CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
/

Next, create the table function. This function accepts a comma-delimited string, which it splits and turns into rows in a table. Once the table is fully populated it is returned.

    CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
      RETURN t_in_list_tab
    AS
      l_tab   t_in_list_tab := t_in_list_tab();
      l_text  VARCHAR2(32767) := p_in_list || ',';
      l_idx   NUMBER;
    BEGIN
      LOOP
        l_idx := INSTR(l_text, ',');
        EXIT WHEN NVL(l_idx, 0) = 0;
        l_tab.extend;
        l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));
        l_text := SUBSTR(l_text, l_idx + 1);
      END LOOP;

      RETURN l_tab;
    END;
    /

The following query shows the table function in action.

    SELECT *
    FROM   emp
    WHERE  job IN (SELECT * FROM TABLE(in_list('SALESMAN, MANAGER')))
    ORDER BY ename;

         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
          7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

    7 rows selected.

    SQL>

Piplined Function:

The following code uses a similar approach, but with a pipelined table function. The advantage here is that rows are piped to the query as they are produced, rather than building the entire table before the rows are returned.

    CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
      RETURN t_in_list_tab PIPELINED
    AS
      l_text  VARCHAR2(32767) := p_in_list || ',';
      l_idx   NUMBER;
    BEGIN
      LOOP
        l_idx := INSTR(l_text, ',');
        EXIT WHEN NVL(l_idx, 0) = 0;
        PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
        l_text := SUBSTR(l_text, l_idx + 1);
      END LOOP;

      RETURN;
    END;
    /

The following query shows the pipelined table function in action.

    SELECT *
    FROM   emp
    WHERE  job IN (SELECT * FROM TABLE(in_list('SALESMAN, MANAGER')))
    ORDER BY ename;

         EMPNO ENAME      JOB              MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- -------------------- ---------- ---------- ----------
          7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300         30
          7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                    10
          7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400         30
          7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0         30
          7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500         30

    7 rows selected.

    SQL>

Advangtage Of Piplined functions :

1) It reduce Memory consumptions:
2) Improve Performance of ETL while there is large data to be processed.
3) Client gets first row before function generates last row.
4) Function doesn't need to build big honking array in memory.