Showing posts with label multiple return value Oracle function. Show all posts
Showing posts with label multiple return value Oracle function. Show all posts

Friday, May 28, 2021

Oracle Function Returns Two Values

There is a table in a schema that contains three columns, p, low and hi. In the table, p is the primary key. I want to develop a function to return low and hi based on an input variable p. First I create a type.

create or replace type t_low_hi as object ( low number, hi number);  

Then I create a function that finds low and hi based on p, constructs a type object and returns it.

create or replace function f_prob (p_p number)  
return t_low_hi is  
p_Low number;  
p_Hi number;  
Str_sql varchar2(2000);  
begin  
Str_sql := 'Select low, hi from t_lookup where p=:1';  
Execute immediate str_sql into p_low, p_hi using p_p;  
return t_low_hi(p_low, p_hi);  
end;  
/ 

I call the function and retrieve low and hi for p with a value of 0.99.

select x.v.low , x.v.hi from (select f_prob(0.99) v from dual) x;