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.