Re: commit after or before close cursor - best practise

Grzegorz Goryszewski wrote:
> Hello,
>
> which is better from 'best practise' perspective:
>
> declare
> cursor ...
>
> begin
> open ...
> loop
> fetch ...
> exit when %NOTFOUND
> insert ...
> end loop;
> commit;
> close ...
>
>
> OR
>
> declare
> cursor ...
>
> begin
> open ...
> loop
> fetch ...
> exit when %NOTFOUND
> insert ...
> end loop;
> close ...
> commit;
>
> Regards.
> Grzegorz

Better ... well really best ... is to stop using cursor loops at all except in very rare circumstances. It is highly preferable to use array processing with BULK COLLECT and FORALL.

My personal preference with cursor loops, when I do have occassion to use them is to commit first. This being based on the fact that if something were to go terribly wrong ... I want my changes to be committed to the database as soon as possible. Not a big difference, and not something that has ever bought me anything, but conceptually I like it that way.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)