Re: Commit in an open cursor

In article <ZSqW3.1299$j%2.146_at_cabot.ops.attcanada.net>,   "Hisham Douba" <hdouba_at_netscape.net> wrote:
> 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?
>

As far as I know they are released on commit. I would take this question to Oracle support and post if they are not released in this case. We have a lot of processes that operate as described in this thread and we have not noticed problems so I suspect that the rbs data is released, but stranger things have happened.

> <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 --
> >

--
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 Thu Nov 11 1999 - 08:52:36 CST