In Oracle, you can create an autonumber field 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.
Syntax :
The syntax to create a sequence in Oracle:
CREATE SEQUENCE Sequence_Name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
Example :
CREATE SEQUENCE XX_PO_SEQ
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 50 / NONCACHE;
Note :
- If you exclude the MAXVALUE option, your sequence will automatically default to: MAXVALUE 999999999999999999999999999
- The cache option specifies how many sequence values will be stored in memory for faster access. The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values.
Retrieve the next value in the sequence :
To retrieve next value in sequence user Sequence_Name.NEXTVAL
INSERT INTO XX_PO_TABLE
(PO_ID, PO_NUMBER)
VALUES
(XX_PO_SEQ.NEXTVAL, 'PO-1234');
Check Cuurent value in the sequence :
To Check current value in sequence user Sequence_Name.CURRVAL
Select XX_PO_SEQ.NEXTVAL from dual;
Drop Sequence :
Syntax :
The syntax to a drop a sequence in Oracle is:
DROP SEQUENCE sequence_name;
DROP SEQUENCE XX_PO_SEQ;
Note :
To recover the lost sequence values or skip some values you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.
