The strange old case of ORA-01403 no data found exception

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Even if I found out later that i am only three years late in discovering the situation I am going to describe, when i saw the results i was really puzzled.

I don't know if i was more puzzled by the curious handling of the NO_DATA_FOUND exception in SQL or by the fact that i stumbled on it only today.
At any rate, it's something that can lead to unexpected results if you make the mistake to mix SQL and PL/SQL without taking the appropriate countermeasures.

create or replace
function strange(p in number)
return number
as
n number;
begin
if p = 0 then
select p
into n
from dual;
elsif p = 1 then
select p
into n
from dual
where 1=0;
else
begin
select p
into n
from dual
where 1=0;
exception
when no_data_found then
raise_application_error(-20001, 'no_data_found');
end;
end if;
return n;
end;

select strange(0) v from dual;

V
----
0

select strange(1) v from dual;

V
----
(null)

select strange(2) v from dual;

ORA-20001: no_data_found

declare
n number;
begin
n := strange(1);
end;
/

ORA-1403: no data found

If you wonder why SQL handles differently the same function call to STRANGE(1), you may want to have a look at the following thread on AskTom.
Until we fiddle with SELECT function FROM DUAL statements in SQLPlus for quick testing purposes we may be surprised to see that it returns NULL instead of an error, but the potential for more subtle problems comes when you invoke this function as a SQL cursor inside a PL/SQL program:

set serveroutput on
declare
n integer;
begin
select strange(1)
into n
from dual;
dbms_output.put_line(nvl(n,-1));
end;
/

-1

The lesson here is to always trap the NO_DATA_FOUND error inside the function if we want to propagate the error to the caller, because we cannot predict if the caller will invoke the function from within a SQL cursor or as a PL/SQL function.

As a last note of folklore, in the PL/SQL Reference Manual for 10gR2 there is a little note warning the user about this eccentric exception, but for some reason it seems it has been removed from the corresponding manual of 11gR2, however the behavior remained "consistent" across the oracle versions.

See message translations for ORA-01403 and search additional resources.

Update ... returning into misunderstandings

I don't know why, but for some time i have been living in the belief that the following statement would end up in an ORA-01403 no data found error:

update some_table
set some_column = some_value
where 1 = 0
returning some_other_column into some_variable;

Probably this is due to the fact that the documentation, up to date, doesn't cover this specific situation (correct me if i am wrong) and much is left to the imagination of the programmer.

Hence, if you are still convinced that the sql statement above should cause a run time exception, as it would happen with

select some_column
into some_variable
from some_table
where 1 = 0;

ORA-01403: no data found

well, you'd be better off checking your programs right now, especially if you work in a nuclear plant or at some missile shield project :-)

As perhaps Oscar Wilde would say if he had had a chance to be a PL/SQL programmer, "there is only one thing in the world worse than a program raising an unexpected exception, and that is a program not raising an expected exception."

Indeed, the update will run smoothly and guess what, some_variable will not be updated at all, that means, it will retain its previous value, if any.

In other words, if some_variable's value prior to executing the update was 'X', it will still be 'X' after executing the update.
This means that if you want to be sure that this variable holds a consistent value after the update, whatever the outcome of update will be, you should take no chances and reset the variable yourself, before executing that statement.

some_variable := null;
update ...
returning ... into...;

Interestingly enough, if the where condition in the update doesn't result in a unique fetch, you'll get:

ORA-01422: exact fetch returns more than
requested number of rows

If you trap this error and inspect the content of some_variable, you'll see it contains the value of the column retrieved from the first matching record.
And i guess that in certain situations this could be a useful feature.

If you need to handle multiple rows in one shot, remember that you can use the UPDATE ... BULK COLLECT INTO syntax:

declare
type my_array_type is
table of some_table.some_column%type
index by binary_integer;
my_array  my_array_type;
begin
...
update some_table
set some_column = some_value
where ...
returning some_expression bulk collect into my_array;

end;

No nukes, please!

ORA-01403 in ApEx weighted page perfomance statistics

If you are wondering why you're getting:
ORA-01403: no data found
when you open "Page Views by Weighted Page Performance" in the Monitor Activity section of Oracle Application Express, version 2.2.1.00.04 (or earlier i presume), then check in the lower left corner of the page what is the current language detected by the browser.

This report is flawed by a problem with the default decimal point character derived from the browser language settings.

In brief, if you set your browser's primary language to en (english) or es-mx (spanish-mexico) , the report will work because the decimal point character is the period (.), but if you have it (italian) or es (Spanish international) or another language with the comma as decimal separator, the report will not work.