Re: where NO_DATA_FOUND exception

Michael,

Your fu function does have an error, NO_DATA_FOUND; the "return res;" is never executed, so the return value of the function is never defined, which is different to a null! (Try adding this exception to your function.)

This doesn't affect the two select statements, but does affect the assign and Oracle uses the last meaningful error, in this case, ORA-01403 No Data Found.

Cheers, Clive.

"Michael Maramzin" <mvm_at_e-mail.ru> wrote in message news:3DB9B96A.4030600_at_e-mail.ru...
> The following seems to act strange and tricky to me.
>
> Say there's a function like this
>
> create or replace function fu return varchar2
> is
> res varchar2(20);
> begin
> select 'u' into res from dual where dummy='Y';
> return res;
> end;
>
> so
>
> select fu() from dual;
> and
> declare
> s varchar2(20);
> begin
> select fu() into s from dual;
> end;
> /
>
> work with no exceptions whereas
>
> declare
> s varchar2(20);
> begin
> s:=fu();
> end;
>
> raises ORA-01403 (no data found) just like I expect in both cases.
>
> Furthermore if we change our function this way
>
> create or replace function fu return varchar2
> is
> res varchar2(20);
> begin
> select 'u' into res from dual where dummy='Y';
> return res;
> exception
> when others then raise_application_error(-20001, 'user exception',
> true);
> end;
>
> in this case (of course)
>
> select fu() from dual;
>
> and
>
> declare
> s varchar2(20);
> begin
> select fu() into s from dual;
> end;
> /
>
> raise the exception
>
> Would you explain what it is all about
>
> Sincerely Michael
>

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.408 / Virus Database: 230 - Release Date: 24/10/2002