SQL Function call does not fail on no_data_found ?
webtourist
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 ?
Michel Cadot
"webtourist" <webto...@gmail.com> a écrit dans le message de news:
b2ae3703-61bc-480e...@g11g2000yqe.googlegroups.com...
Yes, no_data_found is trapped by SQL engine and converted to a NULL value.
Regards
Michel
Mladen Gogala
> 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?
ddf
Where did you handle that exception? Possibly this is how you should
have coded your function:
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 ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee with empno '||p_empno);
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 ;
Then it would fail on NO_DATA_FOUND. Oracle did raise the exception
but there was nothing coded to properly handle it.
David Fitzjarrell
Maxim Demenko
> 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 Boss
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
Mladen Gogala
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.
Mladen Gogala
> 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!
ddf
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
John Hurley
snip
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.
Galen Boyer
How would the above fail? You just swallowed the exception with the
above code.
> Oracle did raise the exception but there was nothing coded to properly
> handle it.
The idea was that without handling the exception, Oracle should have
thrown it.
--
Galen Boyer
--- news://freenews.netfront.net/ - complaints: ne...@netfront.net ---
Galen Boyer
> I would add that the SELECT INTO is a dangerous construct ... using a
> cursor is a better approach in PLSQL.
How is that true? PLSQL over SQL? When is that ever better?
Vladimir M. Zakharychev
> On Mar 23, 12:45 pm, webtourist <webtour...@gmail.com> wrote:
>
>
> 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.
SELECT INTO is not dangerous. It's a single row fetch with all checks
(at least one row, at most one row) embedded and corresponding
exceptions thrown automatically. Saves you a lot of keystrokes.
Equivalent code with explicit cursor would be like this:
DECLARE
CURSOR C1 IS SELECT ... FROM ... WHERE ...;
X C1%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO X;
-- at least one row
IF C1%NOTFOUND THEN RAISE NO_DATA_FOUND; END IF;
-- at most one row
FETCH C1 INTO X;
IF C1%FOUND THEN RAISE TOO_MANY_ROWS; END IF;
-- If the second fetch doesn't find anything, X will still hold
-- the row retrieved by the first fetch, so we can safely proceed
-- with processing it.
<process data here>
-- we won't get here if any of the above conditions are met and
-- exceptions are raised, so we also need to explicitly close the
-- cursor in the exception handler to prevent cursor leak.
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN
-- we might get an exception on OPEN, so we need to check if
-- the cursor is actually opened before attempting to close it,
-- otherwise we'll get INVALID_CURSOR exception inside the
-- exception handler itself.
IF C1%ISOPEN THEN CLOSE C1; END IF;
RAISE; -- bubble the original exception up
END;
The above code more or less covers what single SELECT INTO does. Is it
safer? Shorter? More readable? Name a single reason why we should
prefer explicit cursors to single-fetch implicit cursors please. :)
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
ddf
>
> - Show quoted text -
You're behind on the posts to this thread, Galen. :) I've already
admitted my original code still wouldn't fail even with the exception
handler as it's a function. I'll post the revised code again:
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 ;
There is no NO_DATA_FOUND exception to trap because, as Jonathan
Lewis noted, an ORA-01403 is thrown at the end of a fetch when no more
rows are available:
"...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. "
Look at my first example again and you will find an exception handler
for NO_DATA_FOUND but it won't be handled as expected in a function
due to Jonathan's explanation. The exception wasn't 'swallowed' by my
code, but it won't trap what cannot be trapped and, in a function,
that's NO_DATA_FOUND.
David Fitzjarrell
John Hurley
snip
> How is that true? PLSQL over SQL? When is that ever better?
It was PLSQL all along ...
BEGIN
SELECT ename INTO l_ename FROM emp WHERE empno = p_empno ;
RETURN l_ename ;
END ;
AFAIK the only way to do SELECT INTO in oracle is in PLSQL.
John Hurley
<vladimir.zakharyc...@gmail.com> wrote:
snip
> The above code more or less covers what single SELECT INTO does. Is it
> safer? Shorter? More readable? Name a single reason why we should
> prefer explicit cursors to single-fetch implicit cursors please. :)
An easy reason is something changes somewhere and where you used to
get 1 row ( or zero rows ) now you get more than 1 row.
Anyone ever hear a Developer ever say "But we were supposed to only
get back 1 row here?" ...
Take a look at the code posted by the OP.
Mladen Gogala
> AFAIK the only way to do SELECT INTO in oracle is in PLSQL.
You can do it any 3GL language interface.
Vladimir M. Zakharychev
If this was an expected change then you already know it will happen
and you need to change the code to a cursor loop/bulk fetch. If it's
an unexpected change, TOO_MANY_ROWS thrown on single-row fetch will
immediately alert you of a bug/data corruption and you'll be right on
track fixing it. So no, this is not a valid reason.
As of the OP's code - don't see anything wrong with it. Presumably, he
knows the data model. In particular, he knows that empno is the
primary key so the query can not return more than one row for any
empno. Zero rows can be returned though and NO_DATA_FOUND will be
raised in this case. If the function is called from PL/SQL, this
exception is not handled automatically and bubbles up. If called from
SQL however, this exception is expected by the client and means "no
more data." Now, since the function didn't actually return a value,
NULL (what else?) is assumed as the result.
How would you code this any different with explicit cursor? If you
want an exception to be raised and not ignored by the SQL engine, your
only options are to trap NO_DATA_FOUND in the function itself and
raise an exception that has no special meaning to the SQL engine or
expect NULL returns for "wrong" arguments and treat them accordingly.
But this has nothing to do with implicit vs. explicit cursors.