Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle SEQUENCE Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: SEQUENCE

Definition:
The Oracle SEQUENCE function allows you to create auto-numbering fields by using sequences. An Oracle sequence is an object that is used to generate incrementing or decrementing numbers.

Example Syntax:

CREATE SEQUENCE <sequence_name>
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;



Example Usage:

CREATE SEQUENCE user_seq
MINVALUE 1
MAXVALUE 20000
START WITH 1
INCREMENT BY 1
CACHE 100;


The example above will create a sequence object called user_seq Because of the 'START WITH 1' parameter the first number in the sequence will be '1'.

The 'MAXVALUE 20000' parameter tells Oracle to stop generating sequence numbers at 20,000.

The 'INCREMENT BY 1' parameter will cause the sequence numbers to increment by 1 (i.e. 2,3,4, and so on.).

The last line, 'CACHE 100' tells Oracle to pre-generate or cache the next 100 sequence values and store them in system memory for increased performance. If the database fails (crashes), the sequence numbers are lost, and when the application is restarted, a gap may occur in the sequence numbers. To prevent this possibility you can specify NOCACHE, which defers the caching of sequence numbers in memory. Using NOCACHE may result in slightly reduced performance, but it will prevent any gaps from appearing in the sequence should a database failure take place. In general, Oracle recommends caching sequence numbers and not using the NOCACHE parameter unless the continuity of sequence numbers is considered a critical factor.

Incrementing By Steps

CREATE SEQUENCE user_seq
MINVALUE 1
MAXVALUE 20000
START WITH 2
INCREMENT BY 2
CACHE 100;


The example above will create an Oracle sequence that starts at 2 and increments by 2. The resulting sequence would be '2, 4, 6, 8, 10', and so on. Using the 'START WITH' and 'INCREMENT BY' parameters allow you to start and step by any required values. You can also use negative numbers for the 'START WITH' and 'INCREMENT BY' parameters if necessary.


Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 154 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?