DECLARE n NUMBER(10 ); tsql VARCHAR2(100 ); p_seqName varchar2(20 ); BEGIN p_seqName := 'SEQ_RUN_ID'; EXECUTE IMMEDIATE 'SELECT ' || p_seqName || '.NEXTVAL FROM dual ' INTO n; n := - (n - 1); tsql := 'alter sequence '|| p_seqName ||' increment by ' || n; EXECUTE IMMEDIATE tsql; EXECUTE IMMEDIATE 'SELECT ' || p_seqName || '.NEXTVAL FROM dual ' INTO n; tsql := 'alter sequence '|| p_seqName ||' increment by 1' ; EXECUTE IMMEDIATE tsql; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( SQLERRM); END;

CREATE OR REPLACE PROCEDURE RESET_SEQUENCE(p_sSeqName IN VARCHAR2) IS n NUMBER(10 ); tsql VARCHAR2(100 ); BEGIN EXECUTE IMMEDIATE 'SELECT ' || p_sSeqName || '.NEXTVAL FROM dual ' INTO n; n := - (n - 1); tsql := 'alter sequence ' || p_sSeqName || ' increment by ' || n; EXECUTE IMMEDIATE tsql; EXECUTE IMMEDIATE 'SELECT ' || p_sSeqName || '.NEXTVAL FROM dual ' INTO n; tsql := 'alter sequence ' || p_sSeqName || ' increment by 1' ; EXECUTE IMMEDIATE tsql; EXCEPTION WHEN OTHERS THEN NULL; END RESET_SEQUENCE;