Re: SQL Function call does not fail on no_data_found ?

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 24 Mar 2010 01:09:47 +0000 (UTC)
Message-ID: <hoboor$j7j$1_at_solani.org>

On Tue, 23 Mar 2010 23:16:54 +0100, Maxim Demenko wrote:

> On 23.03.2010 22:11, Mladen Gogala wrote:

>> On Tue, 23 Mar 2010 18:16:23 +0100, Michel Cadot wrote:
>>
>>
>>> Yes, no_data_found is trapped by SQL engine and converted to a NULL
>>> value.
>>
>> Why is that? It looks like a huge bug to me. Is that documented
>> anywhere?
>>
>>
>>
>>

> Afair, what Michel stated, was always expected behaviour. There is a
> couple of references on MOS on this subject (is kinda of personal
> preferences whether it can be considered as oficially documented,
> however, i'm not sure, maybe it found already its way into Oracle
> manuals)
>
> Note 226211.1
> Note 258653.1
> Bug 893670
> Bug 299941
>
> Best regards
>
> Maxim

First, the exception handler catches the exception:

SQL> declare
  2 v_ename varchar2(10);
  3 begin
  4 select ename into v_ename
  5 from emp where empno=9999;
  6 exception
  7 when NO_DATA_FOUND then
  8 dbms_output.put_line('Exception thrown!');   9 end;
 10 /
Exception thrown!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Let's try without the exception handler:

  1 declare
  2 v_ename varchar2(10);
  3 begin
  4 select ename into v_ename
  5 from emp where empno=9999;
  6 dbms_output.put_line('Ename is:'||v_ename);   7* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Elapsed: 00:00:00.00

So, the exception is thrown, no silent conversions to NULL.

This is the latest and the greatest version: SQL> select * from v$version;

BANNER

---

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production

So, I have to repeat my question: when does the exception get silently converted to NULL? Converting an exception silently to NULL would be an enormous bug. The schema is, of course, everybody's favorite SCOTT schema.

-- 
http://mgogala.freehostia.com

Received on Tue Mar 23 2010 - 20:09:47 CDT