Manually forward a sequence - oracle sql Manually forward a sequence - oracle sql sql sql

Manually forward a sequence - oracle sql

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 sequenceBEGIN    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;/

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).

you can just

select seq.nextval from dual 

until it is big enough...