Click here to Skip to main content
15,860,859 members
Articles / Database Development
Tip/Trick

Reset PLSQL sequence value

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
24 Dec 2010CPOL 14.4K   2  
Reset PLSQL sequence value
In Oracle, sequences are generally used to auto generate column IDs. Re-setting might be needed when someone sets the increment value wrongly or it appears this sequence has not always been used to insert new values into the table and you need to correct it.

In a particular project of mine, I have a sequence used to seed my (Integer based) primary keys in an Oracle table. I need to use the sequence and then have to revert back to old value. In SQL server TSQL reseed identity is nice and easy, but with Oracle PLSQL, it's not that straight forward, so after doing some R&D, I have come up with this solution:

SQL
select job_seq.nextval from dual;
ALTER SEQUENCE job_seq increment by -1 minvalue 0;
select job_seq.nextval from dual;
ALTER SEQUENCE job_seq increment by 1;

job_seq is the particular sequence which needed to be reset. What the above code does is retrieve the next sequence value then alter the sequence by -1 so that next time sequence next value is called it increments by -1, and after that we alter the sequence to the original declaration. If current sequence number of job_seq is 112, then the above code will yield:

113
ALTER SEQUENCE job_seq succeeded.
112
ALTER SEQUENCE job_seq succeeded.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

Comments and Discussions

 
-- There are no messages in this forum --