SQL> create sequence ots_seq;
Sequence created.
SQL> select ots_seq.nextval from dual ;
NEXTVAL
———-
1
SQL> /
NEXTVAL
———-
2
SQL> /
NEXTVAL
———-
3
SQL> /
NEXTVAL
———-
4
SQL> /
NEXTVAL
———-
5
CREATE or REPLACE PROCEDURE reset_seq (seq_name IN VARCHAR2) as
curr_val INTEGER;
BEGIN
EXECUTE IMMEDIATE ‘alter sequence ‘ ||seq_name||’ MINVALUE 0′;
EXECUTE IMMEDIATE ‘SELECT ‘ ||seq_name ||’.nextval FROM dual’ INTO curr_val;
EXECUTE IMMEDIATE ‘alter sequence ‘ ||seq_name||’ increment by -‘||curr_val;
EXECUTE IMMEDIATE ‘SELECT ‘ ||seq_name ||’.nextval FROM dual’ INTO curr_val;
EXECUTE IMMEDIATE ‘alter sequence ‘ ||seq_name||’ increment by 1′;
END reset_seq;
/
Procedure created.
SQL> exec reset_seq(‘ots_seq’);
PL/SQL procedure successfully completed.
SQL> select ots_seq.nextval from dual ;
NEXTVAL
———-
1
SQL>