ora-1403 :no data found

 

在编写select...into...from 的语句中,如果返回的结果集为空，就会出现ora-1403 :no data found，解决方法如下：   

 exception
        when NO_DATA_FOUND etc etc etc.
 end;

 

You Asked (Jump to Tom's latest followup)

Tom,

 

We´ve just migrated from Oracle 9.0.1 to 9.2 and, coincidence or not, I´m facing

a problem I had never faced before.

 

The NO_DATA_FOUND exception is not being raised from my PL/SQL functions

anymore!!! If I put a exception block to handle the excpetion, it´s trapped and

all the instructions in the block are executed. But if I remove the exception

block, no exception is raised to the caller.

 

In procedures, this doesn´t happen.

 

Some examples of this strange behavior follows:

 

create or replace procedure proc_foo is

 l_value varchar2(1);

begin

 select dummy

 into l_value

 from dual

 where dummy = '-'; 

 

 DBMS_OUTPUT.Put_Line(l_value);

end;

/

 

Procedure created.

 

create or replace function func_foo return varchar2 is

 l_value varchar2(1);

begin

 select dummy

 into l_value

 from dual

 where dummy = '-'; 

 

 return l_value;

end;

/

 

Function created.

 

-- This one raises an error

begin

 proc_foo;

end;

/

begin

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "AMESP.PROC_FOO", line 5

ORA-06512: at line 2

 

-- This one does not

select func_foo from dual;

 

 

FUNC_FOO

----------------------------------------------

 

 

Is there some parameter (or anything else) that controls this?

 

Thanks.

 

and we said...

it has ALWAYS been that way

 

tkyte@ORA716> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle7 Server Release 7.1.6.2.0 - Production Release

PL/SQL Release 2.1.6.2.0 - Production

CORE Version 2.3.7.1.0 - Production (LSF Alpha)

TNS for SVR4: Version 2.1.6.0.0 - Production

NLSRTL Version 2.3.6.0.0 - Production

 

tkyte@ORA716> exec proc_foo

begin proc_foo; end;

 

 *

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at "TKYTE.PROC_FOO", line 4

ORA-06512: at line 1

 

 

tkyte@ORA716> select func_foo from dual;

 

no rows selected

 

no data found in a function called from sql just says "ok, no more data, please

stop"

 

this is true for 71, 72, 73, 80, 8ir1, 8ir2, 8ir3, 9ir1, 9ir2....