Re: SQL Function call does not fail on no_data_found ?
Date: Thu, 25 Mar 2010 06:54:21 -0700 (PDT)
Message-ID: <869e4955-de63-4c78-ba97-88c2efcfdb37_at_g1g2000pre.googlegroups.com>
On Mar 24, 9:52 pm, Galen Boyer <galen_bo..._at_yahoo.com> wrote:
> ddf <orat..._at_msn.com> writes:
> > On Mar 23, 12:45 pm, webtourist <webtour..._at_gmail.com> wrote:
> >> 10gR2:
>
> >> given a function (in package "test") like this:
>
> >> FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
> >> l_ename VARCHAR2(90);
> >> BEGIN
> >> SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
> >> RETURN l_ename ;
> >> END ;
>
> >> And a SQL function calls:
>
> >> > select test.get_ename(7499) from dual;
>
> >> TEST.GET_ENAME(7499)
> >> ------------------------
> >> ALLEN
>
> >> > select test.get_ename(74992) from dual;
>
> >> TEST.GET_ENAME(74992)
> >> -----------------------
>
> >> I just realized the the above query wouldn't fail with "no_data_found"
> >> - has this always been like this behavior ?
>
> > Where did you handle that exception? Possibly this is how you should
> > have coded your function:
>
> > FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
> > l_ename VARCHAR2(90);
> > BEGIN
> > SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
> > RETURN l_ename ;
> > EXCEPTION
> > WHEN NO_DATA_FOUND THEN
> > DBMS_OUTPUT.PUT_LINE('No employee with empno '||p_empno);
> > 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 ;
>
> > Then it would fail on NO_DATA_FOUND.
>
> How would the above fail? You just swallowed the exception with the
> above code.
>
> > Oracle did raise the exception but there was nothing coded to properly
> > handle it.
>
> The idea was that without handling the exception, Oracle should have
> thrown it.
>
> --
> Galen Boyer
>
> --- news://freenews.netfront.net/ - complaints: n..._at_netfront.net ---- Hide quoted text -
>
> - Show quoted text -
You're behind on the posts to this thread, Galen. :) I've already admitted my original code still wouldn't fail even with the exception handler as it's a function. I'll post the revised code again:
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 ;
There is no NO_DATA_FOUND exception to trap because, as Jonathan
Lewis noted, an ORA-01403 is thrown at the end of a fetch when no more
rows are available:
"...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. "
Look at my first example again and you will find an exception handler for NO_DATA_FOUND but it won't be handled as expected in a function due to Jonathan's explanation. The exception wasn't 'swallowed' by my code, but it won't trap what cannot be trapped and, in a function, that's NO_DATA_FOUND.
David Fitzjarrell Received on Thu Mar 25 2010 - 08:54:21 CDT