rem -----------------------------------------------------------------------
rem Filename: SET_SEQUENCE.SQL
rem Purpose:  SETS an existing sequence to a value of choice without 
rem           dropping it, which would make objects using it invalid. It 
rem           also maintains the other sequence attributes where it can. 
rem Notes:    USEAGE "exec set_sequence ('&seqname' , '&nextval');
rem Date:     22-Dec-2004
rem Author:   Trevor Fairhurst, trevgf@yahoo.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE  PROCEDURE set_sequence (seqname IN VARCHAR2, newnumber IN INTEGER) as
   curr_val INTEGER;
   curr_inc INTEGER;
   curr_min INTEGER;
BEGIN
   SELECT INCREMENT_BY, MIN_VALUE into curr_inc, curr_min from user_sequences where sequence_name = seqname;
   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seqname||' MINVALUE ' || LEAST((newnumber - curr_inc - 1) , curr_min) ;
   EXECUTE IMMEDIATE 'SELECT ' ||seqname ||'.nextval FROM dual' INTO curr_val;
   IF (newnumber - curr_val - curr_inc) != 0 THEN
     EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seqname||' INCREMENT BY '||(newnumber - curr_val - curr_inc);
   END IF;
   EXECUTE IMMEDIATE 'SELECT ' ||seqname ||'.nextval FROM dual' INTO curr_val;
   EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seqname||' INCREMENT BY ' || curr_inc;
END set_sequence;
/