Reference a sequence

After you create a sequence, you can reference the sequence.

To use a sequence, you must qualify CURRVAL and NEXTVAL with the name of the sequence followed by a period (.).

For example, if the name of the sequence is SEQ_FOO, you can use SEQ_FOO.CURRVAL to query the current value of the SEQ_FOO sequence. Likewise, you can use SEQ_FOO.NEXTVAL to query the next value of the SEQ_FOO sequence.

CURRVAL and NEXTVAL values of a sequence can be used in:

• The select list of a SELECT statement.

• The VALUES clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Example:

obclient> SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number increases for each execution.*/

obclient> SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number remains unchanged for each execution.*/

The first reference to NEXTVAL returns the start value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined step size 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 reference to NEXTVAL.

Before you refer to CURRVAL of a sequence in the session, you must use NEXTVAL to initialize the sequence values first.

When you create a sequence, you can define its start value and the step size between its values. The first reference to NEXTVAL returns the start value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined step size 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 reference to NEXTVAL.

More information

For more information about operations on a sequence, see the following topics:

• Create a sequence

• Query sequences

• Modify a sequence

• Drop a sequence