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;