Returning a Record Set from a Function in Oracle (Basic 1 - 29)

It is possible to return a record set from a function in Oracle using collections. This can be useful if you need to return multiple rows of data from a function, or if you need to return data from multiple tables.

To return a record set from a function:

1. Create an object type that contains the fields that you want to return.
2. Create a named table type of the object type that you created in step 1.
3. Create a function that returns the named table type that you created in step 2.
4. In the function, use the BULK COLLECT clause to insert the data that you want to return into the named table type.
5. Return the named table type from the function.

Example

The following example shows how to return a record set from a function:

SQL

CREATE OR REPLACE TYPE MyObjectCols
AS OBJECT (i NUMBER, n Varchar2(30));

CREATE or REPLACE TYPE Type_Nested_Table AS Table of MyObjectCols;

CREATE OR REPLACE FUNCTION MyFunction RETURN Type_Nested_Table AS
  v_ret Type_Nested_Table;
BEGIN
  v_ret  := Type_Nested_Table();
  v_ret.Extend;
  v_ret(v_ret.count) := MyObjectCols(1, 'ONE');
  v_ret.Extend;
  v_ret(v_ret.count) := MyObjectCols(2, 'TWO');
  v_ret.Extend;
  v_ret(v_ret.count) := MyObjectCols(3, 'THREE');
  Return v_ret;
END MyFunction;

SELECT * From Table(MyFunction);

Output:

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

Example using a table

The following example shows how to return a record set from a function using a table:

SQL

CREATE TABLE MyTab
(
  Test_ID      NUMBER(9) NOT NULL,
  Test_Desc    VARCHAR(30),
  Test_DATE    DATE
);

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

COMMIT;

CREATE OR REPLACE TYPE TEST_OBJ_TYPE AS OBJECT (
  Test_ID      NUMBER(9),
  Test_Desc    VARCHAR(30)
);

CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE;

CREATE OR REPLACE FUNCTION FN_GET_RESULT
RETURN TEST_TABTYPE
AS
  V_Test_Tabtype Test_TabType;
BEGIN
  SELECT TEST_OBJ_TYPE(A.Test_Id, A.Test_Desc)
  BULK COLLECT INTO V_Test_TabType
  FROM
  (SELECT Test_Id, Test_Desc FROM MyTab) A;
  RETURN V_Test_TabType;
EXCEPTION
  WHEN OTHERS THEN
  v_Test_TabType.DELETE;
  RETURN v_Test_TabType;
END;

SELECT * FROM TABLE(FN_GET_RESULT);

Output:

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

Conclusion

Returning a record set from a function in Oracle can be a useful way to encapsulate complex logic and return multiple rows of data to the caller.