Oracle Create Sequence Tutorial

In this oracle tutorial, we will learn how to construct a new sequence in Oracle using the Oracle CREATE SEQUENCE statement.

An Oracle object called a sequence is used to create a numerical sequence. When you need to generate a special number to use as a primary key, this can be helpful.

Here we will discuss

• How to create a Sequence in Oracle 21c
• Oracle create sequence if not exists
• Oracle create sequence to increment by 1
• Oracle create sequence with start value from select
• Oracle create sequence for primary key
• Oracle create sequence order
• Oracle create sequence with max value
• Oracle create sequence replace
• Oracle create sequence on column
• Oracle create sequence and trigger
• Oracle create alter sequence
• Oracle create sequence in stored procedure
• Oracle create sequence default values

How to create a sequence in oracle 21c

• Only users with the CREATE SEQUENCE privilege can create Sequences in their own schema. Granting the Build ANY SEQUENCE privilege will enable a user to create a Sequence in any schema.
• No matter whether the transaction commits or rolls back, the sequence is incremented when a sequence number is generated.

Oracle create sequence syntax

Till now, we have understood what sequencing is in a database and we also discussed the purpose of sequencing. Next, we will take a step further and understand how to create a sequence in oracle 21c.

For this, we need to understand the syntax to create a sequence in oracle 21c which is given below.

CREATE SEQUENCE schema_name.seq_name
[INCREMENT BY interval]
[START WITH initial_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE]
[ORDER | NOORDER];

In the above syntax

• CREATE SEQUENCE: After the CREATE SEQUENCE keywords, add the sequence’s name.
• INCREMENT: After the INCREMENT BY keyword, it specifies the gap between sequence numbers. Less than 28 digits can comprise the interval. Furthermore, it has to be lower than MAXVALUE – MINVALUE. The sequence will begin producing numbers at 1 and increase by 1.
• start: This parameter defines the initial number in the sequence. The seq series begins with the given value of 0 because the START WITH clause is set to 0.
• MAXVALUE: Indicate the sequence’s greatest value.
• If you want to specify a maximum value of 10^27 for an ascending series or -1 for a descending sequence, you can specify NOMAXVALUE.
• MINVALUE: MINVALUE should be less than or similar to START WITH and must be lower than MAXVALUE.
• NOMINVALUE: To provide a minimum value of 1 for an ascending series or -10^26 for a descending sequence, use the NOMINVALUE keyword.
• CYCLE: If we define CYCLE, the sequence will keep producing values even after it reaches either its minimum or maximum value.
• NOCYCLE: If you want the sequence to end by producing the next number once it reaches its limit, use NOCYCLE.
• CACHE: You can specify how many sequence values Oracle will preallocate and maintain in memory for quicker access. The cache’s smallest size is two.
• Order: To make sure Oracle generates sequence numbers in the order of requests, use ORDER.

Note:

You need to have the CREATE SEQUENCE system privilege in order to create a sequence in your own schema.

Read: Oracle Database vs MySQL Database

Oracle Create Sequence Example

After discussing the syntax, Now we will discuss the example of how to create a sequence in oracle.

CREATE SEQUENCE id_seq
    INCREMENT BY 10
    START WITH 10
    MINVALUE 10
    MAXVALUE 100
    CYCLE
    CACHE 2;

In the above example, we created a sequence called, starting from 5, incrementing by 20, minimum value 2, maximum value 1000. The CYCLE parameter causes the sequence to return 5 once it reaches 1000.

We will utilize the NEXTVAL to obtain the next value in the sequence.

SELECT 
    new_seq.NEXTVAL 
FROM 
    dual;

This is how to create a sequence in oracle 21c.

Read: How to create a database in Oracle 19c

Oracle create sequence to increment by 1

To construct a series for the customer number column, use the simple Create Sequence command. Because there is no Order clause provided, the series will begin generating numbers at 1 and increase by 1, but not in any particular order.

Example:

CREATE SEQUENCE customer_seq
INCREMENT BY 1;

We will utilize the NEXTVAL to obtain the next value in the sequence.

SELECT 
    customer_seq.NEXTVAL 
FROM 
    dual;

In this example, we have understood how to create a sequence to increment by 1 in oracle.

Read: How to connect to the Oracle database

Oracle create sequence with a max value

• Now we will discuss how to create a sequence in oracle with having max value.
• In this example, we will create a sequence with the MAXVALUE option. After reaching number six, this sequence will stop generating the number.
• The first number specified after the START WITH keywords must be equal to or more than the max value.

Oracle Create Sequence with having max value Example

Now we will discuss the example of how to create a sequence in oracle with max value.

CREATE SEQUENCE employee_new_seq start with 1
INCREMENT BY 1 maxvalue 6;

We will use the NEXTVAL to fetch the next value in the sequence.

SELECT 
    employee_new_seq.NEXTVAL 
FROM 
    dual;

In the above example, we have set the starting point of the sequence and the MAXVALUE to set the endpoint of the sequence. In this example, the starting value is 1 and the max value is 6.

If you will set the starting point=7 then it will raise an error because we have already set the maximum value as 6.

This kind of error raises if you set the starting value to more than the max value.

Read: How to create a user in Oracle

Oracle create sequence order

• In this section, we will understand how to create a sequence to produce numbers in order.
• To make sure Oracle generates sequence numbers in the order of requests, by using the ORDER option.

For this, we need to understand the syntax to create a sequence to generate numbers in order in oracle 21c which is given below.

CREATE SEQUENCE seq_name
[INCREMENT BY order]
[START WITH initial_number]
[MAXVALUE max_value | NOMAXVALUE]

Oracle Create Sequence Example

After discussing the syntax, Now we will discuss the example of how to create a sequence in oracle.

CREATE SEQUENCE student_seq
    INCREMENT BY 1 order
    START WITH 90
    MAXVALUE 100;

In this example, we have created a sequence ‘student_seq’ and assigned the starting value of 90 with a maximum value that is 100. Now we want to display the numbers between 90-100 in sequence order increment by 1 value.

SELECT 
    student_seq.NEXTVAL 
FROM 
    dual
CONNECT BY level <=9;

In the above code, we used the nextval for generating the next value and then use This SELECT statement using the student_seq.NEXTVAL value repeatedly increments by 1.

Note:

The CONNECT BY clause establishes the hierarchy between the parent rows and the child rows.

This is how to create a sequence to generate numbers in order.

Read: How to get database name in Oracle

Oracle create alter sequence

• Here we will discuss how to modify a sequence object’s attributes and behavior using the Oracle ALTER SEQUENCE statement.
• We can easily change an existing sequence’s increment, minimum and maximum values, and cached number with the ALTER SEQUENCE command.

Oracle alter sequence syntax

Till now, we have understood what altering is in a database and we also discussed the purpose of altering. Next, we will take a step further and understand how to alter a sequence in oracle 21c.

For this, we need to understand the syntax to alter a sequence in oracle 21c which is given below.

ALTER SEQUENCE sequence_name
[INCREMENT BY interval]
[START WITH initial_number]
[MAXVALUE max_value | NOMAXVALUE]
[MINVALUE min_value | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE cache_size | NOCACHE]
[ORDER | NOORDER];

In this example, we will modify the increment value of a given sequence for example when we created the sequence we mention the increment value 2. Now we want to change the value from 2 to 3.

For this first, we will create the sequence and set the starting value max value as well as the increment option.

CREATE SEQUENCE number_sequence
    INCREMENT BY 2 
    START WITH 6
    MAXVALUE 20;
SELECT 
    number_sequence.NEXTVAL 
FROM 
    dual
CONNECT BY level <=7;

Now we will change the increment value by 2 to 3 by using the alter statement

ALTER SEQUENCE number_sequence 
INCREMENT BY 3;

Now to display the values after modifying, we will use the below query

SELECT 
    number_sequence.NEXTVAL 
FROM 
    dual
CONNECT BY level <4;

As you can see in the Screenshot the values have been updated and the values are incremented by 3.

Read: How to check database status in Oracle

Oracle create sequence on column

• In this section, we will discuss how to create a sequence on a column in the Oracle 21c
• The CREATE TABLE statement allows you to specify the sequence’s NEXTVAL configuration and assign a sequence to a table column.

Here is the table for our example execution and this customers table is located in the pdb database.

Next, we will create a sequence for the customer_id column of the customers table

CREATE SEQUENCE customer_id_seq;

After creating the sequence we will insert the values into the customer table

INSERT INTO customers VALUES(customer_id_seq.NEXTVAL, 'Sequence created in Oracle');

INSERT INTO customers VALUES(customer_id_seq.NEXTVAL, 'Examine Sequence Values');

Now to fetch the data into the column, we can easily use the below query

SELECT  
    customer_id, title
FROM
    customers;

In this example, we have understood how to create a sequence on a column in the Oracle 21c.

Read: Install sample schemas 21c in Oracle

Oracle create sequence for primary key

• Now let us discuss how to create a sequence for the primary key in oracle.
• The table we’ll be utilizing must first be CREATED before we can create a unique primary key for it.

Here is the table for our example execution and this employees table is located in the pdb database.

In the above employee table, we have assigned the emp_id as a primary key and now I want to create a sequence in the primary key. For this first, we will create a sequence.

Next, we will create a sequence for the emp_id column of the employees table

CREATE SEQUENCE empi_id_seq;

In this step, we’ll design the SEQUENCE that will be used to produce the unique, After creating the sequence we will insert the values into the employee table

INSERT INTO employees VALUES(empi_id_seq.NEXTVAL, 'U.S.A');
INSERT INTO employees VALUES(empi_id_seq.NEXTVAL, 'China');

Now we will display the primary key column values

SELECT  
    emp_id, country
FROM
    employees;

Oracle create sequence and trigger

In this instance, we want to run our TRIGGER first, making sure that it increments our SEQUENCE and passes the new value onto our primary key column before we insert data into the employees database.

Oracle create sequence replace

If a procedure with the same name already exists in the database schema, Oracle CREATE OR REPLACE’s OR REPLACE clause enables the compiler to replace it.

In oracle, we can also use Create or replace but are restricted to some objects like sequences. Therefore we cannot replace the existing sequence in oracle.

As you can see in the Screenshot the error has occurred and the possible reason behind this error is that replace does not work in sequence objects while working with oracle 21c.

Read: How to backup a table in Oracle

Oracle create sequence default values

• In this section, we will discuss how to create a sequence with default values in oracle.
• To create a sequence, the CREATE SEQUENCE statement supports a number of clauses. By default, a new sequence of integers does not have a minimum or maximum limit.
• The default value for the INCREMENT BY feature in a database sequence is 1, which results in an ascending sequence that increases the integer values by 1. Set INCREMENT BY to a negative integer to produce a descending order. It is not possible to set INCREMENT BY to 0.
• The seq series begins with the given value of 0 because the START WITH clause is set to 0.

Example of Oracle create sequence default values

CREATE SEQUENCE new_sequence
START WITH  0
MINVALUE    0
INCREMENT BY 1
ORDER; 

The new_sequence sequence begins with the provided value of 0 because the START WITH clause is set to 0. The minimum value in the new_sequence sequence is set to 0 in the MINVALUE clause.

An ascending sequence’s default minimum value is 1. The value of the INCREMENT BY clause is 1. The ORDER clause ensures that the request-based order will be maintained while generating the sequence integers.

Read: How to Get List all Tables in Oracle Database

Oracle create sequence if not exists

• Now let us understand how to create a sequence in oracle if does not exists in oracle.
• To discover all sequences available to the schema user, use the select sequence name from all sequences.

select * from User_Sequences
where sequence_name='number_se';

Now we will take an existing sequence.

Oracle create sequence with start value from select

• In this section, we will discuss how to create a sequence with a start value from select.
• Only users with the CREATE SEQUENCE privilege can create Sequences in their own schema. Granting the Build ANY SEQUENCE privilege will enable a user to create a Sequence in any schema.

declare
 ID number;
begin
  select MAX(EMP_ID)  + 1 into ID from EMPLOYEES;
  If ID > 0 then
    begin
            execute immediate 'DROP SEQUENCE new_sequence';
      exception when others then
        null;
    end;
    execute immediate 'CREATE SEQUENCE new_sequence INCREMENT BY 1 START WITH ' || ID || ' NOCYCLE CACHE 20 NOORDER';
  end if;
end;

In the above code first, we created the employees table and then we add the values into the emp_id column select MAX(EMP_ID) + 1 into ID from EMPLOYEES;

Next, we will fetch the values by using the below query

SELECT 
    new_sequence.NEXTVAL 
FROM 
    dual;

Read: How to add a primary key in Oracle

Oracle create sequence in stored procedure

• Here we will discuss how to create a sequence in a stored procedure in oracle 21c.
• An important layer of protection is added by a stored procedure between the user interface and the database. Because end users can add or modify data but not develop procedures, it enables security through data access controls.

Here is the table for our example execution and this student table is located in the pdb database.

Now we will discuss the example of how to create a sequence in oracle.

CREATE SEQUENCE new_stu_seq
   START WITH 1
   INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE stu_Insert AS
  BEGIN
      INSERT INTO student (stu_id)
        VALUES (new_stu_seq.nextval);
        COMMIT;
    END stu_Insert;

You may also like to read the following Oracle tutorials.

• Connect to Oracle Database using Python
• How to Fetch Data from Oracle Database in Python
• Connect Excel to Oracle Database

In this oracle tutorial, we have learned how to create a new sequence in Oracle using the Oracle CREATE SEQUENCE statement. And also we have covered the following given topics

• How to create a Sequence in Oracle 21c
• Oracle create sequence if not exists
• Oracle create sequence to increment by 1
• Oracle create sequence with start value from select
• Oracle create sequence for primary key
• Oracle create sequence order
• Oracle create sequence with max value
• Oracle create sequence replace
• Oracle create sequence on column
• Oracle create sequence and trigger
• Oracle create alter sequence
• Oracle create sequence in stored procedure
• Oracle create sequence default values