Hope you are doing great.
You might have been working on Oracle technology and definitely you might have noticed that Oracle does not provide any specific keyword that can help to reset Sequences. Recently, I was working on some stuff and encountered this issue.
Finally, I came up with a solution. I did Google and found some other blogs on the same. So, I decided to explain this process little more.
Let’s see the logic first and then we will code it.
It looks little tricky but very easy. What we need to do is, store the sequence NEXTVAL into a variable. Alter the sequence using ALTER SEQUENCE DDL statement and set INCREMENT BY negative NEXTVAL value which we stored into the variable and minvalue 0. Now, when we call NEXTVAL again CURRVAL value will become 0 (as we have already set INCREMENT BY in negative).
In the next step call the NEXTVAL into the variable to initiate the sequence and alter the sequence back to the same structure.
We need a sequence first.
Let’s run this sequence 10 times and see the value. To run this sequence 10 times, I am using CONNECT BY CLAUSE.
We can see that Current Value of the sequence is 10. Let’s alter the sequence with increment by ‘-10’.
As per the Logic explained, call the sequence’s NEXTVAL to restore it.
You can see the output as 0. When you call the same sequence again with NEXTVAL in your program or table, it will start with 1 and this this the value we set as START WITH while creating this sequence. Do not forget the last step, alter the sequence to its original state.
As we have understood the concept. Binding these steps in a procedure is a very good idea.
To run DDL commands in PLSQL block, we use EXECUTE IMMEDIATE dynamic SQL.
Now, whenever you feel need to reset your any sequence just use this procedure with your sequence name.
If you have any questions please comment below or write me an email.