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;