Re: SQL Function call does not fail on no_data_found ?
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