Re: errors while fetching...

From: <rtproffitt_at_my-deja.com>
Date: Thu, 06 Apr 2000 15:47:42 GMT
Message-ID: <8cibio$qu$1@nnrp1.deja.com>

Peter,

Most likely, in your loop, you have a
SELECT INTO statement which is returning no data found and you do not have an EXCEPTION clause for it. Another possible cause is that you are not exiting properly from your cursor loop after the last record if fetched... Although by itself, this wouldn't create the error, if you later use a fetched variable in another select, you might be causing no data to be found....

1.

   For i in 1..4 loop

     ... do some stuff...
     SELECT Mydata INTO x
     WHERE MyColumn = i;

   End Loop;
[ the i where clause causes

    a no data result, which crashes
    proc ]

   For i in 1..4 loop

     ... do some stuff...
     Begin
     SELECT Mydata INTO x
     WHERE MyColumn = i;
     Exception
       when NO_DATA_FOUND then
          ...do something..or null;
     End;

   End Loop;
[ successfully trap the bad condition ]

2.

   For xrec in MyCursor loop

      ... do stuff ...
   End Loop;
[ by itself, won't crash (at least in
     Oracle 8) ]

   For xrec in MyCursor loop

      ... do stuff ...
      Select x into y
      From AnotherTable
      Where Mycolumn = MyCursor.Column;

   End Loop;
[ This would cause an error if

     first cursor opens and returns
     a value used in second select (MyCursor.Column)
     which causes no data to be returned.]

Robert Proffitt
Beckman Coulter
Brea, California
RTProffitt (AT) beckman (DOT) com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 06 2000 - 10:47:42 CDT