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:
>> 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: 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