In the statement for compiling Select... into... from, if the returned result set is null, The ora-1403: No data found will appear, and the solution is as follows:
Exception
When no_data_found 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 = '-'; dBm S_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 errorbegin proc_foo; end;/begin * error at line 1: ORA-01403: No data foundORA-06512: At "amesp. proc_foo ", line 5ora-06512: At Line 2 -- this one does nots Elect 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 implements oracle7 Server Release 7.1.6.2.0-production releasepl/SQL release 2.1.6.2.0-productioncore version 2.3.7.1.0-production (LSF alpha) TNS for svr4: Version 2.1.6.0.0-productionnlsrtl version 2.3.6.0.0-production tkyte @ ora716> exec proc_foobegin proc_foo; end; * error at line 1: ORA-01403: No data foundORA-06512: At "tkyte. proc_foo ", line 4ora-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 ....
