In this article, I’ll explain what an Oracle sequence is, how to create one, and how to use them.
What is a Sequence in Oracle?
A sequence is a database object that is used to generate unique numbers.
They are most often used for generating primary keys, as a primary key needs to be unique, and doesn’t need to be a value that’s relevant to your application or business.
Generating a unique number with a sequence is easy after it has been created.
Why Use a Sequence?
You might be wondering, why would I want to use a sequence?
Couldn’t I just use a table that stores the Primary Key and increment it each time? Or set a column as AUTO INCREMENT?
Well, a sequence is a great option for a few reasons.
If you’re running Oracle 12c, you can use a new feature that allows you to create an auto-incrementing column. You can read more about this here. However, this was only introduced in Oracle 12c, so if you’re on 11g or 10g or earlier, then you can’t use this feature.
The other option would be to use a table to store a primary key value. This could be done something like this:
- Create a new table to store a single value
- Each time you insert a new value into your desired table, use this value in the new table
- Then, update the new table by increasing it by one
- This logic can all go into a stored procedure if required.
However, there are several problems with this approach:
- If your transaction fails halfway through, then you could end up with duplicate data (your inserted data has an ID but the new table was not incremented). This could be partially avoided by incrementing first and then inserting, but it’s still a risk that it could fail halfway through.
- Updating a table will place a lock on it, which can cause issues for multiple transactions or users. This could be OK if there was one user, but if your application has two or more, then this can cause issues.
- It’s more work to create and maintain a table and the code to do this.
A sequence is a much better approach because:
- It’s simpler to create, as we’ll see shortly.
- It’s simpler to use
- It’s transaction safe – no risk of locking and causing issues for other transactions.
Let’s take a look at how you can create a sequence.
Create Sequence in Oracle
To create a sequence in Oracle, we use the CREATE SEQUENCE command.
CREATE SEQUENCE [schema.]sequence_name [MINVALUE minval|NOMINVALUE] [MAXVALUE maxval|NOMAXVALUE] [START WITH startval] [INCREMENT BY incrementval] [CYCLE|NOCYCLE] [CACHE cacheval|NOCACHE] [ORDER|NOORDER];
There are a lot of parameters available, but most of them are optional. These parameters are:
- schema: The name of the schema where the sequence will be stored. If omitted, the sequence is created in your own schema.
- sequence_name: The name of the sequence to create. This is the only required parameter.
- MINVALUE minval: This is the minimum value that is generated by the sequence. It must be an integer of up to 28 digits. It must be less than or equal to START WITH and must be less than MAXVALUE.
- NOMINVAL: This indicates that there is no specified minimum value for the sequence, but uses -1026 as the minimum for descending sequences and 1 for ascending sequences. This is specified instead of the MINVALUE and is the default value (as opposed to MINVALUE).
- MAXVALUE maxval: This is the maximum value that is generated by the sequence. It must be an integer of up to 28 digits. It must be greater than or equal to START WITH and must be greater than MINVALUE.
- NOMAXVALUE: This indicates that there is no specified maximum value for the sequence, but uses 1027 as the maximum for ascending sequences and -1 for descending sequences. This is specified instead of the MAXVALUE and is the default value (as opposed to MAXVALUE).
- START WITH startval: This is the first number to be generated from the sequence. For ascending sequences, the default is the minimum value, and for descending sequences, the default is the maximum value. It can be up to 28 digits.
- INCREMENT BY incrementval: This is the interval between sequence numbers. It must be an integer, can be positive or negative, but cannot be 0. It can be up to 28 digits. The absolute value must be less than the difference between MAXVALUE and MINVALUE. If the increment value is negative, the sequence goes in descending order. If it is positive, then it goes in ascending order. If this is omitted, then the default value is 1.
- CYCLE: This indicates that the sequence keeps generating values after the MAXVALUE or MINVALUE is reached. If the sequence is ascending, and reaches the MAXVALUE, it generates the MINVALUE. If the sequence is descending, and reaches the MINVALUE, it generates the MAXVALUE.
- NOCYCLE: This indicates that the sequence can’t generate any more values after the maximum is reached. This is the default.
- CACHE cacheval: This is how many values of the sequence that the database allocates and keeps in memory to be able to access it faster. It must be greater than 2, and can be up to 28 digits. If a system failure occurs, then the cached values are lost. This is usually OK, as if they are used for primary keys, they don’t need to be sequential. The CACHE value needs to be less than the number of sequences in a cycle, if the sequence is set to cycle. The maximum for the CACHE value is:
(CEIL (MAXVALUE – MINVALUE)) / ABS(INCREMENT)
- NOCACHE: This indicates that no sequence values are allocated and cached. If you omit both CACHE and NOCACHE, then the default setting is to cache 20 sequence numbers.
- ORDER: This indicates that sequence numbers are generated in the order they are requested. This can be useful if you are using the sequence numbers for timestamps.
- NOORDER: This indicates that the sequence numbers are not guaranteed to be in the order they were requested. This is the default value.
This table indicates some scenarios using the different parameters for sequences:
|n/a||n/a||1||1||No||Default. Values of 1, 2, 3, 4, 5.|
|10||n/a||10||1||No||Values of 10, 11, 12, 13, 14.|
|n/a||1000||1||5||No||Values of 1, 6, 11, 16, 21. Error caused when value over maximum reached.|
|10||1000||10||5||No||Values of 10, 15, 20, 25, 30. Error caused when value over maximum reached.|
|10||1000||10||50||Yes||Values of 10, 60, 110, 160. When maximum reached, values of 10, 60, 110 generated again.|
|10||500||500||-1||No||Values of 500, 499, 498, 497. Error caused when value under minimum reached.|
|10||500||500||-10||Yes||Values of 500, 490, 480, 470. When minimum reached, values of 500, 490, 480 generated again.|
Create Sequence Examples
Let’s see some examples of creating sequences.
Example 1: Simple
This is an example of creating a simple sequence.
CREATE SEQUENCE seq_simple;
The sequence is created with all of the default values and can be used.
Example 2: MINVALUE and START WITH
This example uses both the MINVALUE and START WITH parameters.
CREATE SEQUENCE seq02 MINVALUE 10 START WITH 10;
This sequence will start at a value of 10 and generate values of 11, 12, 13, and so on.
Example 3: MAXVALUE and INCREMENT
This example uses both the MAXVALUE and INCREMENT parameters.
CREATE SEQUENCE seq03 MAXVALUE 1000 INCREMENT BY 5;
This sequence will start at a value of 1, and increment by 5 (generating values of 6, 11, 16, and so on), until it reaches a value over 1000. It will then cause an error.
Example 4: MINVALUE, MAXVALUE, START WITH, and INCREMENT
This example uses all four of these parameters.
CREATE SEQUENCE seq04 MINVALUE 10 MAXVALUE 1000 START WITH 10 INCREMENT BY 5;
This will create a sequence that generates values of 10, 15, 20, 25, and so on. Once a value over the maximum is generated, an error will occur.
Example 5: CYCLE
This example uses the CYCLE parameter.
CREATE SEQUENCE seq05 MINVALUE 10 MAXVALUE 1000 START WITH 10 INCREMENT BY 50 CYCLE;
This sequence will generate values of 10, 60, 110, 160, and so on. Once the maximum is reached, instead of generating an error, it will restart at the minimum value of 10, and continue with 60, 110, and so on.
Example 6: NOCYCLE
This sequence specifies the NOCYCLE parameter, which is the default parameter.
CREATE SEQUENCE seq06 MINVALUE 10 MAXVALUE 1000 START WITH 10 INCREMENT BY 50 NOCYCLE;
This sequence will generate values of 10, 60, 110, 160, and so on. Once the maximum is reached, an error will occur.
Example 7: Negative Increment
This sequence specifies a negative value for the increment.
CREATE SEQUENCE seq07 MINVALUE 10 MAXVALUE 500 START WITH 500 INCREMENT -1;
This sequence will start generating numbers of 500, 499, 498, and so on. Once it gets below the minimum, an error will occur.
Example 8: Negative Increment and Cycle
This example uses a negative increment value and a cycle.
CREATE SEQUENCE seq08 MINVALUE 10 MAXVALUE 500 START WITH 500 INCREMENT BY -10;
This sequence will start generating numbers of 500, 490, 480, and so on. Once it gets below the minimum, it will go back to 500, then 490, 480, and so on.
Example 9: CACHE
This example uses the CACHE parameter.
CREATE SEQUENCE seq09 CACHE 50;
This sequence will generate values of 1, 2, 3, and so on, but will cache the first 50 values.
Example 10: ORDER
This example uses the ORDER parameter.
CREATE SEQUENCE seq10 ORDER;
It will ensure sequence values are generated in order, which will become apparent when multiple users are using the system.
Using Sequences: How to Generate Sequence Values
So, now we’ve created a sequence to generate the values we want.
How to we generate those values?
We can use one of two pseudocolumns available for a sequence:
- CURRVAL: This gets the current value of the sequence.
- NEXTVAL: This gets the next value of the sequence and increments the sequence by its increment value.
So, to get the next value of the sequence (for example, to use in an INSERT statement to generate a primary key), you’ll need to use the NEXTVAL pseudocolumn.
INSERT INTO customer(customer_id, full_name) VALUES (seq_simple.NEXTVAL, ‘John Smith’);
You can then query the table to see what value was inserted.
SELECT customer_id, full_name FROM customer;
One thing to remember is that when calling the NEXTVAL pseudocolumn, it returns a new value for each row of data that is being selected or inserted.
For example, we can run this query to get the NEXTVAL of the sequence:
SELECT seq_simple.NEXTVAL FROM dual;
Then, if I run this query with several NEXTVAL commands:
SELECT seq_simple.NEXTVAL AS nv1, seq_simple.NEXTVAL AS nv2, seq_simple.NEXTVAL AS nv3 FROM dual;
This shows that the NEXTVAL was only called once for this record, and the value is the same each time.
To find the current value of the sequence, without incrementing the number, use CURRVAL.
SELECT seq_simple.CURRVAL FROM dual;
These two pseudocolumns can be used in several places:
- The VALUES clause of an INSERT statement.
- The SELECT list of a SELECT statement
- The UPDATE clause of an UPDATE statement
They can’t be used in these places:
- The condition of a CHECK constraint
- A view query
- A materialised view query
- A subquery
- A SELECT statement with the DISTINCT keyword, GROUP BY clause, or ORDER BY clause
- A SELECT statement that’s combined with another SELECT statement using the UNION, INTERSECT, or MINUS operator
- The WHERE clause of a SELECT statement
- The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
This last point means you can’t set a column to auto increment using a sequence using the DEFAULT keyword. There is a way to create auto-increment columns, and that’s to use a trigger and a sequence, which I’ve written about here.
In Oracle 12c, a new feature was added to allow auto-increment columns to be added easier, which you can read about here.
To alter a sequence after it is created, use the ALTER SEQUENCE command.
You can change most properties of a sequence after it is created. The only thing you can’t change is the starting number of the sequence and the sequence’s current value. To change those, you’ll need to drop and recreate the sequence.
To use the ALTER SEQUENCE command:
ALTER SEQUENCE seq02 INCREMENT BY 20 MAXVALUE 100000 CYCLE;
This will make the above adjustments to the existing seq02 sequence. A common usage of this command is ALTER SEQUENCE INCREMENT BY, which will increment the sequence by a specified number, as we’ve seen in this example.
To drop a sequence, it must be in your schema, or you must have the DROP ANY SEQUENCE privilege to drop sequences in other schemas.
You can drop a sequence by using the DROP SEQUENCE command. It looks like this:
DROP SEQUENCE seq05;
This will drop the sequence seq05 from the database.
So, that’s how you can create and use sequences. They are handy database objects and have many advantages over the alternatives.