Oracle sequences (AUTONUMBER)

In Oracle/PLSQL, you can create autonumbering using a sequence. A sequence is an Oracle object that is used to generate a number sequence. This can be useful when you need to create a unique number as a primary key.

CREATE SEQUENCE

Syntax

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

• sequence_name of the sequence you want to create.

CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

This code will create a sequence object called supplier_seq. The first number of the sequence is 1, each subsequent number will be incremented by 1 (i.e., 2,3,4, …). This will cache up to 20 values for performance.

If you omit the MAXVALUE parameter, your default sequence will be to:

MAXVALUE 999999999999999999999999999

In this way, you can simplify the CREATE SEQUENCE. By writing the following:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

Now that you have created a sequence object to auto-number the counter field, we will look at how to get a value from that sequence object. To get the next value, you need to use NEXTVAL.
For example, you can use NEXTVAL:

supplier_seq.NEXTVAL;

This will allow you to extract the next value from the supplier_seq sequence. You need to use the NEXTVAL clause in your SQL query. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods');

This insert request will insert a new entry into the table (suppliers). The Supplier_id field will be assigned the following number from the supplier_seq sequence. The supplier_name field will be set to ‘Kraft Foods’.

DROP SEQUENCE

Once you have created a sequence in Oracle, you may need to remove it from the database.

Syntax

DROP SEQUENCE sequence_name;

• sequence_name is the name of the sequence you want to delete.

Let’s look at an example of how to remove a sequence in Oracle.

DROP SEQUENCE supplier_seq;

This example will remove the sequence supplier_seq.

FAQS

Q: When creating a sequence, what do the cache and nocache options mean? For example, you could create a sequence with the cache 20 option as follows:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

Or you could create the same sequence, but with the nocache option:

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;

A:As for the sequence, the cache option determines how many sequence values will be stored in memory for quick access.

The disadvantage of creating a sequence with cache is that if a system failure occurs, all cached sequence values that were not used will be lost. This will cause gaps in the values assigned to the sequence. When the system recovers, Oracle will cache new numbers from where the sequence was interrupted, ignoring the lost sequence values.

Note: To recover lost sequence values, you can always execute the ALTER SEQUENCE command to reset the counter to the correct value. nocache means that none of the sequence values are stored in memory. This option may reduce performance, however, you should not experience any gaps in the values assigned to the sequence.

Q: How do I set the lastvalue value to an Oracle sequence?

A: You can change the lastvalue for an Oracle sequence by executing the ALTER command in the sequence.

For example, if the last value of the Oracle sequence in use was 100 and you want the next value to be 225. You must execute the following commands.

ALTER SEQUENCE seq_name
IT'S BY 124;

SELECT seq_name.nextval FROM dual;

ALTER SEQUENCE seq_name.
IT'S BY 1;

The next sequence value to use is now 225.

SQL tutorial: SEQUENCE in Oracle Database