How to create and use an Oralce Sequence
By Ali Hammad Baig on 10:55 PM
Filed Under:
This is how you can create an oracle sequence
Syntax
CREATE SEQUENCE sequence_name
MINVALUE VALUE
MAXVALUE VALUE
START WITH VALUE
INCREMENT BY VALUE
CACHE VALUE;
A Demo
CREATE SEQUENCE my_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
– If you don’t provide MAXVALUE its default value is 99999999999999999999999999
Using Sequence
SELECT my_seq.NEXTVAL FROM dual;
NEXTVAL
———-
1
SELECT my_seq.NEXTVAL FROM dual;
NEXTVAL
———-
2
SELECT my_seq.NEXTVAL FROM dual;
NEXTVAL
———-
3
SELECT my_seq.NEXTVAL FROM dual;
NEXTVAL
———-
4
Drop Sequence
DROP sequence my_seq;
Increment/Decrement/Alter/Reset Sequence
Now if I want to reset the sequence to a previous value, lets say my current sequence number is 4 and I want my sequence number back at 1. Here is how you can do that.
ALTER SEQUENCE my_seq INCREMENT BY -3;
SELECT my_seq.NEXTVAL FROM dual;
NEXTVAL
—————-
1
If you accendently decremented it by -4, you may get the following error
ORA-08004: sequence my_seq.NEXTVAL goes below MINVALUE and cannot be instantiated
In that case execute the following statment
ALTER SEQUENCE my_seq INCREMENT BY 4;
SELECT my_seq.NEXTVAL FROM dual;
This may solve your problem
0 comments for this post