Re: Delete a row fetched by a cursor in a cursor loop

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<Xns9539B9874F74CSunnySD_at_68.6.19.6>...
> Albert Dupond <ad_at_ad.com> wrote in
> news:410e5be7$0$31401$636a15ce_at_news.free.fr:
>
> > Hello,
> >
> > I small example would be better than a lot of words.
> > Can I do this ?
> >
> ><<<
> > declare
> > cursor myCursor is select objectid from myTable where value = 'TEST';
> > begin
> > for myRecord in myCursor loop
> > delete from myTable where objectid = myRecord.objectid;
> > commit;
> > end loop;
> > end;
> > >>>
> >
> >
> > I would really appreciate to be able to do this but I am afraid of side
> > effects, because I modify the cursor data set during its usage.
> >
> > Which precaution must I take ? Any advice welcome.
> >
> > Thanks a lot
> >
> > AD
> >
>
> If the data inmyTable gets modified (& COMMITted) after myCursor opens,
> this procedure will never see those changes.
>
> What problem(s) do expect or want to avoid?

Albert, for the example posted your logic is fine and will work. Ana brought up a point that though that you always need to consider. The data selected in the driving cursor is at a consistent point in time. The inside DML statement, delete in this case, goes against "the time of execution" current row which may have changed from the driving cursor. If there are other conditions to the driving cursor then especially in the case of update you need to retest those conditions to make sure the target row still meets the driving query conditions.

Sometimes you might need to use a select for update cursor and then a delete or update where current of [the driving cursor] to perform the DML activity.

HTH -- Mark D Powell -- Received on Wed Aug 04 2004 - 08:20:45 CDT