An Essential Guide to the Oracle Sequence By Practical Examples

What is a sequence

A sequence is a list of integers in which their orders are important. For example, the (1,2,3,4,5) and (5,4,3,2,1) are totally different sequences even though they have the same members.

Creating a sequence

The CREATE SEQUENCE statement allows you to create a new sequence object in your own schema.

For example, this statement uses the CREATE SEQUENCE statement to create a new sequence object named item_seq:

CREATE SEQUENCE item_seq;Code language: SQL (Structured Query Language) (sql)

You use the sequence object to generate a sequence of unique integers, mostly for surrogate key columns.

Note that Oracle 12c automatically generates a sequence object associated with the identity column of the table.

Using a sequence

To access the next available value for a sequence, you use the NEXTVAL pseudo-column:

SELECT item_seq.NEXTVAL
FROM dual;Code language: SQL (Structured Query Language) (sql)

   NEXTVAL
----------
         1

Once, you acquire the sequence number through the NEXTVAL pseudo-column, you can access it repeatedly using the CURRVAL pseudo-column:

SELECT item_seq.CURRVAL
FROM dual;Code language: SQL (Structured Query Language) (sql)

   CURRVAL
----------
         1Code language: SQL (Structured Query Language) (sql)

The following statement uses the item_seq sequence repeatedly in an SQL statement:

SELECT item_seq.NEXTVAL
FROM   dual
CONNECT BY level <= 5;Code language: SQL (Structured Query Language) (sql)

   NEXTVAL
----------
         2
         3
         4
         5
         6

This example uses the item_seq sequence in the INSERT statements to populate values for the item_id column of the items table:

CREATE TABLE items(
    item_id NUMBER
);

INSERT INTO items(item_id) VALUES(item_seq.NEXTVAL);
INSERT INTO items(item_id) VALUES(item_seq.NEXTVAL);

COMMIT;

SELECT item_id FROM items;Code language: SQL (Structured Query Language) (sql)

Here is the output:

   ITEM_ID
----------
         7
         8Code language: SQL (Structured Query Language) (sql)

From Oracle 11g onward, you can use sequences in PL/SQL. Behind the scenes, Oracle still uses a query from the dual table, but it makes the code cleaner:

DECLARE
    v_seq NUMBER;
BEGIN
    v_seq  := item_seq.NEXTVAL;
    DBMS_OUTPUT.put_line('v_seq=' || v_seq);
END;Code language: SQL (Structured Query Language) (sql)

v_seq=9Code language: SQL (Structured Query Language) (sql)

Modifying a sequence

To modify the attributes and behavior of an existing sequence object, you use the ALTER SEQUENCE statement.

The following example uses the ALTER SEQUENCE to set the maximum value for the item_seq to 100:

ALTER SEQUENCE item_seq MAXVALUE 100;Code language: SQL (Structured Query Language) (sql)

Removing a sequence

To remove an existing sequence from the database, you use the DROP SEQUENCE statement. The following example uses the DROP SEQUENCE statement to delete the item_seq sequence:

DROP SEQUENCE item_seq;Code language: SQL (Structured Query Language) (sql)

Oracle sequence privileges

Oracle provides the CREATE SEQUENCE system privilege to allow you to create, alter, and drop sequences.

This statement grants a user the CREATE SEQUENCE privilege:

GRANT CREATE SEQUENCE 
TO user_name;Code language: SQL (Structured Query Language) (sql)

In addition, Oracle provides the following privileges that allow you to manipulate sequences in all schemas, including the built-in ones:

CREATE ANY SEQUENCE
ALTER ANY SEQUENCE
DROP ANY SEQUENCE
SELECT ANY SEQUENCE

Therefore, you should consider seriously before executing the following command:

GRANT CREATE ANY SEQUENCE, 
    ALTER ANY SEQUENCE, 
    DROP ANY SEQUENCE, 
    SELECT ANY SEQUENCE 
TO user_name;Code language: SQL (Structured Query Language) (sql)

If you are the owner of the sequence, you will have the full privileges on the sequence. To grant another user access to a sequence, you can grant the SELECT object privilege to that user as shown in the following command:

GRANT SELECT ON user_name.sequence_name 
TO another_user;Code language: SQL (Structured Query Language) (sql)