sequence

In Oracle, a sequence is a database object that generates unique numeric values, often used to create primary key values automatically.

Syntax to create a sequence

CREATE SEQUENCE sequence_name
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE
CACHE 20;

Example

Create a sequence:

CREATE SEQUENCE emp_seq
START WITH 1001
INCREMENT BY 1;

Use the sequence while inserting data:

INSERT INTO employee (emp_id, emp_name)
VALUES (emp_seq.NEXTVAL, 'John');

Get the current value of the sequence:

SELECT emp_seq.CURRVAL FROM dual;

Note: CURRVAL can only be used after NEXTVAL has been called in the current session.

Get the next value:

SELECT emp_seq.NEXTVAL FROM dual;

Common sequence pseudocolumns

• NEXTVAL – Generates and returns the next sequence number.
• CURRVAL – Returns the current sequence number for the session.

Advantages of sequences

• Automatically generates unique numbers.
• Commonly used for primary keys.
• Avoids duplicate ID values.
• Supports multiple users efficiently without conflicts.

Example output:
If emp_seq starts at 1001:

• First NEXTVAL → 1001
• Second NEXTVAL → 1002
• Third NEXTVAL → 1003