Manually forward a sequence - oracle sql

Question

I need to forward a set of sequences with only DML access. Due to a bug in a piece of code several values were grabbed without a sequence but instead manually, so now the sequence is duplicating those values. So, I would like to push the sequence to the max value so that the next time nextval is called, it gives a value higher than the maximum. I've got about 50 sequences that each have to go a few thousand forward.

Is this possible with only DML access? If so, how should I go about it?

Answer 1

You should determine the difference between the next value of the sequence and the required value. The required value is typically the max value of a primary key column (let's name it ID).

DECLARE
    maxid NUMBER;
    maxseq NUMBER;
    temp NUMBER;  -- without this variable Oracle would skip to query the sequence
BEGIN
    SELECT MAX(ID) INTO maxid FROM MYTABLE;
    SELECT MYSEQ.NEXTVAL INTO maxseq FROM DUAL;
    FOR i IN maxseq .. maxid LOOP
        SELECT MYSEQ.NEXTVAL INTO temp FROM DUAL;
    END LOOP;
END;
/

Answer 2

You can use dynamic SQL to do this. For example, this bit of code will select the next 10,000 values from each of a list of sequences.

DECLARE
  l_num INTEGER;
BEGIN
  FOR seq IN (select * 
                from all_sequences
                where sequence_name in (<<list of 50 sequences>>) 
                  and sequence_owner = <<owner of sequences>>)
  LOOP
    FOR i IN 1 .. 10000
    LOOP
      execute immediate 
         'select ' || seq.sequence_owner || '.' || seq.sequence_name || '.nextval from dual'
         into l_num;
    END LOOP;
  END LOOP;
END;

If you had the ability to issue DDL against the sequence, you could use a similar approach to set the INCREMENT to 10,000, select one value from the sequence, and set the INCREMENT back down to 1 (or whatever it is now).