A Sequence is a database object which generates integer sequence. We generally use it for populating numeric Primary Key columns.

In order to create a sequence we use create sequence DDL statement. Let’s take a look at CREATE SEQUENCE DDL Syntax

CREATE SEQUENCE sequence_name
 [START WITH start_num]
 [INCREMENT BY increment_num]
 [MAXVALUE maximum_num | NOMAXVALUE]
 [MINVALUE minimum_num | NOMINVALUE]
 [CACHE cache_num | NOCACHE]
 [CYCLE | NOCYCLE]
 [ORDER | NOORDER];

Create Sequence DDL statement starts with CREATE and SEQUENCE, both of which are Oracle Reserved keywords followed by the name of the sequence which is purely user defined meaning you can give any name of your choice to your sequence. Next we have few attributes of the sequence. First attribute is: 

START WITH

Here you have to specify a numeric value from which you want your sequence to start. Whatever number you specify will be the first number generated by your sequence.

INCREMENT BY

This attribute also takes a numeric value, to increment the sequence by. The number that you specify here will serve as the interval between sequence numbers. The value for INCREMENT BY cannot be 0 but it can be any positive or negative value. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

MAXVALUE / NOMAXVALUE

Next attribute is MAXVALUE or NOMAXVALUE. Using these attributes you can set the maximum upper bound for your sequence. Always remember MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE attribute. In case you don’t want to set the MAXVALUE for your sequence then you can use NOMAXVALUE attribute.

MINVALUE / NOMINVALUE

Similar to MAXVALUE we use MINVALUE attribute to set the lower bound of our sequence. As a value this attribute also accepts the numeric value and should be less than or equal to START WITH as well as less than MAXVALUE. In case you don’t want to set the lower bound for your sequence then you can use NOMINVALUE attribute instead. 

CACHE/ NOCACHE

As the value of cache attribute, you specify the number of integers to keep in memory. The default number of integers to cache is 20. The minimum number of integers that may be cached is 2. The maximum integers that may be cached is determined by the formula: 

CEIL(maximum_num -minimum_num)/ABS(increment_num).

Specify NOCACHE to indicate that values of the sequence are not pre-allocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

CYCLE/NOCYCLE

CYCLE and NOCYCLE are two flags which you have to set. If you set the flag on cycle then your sequence continues to generate values after reaching either its maximum or minimum value. You specify NOCYCLE flag when you do not want your sequence to generate more values after reaching its maximum or minimum value. If in case you omit both these flags then by default oracle engine will set the flag on NOCYCLE.

ORDER/ NOORDER

At last we have two more flags which are ORDER and NOORDER. ORDER Flag guarantees that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences that are used to generate primary keys.

Set the flag on NOORDER if you do not want to guarantee that the sequence numbers are generated in order of request. NOORDER is the default flag in case you omit either of them.

How to Create a Sequence

Let’s create a sequence by the name of sq_demo 

CREATE SEQUENCE sq_demo1
 START WITH 1
 INCREMENT BY 2
 MAXVALUE 10 
 MINVALUE 1
 CACHE 3
 CYCLE 
 ORDER;

This is a simple sequence by the name of sq_demo which is starting with 1 and the interval between every sequence is 2. The Max value for this sequence is 10 and Min value is 1 and this sequence will cache 3 integers at a time and will cycle after reaching the maximum value which is 10. Also I have set ORDER FLAG which means the numbers will be generated in the guaranteed order. Few things which you should know: 

1. You can specify any of these attributes and flags in any order. Means order of these flags is not fixed.
2. All these attributes and flags are optional. If you omit all of them then oracle engine will create a default sequence for you. Let’s see an example

CREATE SEQUENCE sq_demo02;

How To Use A Sequence

To use a sequence we use NEXTVAL and CURRVAL. Both these are pseudo columns of a sequence using which we can retrieve next value and current value of a sequence. NEXTVAL column returns the next value of the sequence as well as initializes the sequence whereas CURRVAL column will return the current value of the sequence.

SELECT sq_demo.NEXTVAL FROM dual;

This query will initialize and return the first value of your newly created sequence.

To get the current value of your sequence you use CURRVAL pseudo column of a Sequence let’s see how:

SELECT sq_demo.CURRVAL FROM dual;

This query will give you the current value stored in your sequence.

Points to be remembered: Before using any sequence it’s mandatory to initialize it first. If you will try to retrieve current value without initializing it then it will give you an error. We use NEXTVAL pseudo column to initialize a sequence as well as to retrieve next value of the sequence. This means after creating a sequence you have to execute the NEXTVAL query before the CURRVAL one.

How To Populate Numeric Primary key column using a Sequence

Suppose there is a table by the name of demo which has two columns demo_id and demo_name where column demo_id is a numeric primary key column and column demo_name is capable of holding variable character string as its data type is VARCHAR2. You can use sequence to populate primary key column. Let’s see how:

INSERT INTO demo VALUES (sq_demo.nextval, ‘xyz’);

How To Modify A Sequence

You can modify a sequence using the ALTER SEQUENCE statement. There are some limitations on what you can modify in a sequence such as:

1. You cannot change the start value of a sequence.
2. The minimum value cannot be more than the current value of the sequence.
3. The maximum value cannot be less than the current value of the sequence.

Suppose you want to modify the value of INCREMENT BY attribute from 2 to 4, so for that ALTER SEQUENCE command will be:

SQL>ALTER SEQUENCE sq_demo INCREMENT BY 4;

How To Drop A Sequence

To delete any sequence from the schema we use DROP DDL statement. Say you want to delete the Sequence sq_demo which we just created, so for that the statement will be:

SQL>DROP SEQUENCE sq_demo;

That’s all guys in this blog on SQL sequence in Oracle Database. Hope it was useful. Kindly please share this with your friends on social network and help me reach out to more people. Thanks and have a great day!