Table Function In Oracle

Table functions are used to return collection and its queried like a table using "TABLE " keyword.

Simple words we can say as "Functions that can be called in the FROM clause of query.



Syntax:
Select Column_Value From Table(Collection_Name);

We may have functions which has return type as collection those kind of functions also can be called with this.

Syntax:

Select Column_Value From Table(Function_Name());

CREATE OR REPLACE TYPE INTEGER_TYPE IS TABLE OF INTEGER;

SQL> SELECT COLUMN_VALUE FROM TABLE(INTEGER_TYPE(1,2,3,4,5));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5


Let's see one eg. with function which returns multiple values.

In 1st Step create nested table at SQL level so that it can be used as data type wherever we required.



SQL> CREATE OR REPLACE TYPE INTEGER_TYPE IS TABLE OF INTEGER;
  2  /


Create Function to return employee number list which are belongs to particular deptno.



SQL> CREATE OR REPLACE FUNCTION TABLE_FUNCTION(V_DEPNO INTEGER)
  2  RETURN INTEGER_TYPE
  3  IS
  4  V_int Integer_Type;
  5  BEGIN
  6    SELECT Empno
  7      BULK COLLECT INTO V_Int
  8      FROM EMP
  9     WHERE DEPTNO = V_DEPNO;
 10   RETURN V_INT;
 11  EXCEPTION
 12   WHEN OTHERS THEN
 13     DBMS_OUTPUT.PUT_LINE('ERR MSG '||SQLERRM );
 14  END;
 15  /
SQL> ALTER FUNCTION TABLE_FUNCTION COMPILE;



Here we are calling above function in table function..
 
SQL> SELECT * FROM TABLE(TABLE_FUNCTION(V_DEPNO => 10));

COLUMN_VALUE
------------
        7782
        7839
        7934
 


If see above ,I Used "Select * From "  and closely observe the column name what it displays in while printing Output,It's Column_Value.


Here "COLUMN_VALUE " Is pseudo-column which we use to deal with nested table as below.




SQL> SELECT COLUMN_VALUE AS emp_no FROM TABLE(TABLE_FUNCTION(V_DEPNO => 10));

    EMP_NO
----------
      7782
      7839
      7934

SQL> 


Pls post your queries if you have any doubts on this topic.Thank you.