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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Vivek.

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

Answered by: Connor McDonald - Last updated: May 22, 2019 - 12:32 am UTC

Category: PL/SQL - 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 we 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.


and you rated our response

  (6 ratings)

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

Reviews

And there are different ways to do the same.

September 06, 2017 - 1:14 am UTC

Reviewer: Sree Appachi from MD USA

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

September 06, 2017 - 1:26 am UTC

Reviewer: Abhay baldawa from New jersey


March 25, 2018 - 12:04 pm UTC

Reviewer: A reader

when i run
select f from dual;

it results below, why different from example ?

[JOB.THREE_VALUES]
Chris Saxon

Followup  

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

March 26, 2018 - 10:37 pm UTC

Reviewer: A reader

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

Followup  

March 27, 2018 - 5:29 am UTC

agreed.

Problem with multiple function call

April 26, 2018 - 11:57 am UTC

Reviewer: Masek from CZ

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

Followup  

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

May 21, 2019 - 8:38 am UTC

Reviewer: A reader

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

Followup  

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 
)



More to Explore

PL/SQL

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