I am trying to write Oracle SQL function. The should take country code, min year and max year as inputs and should return table which contains information for that country in the specified years. This is what I tried to write, but I am new to SQL functions. This is how the data looks and I will be glad for any help.

create or replace type african_crisis_row as object(
country_abv varchar(4),
year number(5),
banking_crisis varchar(10)
);

create or replace type t_african_crisis_table as table of african_crisis_row;

create or replace function african_crisis (   
    country_abv in varchar,
    year_min in number,
    year_max in number
)
return t_african_crisis_table as v_ret table t_african_crisis_table;

begin
    select 
       african_crisis_row(country_abv, year)
    bulk collect into
       v_ret
    from
       africancrisisdata
    where
        country_abv = country_abv and year between year_min and year_max;
    return v_ret
end african_crisis

CodePudding user response：

You need to:

• remove table after the v_ret declaration.
• Include the 3rd banking_crisis value in the call to the african_crisis_row object constructor.
• Include ; statement terminators after the return and final end statements.

(Oracle uses VARCHAR2 and VARCHAR is an alias to VARCHAR2.)

Something like this:

create or replace function african_crisis (   
  country_abv in varchar2,
  year_min    in number,
  year_max    in number
) return t_african_crisis_table
as
  v_ret t_african_crisis_table;
begin
  select african_crisis_row(country_abv, year, banking_crisis)
  bulk collect into v_ret
  from  africancrisisdata
  where country_abv = country_abv
  and   year between year_min and year_max;

  return v_ret;
end african_crisis;
/

db<>fiddle here