Returning TABLE From a Function

Is it possible to return a Record Set (Table) from a Function in ORACLE?

Yes, with COLLECTIONS, it is possible to return a record set from a Function. Function which returns a record set requires following steps:

•         Object Type with attributes to be created first.
•         Create named table type of the Object Type created in first step.
•         Now, we can return this named table type from a Function.

First, we need to create a new object type that contains the fields that are going to be returned.

CREATE or REPLACE TYPE MyObjectCols

AS OBJECT (i NUMBER, n Varchar2(30));

Once above type is created we need to create a Nested Type of above Object Type.

CREATE or REPLACE TYPE Type_Nested_Table AS Table of MyObjectCols;

Now, we can create a Function which will actually return above Nested Table Type.

SQL> CREATE OR REPLACE FUNCTION MyFunction RETURN Type_Nested_Table AS
  2  v_ret Type_Nested_Table;
  3  BEGIN
  4  v_ret  := Type_Nested_Table();
  5  v_ret.Extend;
  6  v_ret(v_ret.count) := MyObjectCols(1, 'ONE');
  7  v_ret.Extend;
  8  v_ret(v_ret.count) := MyObjectCols(2, 'TWO');
  9  v_ret.Extend;
 10  v_ret(v_ret.count) := MyObjectCols(3, 'THREE');
 11  Return v_ret;
 12  End MyFunction;
 13  /

Function created.

Let’s see how we can use this Function.

SQL> SELECT * From Table(MyFunction);

         I N
---------- ------------------------------
         1 ONE
         2 TWO
         3 THREE

Let’s see one more Example in which we will create a Table and populate it with some Data.

SQL> CREATE TABLE MyTab
  2  (
  3  Test_ID      NUMBER(9) NOT NULL,
  4  Test_Desc    VARCHAR(30),
  5  Test_DATE    DATE
  6  );

Table created.

SQL> INSERT INTO MyTab(TEST_ID, TEST_DESC, TEST_DATE)
  2  SELECT ROWNUM, TABLE_NAME, SYSDATE
  3  FROM USER_TABLES WHERE ROWNUM<=5;

5 rows created.

SQL> COMMIT;
Commit complete.

/*Here is the Data in Table*/

SQL> SELECT * From MyTab;

   TEST_ID TEST_DESC                      TEST_DATE
---------- ------------------------------ ---------
         1 PORTFOLIO_MANAGER_T            13-MAR-17
         2 ADVISOR_T                      13-MAR-17
         3 TEST_OBJECT                    13-MAR-17
         4 MYTAB1                         13-MAR-17
         5 MYTAB2                         13-MAR-17

Now suppose, we want to Return TEST_ID and TEST_DESC to calling application using function. First of all, we will create an object type. Make sure that proper privileges are assigned to the user. Follow the same steps which we did in the previous Example.

Once object type is created, we will create named table type of above object type.

CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE;

Now let us write function code to return the result set.

SQL> CREATE OR REPLACE FUNCTION FN_GET_RESULT
  2  RETURN TEST_TABTYPE
  3  AS
  4  V_Test_Tabtype Test_TabType;
  5  BEGIN
  6  SELECT TEST_OBJ_TYPE(A.Test_Id, A.Test_Desc)
  7  BULK COLLECT INTO V_Test_TabType
  8  FROM
  9  (SELECT Test_Id, Test_Desc FROM MyTab) A;
 10  RETURN V_Test_TabType;
 11  EXCEPTION
 12  WHEN OTHERS THEN
 13  v_Test_TabType.DELETE;
 14  RETURN v_Test_TabType;
 15  END;
 16  /

Function created.

Create above mentioned function and then run following SQL to execute it. Below is the SQL followed by result.

SQL> SELECT * FROM TABLE(FN_GET_RESULT);

SQL> SELECT * FROM TABLE(FN_GET_RESULT);

   TEST_ID TEST_DESC
---------- ------------------------------
         1 PORTFOLIO_MANAGER_T
         2 ADVISOR_T
         3 TEST_OBJECT
         4 MYTAB1
         5 MYTAB2

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.