Re: Holdable cursors in Oracle?

Don't know anything about RDB. But perhaps "fetch across commits" is what this "holdable cursor" is all about.

There is type of ORACLE cursor called "for update cursor". Upon open, this cursor will lock the rows that will be returned. You can not have a commit or rollback within a "cursor for update" loop. A commit or rollback in the loop will terminate the transaction and release the lock on those rows. When you next do a fetch, you will get an ORA-1002 "out of sequence" error. So, you will have to do your commit outside the cursor loop, or don't use the for update cursor.

Not sure why you are asking this question. If you are thinking of building OLAP cubes, then you want to look into ORACLE OLAP. There are PL/SQL packages that help you set up cube, measure, dimension, .. etc. However, I wonder how useful this would be at this point in time - if you are not using ORACLE Express. AFAIK, most, if not all, OLAP querying tools do not currently support the ORACLE 9i OLAP.

• Jusung Yang

"Hillel Eilat" <hillel_at_attunity.co.il> wrote in message news:<b5bqi4$nks$1_at_news2.netvision.net.il>...
> Holdable cursors may contribute a lot for applications where massive
> reads are performed followed by successeive store/update operations.
>
> For example - preparing a "cube" for OLAP/ data mining may
> require massive reads before a single store/update is issued.
>
> While doing this read/update sequence repetaedly - it does make
> sense to commit the stored/updated data peridically.
>
> But upon commit - cursors would be closed - unless they are holdable.
> So - without holdable cursors one has to keep track on the cursor position
> and renew it after every commit.
>
> Yes - data integrity and stability are not maintained - but this is not the
> issue here.
>
>
> Hillel
> "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> news:ahgg7von09vo7b1nf0umr8dngj61nu1n8k_at_4ax.com...
> > On Wed, 19 Mar 2003 10:09:53 +0000, Billy Verreynne
> > <vslabs_at_onwe.co.za> wrote:
> >
> > >Hillel Eilat wrote:
> > >
> > >> Some DBMSs support a feature called "Holdable Cursors".
> > >>
> > >> A holdable cursor is kept open accross transaction boundaries,
> > >> as opposed to the 'normal' behavior of most known DBMS's.
> <snipped>
> > >> Is there an equivalent / similar functionaly supported in Oracle?
> > >
> > >What is the reasoning, functionally and technically, behind such a
> concept?
> to avoid parsing
> > >How are transactions from different users/sessions handled on this cursor
> > >and data integrity guaranteed?
> > >
> > A cursor exists *within* a session
> >
> > >And how is this better than treating a table conceptually as a "holdable
> > >cursor"?
> > >
> > >Issues wrt to resource usage are moot IMO. I fail to see how these can
> > >dictate a fundemental concept such as holdable cursors.
> >
> > They aren't. In fact in Pro*C, you can close a cursor and you can
> > release it. If you don't release it, and only reevaluate it, you can
> > save a parse.
> >
> >
> > >
> > >Not all problems are nails. ;-)
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
Received on Thu Mar 20 2003 - 11:42:14 CST