Re: SQL Function call does not fail on no_data_found ?
From: ddf <oratune_at_msn.com>
Date: Tue, 23 Mar 2010 14:32:46 -0700 (PDT)
Message-ID: <686dbd01-1ea6-44c0-a9ce-5b866bc72761_at_z1g2000prc.googlegroups.com>
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 ?
Date: Tue, 23 Mar 2010 14:32:46 -0700 (PDT)
Message-ID: <686dbd01-1ea6-44c0-a9ce-5b866bc72761_at_z1g2000prc.googlegroups.com>
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. Oracle did raise the exception but there was nothing coded to properly handle it.
David Fitzjarrell Received on Tue Mar 23 2010 - 16:32:46 CDT