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 ?

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

Original text of this message