Re: Exception inside function not being propagated to query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Feb 1999 01:26:58 GMT
Message-ID: <36c431ae.1489752@192.86.155.100>

A copy of this was sent to bdg_at_hotmail.com (if that email address didn't require changing) On Wed, 10 Feb 1999 23:38:05 GMT, you wrote:

>I'm probably missing something simple here, but . . .
>
>I have a function with the definition
>
>FUNCTION test_function(p_value NUMBER) RETURN VARCHAR2 IS
>BEGIN
> IF p_value = 1 THEN
> RETURN 'Valid';
> ELSE
> RAISE NO_DATA_FOUND;
> END IF;
>END;
>
>When called from a query, the function doesn't seem to be propagating the
>exception as I would expect it to. For example . . .
>

well it does but in the context of the query, the no_data_found is being interpreted as "NULL".

raise program_error; instead and you'll get:

select test_function(2) from dual

       *
ERROR at line 1:

ORA-06501: PL/SQL: program error
ORA-06512: at "TKYTE.TEST_FUNCTION", line 6
ORA-06512: at line 1




 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Feb 10 1999 - 19:26:58 CST