Re: reading sequence.CURRVAL

Okay, I see your point, and it is valid.

But I guess I would use a PL/SQL temp variable or something to accomplish your example:

id = seq.nextval
insert into parent table ( ...., id, ...) insert into child_table ( ..., id, )

Not as clean as the way it works today, I guess, but that way currval could give you the current value without any locking behavior.

Bill Dietrich
bill_dietrich_at_wayfarer.com

Saad Ahmad wrote:

> Sequences behave this way because that is how they are
> designed. And this design provides the performance benefits
> that sequences promise.
>
> Sequences are there to provide a mechanism to give unique values
> that are optionally ordered. The values returned by them are independent
> of transactions.
>
> currval is to give the value that was last retrieved for that session using
> .nextval. This is provided to code something like
> insert into parent table ( ...., seq.nextval, ...)
>
> insert into child_table ( ..., seq.currval, );
>
> For what you want to do, either you need to implement your own sequences
> or use triggers with alerts.
>
> To provide something like actual_current_value, a locking mechanism will be
>
> needed when anyone accesses the sequence - quite frankly I would
> prefer effecient sequences over sequences that provide that method ...
>
> One alternative is to call .nextval every 10 seconds or so and if the value
>
> is not one more than last time, then you can decipher that something else
> occurred. Yes that will mean that you will lose 6*60*24 = 8640 sequence
> numbers a day - but if your sequence is defined appropriately this should
> not pose a problem.
>
> Bill Dietrich <bill_dietrich_at_wayfarer.com> wrote in article
> <353F99E3.8CBA4271_at_wayfarer.com>...
> > I'm using Oracle 7.3..3, and I want my app to check every now and then
> > to see if new records have been added to a table (without using
> > triggers).
> >
> > What I'd like to do is just read sequence.CURRVAL, where
> > this is a sequence that generates record IDs for the table. If CURRVAL
> > has
> > increased since the last time I read it, I know there are new records,
> > and what range their IDs are in.
> >
> > But the documentation says "Note that before you use CURRVAL
> > for a sequence in your session, you must first initialize
> > the sequence with NEXTVAL." And it is true; I've tested it.
> >
> > This means that I can't just read CURRVAL; first I have to
> > reference NEXTVAL, which increments the value ! I don't want
> > to do this.
> >
> > Is there any other way to read CURRVAL without changing its value ?
> >
> > I can't believe sequences have this behavior !
> >
> > Bill Dietrich
> > bill_dietrich_at_wayfarer.com
> >
> >
> >
Received on Thu Apr 23 1998 - 18:44:53 CDT