Re: SQL Function call does not fail on no_data_found ?

snip

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

Looks like your question has been addressed for the most part.

I would add that the SELECT INTO is a dangerous construct ... using a cursor is a better approach in PLSQL.

You can then test for %FOUND and/or %NOTFOUND and do logical checking on how many rows or none ... etc. Received on Wed Mar 24 2010 - 09:40:03 CDT