Re: reading sequence.CURRVAL

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 - 16:00:28 CDT