Re: Cursor, loop and delete...

On Mon, 02 Aug 2004 17:25:08 +0200, Albert Dupond <ad_at_ad.com> 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

1. use the for update clause on the select statement
2. replace the where clause by where current of mycursor 3 ) do NOT commit every individual delete 4 ) do NOT commit inside a loop

question is why you think you need this. delete where value = 'TEST' woul be much more efficient. you're going to kill performance,especially when you leave this code unchanged.

--
Sybrand Bakker, Senior Oracle DBA