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

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
------
1But, 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
10That’s it. Here we seen how to return multiple values from a function in oracle.