Re: PL-SQL: Exception NO_DATA_FOUND or %NOTFOUND

In article <uifqjapc3uhu5d_at_corp.supernews.com>, "Sybrand says...
>
>
>"Henk Hultink" <hhu_at_stopspam.stoas.nl> wrote in message
>news:3d259174$1_at_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_at_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 ...;
[Quoted]    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_at_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/  
[Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corp