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 CURRVALpseudo-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 ADMINuser, and create a user (seq_useras example) with the CREATE USERcommand 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 RESOURCEprivilege 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 SEQUENCEprivilege 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 SEQUENCEprivilege. The following statement grants the seq_useruser 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_useruser to create a sequence.

Use the CREATE SEQUENCEstatement 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 BYattribute as 1, which creates an ascending sequence that increments the integer values by 1. To create a descending sequence set INCREMENT BYto a negative integer. The INCREMENT BYcannot 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 WITHclause is set to 0, which starts the catalog_seqsequence at the specified value of 0. The MINVALUEclause is set to 0, which is the minimum value in the catalog_seqsequence. The default MINVALUE for an ascending sequence is 1. The INCREMENT BYclause is set to 1. The ORDERclause 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 NEXTVALpseudo-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 WITHclause, which defaults to the MINVALUEclause setting.

the next value in a database sequence

To obtain a sequence of the next 10 values, use the CONNECT BY levelclause, 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 CURRVALpseudo-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 CURRVALpseudo-column, the NEXTVALpseudo-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_seqsequence, to set the MINVALUEto 1, MAXVALUEto 10, CACHEto 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 MINVALUEcannot 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 BYvalue must be less than the MAXVALUE-MINVALUEdifference, 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_seqsequence, 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 CYCLEclause, 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 KEYcolumn.

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 CURRVALpseudo-column, or the next value with the NEXTVALpseudo-column. To restart a sequence, several options are available:

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

As an example, if the current value is6, and the current value needs to be set to 1, call NEXTVAL5 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 CURRVALvalue:

SELECT

   catalog_seq.CURRVAL

FROM

   dual;

 

CURRVAL

———-

         1

1 row selected.

 

The RESTARTclause may be used as follows:

ALTER SEQUENCE catalog_seq RESTART;

The RESTARToption 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 catalogwith 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 catalogtable with an INSERT …VALUESstatement, 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 catalogtable 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_seqsequence could be used to update the catalogtable with an UPDATEstatement, as follows:

UPDATE catalog

   SET id = catalog_seq.NEXTVAL

   WHERE magazine_name = 'WebLogic Magazine';

The idvalue 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_seqsequence could be used to create a new table that is a duplicate of the catalogtable 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_seqsequence could be used to add values to an existing table by selecting from another table with an INSERT … SELECTstatement, as follows; the catalog_duptable 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_duptable:

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_seqsequence 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_seqsequence 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 catalogtrigger 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 catalogtable, and use the catalog_on_inserttrigger to add values for the idcolumn:

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 idcolumn 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_seqare listed as idcolumn values as shown in Figure 11.

Table id column values generated by a BEFORE INSERT ON trigger

The complete output from the Selectstatement 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_seqsequence 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!

About the Author

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.

Start the discussion at forums.toadworld.com