Skip to Main Content
  • Questions
  • CALL statement ignores NO_DATA_FOUND exception

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, SeongHoon.

Asked: June 13, 2007 - 11:01 pm UTC

Last updated: October 17, 2008 - 7:04 pm UTC

Version: 9.2.0

Viewed 1000+ times

You Asked


CALL statement seems to ignore NO_DATA_FOUND exception.

create or replace procedure raise_no_data_found is
begin   
        raise NO_DATA_FOUND;
end;
/

create or replace procedure raise_other_error is
begin   
        raise_application_error(-20001,'an application error');
end;
/

walldba@WALL/waltz> begin raise_no_data_found; end;
  2  /
begin raise_no_data_found; end;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "WALLDBA.RAISE_NO_DATA_FOUND", line 3
ORA-06512: at line 1

walldba@WALL/waltz> CALL raise_no_data_found();

Call completed.

walldba@WALL/waltz> CALL raise_other_error();
call raise_other_error()
     *
ERROR at line 1:
ORA-20001: an application error
ORA-06512: at "WALLDBA.RAISE_OTHER_ERROR", line 3


Why does it act differently like that?

Needless to say, I could not test all kind of exceptions.
but CALL statement doesn't ignore exceptions that I tested except NO_DATA_FOUND.











and Tom said...

'call' is technically SQL.

a client application, upon receiving NO_DATA_FOUND from SQL says "okey dokey, thanks much, lets move on"

eg:

ops$tkyte%ORA9IR2> create or replace function raise_no_data_found return number
  2  is
  3  begin
  4          raise NO_DATA_FOUND;
  5                  return 0;
  6  end;
  7  /

Function created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace function raise_other_error return number
  2  is
  3  begin
  4          raise_application_error(-20001,'an application error');
  5                  return 0;
  6  end;
  7  /

Function created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select raise_no_data_found from dual;

RAISE_NO_DATA_FOUND
-------------------


ops$tkyte%ORA9IR2> select raise_other_error from dual;
select raise_other_error from dual
       *
ERROR at line 1:
ORA-20001: an application error
ORA-06512: at "OPS$TKYTE.RAISE_OTHER_ERROR", line 4



so, sqlplus (a client application) issues some SQL - select raise_no-data_found from dual - and when it gets NO_DATA_FOUND says "that's cool, thanks, we are done"


so, sqlplus (a client application) issues some SQL - call raise_no-data_found - and when it gets no data found .....



This is SQLPlus saying "no data found is expected, normal, dandy"


So, it is not acting differently, the client application is just saying no data found is perfectly OK from SQL and not an error in this case.

Your client application may work differently.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, I understood it

SeongHoon Park, June 14, 2007 - 10:24 pm UTC

I am using stored procedure mostly for executing a series of DMLs and using CALL or not is not a choice of me but application developers.

So I think that raising an explicit error instead of NO_DATA_FOUND can be a proper workaround for most of our cases.

SQL> create or replace function f_explicit return varchar2 is
l_v number;
begin
begin
select 1 into l_v from dual where 1=0;
exception
when NO_DATA_FOUND then
raise_application_error(-20001,'an explicit error');
end;
/* dmls */
end;
/

Thanks , Tom.




Bug with exceptions propagation in SQL Statement Executor

Sergey Trotsky, September 25, 2007 - 7:57 am UTC

Some of the exceptions are suppressed by SQL Statement Executor. The different exceptions lead to absolutely unpredictable results. Sometimes SQL Statement Executor treats the function return value as NULL, sometimes the rows with the unhandled exceptions magically disappear from the query results...

create or replace function Func1 return integer is
  Res integer;
begin
  -- raise ORA-01403: no data found
  select SID into Res from V$MYSTAT where (ROWNUM=0);
  return(Res);
end Func1;
/

create or replace function Func2 return integer is
  Res integer;
begin
  -- raise ORA-01422: exact fetch returns more then requested number of rows
  select SID into Res from V$MYSTAT;
  return(Res);
end Func2;
/

select ROWNUM, SID from V$SESSION where (SID=Func1)
/

select ROWNUM, Func1 from DUAL
/

select ROWNUM, SID from V$SESSION where (SID=Func2)
/

select ROWNUM, Func2 from DUAL
/


This is the script results:
Function created
 
Function created
 
    ROWNUM        SID
---------- ----------
 
    ROWNUM      FUNC1
---------- ----------
         1 
 
    ROWNUM        SID
---------- ----------
 
    ROWNUM      FUNC2
---------- ----------

There is no complete list of exceptions with unexpected behaviour. I did not find any mentions about this "feature" in documentation. This bug was gotten on Oracle 9.2 and can be reproduced on Oracle 10.2.
Tom Kyte
September 26, 2007 - 9:22 pm UTC

it is entirely the clients fault here - please consider what the client code probably looks like:

open cursor
loop
   fetch
   when no data found is raised... then let us exit
   print output

SQL Statement Executor ignore ORA-01403 (no data found)

Sergey Trotsky, September 27, 2007 - 7:30 am UTC

Thanks! It is looks like a real client fault with ORA-01422 (Exact fetch returns more then requested number of rows). Exception was raised but the client does not display it.

However with ORA-01403 (no data found) the situation is still unclear. SQL Statement Executor returns the rows in spite on unhandled exceptions.

Are there any other exceptions that can be "handled" by SQL Statement Executor?
Where does it documented?
Tom Kyte
September 28, 2007 - 3:07 pm UTC

not sure what you mean in the last bit - not sure what "sql statement executor" is by your definition

Oracle SQL Engine vs Oracle PL/SQL Engine

Sergey Trotsky, October 01, 2007 - 11:49 am UTC

"SQL Statement Executor" = "Oracle SQL Engine"
create or replace function Func1 return integer is
  Res integer;
begin
  -- raise ORA-01403: no data found
  select 1 into Res from DUAL where (0=1);
  return(Res);
end Func1;
/

declare
  Res integer;
begin
  Res := Func1;
end;
/

select RowNum, Func1 from DUAL
/

Oracle PL/SQL Engine returns an exception. OK.

Oracle SQL Engine returns the row. There is no exception. It is a bug!
Tom Kyte
October 03, 2007 - 4:03 pm UTC

sql doesn't throw exceptions - sql can raise errors and no data found is not an error in sql, it is a state of being - no more data.

SQL called from PL/SQL also has this problem...

Devin R, October 15, 2008 - 2:41 pm UTC

Note that using PL/SQL as a "client" also causes this problem. For instance, assuming raise_no_data_found has been modified to return a VARCHAR2:

declare
p_dummy varchar2(200);
begin
select raise_no_data_found into p_dummy from dual;

dbms_output.put_line( 'No error.' );

if p_dummy is null then
dbms_output.put_line( 'Var is null.' );
end if;

exception
when others then
dbms_output.put_line( 'Error: ' || sqlerrm );
end;

The output will be:

No error.
Var is null.

In other words, Oracle's own PL/SQL engine, acting as a SQL
"client", will not throw an error in this case. This seems
somewhat counter-intuitive, considering a usual "empty"
call would throw the NO_DATA_FOUND error from the SELECT
line. Developers should probably be aware of this caveat
of calling functions via SQL within PL/SQL code.
Tom Kyte
October 17, 2008 - 7:04 pm UTC

I'll ask about that particular case, that doesn't look right.

counterintuitive

Duke Ganote, November 29, 2008 - 12:22 pm UTC

The 10gR2 note on NO_DATA_FOUND says "Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query."

I included this in my exception handler:

EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'NO_DATA:FATALITY (just ORA-01403 not guaranteed fatal)');


Mehmet Kaplan, April 15, 2009 - 2:35 pm UTC

Instead of using
call my_procedure();
Using
exec my_procedure();
or
begin
my_procedure;
end;
/
solves the problem in a quick manner.

But still the below example proves that all exceptions do not behave smilar so must be treated as a "bug".


create function raise_no_data_found return varchar2 is
begin
raise no_data_found;
return 'x';
end;
Function created

create function raise_too_many_rows return varchar2 is
begin
raise TOO_MANY_ROWS;
return 'x';
end;
Function created

select nvl(raise_no_data_found, 'aa')
from dual
NVL(RAISE_NO_DATA_FOUND,'AA')
--------------------------------------------------------------------------------
aa
1 row selected


select nvl(raise_TOO_MANY_ROWS, 'x')
from dual
0 rows selected



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library