Reset Sequences in Oracle

CREATE OR REPLACE

PROCEDURE RESETSEQ

AS

L_CURSEQVAL NUMBER ;

--- SELECT ALL THE SEQUENCES IN THE DB FOR WHICH THE SEQUENCES HAS TO BE RESET.
--- Ex: HERE THE PRODUCT 'XXX' HAS SEQUENCES STARTING WITH XXX_SEQ_T_***

CURSOR SEQNAMESCURSR
IS
SELECT US.SEQUENCE_NAME AS V_SEQNAME
FROM USER_SEQUENCES US
WHERE ( SEQUENCE_NAME LIKE '%XXX_SEQ_T%' ) ;
CURROW SEQNAMESCURSR%ROWTYPE ;

BEGIN
OPEN SEQNAMESCURSR
LOOP
FETCH SEQNAMESCURSR INTO CURROW ;
EXIT WHEN SEQNAMESCURSR%NOTFOUND ;

--- GET THE NEXT VALUE OF THE SEQUENCE
EXECUTE IMMEDIATE
'SELECT' || CURROW.V_SEQNAME || '.NEXTVAL FROM DUAL' INTO L_CURSEQVAL ;

--- ALTER THE SEQUENCE TO RESET THE SEQUENCE TO ZERO BY INCREMENTING WITH THE NEGATIVE VALUE OF L_CURSEQVAL (QUERIED ABOVE)
EXECUTE IMMEDIATE
'SELECT' || CURROW.V_SEQNAME || 'INCREMENT BY -' || L_CURSEQVAL || 'MINVALUE 0' ;

--- GET THE NEXT VALUE FROM THE SEQUENCE. THIS WILL SET THE SEQUENCE TO ZERO

EXECUTE IMMEDIATE
'SELECT' || CURROW.V_SEQNAME || '.NEXTVAL FROM DUAL' INTO L_CURSEQVAL ;

--- RESET THE INCREMENT VALUE TO 1 BY ALTERING THE SEQUNCE
EXECUTE IMMEDIATE
'SELECT' || CURROW.V_SEQNAME || 'INCREMENT BY 1 MINVALUE 0' ;
COMMIT ;

END LOOP ;
CLOSE SEQNAMESCURSR ;

COMMIT ;

EXCEPTION

WHEN OTHERS THEN
IF SEQNAMESCURSR%ISOPEN THEN
CLOSE SEQNAMESCURSR ;
END IF ;
RAISE ;
ROLLBACK ;

END RESETSEQ ;