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.
CREATE SEQUENCE seq1 start with 1 minvalue 0 increment by 1;
Let’s run this sequence 10 times and see the value. To run this sequence 10 times, I am using CONNECT BY CLAUSE.
SELECT seq1.NEXTVAL FROM dual CONNECT BY level <= 10; / SELECT seq1.CURRVAL FROM dual; --Output CURRVAL ---------- 10
We can see that Current Value of the sequence is 10. Let’s alter the sequence with increment by ‘-10’.
ALTER SEQUENCE seq1 INCREMENT BY -10 MINVALUE 0; --Output Sequence SEQ1 altered.
As per the Logic explained, call the sequence’s NEXTVAL to restore it.
SELECT seq1.NEXTVAL FROM dual; --Output NEXTVAL ---------- 0
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.
ALTER SEQUENCE seq1 INCREMENT BY 1 MINVALUE 0; --Output Sequence SEQ1 altered.
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.
CREATE OR REPLACE PROCEDURE reset_seq(seq_name in varchar2 ) IS var_val number; BEGIN execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO var_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by -' || var_val ||' minvalue 0'; execute immediate 'select ' || p_seq_name || '.nextval from dual' INTO var_val; execute immediate 'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; END; /
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.