How to return multiple values from a function in Oracle PL/SQL?

Generally we write functions for computing some value. Function can return only one value, though it’s definition may contains more than one return statements. This article explains us how to return multiple values from a function in oracle using PL/SQL language.

Let us see the below defined function func_test. Its definition is having multiple return statement.

CREATE OR REPLACE
  FUNCTION func_test
    RETURN NUMBER
  AS
  BEGIN
    RETURN 1;
    RETURN 2;
  END;
  /

SELECT func_test AS value FROM dual;

VALUE
------
1

But, upon executing this function, it is returning only one value. Now, Let us see how to return more than one value from function. For this, We need to create a collection/record object.
For example, I am creating numbers collection of NUMBER data type.

CREATE OR REPLACE TYPE numbers IS TABLE OF NUMBER;
/

Below function simply returns 10 numbers values from 1 to 10 in the collection objects of numbers.

CREATE OR REPLACE
  FUNCTION func_multi_val
    RETURN numbers 
  AS
    l_numbers numbers := numbers();
  BEGIN
    FOR i IN 1 .. 10
    LOOP
      l_numbers.EXTEND();
      l_numbers(i) := i;
    END LOOP;
    RETURN l_numbers;
  END;
  /

Now we will check whether our function is returning multiple values or not.

SELECT * FROM TABLE(func_multi_val);

COLUMN_VALUE
-------------
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10

That’s it. Here we seen how to return multiple values from a function in oracle.

How to return multiple values from a function in Oracle PL/SQL?