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.
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.
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.
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.
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.
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.
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.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:
- Drop the sequence and create a new sequence
- Call the NEXTVALmultiple times to increment it to bring the current value to a specific value that is needed
- 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.
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.
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.
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.
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.
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.
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.
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.
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!
Start the discussion at forums.toadworld.com