Path: news.easynews.com!easynews!news.he.net!cyclone-sf.pbi.net!199.106.71.17!pln-w!spln!dex!extra.newsguy.com!newsp.newsguy.com!drn
From: Thomas Kyte <tkyte@oracle.com>
Newsgroups: comp.databases.oracle.tools
Subject: Re: PL-SQL: Exception NO_DATA_FOUND or %NOTFOUND
Date: 7 Jul 2002 06:49:39 -0700
Organization: Oracle
Lines: 94
Message-ID: <ag9gtj01gbh@drn.newsguy.com>
References: <3d259174$1@news.wau.nl> <uifqjapc3uhu5d@corp.supernews.com>
NNTP-Posting-Host: p-942.newsdawg.com
X-Newsreader: Direct Read News 2.96
Xref: easynews comp.databases.oracle.tools:52255
X-Received-Date: Sun, 07 Jul 2002 07:17:41 MST (news.easynews.com)

In article <uifqjapc3uhu5d@corp.supernews.com>, "Sybrand says...
>
>
>"Henk Hultink" <hhu@stopspam.stoas.nl> wrote in message
>news:3d259174$1@news.wau.nl...
>> Hi,
>>
>> Does anyone know if there is a difference between:
>>
>> OPEN <cursor>;
>> FETCH <cursor>   INTO <variable>;
>>     EXCEPTION
>>        WHEN NO_DATA_FOUND THEN
>>        ....
>>
>> and
>>
>> OPEN <cursor>;
>> FETCH <cursor>   INTO <variable>;
>>    WHEN <cursor>%NOTFOUND THEN
>>        ....
>>
>> I remember having heard that thers IS a difference; however I can't find
>it
>> in the manuals.
>>
>> Regards,
>> --
>> H. Hultink
>> Software Engineer
>> Stoas, Division IOAL
>> Wageningen, The Netherlands
>> "Activating Knowledge"
>> http://www.stoas.nl
>>
>> e-mail: hhu@stopspam.stoas.nl
>>
>>
>
>Basically there is no difference.

actually, there is no comparision.  They are used in two mutually exclusive
cases.  They are really night and day different.

>The no_data_found exception is raised when using implicit cursors.

correct.

>The no_data_found condition is raised when using explicit cursors, you

incorrect.

   open c;
   fetch c into ...;
   if c%notfound then .....

the above code makes sense and properly detects the no data found condition
whereas:

   open c;
   begin
      fetch c into ...;
   exception
      when no_data_found then .....
   end;

won't "work" -- it's a bug in the code.  It'll never ever detect that "no data
was found" since the no_data_found exception is raised only when you use a
SELECT INTO.


>always need to handle those. The code you provided is incorrect. It is
><CURSOR>%NOTFOUND and as that isn't an exception, it is
>*IF* <CURSOR>%NOTFOUND
>Most of this can be prevented by using CURSOR FOR loops.
>
>Hth
>
>
>--
>Sybrand Bakker
>Senior Oracle DBA
>
>to reply remove '-verwijderdit' from my e-mail address
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp