Re: errors while fetching...

In article <8cibio$qu$1_at_nnrp1.deja.com>,   rtproffitt_at_my-deja.com wrote:
> 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
>

Robert is probably right; something I found useful was to avoid using cursor when possible and give that complixity to oracle to manage. What I tend to do instead is
BEGIN
....
FOR i IN (SELECT column_name FROM all_tab_columns WHERE table_name = 'EMP')
LOOP
IF i.column_name 'PAY' THEN
...dynamic sql
UPDATE EMP SET i.column_name = 200000;
END IF;
END LOOP;
...
END; I achieve the same thing as your code but I don't bother with cursors

Cheers
JC

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 06 2000 - 11:02:18 CDT