Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Exception in In-Line Function
klabu schrieb:
> This inline function is supposed to error out (no_data_found)
> what is wrong ?
> How do I make the SELECT error out ?
> thanks
> (10gR2)
>
>
>
> SQL> CREATE OR REPLACE
> 2 FUNCTION blah(p_a IN NUMBER) RETURN VARCHAR2
> 3 IS
> 4 l_return VARCHAR2(30);
> 5 BEGIN
> 6 SELECT 'x' || to_char(p_a) INTO l_return
> 7 FROM dual WHERE 1 = 0;
> 8 RETURN l_return;
> 9 EXCEPTION
> 10 WHEN no_data_found THEN
> 11 RAISE ;
> 12 END;
> 13 /
>
> Function created.
>
> Elapsed: 00:00:00.13
> rchin_at_DEV10> select blah('124578') from dual;
>
> BLAH('124578')
> ----------------------------------------------------------------------------------------------------
>
>
> 1 row selected.
>
>
This was recently discussed on oracle-l
http://www.freelists.org/archives/oracle-l/09-2006/msg00820.html if you
are interested.
IMHO this behaviour is incorrect, but it can be easily workarounded by
defining your own exception and raising it instead of NO_DATA_FOUND:
scott_at_ORA102> CREATE OR REPLACE
2 FUNCTION blah(p_a IN NUMBER) RETURN VARCHAR2 3 IS 4 l_return VARCHAR2(30); 5 SQL_NO_DATA_FOUND EXCEPTION; 6 BEGIN 7 SELECT 'x' || to_char(p_a) INTO l_return 8 FROM dual WHERE 1 = 0; 9 RETURN l_return; 10 EXCEPTION 11 WHEN no_data_found THEN 12 RAISE SQL_NO_DATA_FOUND; 13 END;
Function created.
scott_at_ORA102> select blah(12457) from dual; select blah(12457) from dual
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SCOTT.BLAH", line 11 ORA-01403: no data found
BTW - why do you declare a function with a parameter of type number and call it with string ?
Best regards
Maxim Received on Wed Nov 29 2006 - 15:12:38 CST