Re: SQL Function call does not fail on no_data_found ?

From: ddf <oratune_at_msn.com>
Date: Wed, 24 Mar 2010 06:44:39 -0700 (PDT)
Message-ID: <55da68f5-5de6-45f1-bf3e-44ee168d6dd9_at_v34g2000prm.googlegroups.com>

On Mar 23, 5:11 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Tue, 23 Mar 2010 18:16:23 +0100, Michel Cadot wrote:
> > Yes, no_data_found is trapped by SQL engine and converted to a NULL
> > value.
>
> Why is that? It looks like a huge bug to me. Is that documented anywhere?
>
> --http://mgogala.byethost5.com

Accordoing to Jonathan Lewis it probably shouldn't :

"...queries (that run to completion) end with an ignored 'no data found' exception; that's how Oracle reports 'no more data' to the front-end. Try running a simple 'select user from dual' from SQL*Plus with event 10079 set to level 2 and you'll see text like the following in the trace file:

328E320 00000000 00000000 00000000 00000000 [................]

328E330 00000000 00000000 00000000 524F1900 [..............OR] 328E340 31302D41 3A333034 206F6E20 61746164 [A-01403: no data] 328E350 756F6620 000A646E [ found..]

Your example still looks like an anomaly, though, as you could expect a silent return of NO rows from 'select bad_func(1) from dual', rather than a silent return of a null value. I would guess that this is a side effect of the requirement for scalar subqueries to result in a NULL return when they actually find no data. "

It makes sense to me and prety much puts my example in the 'dumper' as it won't catch the NO_DATA_FOUND exception, either. This one might:

 FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS    l_ename VARCHAR2(90);
    v_emp_ct number:=0;
  BEGIN
    select count(*) into v_emp_ct from emp where empno = p_empno;

    if v_emp_ct = 0 then

         raise_application_error(-20111, 'No employee found with empno '||p_empno);

   else
    SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;   end if;
    RETURN l_ename ;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Data integrity error for empno '|| p_empno||': More than one employee assigned to this empno');   END ; David Fitzjarrell Received on Wed Mar 24 2010 - 08:44:39 CDT