Oracle/PLSQL: Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

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

For example:
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;

Now that you’ve created a sequence object to simulate an autonumber field, we’ll cover how to retrieve a value from this sequence object.

To retrieve the next value in the sequence order, you need to use nextval.
For example:supplier_seq.nextval

Written by Ravi Nallakukkala on August 1st, 2007 with no comments.
Read more articles on Database.