I think that since 1403 is not an exceptional situation in SQL (it is
pretty legal to receive no rows from a query), this exception is
simply ignored and NULL is assigned for missing data - exactly the
behavior you demonstrated. Even though you raised this exception
from PL/SQL function, it propagated to SQL engine and was not
considered as an error there.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Edzard" <edzard_at_volcanomail.com> wrote in message
news:5d75e934.0210281059.1f078767_at_posting.google.com...
> Hello,
>
> There is still a point in the original message that needs to be
> clarified. Why does the exception NO_DATA_FOUND get ignored (when
> called from within SQL). I wrote some other example to make the
> question clear. Please see the output below.
>
> Thanks, Edzard Pasma
>
> SQL> create function fu1 return varchar2 is
> 2 begin
> 3 raise no_data_found;
> 4 return 'x';
> 5 end;
> 6 /
>
> Function created.
>
> SQL>
> SQL> create function fu2 return varchar2 is
> 2 begin
> 3 raise too_many_rows;
> 4 return 'x';
> 5 end;
> 6 /
>
> Function created.
>
> SQL>
> SQL> select fu1 from dual;
>
> FU1
> --------------------------------------------------------------------------------
>
>
> SQL>
> SQL> select fu2 from dual;
> select fu2 from dual
> *
> ERROR at line 1:
> ORA-01422: exact fetch returns more than requested number of rows
> ORA-06512: at "RAPT.FU2", line 3
Received on Tue Oct 29 2002 - 03:26:44 CST
