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)
if p = 0 then
elsif p = 1 then
when no_data_found then
select strange(0) v from dual;
select strange(1) v from dual;
select strange(2) v from dual;
n := strange(1);
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:
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.
set serveroutput on
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.