How to reset Sequence

Sequence

admin How to - Series , ,

Hi Folks,
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.

Logic explanation:
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.


EXEC reset_seq('SEQ1');

Thant’s it.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

Invoice Number

Generate Invoice Number

Hi folks, Hope you all are doing well. Recently, one of my friends was asked a question in his interview […]

Leave a Reply

Your email address will not be published. Required fields are marked *