Re: Cursor, loop and delete...

Albert Dupond wrote:

> Hello,
>
> A 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

Great discussion of this in 'Effective Oracle by Design' by Thomas Kyte.

Rather than a piecemeal discussion of this potentially anti-scalable code (which has been discussed many times and is in the archives at http://groups.google.com) I recommend you take the time to read the book.

Bottom line: don't commit in a loop; cursors often reflect procedural-, rather than set-, oriented thinking and can be seriously inefficient; delete sets, not individual records, if possible. Received on Mon Aug 02 2004 - 12:45:53 CDT