About Sequences and their attributes in Oracle10g

A sequence is a database object used to generate unique integers. It is generally used to automatically generate primary key values.

To create sequence, CREATE SEQUENCE privilege must be given in user’s own schema or in the case of creation in another user’s schema, CREATE ANY SEQUENCE privilege must be given.

The command for creation of a sequence is

CREATE SEQUENCE [schema .] sequence
[{ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
}
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
]...
]
;

Here schema specifies the schema to contain the sequence, default is current and own schema, sequence is the name of the sequence, INCREMENT BY specifies the numerical interval between sequence numbers, START WITH specifies the first sequence number ,MAXVALUE specifies the upper limit value of the number to be generated , NOMAXVALUE specifies upper limit as default of 1027 for an ascending sequence or -1 for a descending sequence, MINVALUE specifies the lowest value of the sequence, NOMINVALUE indicates a lowest value of 1 for an ascending sequence or -1026 for a descending sequence, CYCLE specifies that sequence continues to generate values after reaching maximum or minimum value by starting afresh, specify NOCYCLE to indicate sequence stops after generating maximum or minimum value, CACHE to specify the number of values of the sequence that Oracle pre allocates and keeps in memory for faster access, NOCACHE to indicate the values are not pre allocated, ORDER to guarantee that values are ordered as requested (necessary only with Oracle with Real Application Clusters) and NOORDER if no requirement to guarantee the order of sequence.

Example:
CREATE SEQUENCE EMP_SEQ
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

The first reference made to EMP_SEQ.NEXTVAL returns 1000, the next 1001 and henceforth.

Sequences can be altered. To alter sequences in other schemas, user must have the ALTER ANY SEQUENCE system privilege. Using ALTER command, any of the parameters already defined in a sequence can be altered except the sequence starting number.
Example:
ALTER SEQUENCE EMP_SEQ
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;

Sequences can also be dropped in the user’s schema. To drop any sequence in another schema the user must have DROP ANY SEQUENCE privilege.
Example:
DROP SEQUENCE EMP_SEQ;
When the sequence is dropped its definition no longer remains in the Data Dictionary.

When a sequence is created, its values can be accessed in SQL statements with the CURRVAL pseudo column which returns the current value of the sequence or the NEXTVAL pseudo column which returns the next value after incrementing.
Example:
INSERT INTO EMP_DTLS (EMP_NO,EMP_NAME)
VALUES (EMP_SEQ.NEXTVAL,’XXXXX’);

The first reference to EMP_SEQ.NEXTVAL returns the value 1. Subsequent references cause the next value in sequence to be generated. Independent of transactions being committed or rolled back, other users referencing EMP_SEQ.NEXTVAL obtain unique values. If there is more than one user accessing the same sequence, the sequence number may have gaps, because sequence number is being generated by the other users. To use or refer the current value of sequence, EMP_SEQ.CURRVAL is used. This can be used only if EMP_SEQ.NEXTVAL has been referenced in the current session.
Example:
INSERT INTO EMP_DTLS (EMP_NO, EMP_NAME)
VALUES (EMP_SEQ.CURRVAL,’XXXXX’);

The sequence number can be referenced in SELECT of a query or sub query.
Example:
SELECT EMP_SEQ.NEXTVAL FROM DUAL;

CURRVAL and NEXTVAL can be used only in VALUES clauses of INSERT statements, the SELECT list of a SELECT statement and the SET clause of an update statement.

Without sequences, sequential values can be only produced by program logic. Obtaining sequential values programmatically may cause locks in transactions and multiple users may need to wait.

Sequences can thus help eliminate serialization and improve concurrency of a transaction.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |