Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Cefers.br.

Asked: June 10, 2003 - 3:53 pm UTC

Last updated: January 12, 2017 - 3:46 pm UTC

Version: 9.2

Viewed 50K+ times! This question is

You Asked

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....



Rating

  (57 ratings)

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

Comments

Is this behaviour because...

Kamal Kishore, June 10, 2003 - 9:46 pm UTC

Hi Tom,
Is this behaviour because in case of SQL, for ANSI standard, the NO DATA FOUND is not an error condition (rather it is an indicator of so to say, END-OF-FILE or EOF). This just tells the system that what you were trying to read is no more to be found.

In this regard, than it appears that Oracle PL/SQL and SQL behave differently?

SQL, to be ANSI complient, does not treat it as an ERROR whereas PL/SQL (a copyrighted language), treats it as an Error and jumps to the exception block.

Is this assertion correct? Is this the reason why we get an error with PL/SQL block but not with SQL SELECT?
Thanks,


Tom Kyte
June 11, 2003 - 6:33 am UTC


no_data_found is not an error - it is an "exceptional condition". You, the programmer, decide if something is an error by catching the exceptional condition and handling it (making it be "not an error") or ignoring it (making it be an error).

in sql, no data found quite simply means "no data found", stop.


Under the covers, SQL is raising back to the client application "hey buddy -- no_data_found". The client in this case says "ah hah, no data found means 'end of data'" and stops.

Under the covers, PLSQL is raising back to the client application "hey -- no_data_found. The client in this case says "uh-oh, wasn't expecting that from PLSQL -- sql sure, but not PLSQL. Lets print out the text that goes with this exceptional condition and continue on"


You see -- it is all in the way the CLIENT interprets the ORA-xxxxx message. That message, when raised by SQL, is interpreted by the client as "you are done". That message, when raised by PLSQL and not handled by the PLSQL programmer, is on the other hand interpreted as "a bad thing just happened"

Both PLSQL and SQL actually do the same thing here.
It is the CLIENT that is deciding to do something different.

some more details required

Balasubramanian.R, June 11, 2003 - 1:40 am UTC

Hi Tom,
This is something misleading us. I never faced this. Because i always use proper exceptions in functions also. In the same function, if i add no_data_found exception, it throws error. How this is coming ? Also 1 more doubt, as in your reply,
select func_foo from dual will return no rows selected. But if i executed it shows 1 row selected. How is this differs
?

also if i call the function in pl/sql block as below, it throws exception.

declare
l_x varchar2(1) ;
begin
l_x := func_foo ;
end ;

tx,
Balasubramanian.R

Tom Kyte
June 11, 2003 - 7:01 am UTC

see above -- it IS throwing the exeception, the client is CHOOSING to "ignore it" as it is an expected condition from a query, but not from PLSQL

brilliant as usual

Sajid Anwar, June 11, 2003 - 4:15 am UTC

Hi Balasubramanian,

I think TOM's statement clearly states that when you run stored function from SQL you will never get EXCEPTION. He didnt say that you wont get EXCEPTION when you call the function in the PL/SQL block. Am I right TOM?

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

Regards,
Sajid Anwar
London

Tom Kyte
June 11, 2003 - 7:04 am UTC

it is all in how the client interprets the "exception"

The reason for confusion...

Kamal Kishore, June 11, 2003 - 8:39 am UTC

Ho Tom,
I think one of the reason for my earlier statement was because this no data found condition is represented by a different SQLCODE within PL/SQL. The SQLCODE is NOT -1403 but is rather +100 when this condition is encountered within PL/SQL. This difference in the value of SQLCODE is what caused me to think that, yes, there is some difference somewhere, may not be as big, but there seems to be a difference?
Is this thought process correct?

OPS$KKISHORE @ ORA9203.WORLD> set serveroutput on
OPS$KKISHORE @ ORA9203.WORLD> begin
2 raise no_data_found ;
3 exception
4 when others
5 then
6 dbms_output.put_line('Error Code:'||SQLCODE||chr(10)||'Error Text:'||SQLERRM) ;
7 end ;
8 /
Error Code:100
Error Text:ORA-01403: no data found

PL/SQL procedure successfully completed.

OPS$KKISHORE @ ORA9203.WORLD>


Tom Kyte
June 11, 2003 - 6:09 pm UTC

it is that the CLIENT of the SQL statement decided "no data found is not worthy of printing an error message, in fact, we EXPECT to hit no data found, it is normal and healthy"




Oh my...

Cefers.br, June 11, 2003 - 9:00 am UTC

Tom,

I swear I´d never noticed that.

Thanks.

Never noticed this before...

Jon, August 20, 2003 - 7:05 pm UTC

Amazing, in 8 years of Oracle, I'd never come across this until a few days ago - always learning ;-)

2 Questions:
- Where is this documented in the manuals (8.1.7)? I've been looking for this but can't find it.
- As you mentioned above, it depends on how the client handles the exception. When I execute "select func_foo from dual;", is the SQL engine acting as the client of PL/SQL?

Thx.


Tom Kyte
August 21, 2003 - 5:35 pm UTC

o it is not documented cause it is just the way it happens.

when a client recieves the error "no data found", they generally say -- ah hah, end of data, stop printing.

that is just what is happening here. no data found is generally not considered an error but rather an informational message.

It is just unfortunate that SQL queries raise the same no data found normally.


o yes.



It is good for a single query but not for a loop and Procedure

Ram, January 09, 2004 - 9:12 am UTC

I am getting No data found error For the following SQL statement
declare
age_no number(5);
begin
for cursor_fields in (select * from People where name like '%R%')
loop
select age into age_no from PeopleDetails where city = cursor_fields.city
dbms_output.put_line('The city name and age are');
dbms_output.put_line(cursor_fields.city);
dbms_output.put_line(age_no);
end loop;
exception
when NO_DATA_FOUND then
dbms_output.put_line('No data available for this record');
null;
end


My problem is if the Query 'select age .... 'did not find any records then it is throwing the error. 'ORA-01403: no data found'. I am catching that in exception but I want to go back into the loop and continue for the next cursor element in the for loop. And also do I need to modify the 'Select age into age_no.....' Query . Give some suggestions on this . It is very urgent.


Tom Kyte
January 09, 2004 - 9:23 am UTC

you don't have the begin/end blocks in the right place (you are not understanding how scoping in plsql works)

but even worse -- you are doing it all wrong -- totally wrong!!! there should be NO procedural code here, this is a query!


select a.city, b.age_no
from people a, peopleDetails b
where a.name like '%R%'
and a.city = b.city(+);

(if you have cities in people that might not exist in peopleDetails) or simply:

select a.city, b.age_no
from people a, peopleDetails b
where a.name like '%R%'
and a.city = b.city;

if every city in people is represented in peopleDetails.

but -- to do it the wrong way, you would have to code this:

begin
for x in ( select * from t )
loop
BEGIN
select ... into .... from .... where ....;
process selected into record
EXCEPTION
when no_data_found then
whatever you want to do when no data selected into
END;
end loop;
end;


What is happening in your code is that as soon as you hit "no data found", you jump OUT of the loop into the exception block. You need to have the block IN the loop if you want to continue processing.

Exception handling is all fully documented in the PLSQL manuals but in this case, you do not even want to consider that -- you just want "an outer join" and be done with it. don't write code to join.

databases were BORN to join, it is what they do best



I am new to Oracle right now. I have written code like what you have said. But My view is different

Ram, January 09, 2004 - 11:27 am UTC

Tom,
I did not ask proper question but it was some helpful to me.
I have written code like what you have said before only. But my intention is to verify my view. I caught the exception in the inner block only. I have another problem here.

select name into l_name from people where city ='xxx';

If the value 'xxx' does not exist in the database then it is throwing the exception NO_DATA_FOUND. Should we change the query in order to avoid this exception. BEcause in general sql query like
select name from people where city = 'xxx'; if the record is not found it does not throw exception . How to solve this problem.



Tom Kyte
January 09, 2004 - 1:51 pm UTC

select INTO says "at least one row, at most one row"

Use the select into -- and then:

a) IF you are expecting not to find data for some rows AND this is OK, catch the no_data_found and set default values for the into variables

b) IF you are NOT expecting "no data found" don't use an exception block at all -- this is an error, it must be propagated up and out of your code to the caller -- just like an "out of space" error on an insert would be.

forcing an exception

dxl, May 26, 2004 - 4:50 am UTC

Tom

The problem that i have is i have used an insert select * from to collect and process records. Within the insert i have a function that does some processing.
I have come across this behaviour that if the sql query within the function doesn't return any data it just returns a null to the select statement the function is used in, ie:

insert into ..
select ..
..
..
f_my_function(...),
..
from my_table;

So obviously i can force the function to return something by catching any known exceptions within the function code but what about unknown errors?? How will i know if there is a bug in the function?

Just like if you used a WHEN OTHERS any unexpected error is ignored, and this will exhibit the same behaviour, i wont be able to tell when my function has returned any unexpected error. Is there any way around this or should i just be coding it differently??


Tom Kyte
May 26, 2004 - 8:18 am UTC

I'm not following you at all.


if your function is "in error", your function would raise an error (exception). that would stop it dead in its tracks, the insert would be as if it never happened, the caller definitely knows "something went wrong"

if your function returns NULL, so be it, NULL is not indicative of anything "wrong", NULL is a 100% legitimate value.

so, not really sure what the issue is?

dxl, May 26, 2004 - 12:07 pm UTC

Tom

Below illustrates the problem i'm getting:

16:36:00 OPT1@OP1>drop table t1;

Table dropped.

Elapsed: 00:00:00.00
16:36:00 OPT1@OP1>drop table t2;

Table dropped.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>create table t1 (c1 number(10));

Table created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>create table t2 (c2 number(10));

Table created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>insert into t1 values (1);

1 row created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>insert into t1 values (2);

1 row created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>create or replace function f1(p1 in number)
16:36:01 2 return number
16:36:01 3
16:36:01 4 as
16:36:01 5
16:36:01 6 v1 number(10);
16:36:01 7
16:36:01 8 begin
16:36:01 9
16:36:01 10 select c1
16:36:01 11 into v1
16:36:01 12 from t1
16:36:01 13 where c1 = p1;
16:36:01 14
16:36:01 15 return v1;
16:36:01 16
16:36:01 17
16:36:01 18
16:36:01 19 end f1;
16:36:01 20 /

Function created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>insert into t2
16:36:01 2 select f1(1)
16:36:01 3 from dual;

1 row created.

Elapsed: 00:00:00.00
16:36:01 OPT1@OP1>
16:36:01 OPT1@OP1>select * from t2;

C2
----------
1

1 row selected.

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>insert into t2
16:36:02 2 select f1(3)
16:36:02 3 from dual;

1 row created.

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>select * from t2;

C2
----------
1


2 rows selected.

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>
16:36:02 OPT1@OP1>set autoprint on
16:36:02 OPT1@OP1>variable b1 number
16:36:02 OPT1@OP1>exec :b1 := f1(1);

PL/SQL procedure successfully completed.


B1
----------
1

Elapsed: 00:00:00.00
16:36:02 OPT1@OP1>variable b2 number
16:36:02 OPT1@OP1>exec :b2 := f1(3);
BEGIN :b2 := f1(3); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "OPT1.F1", line 10
ORA-06512: at line 1



B2
----------


Elapsed: 00:00:00.01
16:36:03 OPT1@OP1>

You can see that the function doesn't fall over when used in a sql statement but will if executed from sqlplus into a bind variable.

So you are right the function will just return null to the calling sql, but i would also like to know if the function is falling over and thus returning the null, so that the insert does not insert nulls into my table.
i want the fact that it has fallen over be known to the sql statment using the function so that the whole insert fails.

Hope this is clearer.

Thanks

Tom Kyte
May 26, 2004 - 3:35 pm UTC

NO_DATA_FOUND isn't an error in the context of sql you see -- the error is in fact getting propagated back to the client -- (sqlplus in this case). the client is getting 1403 -- no data found. The client is saying "oh, no problem, got a query here, 1403 in the context of a query isn't a failure!"




dxl, May 27, 2004 - 5:25 am UTC

ok then, so how can i tell if it has fallen over?! The only way i can think of is to catch the exception in the function and log the error?

Tom Kyte
May 27, 2004 - 9:24 am UTC

say the function returns null. what does the 3rd party do? pick one of the following


a) fail
b) ignores error and continues
c) logs error and continues


if it is a), there is nothing you can do. You must *fail*

if it is b), you need to find another vendor

if it is c), you can do what you like


if you silently catch the error and log it in your function and return NULL and they just go on their happy way -- you have just killed data integrity in that vendors application. you cannot do that.

dxl, May 27, 2004 - 10:44 am UTC

the client is sql. I am calling the function within a sql statement and inserting into a table.

procedure myproc
..

execute immediate
'INSERT INTO mytable
select col1, myFunction(col2), col3, CASE col4 etc..
from myOtherTable';
..

end myproc;

This is executed from a stored procedure (using execute immediate since I also am using a CASE expression).

At the moment if the function falls over and returns no_Data_found to the sql, then the sql just says fine no more data for that row and carries on with the rest of the insert -- that is bad, i don't want it to do that.

If the function falls over and returns no_data_found i want the whole insert to fail and an error to be returned to whichever client is calling the procedure. This is what i can't work out how to do. The sql just carries on as if nothing has happened. The sql acts like it gets a null returned from the function and thats what is inserted into myTable.

Please tell me how i can stop the insert from completing.


Tom Kyte
May 27, 2004 - 8:14 pm UTC

exception
when no_data_found then
raise program_error;
end;

in the function

step back and re-think the whole thing ...

Gabe, May 27, 2004 - 1:27 pm UTC

Hey dxl ... <quote>ok then, so how can i tell if it has fallen over?!</quote> ... take it out of the SQL and code for it.

An exception (any) won't propagate out when executing the function from SQL ... there is no way around it. So rather than insisting on it just think of something else.

If you just want not to insert when your function returns a not null value then something like this would do:

SQL> insert into t2
  2  select t.c from
  3  (
  4  select f1(1) c from dual union all
  5  select f1(2) c from dual union all
  6  select f1(3) c from dual union all
  7  select f1(5) c from dual
  8  ) t
  9  where t.c is not null
 10  ;

2 rows created.

<quote>how i can stop the insert from completing?</quote>
Do not perform the insert at all when detecting the _problem_ ... hence you may need to split the operation into multiple steps.

It is a bit theoretical right now ... why don't you post your specific, complete problem ... you may find you don't need the pl/sql function at all (and still do the whole thing in one step) ... you may also find you don't need the dynamic sql either (the fact that you use a CASE in there is not, in itself, a justification for abandoning the static SQL).

I mean for the T1, T2 test case you've shown, one could:
create table t1 (c1 number(10) not null primary key);
create table t2 (c2 number(10) not null primary key, constraint fk foreign key (c2) references t1(c1));
and hence ...

SQL> insert into t2 select f1(3) from dual;
insert into t2 select f1(3) from dual
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."T2"."C2")

The point is, one cannot really extrapolate a solution for your real problem based on this test case.

As for ...
execute immediate
'INSERT INTO mytable
select col1, myFunction(col2), col3, CASE col4 etc..
from myOtherTable';

maybe you could have a view over mytable with an instead of trigger to compute the result of myFunction(:new.col2) and raise_application_error whenever whatever error ... again, one would need a real problem to suggest a _real_ solution.

Cheers.
 

Can't you simply catch the no_data_found and raise another error

Gary, May 27, 2004 - 7:39 pm UTC

create table t1 (c1 number(10));

create or replace function f1 return number
as
v1 number(10);
begin
select 10
into v1
from dual
where 1=2;
return v1;
exception
when no_data_found then
raise_application_error(-20001,
'f1 failed with no data found');
end f1;

select f1 from dual;

Without the exception handler, this returns null. With the exception handler, you get the error.
You only need to check for the NO_DATA_FOUND, as this is an exceptional exception. All other exceptions will cause the select invoking the function to fail (and also cause the failure of an INSERT/CREATE TABLE AS... using the select)


not in my tests ...

Gabe, May 27, 2004 - 11:39 pm UTC

Gary,

no_data_found or raise_application_error or any other exception in f1 will not propagate out when f1 is called from a sql statement ... your 'select f1 from dual' will return one row back with null in it ... no exception.



Tom Kyte
May 28, 2004 - 8:03 am UTC

ops$tkyte@ORA9IR2> create or replace function f  return number
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select f from dual;
select f from dual
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.F", line 4

ops$tkyte@ORA9IR2> create or replace function f  return number
  2  as
  3  begin
  4          raise_application_error( -20001, 'Not in my experience' );
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA9IR2> select f from dual;
select f from dual
       *
ERROR at line 1:
ORA-20001: Not in my experience
ORA-06512: at "OPS$TKYTE.F", line 4
 

<b>and using the way back machine:</b>

tkyte@DEV716> create or replace function f  return number
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /
 
Function created.
 
tkyte@DEV716>
tkyte@DEV716> select f from dual;
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
no rows selected
 
tkyte@DEV716>
tkyte@DEV716> create or replace function f  return number
  2  as
  3  begin
  4          raise_application_error( -20001, 'Not in my experience' );
  5  end;
  6  /
 
Function created.
 
tkyte@DEV716> select f from dual;
select f from dual
       *
ERROR at line 1:
ORA-06571: Function F does not guarantee not to update database
 


ops$tkyte@ORA734> create or replace function f  return number
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA734>
ops$tkyte@ORA734> select f from dual;
ERROR:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
no rows selected
 
ops$tkyte@ORA734>
ops$tkyte@ORA734> create or replace function f  return number
  2  as
  3  begin
  4          raise_application_error( -20001, 'Not in my experience' );
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA734> select f from dual;
ERROR:
ORA-20001: Not in my experience
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
no rows selected


ops$tkyte@ORA806> @test
 
Function created.
 
select f from dual
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 
 
 
Function created.
 
select f from dual
       *
ERROR at line 1:
ORA-20001: Not in my experience
ORA-06512: at "OPS$TKYTE.F", line 4
ORA-06512: at line 1
 

<b>It has always worked that way....</b>
 

sorry i thought i'd tried that!

dxl, May 28, 2004 - 4:43 am UTC

Tom

Thanks, re raising the exception worked fine. For some reason i thought i'd already tried that but obviously i didn't! I guess i kinda assumed that if it wasn't propagating the no data found 1403 error then it wouldn't propagate any other when called from a sql statement. This is of course wrong! No wonder you couldn't see what my problem was!

-- to reviewer Gabe : Thanks for your input and time taken to look at this however i'm afraid you're tests aren't quite correct:

" ...An exception (any) won't propagate out when executing the function from SQL ... there is no way around it..."

This is not true see below:

09:04:06 OPT1@OP1>create table t1 (c1 number(10));

Table created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>create table t2 (c2 number(10));

Table created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>
09:04:06 OPT1@OP1>insert into t1 values (1);

1 row created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>
09:04:06 OPT1@OP1>insert into t1 values (2);

1 row created.

Elapsed: 00:00:00.00
09:04:06 OPT1@OP1>
09:04:06 OPT1@OP1>create or replace function f1(p1 in number)
09:04:06 2 return number
09:04:06 3
09:04:06 4 as
09:04:06 5
09:04:06 6 v1 number(10);
09:04:06 7
09:04:06 8 begin
09:04:06 9
09:04:06 10 select c1
09:04:06 11 into v1
09:04:06 12 from t1
09:04:06 13 where c1 = p1;
09:04:06 14
09:04:06 15 return v1;
09:04:06 16
09:04:06 17 exception
09:04:06 18 when no_data_found then
09:04:06 19 raise program_error;
09:04:06 20
09:04:06 21 end f1;
09:04:06 22 /

Function created.

Elapsed: 00:00:00.01
09:04:07 OPT1@OP1>
09:04:07 OPT1@OP1>insert into t2
09:04:07 2 select f1(1)
09:04:07 3 from dual;

1 row created.

Elapsed: 00:00:00.00
09:04:07 OPT1@OP1>
09:04:07 OPT1@OP1>select * from t2;

C2
----------
1

1 row selected.

Elapsed: 00:00:00.00
09:04:07 OPT1@OP1>insert into t2
09:04:07 2 select f1(3)
09:04:07 3 from dual;
select f1(3)
*
ERROR at line 2:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPT1.F1", line 19
ORA-01403: no data found
ORA-06512: at line 1


The program error exception will be re raised it was only the no data found that sql doesn't see as an error.

Also the reason i only posted a test case and not the complete real problem is that my real problem is horrendously complicated (I really did need to use CASE, couldn't see any other way around it except for some hideous DECODES) therefore having used this site for some time i knew TOM would not appreciate seeing my tables with no data and horrible sql, so i posted a very simple runnable problem. -- thanks anyway though!

(Gary from sydney -- cheers you were spot on!)

Back to basics for me ... test, test, test!!!

Gabe, May 28, 2004 - 10:15 am UTC

Didn't have access to a database when I sent that reply ... seriously thought I tried that before. Guess there is something to learn from embarrassing blunders as well.

Thanks Tom.

No data or done fetching?

Alex, January 18, 2005 - 4:55 pm UTC

Tom,

I'm getting this error while tracing through a function in a package via a procedure editor. I've been looking up this message and I'm confused by my findings. This thread leads me to believe it is a self-explanitory message, meaning "there's no data for the conditions you specified".
The oracle error messages and metalink seem to give me the impression however that it is the result of the end of records being fetched. Could you clarify this? In my case, I know there's data because I can run the select that loads the cursor that gets results. The no data found occurs when attempting to open the cursor. The code looks like this:

FUNCTION GET_INFO (pParameter IN NUMBER) RETURN VARCHAR2

IS
vPATS VARCHAR2(48);

CURSOR cPATS IS
SELECT
col1
FROM
tab1
WHERE
id = pParameter;

BEGIN
OPEN cPATS;<-----NO_DATA FOUND Occurs here
FETCH
cPATS
INTO
vPATS;
CLOSE cPATS;

RETURN vPATS;
END;
Metalink article is this :</code> http://metalink.oracle.com/metalink/plsql/ml2_gui.startup <code>
Thanks a ton.

Tom Kyte
January 19, 2005 - 10:09 am UTC

no_data_found will not occur there. You would have to give us a complete test case and a cut and paste from sqlplus to convince me otherwise.



Bad example

A reader, January 19, 2005 - 10:50 am UTC

I thought so too, that's what I needed to hear.
I've since realized the code is fine and TOAD's procedure editor is the reason for this bizarre occurrence I ran the select with the function call (without tracing) and it worked fine.
I'm starting to see why a lot of people detest GUIs when I hit stupid misleading errors like this. Thanks for the clarification.


Alternative to select into

Alex, January 20, 2005 - 4:36 pm UTC

Tom,

Would you mind giving an example of using implicit cursors for a singleton select, something simple like:

select col1
into var1
from t
where col1 = 5;



Tom Kyte
January 20, 2005 - 7:37 pm UTC

you just did, thanks....

that is an implicit cursor.



Example

Alex, January 21, 2005 - 9:31 am UTC

Ok here's what I don't understand. I asked because of what I found in 'Beginning Oracle Programming', that has an example like the one I posted, in a pl/sql block that says:

"Due to the errors that may be encountered, people are using singleton SELECTs less and less these days, preferring to use implicit cursors instead. There is no need to declare local variables to hold values selected in an implicit cursor and there is no need to do exception handling for the cases where the query returns more or less than one row."

I like the sound of this, so I wanted to see an example so I knew for sure how to do it. The statement in book example is this:

set serverout on
declare
l_empno emp.empno%type;
l_ename emp.ename%type;
begin
select empno, ename
into l_empno, l_ename
from emp
where rownum = 1;
dbms_output.putline(l_empno||';'||l_name);
end;
Thanks for taking the time for us newbies too :)

Tom Kyte
January 21, 2005 - 12:11 pm UTC

The example in the book is an example of an implicit cursor.... I confused?


An explicit cursor would be like this:

declare
cursor c is select empno, ename from emp;
begin
open c;
fetch c into ...;
close c;


the implicit cursor is the select into

I'm confused too

Alex, January 21, 2005 - 1:10 pm UTC

The title of the section is "Singleton selects". I'd never heard that before I don't know what that means. As far as the example in the book goes, it looks like a bad one for either an implicit or explicit cursor. If implicit, then they didn't need to declare the variables l_empno and l_ename. If explicit, then it should look like your example.

In any event I understand the difference. This was on page 384-385 if you can track down a copy and make any sense out of it.

Tom Kyte
January 21, 2005 - 7:29 pm UTC

if implicit, you certainly did need to declare l_empno, l_ename.

(track down a copy.... my face is on the cover, i might have one or two lying about.... :)


"singleton select" was "i want to fetch at least one and at most one row"

Another non-exception exception: ORA-01422 Exact fetch returns more than requested number of rows

Kerry, January 21, 2005 - 6:55 pm UTC

create or replace function f return integer
as
z integer;
begin
select rownum into z from all_tables where rownum < 3;
return z;
end;

select f from dual;

This returns no data at all. No exception, no data, unlike the NO_DATA_FOUND case where a null row is returned. We've encountered both of these in obscure cases where the code otherwise looked sound. Hard to debug when no exception is propagated. It's job security to know all these caveats, though.

BTW, Change the constant "3" above to the value "1" and you'll see the NO_DATA_FOUND case returns a NULL row.

Disregard that -- It's PL/SQL Developer ignoring the error

Kerry, January 21, 2005 - 6:58 pm UTC

Oops. Disregard that. This behavior is not reproduceable in Sql*Plus: in this case it is definitely the client I'm using, PL/SQL Developer.

Sorry.

FYI

Alex, January 24, 2005 - 9:17 am UTC

I'm crazy I wasn't thinking went I thought you didn't have to declare variables. I got mixed up because Oracle takes care of all the cursor related stuff.

Also just FYI, this was in Beginning Oracle Programming, not Expert One on One (maybe your face was on BOP too at one point but not on my copy :)). I already incorrectly assumed you had one of those kicking around on a different post. After all, why would you, it's a beginner's book.

Tom Kyte
January 24, 2005 - 11:10 am UTC

I worked on Beginning Oracle Programing as well.

NULL ROW or "no rows selected" ?

Matthias Rogel, February 07, 2005 - 9:02 am UTC

Hallo Tom,

I am confused.

please have a look at your very first answer in this thread.
Has something changed in 9.2.0.5 ?

<QUOTE>
tkyte@ORA716> select func_foo from dual;

no rows selected
</QUOTE>

I try to reproduce this behaviour, however, I GET A NULL ROW
INSTEAD OF NO ROW (!?)


C:\>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on Mo Feb 7 14:59:10 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Benutzernamen eingeben: ....
Kennwort eingeben:

Verbunden mit:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> create or replace function func_foo return varchar2 is
  2  begin
  3     raise no_data_found;
  4  end;
  5  /

Funktion wurde erstellt.

SQL> set null nada
SQL> select func_foo from dual;

FUNC_FOO
--------------------------------------------------------------------------------
nada

SQL> set feedb on
SQL> /

FUNC_FOO
--------------------------------------------------------------------------------
nada

1 Zeile wurde ausgewõhlt.

SQL> exit
Verbindung zu Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production beendet




What is wrong here ?
What do I miss ?

Thank you 

Tom Kyte
February 07, 2005 - 9:13 am UTC

the silent "ignore it" is still there (no data found in a function never raised an error from the sql) -- this is exactly what the original poster posted above?


no

Matthias Rogel, February 07, 2005 - 10:30 am UTC

sorry,

you got

tkyte@ORA716> select func_foo from dual;

no rows selected

(7.1.6.2.0)


,
I get
SQL> select func_foo from dual;

FUNC_FOO
--------------------------------------------------------------------------------
nada

1 Zeile wurde ausgewõhlt.

REM "1 row selected " in English

I didn't expect do get an exception, I expect to get
no row instead of one row consistincg of nulls. 

Tom Kyte
February 07, 2005 - 11:04 am UTC

the original poster was stating:

<quote>
The NO_DATA_FOUND exception is not being raised from my PL/SQL functions
anymore!!!
</quote>

I pointed out -- it never got raised. but if you look at YOUR cut and paste above and theirs, you'll see a striking similarity -- you exactly reproduced stuff that was here.

how to raise a no_data_found inside function

A reader, March 29, 2005 - 11:43 am UTC

Hi I have a package which contains one function and one procedure, the function is called by procedure similar to this:

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
exception
when NO_DATA_FOUND then
dbms_output.put_line ('SQLERRM: ' || sqlerrm);
when others then
dbms_output.put_line ('SQLERRM: ' || sqlerrm);
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
end;
begin
dbms_output.put_line('HIHIHI');
end;
/


here if NO_DATA_FOUND occurs the process should stop but it doesnt. Should I add raise; after dbms_output.put_line ('SQLERRM: ' || sqlerrm); ? Or shall I convert the function into a procedure with an out parameter (l_date)

Just curiousity, i added dbms_output.put_line('HIHIHI'); inthe body but that doesnt do anything right? Nonsense?

thanks

Tom Kyte
March 29, 2005 - 12:28 pm UTC

the when others should be DELETED, it can only be used to ignore errors, remove it!


Is the NO-data-found an error that should terminate this? then just remove it too.

to te reader

A reader, March 30, 2005 - 3:53 am UTC

hi your code should be smth like this

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
exception
when NO_DATA_FOUND then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
raise;
when others then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
raise;
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
end;
begin
dbms_output.put_line('HIHIHI');
end;
/

add the raise statement, this way with dbms_output you know where in your package failed and with raise you stop the processing, the caller should not handle your function exceptions in this case

or there is another approach:

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
exception
when NO_DATA_FOUND then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
return NULL;
when others then
dbms_output.put_line ('(f1) SQLERRM: ' || sqlerrm);
raise;
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
if ( l_fecha_trata is null ) then
raise NO_DATA_FOUND;
end if;
exception
when NO_DATA_FOUND etc etc etc.
end;
begin
dbms_output.put_line('HIHIHI');
end;
/

although I personally prefer the first approach since you know where fails, in this small piece of code it´s easy to see the point where the failure occurs if the second approach is used, but if you have a huge package with recursive functions calls then it´s hard to handle no_data_found exception if there are several functions which can cause the exception

I think the best is handle the exception at lower level possible, at least write to a log table then reraise to outer block if you wanna stop processing

Tom Kyte
March 30, 2005 - 7:23 am UTC

the code should just be:

create or replace package body
is
function f1(p_tab varchar2(30))
return date
as
l_date date;
begin
select fec
into l_date
from tmov
where tabname = p_tab;
return l_date;
end;

procedure pr_tmm_contrato_contrato
as
l_fecha_trata date;
begin
l_fecha_trata := f1('T1');
end;
begin
dbms_output.put_line('HIHIHI');
end;
/

period -- achieves the goal, cannot hide an error, and you still get to see the error message in plus.

Here "when others" is an exception you cannot deal with, there is no reason to catch it.

Here "when no data found" is another exception that you cannot deal with, cannot fix -- no reason to catch it.



catch the error at lower level

A reader, March 30, 2005 - 8:19 am UTC

Hi

You are right, there is no point to catch those errors. The point I try to make is if you catch the error at lower level you can know exactly where in the program failed. In this example the code is simple, if the code is huge and complex if we dont catch the exceptions at low level it´s will be hard to debug.

Say we have this code


select 1
into x
from t1
where rownum < 2;

select 1
into y
from t2
where rownum < 2;

select 1
into z
from t3
where rownum < 2;

exception
when no_data_found ......

if we use a generic exception handling at end of the code then we wouldnt be able to know which query failed



Tom Kyte
March 30, 2005 - 9:13 am UTC

but if none of the queries were expected to fail, I would still say "catch it at the TOP level", we shouldn't be catching it there.

Else, we don't know how we got there, if you let the top level do it, you get the call stack.

log errors

A reader, March 30, 2005 - 10:02 am UTC

Hi

Say we have this code

declare
procedure p1
as
x number;
begin
select 1
into x
from emp
where empno = 7369;
exception
when no_data_found then
log_error('p1');
raise;
end;
procedure p2
as
x number;
begin
select 1
into x
from emp
where empno = 1;
exception
when no_data_found then
log_error('p2');
raise;
end;
procedure p3
as
x number;
begin
select 1
into x
from emp
where empno = 7521;
exception
when no_data_found then
log_error('p3');
raise;
end;
procedure p4
as
x number;
begin
select 1
into x
from emp
where empno = 3;
exception
when no_data_found then
log_error('p4');
raise;
end;
begin
p1;
p2;
p3;
p4;
end;
/


We want to log the errors in a table so in this case it makes sense to catch the exception at lower level no? Otherwise how can we know where the procedure failed (in 10g we can use back trace but not before...). Although these queries arent expected to fail noone can ever ensure that it wont fail for any reason.

If we didnt use exceptions we see messages like this

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 15
ORA-06512: at line 40

We then have to check the whole procedure and see which procedure/function caused this error.

We do this in PRO*C, always log the error to a table with the function name which caused the error.

This is no good... ?



Tom Kyte
March 30, 2005 - 10:25 am UTC

(at top level, the client)....

raised error being ignored

A reader, December 28, 2006 - 8:11 am UTC

Here is a test case where even a raised error is being
ignored.

Happened in a real-life situation. Here is a dummy function
to simulate the case. The function raises an exception when
passed in '1' and not when passed in '2'.

create or replace function myfunc(i number) return number
is
vii number;
begin

if i = 1 then -- force error: many values into one var
select customerid into vii from customer where customerid like '1%';
end if;

return i;

end;


Test the error is raised:

SQL> select myfunc(1) from dual;
select myfunc(1) from dual
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows


Great but now look:

SQL> select myfunc(x) from (
2 select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

The error raised in the function is ignored (!?!)

Tom Kyte
December 28, 2006 - 9:50 am UTC

i cannot run your test case. it is incomplete.

Just try with UNION instead of UNION ALL

A reader, December 28, 2006 - 1:45 pm UTC

Just replace UNION ALL with UNION and see the result. You will be surprised :)

A reader, December 29, 2006 - 6:19 am UTC

Here's a more generic test case.
(I could repeat on 9.2.0.2.1 and 10.2.0.3.0)


SQL> create or replace function myfunc(i number) return number
2 is
3 vii number;
4 begin
5
6 if i = 1 then -- force error: many values into one var
7 select object_name into vii from user_objects;
8 end if;
9
10 return i;
11
12 end;
13 /

Function created.

SQL>
SQL>
SQL> select myfunc(1) from dual;
select myfunc(1) from dual
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "ADMIN.MYFUNC", line 7



SQL> select myfunc(x) from (
2 select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

What happened to that error ????
Tom Kyte
December 29, 2006 - 9:53 am UTC

ops$tkyte%ORA10GR2> create or replace function myfunc(i number) return number
  2  is
  3  vii number;
  4  begin
  5  if i = 1 then -- force error: many values into one var
  6  select object_name into vii from user_objects;
  7  end if;
  8   return i;
  9  end;
 10  /

Function created.

ops$tkyte%ORA10GR2> select myfunc(1) from dual;
select myfunc(1) from dual
       *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6


ops$tkyte%ORA10GR2> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);
ERROR:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6



no rows selected



ops$tkyte%ORA10GR2> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production




ops$tkyte%ORA9IR2> select myfunc(1) from dual;
select myfunc(1) from dual
       *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6


ops$tkyte%ORA9IR2> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);
ERROR:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.MYFUNC", line 6



ops$tkyte%ORA9IR2> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production






How strange

A reader, January 02, 2007 - 5:56 am UTC

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create or replace function myfunc(i number) return number
2 is
3 vii number;
4 begin
5
6 if i = 1 then -- force error: many values into one var
7 select object_name into vii from user_objects;
8 end if;
9
10 return i;
11
12 end;
13 /

Function created.

SQL> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

SQL>

*************

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production
PL/SQL Release 9.2.0.2.1 - Production
CORE 9.2.0.2.0 Production
TNS for 32-bit Windows: Version 9.2.0.2.0 - Production
NLSRTL Version 9.2.0.2.0 - Production

SQL> create or replace function myfunc(i number) return number
2 is
3 vii number;
4 begin
5
6 if i = 1 then -- force error: many values into one var
7 select object_name into vii from user_objects;
8 end if;
9
10 return i;
11
12 end;
13 /

Function created.

SQL> select myfunc(x) from (select 2 x from dual union all select 1 x from dual);

MYFUNC(X)
----------
2

SQL>


Would it be reasonable to refer to the behaviour I'm seeing in 10.2.0.3.0 and 9.2.0.2.1 as Oracle bugs ? Any other possible explanation ?
Tom Kyte
January 02, 2007 - 8:07 am UTC

i wonder - what sqlplus version are you using?

SQLPlus ?

A reader, January 02, 2007 - 8:27 am UTC

Ah!, I get different results with different versions of SQLPlus.

The results posted earlier were with SQLPlus Release 9.2.0.1.0.

When I repeat the test with SQLPlus Release 10.1.0.5.0 I get the same behaviour as you.

We'll be upgrading away from the older version of SQLPlus shortly but as far as lessons learned go, is this a SQLPlus bug ?
Tom Kyte
January 04, 2007 - 9:33 am UTC

it is a bug with dual - try some other one row table of your own creation. The database "knows" too much about dual sometimes.

Any other exceptions silently swallowed...?

Dawn, April 23, 2007 - 5:49 am UTC

Hi Tom,

I've tried searching, but can't find anything that answers my query. I know that if you're calling a function from within SQL and the function returns the no_data_found exception, then SQL doesn't consider that to be an exceptional situation, and no error is raised.

However, I need to know whether any other exceptions that SQL will handle in the same way, or if it's just no_data_found that is handled like that.

Can you help?

Thanks in advance!
Tom Kyte
April 23, 2007 - 4:34 pm UTC

It is not that it is "silently handled", it is that the client absolutely gets this condition raised to them. And the client decided what to do.

So, what would your client do with any other exception?

A reader, April 24, 2007 - 4:42 am UTC

Yes, I get that SQL is the client in question here, and I get that it handles some of the exceptions that may be passed to it - so far, I've identified no_data_found and too_many_rows (which surprised me!) as errors that are handled by the SQL engine/client.

But I need to know if these are the only two errors that get handled by SQL, or whether there are others. (I'm trying to implement error handling within a function that gets called from an "insert as select... from function" statement. Unfortunately, no_data_found is one of those errors, but hey ho!)
Tom Kyte
April 24, 2007 - 11:14 am UTC

too_many_rows is not that way??

no_data_found is about it. everything else in SQL is "an error"

ops$tkyte%ORA10GR2> create or replace function f return numTabType
  2  pipelined
  3  as
  4          l_x   varchar2(1);
  5  begin
  6          select * into l_x
  7            from (select * from dual union all select * from dual);
  8
  9          pipe row(1);
 10          return;
 11  exception
 12          when too_many_rows
 13          then
 14                  dbms_output.put_line( 'too many rows...' );
 15                  RAISE;
 16  end;
 17  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t ( x number );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select * from table(f);<b>
too many rows...</b>
select * from table(f)
                    *
ERROR at line 2:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "OPS$TKYTE.F", line 15



Aha!

Dawn, April 25, 2007 - 4:40 am UTC

After further tests, it was a TOADism that caused the too_many_rows to be handled (but only in the select function from dual format - select * from table(cast(function as whatever)) worked - well, errored - as expected!)

That'll teach me not to test first in SQLPlus!

Thanks very much for your help with this.

Good article

Albert Nelson A, September 07, 2007 - 4:19 am UTC


Error code for No Data found

Praba, September 03, 2008 - 7:41 am UTC

We have recently migrated to Oracle 10g (v. 10.2.0.3.0)

after the migration, One of the Pro*C executables started giving No Data found errors.

Condition used to check the fatal errors is sqlca.sqlcode < 0. if this condition is satisfied, exe prints the error message and exits gracefully.
"No data found" error can be ignored and process can be continued. so, this error checking was working just fine.

But now, in some cases, we are getting "negative" 1403 error for no data found; thus forcing the exe to exit.

Could you tell us in which case SQL raises negative error for no data found? we are not able to find the exact business scenario. All the oracle documents i have read, say "No data found" is raised with a positive error code (either +100 or +1403).

Thanks in advance.

Tom Kyte
September 03, 2008 - 11:55 am UTC

10.2.0.3...

code:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    varchar username[35];
EXEC SQL END DECLARE SECTION;

    exec sql declare c cursor for
    select username from all_users where 1=0;
    exec sql open c;
    sqlca.sqlcode = 0;
    EXEC SQL FETCH c INTO :username;
    printf( "sqlca.sqlcode = %d\n", sqlca.sqlcode);
    EXEC SQL CLOSE c;

    sqlca.sqlcode = 0;
    exec sql select 'hello world' into :username from all_users where 1=0;
    printf( "sqlca.sqlcode = %d\n", sqlca.sqlcode);
}


produces:
[tkyte@dellpe test]$ ./test

Connected to ORACLE as user: /

sqlca.sqlcode = 1403
sqlca.sqlcode = 1403


as it should - 1403 shouldn't be negative - please utilize support for this one.

Thanks Tom. I will contact Oracle support

Praba, September 04, 2008 - 10:44 am UTC


Is this the solution?

Phil, December 10, 2008 - 10:12 am UTC

Hi Tom,

I've been programming in pl/sql since 1994 and never noticed this feature before (unless my memory is going). However, I've managed to get my query to re-raise the exception when the function it calls raises no_data_found. Here's how:

CREATE OR REPLACE FUNCTION fred
RETURN VARCHAR2
IS
lv_fred_val VARCHAR2(1);
le_ansi_no_data_found EXCEPTION;
PRAGMA EXCEPTION_INIT (le_ansi_no_data_found,-100);
BEGIN
SELECT d.dummy
INTO lv_fred_val
FROM dual d
WHERE 2 = 1;
RETURN lv_fred_val;
EXCEPTION
WHEN no_data_found THEN
RAISE le_ansi_no_data_found;
END fred;
/

Function created


SELECT fred FROM dual;

ORA-00100: no data found
ORA-06512: at "MDI.FRED", line 15
ORA-01403: no data found


Is this a reliable solution, or could it be foiled by some system setting? Even if it isn't, I could just replace the RAISE with a RAISE_APPLICATION_ERROR to give a -20000 exception: that's got to be fool-proof.

Regards
Phil

Tom Kyte
December 10, 2008 - 1:36 pm UTC

using the ansi 100 code could fall victim to the same issue by something - somewhere. I don't have a concrete answer.

I went back and forth on this issue - it is a complex 'problem'. Right now the best suggestion would be to always catch when no_data_found when using anything that can raise it - and if it is an ERROR, make it so.

The problem with no_data_found is that it is both "an error" and "not an error" depending on 'who you ask', what you are doing. By catching it and being un-ambiguous "this is an ERROR", the problem is resolved.

It is the one exception (all pun intended)

Similar to question "CALL statement ignores NO_DATA_FOUND exception"

Duke Ganote, December 10, 2008 - 4:04 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." (my bolding added)

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:317260900346187160#1351196600346787943


RE: it is the one exception (all pun intended)

Duke Ganote, December 10, 2008 - 4:14 pm UTC

It is the one exception that rues them all.

Confused about the behaviour in 10g for no_data_found.

Vikram, May 06, 2010 - 2:42 am UTC

Hi Tom,
The below behaviour is confusing me, was it always like this. 
Even if there is no data foubd by the query why is the value 0 getting set in the variable?
And why when count is removed it raises a no_data_found?


SQL> set serveroutput on;
SQL> declare
  2  v_no integer:=3;
  3  begin 
  4  select count(1) into v_no from dual
  5  where 1=2;
  6  dbms_output.put_line('Test '||v_no);
  7  end;
  8  /
Test 0

PL/SQL procedure successfully completed.

SQL> declare
  2  v_no integer:=3;
  3  begin 
  4  select 1 into v_no from dual
  5  where 1=2;
  6  dbms_output.put_line('Test '||v_no);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL>  

Tom Kyte
May 06, 2010 - 2:27 pm UTC

a query that has an aggregate with no group by always (always has, always does, always will) return

a) at least one row
b) at most one row

always...

Too_Many_Rows

Rajeshwaran, Jeyabal, August 27, 2011 - 10:20 am UTC

rajesh@ORA10GR2> create or replace function f(x number)
  2  return varchar2
  3  as
  4     l_ename varchar2(30);
  5  begin
  6     select ename
  7     into l_ename
  8     from emp
  9     where deptno = x;
 10
 11  exception
 12             when no_data_found then
 13                     return null;
 14             when too_many_rows then
 15                     return l_ename;
 16             when others then
 17                     raise_application_error (-20474,sqlerrm);
 18  end;
 19  /

Function created.

Elapsed: 00:00:00.51
rajesh@ORA10GR2>
rajesh@ORA10GR2> variable y varchar2(30);
rajesh@ORA10GR2> exec :y := f(10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
rajesh@ORA10GR2> print y;

Y
--------------------------------
CLARK

rajesh@ORA10GR2>
rajesh@ORA10GR2>


Tom:

When Too_Many_Rows Exception raises, Oracle assigns the first value into that variable. I thought in this case l_ename should be null but it hold the value CLARK. Is this a right behaviour? Is that mentioned in doc's that i behaves like this? I dont find any details about it.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref1962
Tom Kyte
August 30, 2011 - 4:54 pm UTC

when you get either of no_data_found or too_many_rows - the value in l_ename is something you cannot rely on - you KNOW it is damaged goods. You should just be ignoring it - the statement *failed*


http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm#sthref3054


<quote>
By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row</quote>

doubt with nodatafound of execption_init

Ranjan, February 17, 2013 - 1:55 am UTC

Hi Tom,


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> 
SQL> create or replace function f1 return number 
  2  is
  3   x number;
  4  begin
  5  select 1 into x from dual where 1=2;
  6  return 5;
  7  end;
  8  /

Function created.

SQL> select f1 from dual;

        F1
----------


1 row selected.
############################# 

here it has to show zero record and i tried in 9i too same,didnt 
reproduce as "0 Row SELECTED".(I understood why it is not throwing error :) but have doubt
why it is showing one row selected with null should given as no rows selected).
Is it like we should not rely whether it will give 0 rows or 1 row??

#######################
I was just doing somemore analysis on that with exception_init and have some doubt with 
exception_init fro no_dat_found,Please see below testcase.

SQL> 
SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,100);
  4  begin
  5  null;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,-1403);  --------they should include -1403 
  4  begin
  5  null;
  6  end;
  7  /
hello exception;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT


SQL> 
SQL> begin
  2   dbms_output.put_line(sqlerrm(-1403));
  3   dbms_output.put_line(sqlerrm(100));
  4  end;
  5  /
ORA-01403: no data found
ORA-01403: no data found

PL/SQL procedure successfully completed.

SQL> 

#######################

I think they should keep -1403 in error list for "exception_init pragma"
like they have maintained for sqlerrm.
I am not saying it is a bug but I just want to know what is the reason for not including "-1403" for that,
or they have missed it.

Could you please say few lines for this.

thanks & Regards,
Biswaranjan.

Tom Kyte
February 19, 2013 - 9:24 am UTC

if you have an unhandled no data found thrown from a plsql function called from SQL, the SQL engine propagates it to the client as no data found, and the client says "ok, no data is found, time to stop fetching"

and it does.

the problem is that no data found is an exception you are expecting from SQL, but not PLSQL, but because the plsql layer throws it and the sql layer propagates it - to the client it all looks "normal"

therefore, in a plsql routine called from sql, you should catch no data found at the top in general and turn it into something using raise_application_error()

cont to my last post.

Biswaranjan, February 19, 2013 - 10:48 am UTC

Hi Tom,

That I have understood what you have explained just now ,long back from your sites from you only.

But my question here is.
we know we can associate a named exception to a particular oracle error using 
"pragma exception_init".

we also know the positive error "100" is the error code for nodatafound(I mean -1403 or 100) .

SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,100);
  4  begin
  5  null;
  6  end;
  7  /

PL/SQL procedure successfully completed.

----it is ok as we are associating "hello" named exception to nodatafound(100).

SQL> declare
  2  hello exception;
  3  pragma exception_init(hello ,-1403);  --------they should include -1403 
  4  begin
  5  null;
  6  end;
  7  /
hello exception;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00701: illegal ORACLE error number -1403 for PRAGMA EXCEPTION_INIT

---But here it seems odd ,it(exception_init pragma) should accept "hello" 
named exception to nodatafound(-1403).

thanks & Regards,
Biswaranjan.

Tom Kyte
February 25, 2013 - 8:21 am UTC

no data found is "special"

http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/exceptioninit_pragma.htm

<quote>
Error code to be associated with exception. error_code can be either 100 (the numeric code for "no data found" that "SQLCODE Function" returns) or any negative integer greater than -10000000 except -1403 (another numeric code for "no data found").</quote>

To Biswaranjan: Error code -1403 documented as not to be used in pragma exception_init

A reader, February 20, 2013 - 2:09 am UTC

thanks reader,got it :)

Biswaranjan, February 21, 2013 - 1:11 pm UTC


thanks Tom

Biswaranjan, February 25, 2013 - 12:55 pm UTC

Hi Tom,
thanks for the reply.

tomorrow you have something schedule "National Capitol Area Users Group".

Is that like you are sharing some new and special knowledge at some conference!!


Tom Kyte
February 25, 2013 - 1:34 pm UTC

I'm speaking at a user group meeting in Washington DC in the USA that night...

No data found from array

Tony, April 03, 2013 - 7:00 pm UTC

Hi Tom,

In my pl/sql code, 'no data found' error is thrown from line 5 but no error is thrown for lines 1 to 4

1)m_acct_tab_first := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.first;

2)m_acct_tab_last := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.last;

3)m_account_no := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab(m_acct_tab_first).acc_no;

4)m_acct_no_tab_cnt := g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.count;

5)FOR m_account IN 1..g_g_tab(m_bill_no).service_tab(m_service).date_tab(m_period).account_tab.COUNT LOOP

The oracle version is 10g

Any ideas as to how we can explain this

Thank you.

Tom Kyte
April 22, 2013 - 1:56 pm UTC

nope, but only because you didn't give me all of the lines of code so I decided to not even waste my time looking.


seriously.

give a 100% working example. we don't need the stuff that WORKS, just the line that doesn't along with all supporting bits of code.


No data found from array

Tony, April 03, 2013 - 7:03 pm UTC

Hi,

Just to add that m_acct_tab_first,m_acct_tab_last,m_acct_no_tab_cnt have all the same value,1.
Tom Kyte
April 22, 2013 - 1:56 pm UTC

doesn't matter, no example, no look - ever

not always been that way

DAG, October 24, 2014 - 9:26 am UTC

hello Tom,
in your first reply on 10 jun 2003 you wrote,

> tkyte@ORA716> select func_foo from dual;
>
> <b>no rows selected</b>

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

this was very reasonable..but, as noted also from Balasubramanian, on 9.2 and 10.2.0.5.0 (and conceivably other versions)

> select func_foo from dual will return no rows selected.
> But if i executed it shows 1 row selected.

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


<b>1 row selected.</b>

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.



moreover, the function returns 1 row even if there's no "return":

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    -- return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


1 row selected.

10:32:26 SQL> select nvl(func_foo,'X') from dual;
X

1 row selected.



that last beahaviour seems to me quite buggy: I do expect an ORA-06503.. "who" is returning the null value?












not always been that way

DAG, October 24, 2014 - 9:27 am UTC


hello Tom,
in your first reply on 10 jun 2003 you wrote,

> tkyte@ORA716> select func_foo from dual;
>
> <b>no rows selected</b>

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

this was very reasonable..but, as noted also from Balasubramanian, on 9.2 and 10.2.0.5.0 (and conceivably other versions)

> select func_foo from dual will return no rows selected.
> But if i executed it shows 1 row selected.

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


<b>1 row selected.</b>

SQL> select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

5 rows selected.



moreover, the function returns 1 row even if there's no "return":

SQL> create or replace function func_foo return varchar2 is
  2    l_value varchar2(1);
  3  begin
  4    select dummy
  5    into l_value
  6    from dual
  7    where dummy = '-';
  8
  9    -- return l_value;
 10  end;
 11  /

Function created.

SQL> select func_foo from dual;


1 row selected.

10:32:26 SQL> select nvl(func_foo,'X') from dual;
X

1 row selected.



that last beahaviour seems to me quite buggy: I do expect an ORA-06503.. "who" is returning the null value?






Scheduled jobs hides no_data_found exception too

Iurii, January 12, 2017 - 2:18 pm UTC

Hello Thomas,

What do you think about scheduled jobs which can hide no_data_found exception?

create or replace procedure test1 is
begin
  raise no_data_found;
end;
/
begin test1; end;
/
begin
      dbms_scheduler.create_job(job_name        => 'T1',
                                job_type        => 'STORED_PROCEDURE',
                                job_action      => 'TEST1',
                                enabled         => TRUE,
                                start_date      => systimestamp,
                                auto_drop       => true);
END;
/
select ACTUAL_START_DATE, LOG_DATE,STATUS,ERRORS from user_scheduler_job_run_details where job_name='T1';


The final query shows STATUS='SUCCEEDED' even though call of test1() raises a no_data_found exception.

Btw, if job is defined slightly differently

  job_type        => 'PLSQL_BLOCK',
  job_action      => 'BEGIN raise no_data_found; end;',


all works as expected, i.e. STATUS='FAILED'.
Chris Saxon
January 12, 2017 - 3:46 pm UTC

It's a bug. Several have been raised related to this. See MOS note 1331778.1 for details.