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