Geek Answers Handbook

Invalid return type in oracle function

Is it possible to return null from 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, the following exception occurs: ora-01403: no data found .

In this case, I would like the function to return null.

+5
source share
3 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;
+4
source

When you execute an implicit implicit cursor in PL / SQL (this is what you did with your SELECT ... INTO ... ), you should remember that it expects at least 1 row and no more than 1 row.

If you get less or more than 1 row, you will get an exception - either NO_DATA_FOUND or TOO_MANY_ROWS, both of which are pretty clear.

If you want the code to do something if an exception occurs, you will have to handle these exceptions.

For instance:

 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 when no_data_found then return null; when too_many_rows then return null; end GetAmount;
+3
source

If you really know / want to always return a single line, you can change your selection to select nvl(sum(amount),0)

  • sum guarantees that you always get 1 line because you are not grouping
  • nvl replace null with 0 if nothing is found

Remember that you will get the sum of all matching lines, if there are, of course, several of them.

0
source

More articles:


All Articles