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.

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.

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

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?

"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!

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.

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)');

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