Re: Exception in In-Line Function

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 29 Nov 2006 22:12:38 +0100
Message-ID: <ekkt45$7ev$00$1@news.t-online.com>

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;

  14 /

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