After you create a sequence, you can reference it.
When using a sequence, you must prefix CURRVAL and NEXTVAL with the sequence name and separate them with a period (.).
For example, if the sequence name is SEQ_FOO, you can use SEQ_FOO.CURRVAL to get the current value of the SEQ_FOO sequence. Similarly, you can use SEQ_FOO.NEXTVAL to get the next auto-increment value of the SEQ_FOO sequence.
The CURRVAL and NEXTVAL values of a sequence can be used in the following contexts:
In the select-list of a top-level
SELECTstatement.In the
VALUEclause of anINSERTstatement.In the
SETclause of anUPDATEstatement.
For example:
obclient> SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL; /*The sequence number increments with each execution.*/
obclient> SELECT SEQUENCE_NAME.CURRVAL FROM DUAL; /*The sequence number does not change regardless of how many times it is executed.*/
The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL return a new value that is the previous returned value plus the sequence's defined increment. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last NEXTVAL reference.
Before referencing the CURRVAL pseudo-column of a sequence in a session, you must first use the NEXTVAL pseudo-column of the sequence to initialize the sequence value for the session.
When creating a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return a new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last NEXTVAL reference.
To prevent the cache of other sequences from becoming stale, use the ALTER SYSTEM FLUSH SEQUENCE CACHE statement to actively clear the sequence cache so that the system can recover quickly after an issue occurs. For more information about clearing the sequence cache, see ALTER SYSTEM FLUSH SEQUENCE CACHE.
References
For more sequence-related operations, see the following topics: