Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86
From: The Boss on 23 Mar 2010 19:07
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

The description of NO_DATA_FOUND in the PL/SQL User's Guide and Reference
con tains following 'disclaimer':
<q>
Because this exception is used internally by some SQL functions to signal
that they are finished, you should not rely on this exception being
propagated if you raise it within a function that is called as part of a
query.
</q>

Some pointers to discussions on the NO_DATA_FOUND exception:

- Best Practice article "On Exceptions and Rules" by Steven Feuerstein:
http://www.oracle.com/technology/oramag/oracle/08-jul/o48plsql.html
[second half of the article]

- Article "No Data Found: Bug or Feature" on Lewis Cunningham's blog:
http://it.toolbox.com/blogs/oracle-guide/minitip-9-no-data-found-bug-or-feature-15602
[including some interesting comments]

- AskTom thread "NO_DATA_FOUND in Functions":
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10321465390114

- AskTom thread "CALL statement ignores NO_DATA_FOUND exception":
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:317260900346187160

HTH.

--
Jeroen


From: Mladen Gogala on 23 Mar 2010 21:09
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
From: Mladen Gogala on 23 Mar 2010 21:23
On Wed, 24 Mar 2010 01:09:47 +0000, Mladen Gogala wrote:


> So, I have to repeat my question: when does the exception get silently
> converted to NULL?

I read Lewis Cuningham's blog page. Fascinating! I consider this to be a
bug. NO_DATA_FOUND does not get propagated from the function! Wow!



--
http://mgogala.freehostia.com
From: ddf on 24 Mar 2010 09:44
On Mar 23, 5:11 pm, Mladen Gogala <n...(a)email.here.invalid> 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?
>
> --http://mgogala.byethost5.com

Accordoing to Jonathan Lewis it probably shouldn't :

"...queries (that run to completion) end with an ignored 'no data
found' exception; that's how Oracle reports 'no more data' to the
front-end. Try running a simple 'select user from dual' from SQL*Plus
with event 10079 set to level 2 and you'll see text like the following
in the trace file:

328E320 00000000 00000000 00000000 00000000 [................]
328E330 00000000 00000000 00000000 524F1900 [..............OR]
328E340 31302D41 3A333034 206F6E20 61746164 [A-01403: no data]
328E350 756F6620 000A646E [ found..]

Your example still looks like an anomaly, though, as you could expect
a silent return of NO rows from 'select bad_func(1) from dual', rather
than a silent return of a null value. I would guess that this is a
side effect of the requirement for scalar subqueries to result in a
NULL return when they actually find no data. "

It makes sense to me and prety much puts my example in the 'dumper' as
it won't catch the NO_DATA_FOUND exception, either. This one might:

FUNCTION get_ename (p_empno IN NUMBER) RETURN VARCHAR2 IS
l_ename VARCHAR2(90);
v_emp_ct number:=0;
BEGIN
select count(*) into v_emp_ct from emp where empno = p_empno;

if v_emp_ct = 0 then
raise_application_error(-20111, 'No employee found with empno
'||p_empno);
else
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
end if;
RETURN l_ename ;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Data integrity error for empno '||
p_empno||': More than one employee assigned to this empno');
END ;


David Fitzjarrell
From: John Hurley on 24 Mar 2010 10:40
On Mar 23, 12:45 pm, webtourist <webtour...(a)gmail.com> wrote:

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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: oracle 10g disaster recoveries
Next: best tutorial for installing oracle 10 for Solaris 10 x86