Manually forward a sequence - oracle sql
you can just
select seq.nextval from dual
until it is big enough...
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;
/
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).