Click here to Skip to main content
Click here to Skip to main content

Reset PLSQL sequence value

, 24 Dec 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
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:
 
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)

Share

About the Author

Shahriar Iqbal Chowdhury/Galib
Technical Lead
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 --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 24 Dec 2010
Article Copyright 2010 by Shahriar Iqbal Chowdhury/Galib
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid