Thursday, July 3, 2008

Querying current value of sequence without incrementing it

As we know when we use SequenceName.NextVal, sequence got increment by whatever increment number is defined while it was created.

Eg.
CREATE SEQUENCE seq1
START WITH 1
INCREMENT BY 1
CACHE 20;



If we try to issue SELECT seq1.CurrVal FROM dual without issuing se1.NextVal. It will give error stating that sequence is not yet defined in this session. But, if we use seq1.NextVal, it will increment sequence by 1 which we don’t want. So here is one easy way for getting this:

ALTER SEQUENCE seq1 NOCACHE;
SELECT last_number FROM user_sequences WHERE sequence_name = ‘seq1’;
ALTER SEQUENCE seq1 CACHE 20; --* or whatever is defined earlier

No comments: