How to Use the CREATE SEQUENCE Statement in Oracle

In this article, we are going to talk about the Oracle CREATE SEQUENCE statement, the primary purpose of which is to offer a reliable means of generating unique and sequential numeric values, often utilized for primary key fields within database tables. This capability is particularly important in maintaining data integrity and efficiency, ensuring the orderly assignment of identifiers across diverse records. In essence, this command facilitates and streamlines data management processes. In exploring Oracle CREATE SEQUENCE, one tool seamlessly complements the process and enhances overall Oracle database management — dbForge Studio for Oracle. As we delve into the syntax and usage examples of CREATE SEQUENCE, you will witness how dbForge Studio becomes our perfect companion, offering a user-friendly interface and robust functionalities for efficient sequence creation and management.

Contents

• Understanding sequences in Oracle
• A detailed explanation of syntax components
• Advanced options and considerations
• Using sequences in practice
• Managing sequences
• Conclusion

Understanding sequences in Oracle

Let us begin this article with the basic concept of sequences and proceed to practical and more complex examples. As mentioned in the introduction, Oracle sequences are a fundamental and versatile feature designed to generate unique, ordered numeric values. The basic concept revolves around providing a systematic and efficient approach for managing unique identifiers within a database.

SEQUENCE is not the only tool in Oracle that is capable of generating unique numbers for identification in a database. Auto-increment can perform similar functions while operating in a slightly different way:

	Sequences	Auto-increment
What it does	Sequences are a more flexible way of generating unique numbers. You can use a sequence to create a set of numbers and then manually assign those numbers to different rows in different tables as needed.	In contrast, auto-increment is like an automatic counter managed by the database. When you insert a new row into a table, if a column is set to auto-increment, the database automatically assigns it a unique number, usually one more than the previous row.
How it works	You create a sequence with certain rules (like starting number, increment, etc.), and then you can fetch values from the sequence to use as unique identifiers wherever you need them in your database.	Auto-increment is like a self-updating number that saves you from manually specifying a unique identifier for each new record. The database takes care of it for you.
What’s the difference?	Sequences are independent of tables. They provide you with more control.	Auto-increments are typically tied to a specific table and the process is automatic.

Since we are focusing on the Oracle CREATE SEQUENCE statement in this article, let us take a look at its basic syntax:

CREATE SEQUENCE sequence_name
  [INCREMENT BY n]
  [START WITH n]
  [MAXVALUE n | NOMAXVALUE]
  [MINVALUE n | NOMINVALUE]
  [CYCLE | NOCYCLE]
  [CACHE n | NOCACHE]
  [ORDER | NOORDER];

If you encounter this type of query in the wild, it will look somewhat like this:

CREATE SEQUENCE product_id_seq
  MINVALUE 1
  MAXVALUE 999999
  START WITH 1000
  INCREMENT BY 1
  CACHE 50;

This sequence is designed to generate product IDs, starting from 1 and incrementing by 1, with a maximum value of 999999. The caching mechanism enhances efficiency by storing a batch of 50 values in memory for faster access. Stay tuned for the next section of our article for a detailed breakdown of the syntax.

A detailed explanation of syntax components

Syntax-wise, the CREATE SEQUENCE statement is rather versatile and can be easily adjusted to your particular needs with the help of the following operators:

• sequence_name: Specifies the name of the sequence.
• INCREMENT BY n: Determines the interval between sequence numbers. If not specified, the default value is 1.
• START WITH n: Sets the initial value of the sequence. If not specified, the default value is 1.
• MAXVALUE n | NOMAXVALUE: Establishes the maximum value for the sequence or signifies no upper limit.
• MINVALUE n | NOMINVALUE: Defines the minimum value for the sequence or indicates no lower limit.
• CYCLE | NOCYCLE: Specifies whether the sequence should cycle back to the minimum value after reaching the maximum (or vice versa) or not.
• CACHE n | NOCACHE: Determines how many sequence values are pre-allocated and stored in memory for better performance.
• ORDER | NOORDER: Dictates whether the generated sequence values should be in ascending order (default) or without any specific order.

You can include or exclude the mentioned operators from the final query in order to make it work specifically for you. Keep in mind that if you do not specify some of these values, Oracle will use the default ones.

Advanced options and considerations

We hope you are ready to jump from the basic syntax of CREATE SEQUENCE to some more advanced techniques because we are. These advanced options and considerations offer a high degree of customization and adaptability, allowing database administrators to tailor sequence behavior to specific deployment scenarios, whether in a clustered environment, with decimal support, or within a sharded database architecture.

SHARING Clauses: (METADATA, DATA, NONE)

• METADATA: Specifies that the metadata for the sequence is shared among all instances in a Real Application Clusters (RAC) environment. The actual sequence values, however, are not shared and are specific to each instance.
• DATA: Indicates that both the metadata and the sequence values are shared among all instances in an RAC environment. This option ensures that the sequences generate unique values across all instances.
• NONE: This is the default option, where neither the metadata nor the sequence values are shared among instances. Each instance maintains its own separate sequence.

SCALE, EXTEND, and NOSCALE options

• SCALE: Introduced in Oracle Database 18c, this option allows sequences to support decimal numbers. The scale specifies the number of decimal digits the sequence should maintain. For example, with a scale of 2, the sequence can generate values like 1.23, 2.45, etc.
• EXTEND: This option extends the maximum number of decimal digits that can be specified using this option. It is used in conjunction with SCALE to allow for a larger scale value.
• NOSCALE: The default option, which indicates that the sequence generates integer values only. This is the traditional behavior of sequences without decimal support.

SHARD and SESSION specifications

• SHARD: Introduced in Oracle Database 12c Release 2, this clause allows for creating sharded sequences in a sharded database. Sharded sequences are designed for use in sharded databases, where data is horizontally partitioned across multiple physical databases (shards). This enables sequences to be globally unique across all shards.
• SESSION: This clause, also introduced in Oracle Database 12c Release 2, is used in conjunction with the SHARD clause. It specifies that the sequence values are unique only within the context of a session, ensuring that each session has its own distinct set of sequence values in a sharded environment.

Using sequences in practice

While theoretical knowledge is no doubt important, too much of it can bore almost anyone. Besides, this knowledge is only helpful with practical application. Thus, let us go ahead and try it out using dbForge Studio for Oracle. This powerful integrated development environment (IDE) helps Oracle SQL developers increase PL/SQL coding speed and provides versatile data editing tools for managing in-database and external data.

With dbForge Studio for Oracle, you benefit from a bunch of features tailored specifically to streamline your workflow. The Smart PL/SQL Formatter ensures code readability and adherence to best practices, while Code Completion expedites the coding process by suggesting context-sensitive options. Code Snippets provide reusable code templates, simplifying the creation of common code structures, and the SQL Editor offers a comprehensive environment for writing, testing, and debugging SQL queries. These features collectively enhance productivity, reduce development time, and facilitate seamless database management.

Accessing sequence values (NEXTVAL and CURRVAL)

Before we even start experimenting with the functions, we need to create a simple sequence:

-- Creating a sequence named "example_sequence"
CREATE SEQUENCE example_sequence
  START WITH 1
  INCREMENT BY 1;

The query above establishes a sequence and names it example_sequence. This sequence initiates with a starting value of 1 and increments by one at each step.

Now that we have a sequence on hand, we can use the CURRVAL and NEXTVAL functions to retrieve values from it.

For example, to access the current sequence value without incrementing, you can use this query with CURRVAL:

-- Accessing current sequence value without incrementing
SELECT EXAMPLE_SEQUENCE.currval
  FROM dual;

However, when you need to retrieve one value in a sequence after another, use NEXTVAL:

-- Accessing the next sequence value
SELECT EXAMPLE_SEQUENCE.nextval
  FROM dual;

As you can see, every time you execute this query, Oracle returns the next value from the example_sequence we created earlier.

Practical examples and scenarios

Looking back at what we have already covered, we can now move on to some real-life examples where sequences can be an irreplaceable tool to get the job done. For instance, when you need unique employee IDs, order numbers, or customer IDs.

In the first example, we use sequences in an INSERT statement. We also added a SELECT statement to the query so that we can see the insertion results right away.

-- Using sequences in an INSERT statement
INSERT INTO EMPLOYEES (
  employee_id, employee_name
)
VALUES (EXAMPLE_SEQUENCE.nextval, 'Name LastName');

SELECT *
  FROM EMPLOYEES;

The second example illustrates generating unique order IDs with the help of Oracle sequences:

-- Generating unique order IDs
INSERT INTO ORDERS (
  order_id, product_name
)
VALUES (EXAMPLE_SEQUENCE.nextval, 'ProductName');

SELECT *
  FROM ORDERS;

In the third example, we are demonstrating the creation of company IDs:

-- Creating company IDs
INSERT INTO COMPANIES (
  company_id, company_name
)
VALUES (EXAMPLE_SEQUENCE.nextval, 'Name LastName');

SELECT *
  FROM COMPANIES;

Integrating sequences with tables

The following code snippets showcase the practical implementation of sequences in Oracle, from fetching values to integrating them seamlessly with tables:

-- Creating a trigger to automatically insert values using the sequence
CREATE OR REPLACE TRIGGER SALES_TRIGGER
  BEFORE INSERT
  ON SALES
  FOR EACH ROW
BEGIN
  SELECT EXAMPLE_SEQUENCE.nextval
    INTO :new.SALE_ID
    FROM DUAL;
END;

In this example, sales_trigger is created to automatically insert the next sequence value into the sale_id column before each row insertion.

-- Inserting data into the table
INSERT INTO SALES (
  PRODUCT_NAME, SALE_DATE
)
VALUES ('ProductName', SYSDATE);

By executing the query above, you can insert as many rows to the sales table as you would like:

Managing sequences

After we have covered the creation and practical usage of Oracle sequences, we need to turn our attention to managing them. Namely, we will now discover how to make changes in the existing sequences and how to mercilessly get rid of them when you no longer need them.

Use the ALTER SEQUENCE statement to change the increment, minimum, and maximum values, cached numbers, and behavior of an existing sequence. Keep in mind that this statement only affects future sequence numbers.

ALTER SEQUENCE EXAMPLE_SEQUENCE
MAXVALUE 20;

SELECT EXAMPLE_SEQUENCE.nextval
  FROM DUAL;

Use the DROP SEQUENCE statement to remove a sequence from the database. You can also use this statement to restart a sequence by dropping and then re-creating it.

DROP SEQUENCE EXAMPLE_SEQUENCE;

CREATE SEQUENCE EXAMPLE_SEQUENCE
START WITH 1
INCREMENT BY 1;

SELECT EXAMPLE_SEQUENCE.nextval
  FROM DUAL;

Conclusion

This article has navigated the diverse facets of Oracle sequences, starting from a foundational understanding and progressing through a detailed exploration of syntax components. We went through advanced options and considerations, unraveling the intricacies that allow users to tailor sequences to specific needs. By examining practical use cases, we provided insights into integrating sequences seamlessly within real-world database scenarios. dbForge Studio for Oracle stands as a powerful ally for these tasks and beyond. Offering a 30-day fully functional trial, it presents an invaluable resource for efficient sequence management and diverse database endeavors.