Re: SQL Function call does not fail on no_data_found ?
Date: Tue, 23 Mar 2010 14:32:46 -0700 (PDT)
On Mar 23, 12:45 pm, webtourist <webtour..._at_gmail.com> wrote:
> given a function (in package "test") like this:
> FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
> l_ename VARCHAR2(90);
> 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;
> > select test.get_ename(74992) from dual;
> 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
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ; RETURN l_ename ;
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