Re: SQL Function call does not fail on no_data_found ?

From: Galen Boyer <galen_boyer_at_yahoo.com>
Date: Wed, 24 Mar 2010 21:52:01 -0400
Message-ID: <uy6hhqibi.fsf_at_www.yahoo.com>



ddf <oratune_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: news_at_netfront.net ---
Received on Wed Mar 24 2010 - 20:52:01 CDT

Original text of this message