Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Nullable return type in oracle function

Is it possible to return null from an oracle function?

I have the following oracle function:

create or replace function vta.GetAmount(p_month NUMBER)
  return number is
  v_amount number(9);
begin
  select amount
    into v_amount
    from salary
   where salary.month = p_month;
  return v_amount;
end GetAmount;

When the select statement returns zero rows, it raises the following exception: ora-01403: no data found.

In this case I would like that the function returns null.

like image 993
Rene Avatar asked Apr 22 '15 09:04

Rene


1 Answers

create or replace function vta.GetAmount(p_month NUMBER)
  return number is
  v_amount number(9);
begin
  select amount
    into v_amount
    from salary
   where salary.month = p_month;
  return v_amount;
  exception   -- code to handle no data
  when no_data_found then
  return null;
  end GetAmount;
like image 197
anudeepks Avatar answered Sep 29 '22 12:09

anudeepks