Home » SQL & PL/SQL » SQL & PL/SQL » NO DATA Exception (Oracle 8i, Unix)
NO DATA Exception [message #288167] Sat, 15 December 2007 04:58 Go to next message
josephnghwl
Messages: 1
Registered: December 2007
Junior Member

Hi guys,

Currently I was asked to debug a particular Oracle procedure. After looking at the logs, I realised that the procedure was raising a NO DATA exception.

I have tried to go through each line and each SQL block within the procedure but it seem like everything is well.

Is there any other causes for the NO DATA exception?
Re: NO DATA Exception [message #288168 is a reply to message #288167] Sat, 15 December 2007 05:05 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

the first reason which comes to my mind is when your select into statements doesn't return any value.something like this.

declare
l_ename varchar2(30);
begin
select ename into l_ename from emp where empno=778;
dbms_output.put_line(l_ename);
end;
Error report:
ORA-01403: no data found
ORA-06512: at line 4
01403. 00000 -  "no data found"



regards,
Re: NO DATA Exception [message #288175 is a reply to message #288167] Sat, 15 December 2007 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is there any other causes for the NO DATA exception?

Anywhere you try to get/fetch data and it does not exist.

Regards
Michel
Re: NO DATA Exception [message #288182 is a reply to message #288167] Sat, 15 December 2007 09:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
consider these two code snippets. Each is trying to get data but does not find any:

declare
   v1 varchar2(1);
begin
   select dummy into v1 from dual where 1 = 2;
end;
/


the select into fails to find a row

declare
   type t1 is table of varchar2(1) index by binary_integer;
   a1 t1;
   v1 varchar2(1);
begin
   v1 := a1(1);
end;
/


the array lookup fails to find an item

SQL> declare
  2     v1 varchar2(1);
  3  begin
  4     select dummy into v1 from dual where 1 = 2;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


SQL> 
SQL> declare
  2     type t1 is table of varchar2(1) index by binary_integer;
  3     a1 t1;
  4     v1 varchar2(1);
  5  begin
  6     v1 := a1(1);
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6


I seem to racall there was a third way to get a NO_DATA_FOUND, but I do not remember what it was.

good luck, Kevin
Re: NO DATA Exception [message #288185 is a reply to message #288167] Sat, 15 December 2007 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 10 Handling PL/SQL Errors
Section Summary of Predefined PL/SQL Exceptions

Regards
Michel
Re: NO DATA Exception [message #288187 is a reply to message #288167] Sat, 15 December 2007 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I have tried to go through each line and each SQL block within the procedure but it seem like everything is well.
>Is there any other causes for the NO DATA exception?
I have been programming for over THREE Decades.
My advice you you is believe the error message!
My advice you you is believe the error message!
My advice you you is believe the error message!
My advice you you is believe the error message!

Continue to ask yourself under what conditions would the error message be true.

I wonder why you come here asking for assistance about debugging some PL/SQL code but don't post the code producing the error.

Why do you expect anything but generalizations & guesses, since why have nothing specific to work with?
Re: NO DATA Exception [message #288267 is a reply to message #288167] Sun, 16 December 2007 17:08 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe way to go Michel, I knew not remembering would bother me for some time.

Kevin
Previous Topic: ORA-27046 when transfer files
Next Topic: group by & having clause
Goto Forum:
  


Current Time: Wed Dec 07 18:52:24 CST 2016

Total time taken to generate the page: 0.08447 seconds