Oracle SQL user defined function

Question

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

MT0 · Accepted Answer · 2022-06-26 14:55:32Z

1

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.
Don't name the function parameters with the same name as the column values.

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

Something like this:

create or replace function african_crisis (   
  i_country_abv in varchar2,
  i_year_min    in number,
  i_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 = i_country_abv
  and   year between i_year_min and i_year_max;

  return v_ret;
end african_crisis;
/

db<>fiddle here

edited Jun 26, 2022 at 14:55

answered Jun 26, 2022 at 10:40

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MT0 Over a year ago

@RafaBarragan Ask a new question because RETURN DATE AS SELECT ... is invalid syntax in Oracle and there is insufficient context in your comment to work out what you are trying to achieve and, since I can't quite understand what the goal is, I am unsure what to suggest would be a "correct" solution.

Rafa Barragan Over a year ago

I have created a new question, do you know where i could be wrong? this is the link stackoverflow.com/questions/74483231/…

Collectives™ on Stack Overflow

Oracle SQL user defined function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related