Toad World Blog

How to Create an Oracle Database Sequence: Explained with Examples

Mar 29, 2022 8:00:00 AM by Deepak Vohra

It is often necessary to assign integer column values that are in sequence across different rows of data in a relational database. Oracle Database provides Oracle Sequence for creating a sequence of integers, with additional features such as cache, upper/lower limits, and flexibility that lets a user create a customizable database sequence of integers. This is in contrast to the AUTO_INCREMENT attribute in MySQL which is limited in scope as it only generates a sequence of integers that increment by 1, with no provision to set upper or lower limits, or cache values.

Blog-TW-OracleDatabaseSequence-JY-71545

What is an Oracle Sequence?

An Oracle Sequence is a database object, just like a table or view, that represents a sequence of integers that can be used by any table or view in the global database namespace. A Sequence’s values can be accessed using the NEXTVAL, and CURRVAL pseudo-columns. A Sequence can be ascending or descending.

MediaBanner-ToadWorld-600x100-IM-JY-63709.psd-1-Jan-18-2022-05-54-52-11-PM

Preliminary setup

This article is based on Oracle Database 19c; for earlier versions, such as 12c, some differences may exist. As a preliminary setup, install Toad for Oracle, create an instance of Oracle Autonomous Database 19c (or other), and connect to the database instance, all of which is discussed in How to download Toad for Oracle.

Create a user and grant privileges

Connect as the ADMIN user, and create a user (seq_user as example) with the CREATE USER command that we’ll use to create a Sequence.

CREATE USER seq_user

   IDENTIFIED BY Seq_usr_1_pw

   DEFAULT TABLESPACE example

   QUOTA 10M ON example

   TEMPORARY TABLESPACE temp

   QUOTA 5M ON system;

 

User created.

Grant privileges to the user to connect and create a session. Grant the RESOURCE privilege that lets a user create a sequence in its own schema, in addition to creating other database objects such as a table and a PL/SQL procedure.

GRANT CONNECT, RESOURCE, CREATE SESSION to seq_user;

The CREATE SEQUENCE privilege may also be grant separately to an existing user:

GRANT CREATE SEQUENCE TO seq_user;

The CREATE SEQUENCE privilege only lets a user create a Sequence in its own schema. To allow a user to create a Sequence in any schema, grant the CREATE ANY SEQUENCE privilege. The following statement grants the seq_user user the privilege to create/alter/drop a sequence in any schema.

GRANT CREATE ANY SEQUENCE, ALTER ANY SEQUENCE, DROP ANY SEQUENCE, SELECT ANY SEQUENCE TO seq_user;

Create a sequence

Connect as the seq_user user to create a sequence.

Use the CREATE SEQUENCE statement to create an Oracle Database Sequence. The CREATE SEQUENCE statement supports several clauses to customize a sequence. By default a new sequence does not have a minimum/maximum limit for the sequence of integers, does not cycle values after reaching its minimum/maximum value, and does not guarantee that sequence numbers are generated in the order of request. By default, a database sequence has the INCREMENT BY attribute as 1, which creates an ascending sequence that increments the integer values by 1. To create a descending sequence set INCREMENT BY to a negative integer. The INCREMENT BY cannot be set to 0.

Create an example sequence, catalog_seq, for a magazine catalog:

CREATE SEQUENCE catalog_seq

START WITH     0

MINVALUE         0

INCREMENT BY   1

ORDER;

The START WITH clause is set to 0, which starts the catalog_seq sequence at the specified value of 0. The MINVALUE clause is set to 0, which is the minimum value in the catalog_seq sequence. The default MINVALUE for an ascending sequence is 1. The INCREMENT BY clause is set to 1. The ORDER clause guarantees that the sequence integers are generated in the order of the request.

The sequence created as the output from the command is shown in Figure 1.

output from CREATE SEQUENCE statement

Get the next value

To get the next value in sequence use the NEXTVAL pseudo-column, as example:

SELECT

   catalog_seq.nextval

FROM

   dual;

The next value in the database sequence gets output as shown in Figure 2. The first value is the value set in the START WITH clause, which defaults to the MINVALUE clause setting.

the next value in a database sequence

To obtain a sequence of the next 10 values, use the CONNECT BY level clause, as shown:

SELECT

   catalog_seq.NEXTVAL

FROM

   dual

CONNECT BY level <= 10;

 

The next 10 values get output:

 

   NEXTVAL

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

       10

 

10 rows selected.

Get the current value

Get the current value of a sequence with the CURRVAL pseudo-column:

SELECT

   catalog_seq.CURRVAL

FROM

   dual;

The current value gets output as shown in Figure 3.

 the next value in a database sequence-1

To use the CURRVAL pseudo-column, the NEXTVAL pseudo-column must have been called at least once, or the following error message is generated:

Error at line 4

ORA-08002: sequence CATALOG_SEQ.CURRVAL is not yet defined in this session

Alter a sequence

Use the ALTER SEQUENCE statement to alter a database sequence. As an example, alter the catalog_seq sequence, to set the MINVALUE to 1, MAXVALUE to 10, CACHE to 5, and set the CYCLE attribute:

ALTER SEQUENCE catalog_seq

   MINVALUE 1

   MAXVALUE 10

   CYCLE

   CACHE 5;

Altering a sequence may generate errors, since using different clauses and a combination of clauses, has some requirements. As an example, the MINVALUE cannot be made to exceed the current value, or the following error is generated:

ORA-04007: MINVALUE cannot be made to exceed the current value

The number to cache must be less than one cycle, or the following error message is generated:

ORA-04013: number to CACHE must be less than one cycle

The INCREMENT BY value must be less than the MAXVALUE-MINVALUE difference, or the following error message is generated:

ORA-04005: INCREMENT must be less than MAXVALUE minus MINVALUE

Most of these error conditions could also get generated when creating a new database sequence.

As the output in Figure 4 indicates, the sequence gets altered.

sequence altered

If the next 15 values are obtained, after altering the catalog_seq sequence, the output is as follows:

SELECT

   catalog_seq.NEXTVAL

FROM

   dual

CONNECT BY level <= 15;

 

NEXTVAL

----------

         2

         3

         4

         5

         6

         7

         8

         9

       10

         1

         2

         3

         4

         5

         6

 

15 rows selected.

Because we set the CYCLE clause, the sequence numbers are cycled after reaching maximum value. Such a cycled sequence cannot be used for a column that requires unique values such as a PRIMARY KEY column.

Try Toad free for 30 days. 

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

Restart a sequence

Once a sequence has been initialized, it only returns either the current value with the CURRVAL pseudo-column, or the next value with the NEXTVAL pseudo-column. To restart a sequence, several options are available:

  1. Drop the sequence and create a new sequence
  2. Call the NEXTVAL multiple times to increment it to bring the current value to a specific value that is needed
  3. Use the RESTART clause

As an example, if the current value is 6, and the current value needs to be set to 1, call NEXTVAL 5 times as follows:

SELECT

   catalog_seq.NEXTVAL

FROM

   dual

CONNECT BY level <= 5;

 

 

   NEXTVAL

----------

         7

         8

         9

       10

         1

 

5 rows selected.

 

The current value gets set to 1, as indicated by the CURRVAL value:

SELECT

   catalog_seq.CURRVAL

FROM

   dual;

 

CURRVAL

----------

         1

1 row selected.

 

The RESTART clause may be used as follows:

ALTER SEQUENCE catalog_seq RESTART;

The RESTART option was added in Oracle Database 18c, and is not available for earlier versions.

Use a sequence

We have not yet used the catalog_seq sequence, and only discussed how to create the sequence, access its values and alter it.

In this section and sub-sections, we discuss the several types of statements and constructs in which a sequence could be used. First, create a database table called catalog with a primary key column id, and a second column to store a magazine name.

CREATE TABLE catalog (

     id INT NOT NULL PRIMARY KEY,

     magazine_name VARCHAR2(255) NOT NULL

);

Use a sequence in an INSERT ...VALUES statement

The catalog_seq sequence could be used to add values to the catalog table with an INSERT ...VALUES statement, as follows:

INSERT INTO catalog(id, magazine_name) VALUES(catalog_seq.CURRVAL, 'Oracle Magazine');

INSERT INTO catalog(id, magazine_name) VALUES(catalog_seq.NEXTVAL, 'Java Magazine');

INSERT INTO catalog(id, magazine_name) VALUES(catalog_seq.NEXTVAL, 'WebLogic Magazine');

The integers generated by the column get added to the catalog table as indicated by output in Figure 5.

 using a database Sequence in a INSERT...VALUES statement

Subsequently, select table data, and the data added gets listed:

SELECT * FROM catalog;

 

       ID

----------

MAGAZINE_NAME                                                                  

--------------------------------------------------------------------------------

         1

Oracle Magazine                                                                

                                                                              

         2

Java Magazine                                                                  

                                                                              

       3

WebLogic Magazine                                                              

                                                                              

 

3 rows selected.

Use a sequence in an UPDATE statement

The catalog_seq sequence could be used to update the catalog table with an UPDATE statement, as follows:

UPDATE catalog

   SET id = catalog_seq.NEXTVAL

   WHERE magazine_name = 'WebLogic Magazine';

The id value for a column gets updated as shown by output in Figure 6.

using a database sequence in an UPDATE

Subsequently, list the updated data, and the updated column value gets listed:

 

SELECT * FROM catalog;

 

ID

----------

MAGAZINE_NAME                                                                  

--------------------------------------------------------------------------------

         1

Oracle Magazine                                                                

                                                                             

         2

Java Magazine                                                                  

                                                                              

         4

WebLogic Magazine                                                              

                                                                              

 

3 rows selected.

Use a sequence in a CREATE TABLE ... AS SELECT statement

The catalog_seq sequence could be used to create a new table that is a duplicate of the catalog table with a CREATE TABLE ... AS SELECT statement, as follows:

 

CREATE TABLE catalog_dup

   AS SELECT catalog_seq.NEXTVAL, magazine_name FROM catalog;

The new table catalog_dup gets created, as show by output in Figure 7.

 using a database sequence in a CREATE TABLE...AS SELECT

The new table also gets data from the table it is created from, but with new id column values:

SELECT * FROM catalog_dup;

 

   NEXTVAL

----------

MAGAZINE_NAME                                                                  

--------------------------------------------------------------------------------

         5

Oracle Magazine                                                                

                                                                             

         6

Java Magazine                                                                  

                                                                              

        7

WebLogic Magazine                                                              

                                                                              

 

3 rows selected.

Use a sequence in an INSERT ... SELECT statement

The catalog_seq sequence could be used to add values to an existing table by selecting from another table with an INSERT ... SELECT statement, as follows; the catalog_dup table is truncated before adding new data:

 

TRUNCATE TABLE catalog_dup;

INSERT INTO catalog_dup

   SELECT catalog_seq.NEXTVAL, magazine_name

   FROM catalog;

New data gets added as shown by output in Figure 8.

using a sequence in an INSERT...SELECT statement

Subsequently, select data from the catalog_dup table:

SELECT * FROM catalog_dup;

   NEXTVAL

----------

MAGAZINE_NAME                                                                  

--------------------------------------------------------------------------------

         8

Oracle Magazine                                                                

                                                                              

       9

Java Magazine                                                                  

                                                                              

       10

WebLogic Magazine                                                              

                                                                              

 

3 rows selected.

Use a sequence in a PL/SQL procedure

The catalog_seq sequence could be used to add values to an existing table, catalog_java_magazine, by selecting from a sequence with a PL/SQL procedure. First, create the new table catalog_java_magazine:

CREATE TABLE catalog_java_magazine (

     magazine_id INT NOT NULL,

     article_id INT NOT NULL PRIMARY KEY

     title VARCHAR2(255) NOT NULL

);

Subsequently, run the following PL/SQL procedure:

DECLARE

c_seq NUMBER;

BEGIN

SELECT catalog_seq.CURRVAL

INTO   c_seq

FROM   dual;

 

INSERT INTO catalog_java_magazine (magazine_id, article_id, title) VALUES(c_seq,1,'Java 8');

INSERT INTO catalog_java_magazine (magazine_id, article_id, title) VALUES(c_seq,2,'Java 11');

 

COMMIT;

END;

As the output in Figure 9 indicates the PL/SQL procedure completes successfully.

 using a database Sequence in a PL SQL Procedure

Select the new data added:

SELECT * FROM catalog_java_magazine;

MAGAZINE_ID ARTICLE_ID

----------- ----------

TITLE                                                                          

--------------------------------------------------------------------------------

         10         1

Java 8                                                                          

                                                                              

         10         2

Java 11                                                                        

                                                                                

 

2 rows selected.

Use a sequence in a trigger

The catalog_seq sequence could be used to add values to an existing table, catalog, by selecting from a sequence with a trigger. First, create a BEFORE INSERT ON catalog trigger catalog_on_insert:

CREATE OR REPLACE TRIGGER catalog_on_insert

BEFORE INSERT ON catalog

FOR EACH ROW

BEGIN

SELECT catalog_seq.nextval

INTO :new.id

FROM dual;

END;

The trigger gets created as shown in Figure 10.

using a database Sequence in a PL SQL Procedure

Subsequently, truncate the catalog table, and use the catalog_on_insert trigger to add values for the id column:

TRUNCATE TABLE catalog;

 

INSERT INTO catalog(magazine_name) VALUES('Oracle Magazine');

INSERT INTO catalog(magazine_name) VALUES('Java Magazine');

INSERT INTO catalog(magazine_name) VALUES('WebLogic Magazine');

Note that the id column is omitted because its values are generated by a sequence and added by trigger. Select data added from catalog, and the database sequence of integers generated by the catalog_seq are listed as id column values as shown in Figure 11.

Table id column values generated by a BEFORE INSERT ON trigger

The complete output from the Select statement is as follows:

SELECT * FROM catalog;

 

       ID

----------

MAGAZINE_NAME                                                                  

--------------------------------------------------------------------------------

         1

Oracle Magazine                                                                

                                                                              

       2

Java Magazine                                                                  

                                                                              

         3

WebLogic Magazine                                                              

                                                                              

 

3 rows selected.

Drop a database sequence

To drop the catalog_seq sequence use the DROP SEQUENCE statement, as follows:

DROP SEQUENCE catalog_seq;

The sequence gets dropped as indicated by output in Figure 12.

 database sequence dropped

In this article we discussed how to create, and use an Oracle Database sequence

 

More information

Blog: SQL: Different ways to generate sequence

Blog: Oracle Tablespaces and Datafiles Overview

Blog: Why an Oracle Ace thinks Toad® has the best SQL editor in the business

 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

Tags: Toad for Oracle

Deepak Vohra

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.