| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: reading sequence.CURRVAL
On Thu, 23 Apr 1998 12:43:31 -0700, Bill Dietrich
<bill_dietrich_at_wayfarer.com> wrote:
>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.
This assumption is not totaly correct. If sequence's value has increased it doesn't neccessary mean that those values were actualy used with commited data. For example, one can insert new record into the table using sequence's NEXTVALUE for its id, but then the transaction is rolled back (for whatever reason). As a result, sequence's CURRVALUE is increased but there are no new records in a table.
>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 !
You can't get its CURRVALUE like this before you reference its NEXTVALUE in the session. But you can get it by querying the view USER_SEQUENCES (or ALL_SEQUENCES or DBA_SEQUENCES):
SELECT last_number FROM user_sequences
WHERE sequence_name = 'MY_SEQUENCE';
>Bill Dietrich
>bill_dietrich_at_wayfarer.com
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Apr 24 1998 - 05:42:33 CDT
 |
 |