www.oradev.com   for Oracle developers
  Pl/sql articles   Oracle articles   Performance   scripts   Books   Documentation   Links   XML
  Articles
Oracle numeric functions
Number format
Sequence
dbms_lob
dbms_output
Oracle substr
Using a ref cursor
How to use the CASE statement
How to use the DECODE statement
How to use the NVL statement
Oracle training
Oracle certification
Create statistics
dbms_profiler explained
How to use hints
Autonomous transaction
Oracle date format
Oracle sysdate
Rename tables, columns
to_date function
Scheduling in 10g
How to use utl_http




  Sequence

Sequences in Oracle

Oracle has a standard function for automatically generate a sequence of number. This can be very usefull for generating a unique number to insert in a primary key field. A sequence is a stored object in the database.

Sequences can be created in the Oracle database with a CREATE SEQUENCE statement.
The syntax is:
CREATE SEQUENCE sequence_name;
You can add optional parameters to this statement. Example:
CREATE SEQUENCE customer_seq INCREMENT BY 1 CACHE 10;

The following sequence parameters are possible:
Parameter Example Description
INCREMENT BY INCREMENT BY 1 Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. The absolute of this value must be less than the difference of MAXVALUE and MINVALUE. If this value is negative, then the sequence descends. If the increment is positive, then the sequence ascends. If you omit this clause, the interval defaults to 1.
START WITH START WITH 1000 Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. For ascending sequences, the default value is the minimum value of the sequence. For descending sequences, the default value is the maximum value of the sequence. This integer value can have 28 or fewer digits.
MAXVALUE MAXVALUE 99999999 Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE NOMAXVALUE Specify NOMAXVALUE to indicate a maximum value of 1 with 27 zeros for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE MINVALUE=1 Specify the minimum value of the sequence. This integer value can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE NOMINVALUE Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -1026 for a descending sequence. This is the default.
CYCLE CYCLE Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum.
NOCYCLE NOCYCLE Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
CACHE CACHE 10 Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
NOCACHE NOCACHE Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default.
ORDER ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. You may want to use this clause if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys. ORDER is necessary only to guarantee ordered generation if you are using Oracle with Real Application Clusters. If you are using exclusive mode, sequence numbers are always generated in order.
NOORDER NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

How to use a sequence

You can use a sequence in the following way in SQL: select customer_seq.nextval from dual; INSERT INTO customers(id, name) VALUES (customer_seq.nextval,'customer name');