Re: where NO_DATA_FOUND exception

On Fri, 25 Oct 2002 14:36:42 -0700, Michael Maramzin <mvm_at_e-mail.ru> wrote:

>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

1 select into should be *ALWAYS* trapped for NO_DATA_FOUND exceptions, without exception.
2 The code you are showing are examples of recursive sql. (sql within sql). You should avoid that like hell, it doesn't perform 3 You basically demonstrate that when you don't code properly ( create or replace function fu return varchar2 is resul varchar2(10) := 'some result';
-- code
return resul;

the result of a function is initialized to NULL. Did you expect anything else?

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Oct 26 2002 - 08:55:00 CDT