Skip to Main Content
  • Questions
  • Oracle Function returning multiple values

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vivek.

Asked: August 30, 2017 - 2:47 pm UTC

Last updated: September 02, 2021 - 2:00 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 50K+ times! This question is

You Asked

Can Oracle function return multiple values..? if yes, could you give an example for it?

and Connor said...

Yes, you can use a type for this, eg

SQL> create or replace
  2  type three_values as object
  3    ( val_1 int,
  4      val_2 date,
  5      val_3 varchar2(10)
  6    );
  7  /

Type created.

SQL>
SQL> create or replace
  2  function f return three_values is
  3  begin
  4    return
  5      three_values(1,sysdate,'hello');
  6  end;
  7  /

Function created.

SQL>
SQL> select f from dual;

F(VAL_1, VAL_2, VAL_3)
-------------------------------------------------------------
THREE_VALUES(1, '06-SEP-17', 'hello')

1 row selected.

SQL>
SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3   (select f c from dual ) x;

   C.VAL_1 C.VAL_2   C.VAL_3
---------- --------- ----------
         1 06-SEP-17 hello

1 row selected.


Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

And there are different ways to do the same.

Sree Appachi, September 06, 2017 - 1:14 am UTC

And there are different ways to do the same. PIPELINED functions come to mind. I am sure others know that one. Here is something that is lesser known (IMO).

SQL> create or replace function fun ( p_in_1  in number
2                                 , p_in_2 out number )
3                                 return number
4  is
5  begin
6    p_in_2 := 2*p_in_1;
7    return 0;
8  end;
9  /
 
Function created.
 
SQL> sho err
No errors.

Note: Created a function with one OUT VARIABLE and ONE RETURN VARIABLE. Lets execute the function.
 
SQL> var x number;
SQL> var y number;
SQL> exec : x :=0;
 
PL/SQL procedure successfully completed.
 
SQL> exec :y :=1;
 
PL/SQL procedure successfully completed.
 
SQL> select : x
2       , :y
3    from dual
4  /
 
: X        :Y
---------- ----------
0          1
 
SQL> exec :y := fun(10, : x);
 
PL/SQL procedure successfully completed.
 
SQL> select : x
2       , :y
3    from dual
4  /
 
: X        :Y
---------- ----------
20          0

Useful code for dry run

Abhay baldawa, September 06, 2017 - 1:26 am UTC


A reader, March 25, 2018 - 12:04 pm UTC

when i run
select f from dual;

it results below, why different from example ?

[JOB.THREE_VALUES]
Chris Saxon
March 26, 2018 - 2:49 pm UTC

Looks like you've got the type definition, not its contents.

What do you see when you select the attributes, as in Connor's last example:

SQL> select x.c.val_1, x.c.val_2, x.c.val_3
  2  from
  3   (select f c from dual ) x;

Consider using a procedure

A reader, March 26, 2018 - 10:37 pm UTC

You can also use a procedure that has more than one out parameter. This is a good solution if it's used in PL/SQL only.

And note that a function that returns an out parameter is considered bad practice and should be avoided.
From the Oracle documentation: "Do not use OUT and IN OUT for function parameters. Ideally, a function takes zero or more parameters and returns a single value. A function with IN OUT parameters returns multiple values and has side effects."
Connor McDonald
March 27, 2018 - 5:29 am UTC

agreed.

Problem with multiple function call

Masek, April 26, 2018 - 11:57 am UTC

I have problem with method mentioned above. Problem is, that function is called multiple times, once for every column that I reference in select (in your example, it is three times). It is problem if function is not deterministic or is time consuming.
My example of problem:

create or replace
type three_values as object
( val_1 int,
val_2 int,
val_3 int
);
/
--package with one global variable
create package p_x as
l_x int;
end;
/
--non detrministic function
create or replace
function f return three_values is
begin
--every call increment global variable
if p.l_x is null then
p.l_x:=0;
else
p.l_x:=p.l_x+1;
end if;
--return one row with three same values
return three_values(p.l_x, p.l_x, p.l_x);
end;
/
--select return three different values, but my expectations was, that it should return three same values
select x.c.val_1, x.c.val_2, x.c.val_3
from
(select f c from dual ) x;
/

Is there any solution that forces oracle to call function only once, obtain one row and then access items in that one same row?
Connor McDonald
May 01, 2018 - 1:18 am UTC

You can never be guaranteed of how many times we will run a function - so I'm hoping that you are using the global package variable in order demonstrate the issue, rather than having that variable as part of your true application logic.

In any event, you can use some scalar query caching to assist

select x.c.val_1, x.c.val_2, x.c.val_3 
from 
(select ( select f from dual ) c 
 from dual ) x; 


use with select another table

A reader, May 21, 2019 - 8:38 am UTC

create or replace type three_values as object
( val_1 number,
val_2 number,
val_3 number
);


with table_name as (
select 1 col1 , 'ITEM 1' item_name , 1999 purchase_amount from dual
union
select 2 col1 , 'ITEM 1' item_name, 500 purchase_amount from dual
union
select 3 col1 , 'ITEM 3' item_name, 2000 purchase_amount from dual
union
select 4 col1 , 'ITEM 4' item_name, 2780 purchase_amount from dual
union
select 5 col1 , 'ITEM 5' item_name, 4000 purchase_amount from dual
union
select 6 col1 , 'ITEM 6' item_name, 8000 purchase_amount from dual
)

select * from table_name ;


create or replace function get_final_price(p_item_name varchar2,p_purchase_amount number) return three_values is
v_col_1 number;
v_col_2 number;
v_col_3 number;
basic_rate number;
discount number;
begin

if p_item_name = 'ITEM 1' and p_purchase_amount <= 999 then
discount := 10;
elsif p_item_name = 'ITEM 1' and p_purchase_amount >= 1000 and p_purchase_amount <= 2000 then
discount := 25;
elsif p_item_name = 'ITEM 1' and p_purchase_amount >= 2001 and p_purchase_amount <= 3000 then
discount := 30;
elsif p_item_name = 'ITEM 2' then
discount := 20;
elsif p_item_name = 'ITEM 3' then
discount := 5;
elsif p_item_name = 'ITEM 4' then
discount := 7;
elsif p_item_name = 'ITEM 5' then
discount := 9;
elsif p_item_name = 'ITEM 6' then
discount := 25;
end if;

v_col_2 := ( p_purchase_amount * discount ) / 100;
v_col_1 := p_purchase_amount - v_col_2 ;

v_col_3 := v_col_1 - 100 ; -- give 100 Rs Flat Discount
return three_values(v_col_1, v_col_2, v_col_3);
end;





with table_name as (
select 1 col1 , 'ITEM 1' item_name , 1999 purchase_amount from dual
union
select 2 col1 , 'ITEM 1' item_name, 500 purchase_amount from dual
union
select 3 col1 , 'ITEM 3' item_name, 2000 purchase_amount from dual
union
select 4 col1 , 'ITEM 4' item_name, 2780 purchase_amount from dual
union
select 5 col1 , 'ITEM 5' item_name, 4000 purchase_amount from dual
union
select 6 col1 , 'ITEM 6' item_name, 8000 purchase_amount from dual
)

select t.* , get_final_price(p_item_name => item_name,
p_purchase_amount => purchase_amount) price from table_name t ;




Output is
Col1 ITEMNAME PURCHASE_AMOUNT PRICE
1 ITEM 1 1999 (1499.25, 499.75, 1399.25)
2 ITEM 1 500 (450, 50, 350)
3 ITEM 3 2000 (1900, 100, 1800)
4 ITEM 4 2780 (2585.4, 194.6, 2485.4)
5 ITEM 5 4000 (3640, 360, 3540)
6 ITEM 6 8000 (6000, 2000, 5900)


But I want to this

COL1 ITEM_NAME PURCHASE_AMOUNT PRICE.VAL_1 PRICE.VAL_2 PRICE.VAL_3
1 1 ITEM 1 1999 1499.25 499.75 1399.25
2 2 ITEM 1 500 450 50 350
3 3 ITEM 3 2000 1900 100 1800
4 4 ITEM 4 2780 2585.4 194.6 2485.4
5 5 ITEM 5 4000 3640 360 3540
6 6 ITEM 6 8000 6000 2000 5900

Connor McDonald
May 22, 2019 - 12:32 am UTC

Just wrap that with another SQL to get the attributes

select t.* , 
     get_final_price(
       p_item_name => item_name,
       p_purchase_amount => purchase_amount) price 
  from table_name t ;


becomes

select col1 ,  item_name , purchase_amount,   -- cols from t
      val_1, val_2, val_3   -- cols from object
from
(
select t.* , 
     get_final_price(
       p_item_name => item_name,
       p_purchase_amount => purchase_amount) price 
  from table_name t 
)



Syntax Question

Vidhyasankar, November 17, 2020 - 1:31 am UTC

(select f c from dual ) x;

what is this 'c' here ?
looks like c is instance of the function f ?

sorry for the question so naive.
Connor McDonald
November 17, 2020 - 1:56 am UTC

Its just a column alias in the same way that I might do something like

select sal * 0.1 bonus from scott.emp

sal * 0.1 is the expression
bonus is the alias.

follow up question on "use with select another table"

Joe L, January 18, 2021 - 4:34 pm UTC

In response to thread above "use with select another table"

version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

following SQL
select col1 ,  item_name , purchase_amount,   -- cols from t
      val_1, val_2, val_3   -- cols from object
from
(
select t.* , 
     get_final_price(
       p_item_name => item_name,
       p_purchase_amount => purchase_amount) price 
  from table_name t 
)


returns

ORA-00904: "VAL_3": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


follow up question on "use with select another table"

Joe L, January 18, 2021 - 4:52 pm UTC

Even original question (Asked by A reader) results differ

for e.g. for sql

with table_name as (
select 1 col1 , 'ITEM 1' item_name , 1999 purchase_amount from dual
union
select 2 col1 , 'ITEM 1' item_name, 500 purchase_amount from dual
union
select 3 col1 , 'ITEM 3' item_name, 2000 purchase_amount from dual
union
select 4 col1 , 'ITEM 4' item_name, 2780 purchase_amount from dual
union
select 5 col1 , 'ITEM 5' item_name, 4000 purchase_amount from dual
union
select 6 col1 , 'ITEM 6' item_name, 8000 purchase_amount from dual
)

select t.* , get_final_price(p_item_name => item_name,
p_purchase_amount => purchase_amount) price from table_name t ;


it returns

1 ITEM 1 1999 [ETL_RPT.THREE_VALUES]
2 ITEM 1 500 [ETL_RPT.THREE_VALUES]
3 ITEM 3 2000 [ETL_RPT.THREE_VALUES]
4 ITEM 4 2780 [ETL_RPT.THREE_VALUES]
5 ITEM 5 4000 [ETL_RPT.THREE_VALUES]
6 ITEM 6 8000 [ETL_RPT.THREE_VALUES]


I am trying to follow "A reader" e.g. as its without any change.

How to call the function in a higher select and using an id as an input

Tom, September 01, 2021 - 4:56 pm UTC

I set up a function like you have in this example, but I pass in an id to determine the three values returned by doing a lookup on several different tables.

I can get the call:

select x.c.val_1, x.c.val_2, x.c.val_3
from (select f(id) c from dual ) x;

To return the three values - but I have a SQL query that goes through several tables to pull other information. If the function returned just one value this would work:

Select t1.id, t1.name, t2.date, t3.order_id, f(t3.order_id)
From table_customer t1
join table_interactions t2 on t1.id = t2.id
join table_orders t3 on t1.id = t3.id
;

Because the function returns three values -the above select doesn't work. How do I get the three values returned in the above select?

Thank you.
Connor McDonald
September 02, 2021 - 2:00 am UTC

To break apart an object type (ie, what the function is returning) you just need to nest it in an inline view and use a column alias

So

Select t1.id, t1.name, t2.date, t3.order_id, f(t3.order_id)
From table_customer t1
join table_interactions t2 on t1.id = t2.id
join table_orders t3 on t1.id = t3.id


becomes

select id, name, order_id, result.val_1, result,val_2, result.val_3
from
(
Select t1.id, t1.name, t2.date, t3.order_id, f(t3.order_id) result
From table_customer t1
join table_interactions t2 on t1.id = t2.id
join table_orders t3 on t1.id = t3.id
)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library