Ever needed to update a sequence so the next value is greater than the maximum value of a table? Well, the quick and dirty ways are
-
select SEQ.nextval from dual;
(ever did this a million times?)
-
drop sequence SEQ; create sequence SEQ start with MAX+1;
(never had problems with grants and invalided stored procedures?)
If you’re interested in a more elegant and robust way…
The SQL*Plus script upd_seq.sql allows you to specify a sequence, table and column. The script updates the sequence so that the next value is the smallest value larger than the current maximum of the table column (unless the current sequence value is already at least the maximum table column value).
See the documentation in the script for more information.
Nice thinking
See also set_sequence.txt for inspiration
Hello Gert-Jan
A nice dynamic solution but what about the following:
— select the max value from the table
Select the max_value into lMaxVal from table;
–select the sequence number from the sequence
Select sequence_name.nextval into lSeqNr from dual;
— change the increment of the sequence
Execute immediate (‘alter sequence sequence_name increment by ‘||( lMaxVal-lSeqNr));
— select a value from the sequence
select sequence_name.next_val into lSeqNr from dual;
— reset the increment of the sequence
Execute immediate (‘alter sequence sequence_name increment by 1’);
If you implement this in your package you do not have to recreate/grant etc on the sequence and the sequence would not create
invalid objects.
Cheers Marcos