Pipelined Table Functions

Table function

A table function can be used to query like a regular table by using “TABLE” operator in FROM clause,  which required the collection/table to be fully populated before they are returned.
since collections are held in memory, this can be a problem as a large collection can waste a lot of memory and take a lot of time to fetch even first few records. which is problematic in case of large ETL operations.

  • Till Oracle 11g while selecting records “TABLE” keyword was required.

CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  description  VARCHAR2(50)
);
/

Type created.

SQL> CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

Type created.

CREATE OR REPLACE FUNCTION get_tab_tf (p_rows IN NUMBER) RETURN t_tf_tab AS
  l_tab  t_tf_tab := t_tf_tab();
BEGIN
  FOR i IN 1 .. p_rows LOOP
    l_tab.extend;
    l_tab(l_tab.last) := t_tf_row(i, ‘Description for ‘ || i);
  END LOOP;
  RETURN l_tab;
END;
/

Function created.

SELECT * FROM   TABLE(get_tab_tf(10)) ORDER BY id DESC;

ID DESCRIPTION
———- ————————————————–
10 Description for 10
9 Description for 9
8 Description for 8
7 Description for 7
6 Description for 6
5 Description for 5
4 Description for 4
3 Description for 3
2 Description for 2
1 Description for 1

10 rows selected.

  • After 12.2 “TABLE” keyword is not required, we can use the table_function_name alone.

SQL> SELECT * FROM get_tab_tf(10) order by id desc;

ID DESCRIPTION
———- ————————————————–
10 Description for 10
9 Description for 9
8 Description for 8
7 Description for 7
6 Description for 6
5 Description for 5
4 Description for 4
3 Description for 3
2 Description for 2
1 Description for 1

10 rows selected.

Pipelined Table Functions

Before all rows are generated in the collection, pipelined table function uses to piping the rows out of the function as soon as it created, which negates the need to build a huge collection, which saves memory and allowing subsequent processing.

SQL> CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    PIPE ROW(t_tf_row(i, ‘Description for ‘ || i));   
  END LOOP;
  RETURN;
END;
/

Function created.

SQL> SELECT * FROM   TABLE(get_tab_ptf(10)) ORDER BY id DESC;

ID DESCRIPTION
———- ————————————————–
10 Description for 10
9 Description for 9
8 Description for 8
7 Description for 7
6 Description for 6
5 Description for 5
4 Description for 4
3 Description for 3
2 Description for 2
1 Description for 1

10 rows selected.

  • After 12.2 “TABLE” keyword is not required, we can use the table_function_name alone. 

SQL> SELECT * FROM get_tab_tf(10) order by id desc;

ID DESCRIPTION
———- ————————————————–
10 Description for 10
9 Description for 9
8 Description for 8
7 Description for 7
6 Description for 6
5 Description for 5
4 Description for 4
3 Description for 3
2 Description for 2
1 Description for 1

10 rows selected.

NO_DATA_NEEDED Exception

  • A pipelined table function may create more data than is needed by the process querying it. When this happens,  the pipelined table function execution stops, raising the NO_DATA_NEEDED exception. This doesn’t need to be explicitly handled provided you do not include an OTHERS exception handler.
  • The function below returns 10 rows, but the query against it only ask for the first 5 rows, so the function stops processing by raising the NO_DATA_NEEDED exception.

SQL> CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    DBMS_OUTPUT.put_line(‘Row: ‘ || i);
    PIPE ROW(t_tf_row(i, ‘Description for ‘ || i));
  END LOOP;
  RETURN;
END;
/

Function created.

SQL> SET SERVEROUTPUT ON

SQL> SELECT * FROM   TABLE(get_tab_ptf(10)) WHERE  rownum <= 5;

ID DESCRIPTION
———- ————————————————–
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5

Row: 1
Row: 2
Row: 3
Row: 4
Row: 5

SQL> CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    DBMS_OUTPUT.put_line(‘Row: ‘ || i);
    PIPE ROW(t_tf_row(i, ‘Description for ‘ || i));
  END LOOP;
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(‘OTHERS Handler’);
    RAISE;
END;
/

Function created.

SQL> SELECT * FROM   TABLE(get_tab_ptf(10)) WHERE  rownum <= 5;

ID DESCRIPTION
———- ————————————————–
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5

Row: 1
Row: 2
Row: 3
Row: 4
Row: 5
OTHERS Handler

It can be use for cleanup operation 

SQL> CREATE OR REPLACE FUNCTION get_tab_ptf (p_rows IN NUMBER) RETURN t_tf_tab PIPELINED AS
BEGIN
  FOR i IN 1 .. p_rows LOOP
    DBMS_OUTPUT.put_line(‘Row: ‘ || i);
    PIPE ROW(t_tf_row(i, ‘Description for ‘ || i));
  END LOOP;
  RETURN;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    DBMS_OUTPUT.put_line(‘NO_DATA_NEEDED Handler’); –my_package.cleanup;
    — RAISE;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line(‘OTHERS Handler’);
    RAISE;
END;
/

Function created.


SQL> SET SERVEROUTPUT ON

SQL> SELECT * FROM   TABLE(get_tab_ptf(10)) WHERE  rownum <= 5;

ID DESCRIPTION
———- ————————————————–
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5

Row: 1
Row: 2
Row: 3
Row: 4
Row: 5
NO_DATA_NEEDED Handler

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn