Oracle CREATE SEQUENCE Command

Database sequences are useful database objects that allow the database to automatically generate a sequence of values based on a given starting value, the increment step, and the maximum value.

You will often come across database sequences used as primary keys for a database column. Although you can use the built-in primary key generator, sequences provide different properties which enable you to customize how the values are added to the database.

This tutorial will teach you how to use the CREATE SEQUENCE command in the Oracle database to define a new sequence.

Oracle CREATE SEQUENCE Statement

We use the CREATE SEQUENCE statement in Oracle to create a new oracle sequence. We can express the syntax of this statement as shown below:

The statement accepts the following options:

1. sequence_name – this specifies the name of the sequence you wish to create.
2. INCREMENT BY n – the INCREMENT BY n clause allows you to specify the value by which the sequence increases. By default, the sequence will increase from the previous value by a factor of 1.
3. START WITH n – this option allows you to specify the value at which the sequence will start. Similarly, the default value is set to 1.
4. MAXVALUE n – the MAXVALUE parameter allows you to set the maximum value the sequence can generate. The default value is set to 10^27-1.
5. MINVALUE n – in this case, this parameter defines the minimum value generated by the sequence. The default value is set to -10^27+1.
6. CYCLE – the CYCLE parameter defines whether the sequence should cycle the generated values upon maximum. If set, the sequence will start over from the minimum value once it reaches the defined maximum value. To prevent the sequence cycle, use the NOCYCLE option. By default, the sequence will not cycle the values.
7. CACHE n – This clause specifies the number of sequence numbers that are preallocated and stored in memory or cached for faster access. By default, no values are cached.
8. ORDER – the order parameter specifies whether the sequence of numbers is generated in order of request. If this clause is not specified, the default behavior is NOORDER.

Oracle Create Sequence Example

In the following example, we create a sequence called looper_sequence that starts from the value of 2 and reaches 100 with an interval of 2. The sequence will also cycle the values once it reaches the maximum value.

Once we have defined the sequence, we can fetch the values from the sequence using the nxtval() function.

Since this is the first time running the sequence, it should return the first value as shown below:

If we rerun the sequence in the same session, we should get the next value as shown below:

This should continue for each iteration until we hit the maximum value, forcing the sequence to start over.

Oracle Get Current Value

To get the current value in the sequence, you can use the currval() function as shown below:

Result:

Conclusion

In this tutorial, you learned how to use the CREATE SEQUENCE command in Oracle to create a new sequence object in a database. You also learned how to use the nxtval() and currval() functions to get the sequence’s next and current values, respectively.