I appreciate your response on this. However, somebody mentioned that
rollback segments don't get released if a commit is inside an open cursor.
Do rollback segments stay reserved till the end of the loop or do they get
released when I commit?
<markp7832_at_my-deja.com> wrote in message news:80c4lb$qo$1_at_nnrp1.deja.com...
> In article <38290ae7.88234376_at_news.pacbell.net>,
> peter_at_kellner.com (Peter Kellner) wrote:
> > according the oracle pl/sql book, this is no problem. You are right
> > about the "for update" causing problems.
> >
> > On Wed, 10 Nov 1999 04:59:19 GMT, "Hisham Douba" <hdouba_at_netscape.net>
> > wrote:
> >
> > >Hi Everybody,
> > >
> > >Is it valid to have a commit in an open cursor? My code looks like
> > >
> > >declare
> > > cursor cur1 is select * from emp;
> > > counter number;
> > >begin
> > > counter := 0;
> > > for rec in cur1 loop
> > > counter := counter + 1;
> > > insert into emp2 values (rec.no, rec.name);
> > > if counter > 4000 then
> > > commit;
> > > counter := 0;
> > > end if;
> > > end loop;
> > >exception
> > > when others then
> > > return 'error';
> > >end;
> > >
> > >Please note that the select is not for update.
> > >
> > >Your comments are very appreciated.
> > >
> > >Hisham
> > >
> Hisham, Peter is correct in that Oracle allows commits to be issued
> within a non-select for update cursor loop without problem. This is
> not ANSI standard, but is very practical.
>
> But also remember that each commit terminates a transaction and if
> multiple users are running the same code you will need to think about
> Oracle's data consistency model and how these user processes will
> interact. Your sample code is probably single process so this point
> really does not apply to it.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Wed Nov 10 1999 - 21:38:33 CST
