This tutorial focuses on Oracle 19c Sequences, a powerful database object for generating unique, sequential numbers. Learn how to CREATE, ALTER, and DROP sequences, and effectively use NEXTVAL and CURRVAL to manage unique identifiers in your Oracle Database applications.

Sequences: Generating Unique Numbers

Sequences in Oracle 19c are database objects that automatically generate unique numbers. They are commonly used to create primary key values, ensuring each new record has a distinct identifier without manual intervention.

CREATE SEQUENCE

The CREATE SEQUENCE statement is used to define a new sequence object. You can specify various options like starting value, increment, minimum/maximum values, and caching.

Example 1: Basic Sequence Creation

CREATE SEQUENCE emp_id_seq
START WITH 1
INCREMENT BY 1
NOCACHE; -- Creates a sequence named emp_id_seq starting at 1, incrementing by 1, without caching

Explanation This code creates a simple sequence emp_id_seq that generates numbers starting from 1 and increasing by 1 for each call. NOCACHE means values are not pre-allocated in memory.

Example 2: Sequence with Max Value and Cycle

CREATE SEQUENCE order_num_seq
START WITH 100
INCREMENT BY 5
MAXVALUE 1000
CYCLE
NOCACHE; -- Sequence starting at 100, incrementing by 5, cycles after 1000

Explanation This sequence order_num_seq starts at 100, increments by 5, and will cycle back to its start value after reaching 1000, which is useful for limited ranges.

Example 3: Sequence for Negative Numbers

CREATE SEQUENCE neg_val_seq
START WITH -1
INCREMENT BY -1
MINVALUE -100
NOCACHE; -- Creates a sequence that decrements from -1

Explanation This sequence neg_val_seq demonstrates creating a sequence that generates negative numbers, starting at -1 and decrementing by 1.

Example 4: Sequence with Caching

CREATE SEQUENCE product_code_seq
START WITH 1000
INCREMENT BY 10
CACHE 20; -- Caches 20 sequence numbers in memory for faster access

Explanation This sequence product_code_seq uses CACHE 20, which stores 20 pre-generated numbers in memory, improving performance for frequent number generation.

Example 5: Sequence with NOORDER

CREATE SEQUENCE audit_log_id_seq
START WITH 1
INCREMENT BY 1
NOORDER; -- Allows sequence numbers to be generated out of order for performance in RAC environments

Explanation The NOORDER clause for audit_log_id_seq allows sequence numbers to be generated potentially out of order, which can improve performance, especially in Real Application Clusters (RAC).

ALTER SEQUENCE

The ALTER SEQUENCE statement modifies an existing sequence's properties without dropping and recreating it. You can change increment, min/max values, or caching settings.

Example 1: Altering Increment By

ALTER SEQUENCE emp_id_seq
INCREMENT BY 2; -- Changes emp_id_seq to increment by 2

Explanation This statement modifies emp_id_seq so that subsequent calls to NEXTVAL will increase the sequence number by 2 instead of 1.

Example 2: Altering Cache Size

ALTER SEQUENCE product_code_seq
CACHE 50; -- Changes the cache size of product_code_seq to 50

Explanation This alters product_code_seq to cache 50 numbers at a time, potentially further improving performance for concurrent requests.

Example 3: Altering Maxvalue and No Cycle

ALTER SEQUENCE order_num_seq
MAXVALUE 2000
NOCYCLE; -- Increases max value and prevents cycling

Explanation This modifies order_num_seq to have a new maximum value of 2000 and disables the CYCLE option, meaning it will stop generating numbers once 2000 is reached.

Example 4: Restarting a Sequence (Conceptual)

-- To "restart" a sequence, you typically drop and recreate it.
-- ALTER SEQUENCE does not have a "RESTART WITH" option.
-- DROP SEQUENCE emp_id_seq;
-- CREATE SEQUENCE emp_id_seq START WITH 1 INCREMENT BY 1 NOCACHE;

Explanation Oracle's ALTER SEQUENCE command does not support resetting the current value directly. To effectively restart a sequence from a specific value, you must drop and then recreate it.

Example 5: Altering MINVALUE (Only if current value allows)

-- ALTER SEQUENCE neg_val_seq MINVALUE -200;
-- This would work only if the current CURRVAL is greater than or equal to -200.
-- If CURRVAL is -50, setting MINVALUE to -200 is fine. If CURRVAL is -150, setting MINVALUE to -100 would fail.

Explanation You can alter MINVALUE for a sequence, but the new minimum must be less than or equal to the current sequence value; otherwise, an error occurs.

DROP SEQUENCE

The DROP SEQUENCE statement removes a sequence object from the database. Once dropped, the sequence cannot be used to generate numbers.

Example 1: Dropping a Sequence

DROP SEQUENCE emp_id_seq; -- Removes the emp_id_seq from the database

Explanation This command permanently deletes the emp_id_seq sequence object from the Oracle database.

Example 2: Dropping Non-Existent Sequence (Error)

-- DROP SEQUENCE non_existent_seq;
-- This would result in an ORA-02289: sequence does not exist error.

Explanation Attempting to drop a sequence that does not exist in the database will result in an error.

Example 3: Dropping Multiple Sequences (Separate statements)

DROP SEQUENCE order_num_seq;
DROP SEQUENCE neg_val_seq; -- Each sequence must be dropped individually

Explanation To drop multiple sequences, you must execute a separate DROP SEQUENCE statement for each sequence.

Example 4: Impact on Dependent Objects

-- Dropping a sequence does not automatically remove default values or triggers
-- that might have used it. These references will become invalid.
-- For example, if a table's default used emp_id_seq.NEXTVAL, it would fail after drop.

Explanation Dropping a sequence does not cascade to objects that referenced it; any column default values or triggers that used the dropped sequence will become invalid and fail.

Example 5: Verifying Sequence Drop

SELECT SEQUENCE_NAME FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'EMP_ID_SEQ';
-- After DROP, this query should return no rows.

Explanation This query can be used to verify if a sequence has been successfully dropped by checking its presence in the USER_SEQUENCES data dictionary view.

NEXTVAL

NEXTVAL is a pseudocolumn that retrieves the next available number from a specified sequence. Each call to NEXTVAL increments the sequence and returns the new value.

Example 1: Getting Next Value in INSERT

INSERT INTO employees (employee_id, first_name)
VALUES (emp_id_seq.NEXTVAL, 'Alice'); -- Uses the next number from emp_id_seq for employee_id

Explanation This statement inserts a new employee record, using the next unique number generated by emp_id_seq.NEXTVAL for the employee_id.

Example 2: Getting Next Value in SELECT

SELECT emp_id_seq.NEXTVAL FROM DUAL; -- Retrieves the next sequence value from the DUAL table

Explanation This query directly fetches the next value from emp_id_seq using the DUAL pseudotable, which is often used for single-row results.

Example 3: Multiple NEXTVAL in One Statement (Same Value)

SELECT emp_id_seq.NEXTVAL, emp_id_seq.NEXTVAL FROM DUAL; -- Both calls return the *same* next value

Explanation When NEXTVAL is called multiple times within a single SQL statement, it returns the same incremented value for all calls within that statement.

Example 4: Using NEXTVAL in DEFAULT Clause

CREATE TABLE new_products (
    product_id NUMBER DEFAULT product_code_seq.NEXTVAL, -- Automatically assigns next sequence value
    product_name VARCHAR2(100)
);

Explanation This table definition uses product_code_seq.NEXTVAL as the default value for product_id, automatically assigning a unique ID upon insertion if not specified.

Example 5: NEXTVAL and Triggers

-- CREATE OR REPLACE TRIGGER trg_employees_id
-- BEFORE INSERT ON employees
-- FOR EACH ROW
-- BEGIN
--   :NEW.employee_id := emp_id_seq.NEXTVAL;
-- END;
-- /

Explanation This conceptual trigger demonstrates using NEXTVAL to populate a primary key column (employee_id) automatically before a new row is inserted into the employees table.

CURRVAL

CURRVAL is a pseudocolumn that retrieves the current value of a sequence for the current session. It can only be used after NEXTVAL has been called for that sequence in the same session.

Example 1: Getting Current Value

-- First, ensure NEXTVAL has been called at least once in the session
SELECT emp_id_seq.NEXTVAL FROM DUAL; -- Call NEXTVAL to initialize CURRVAL
SELECT emp_id_seq.CURRVAL FROM DUAL; -- Retrieves the last value generated by NEXTVAL in this session

Explanation CURRVAL retrieves the most recently generated NEXTVAL for emp_id_seq within the current session. It requires an initial NEXTVAL call.

Example 2: CURRVAL in Insert (After NEXTVAL)

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (order_num_seq.NEXTVAL, 10, SYSDATE);

INSERT INTO order_items (order_id, item_name, quantity)
VALUES (order_num_seq.CURRVAL, 'Laptop', 1); -- Reuses the order_id from the previous insert

Explanation This demonstrates using CURRVAL to insert related records (order items) with the same order_id that was just generated by NEXTVAL for the main order.

Example 3: Error Using CURRVAL Before NEXTVAL

-- SELECT order_num_seq.CURRVAL FROM DUAL;
-- This would result in ORA-08002: sequence ORDER_NUM_SEQ.CURRVAL is not yet defined in this session.

Explanation Attempting to use CURRVAL before any NEXTVAL call for that sequence in the current session will result in an ORA-08002 error.

Example 4: CURRVAL in a View

CREATE VIEW current_emp_seq AS
SELECT emp_id_seq.CURRVAL AS current_employee_id
FROM DUAL;

Explanation This creates a view that can be queried to see the current value of emp_id_seq within the session, provided NEXTVAL has been called.

Example 5: CURRVAL in Package Variable (Conceptual)

-- PACKAGE my_pkg IS
--   PROCEDURE get_next_and_current(p_next OUT NUMBER, p_curr OUT NUMBER);
-- END my_pkg;
-- PACKAGE BODY my_pkg IS
--   PROCEDURE get_next_and_current(p_next OUT NUMBER, p_curr OUT NUMBER) IS
--   BEGIN
--     p_next := emp_id_seq.NEXTVAL;
--     p_curr := emp_id_seq.CURRVAL;
--   END;
-- END my_pkg;

Explanation This conceptual PL/SQL package demonstrates how NEXTVAL and CURRVAL can be used within procedural code to retrieve and manage sequence values.