Updating sequences, the Developer's way

As a DBA it’s quite common to update sequences by altering the increment of the sequence temporarily. Therefore it’s nice to know how Developers are  acting with the problem that a sequence is not in line with the corresponding id of a table. Just another view on this matter, kind a liked it..

v_num number;
v_max number;

v_table varchar2(30) := ‘table_x’;
v_id    varchar2(10) := ‘s_id’;
v_seq   varchar2(15) := ‘s_seq1’;

execute immediate
‘select  max(‘|| v_id ||’)
from’|| v_table
into v_max;

execute immediate
‘select ‘|| v_seq ||’.nextval from dual’
into   v_num;

dbms_output.put_line(‘max: ‘ || v_max);

while v_num < v_max
execute immediate
‘select ‘|| v_seq ||’.nextval
from   dual’
into   v_num;
dbms_output.put_line(‘seq is now:’ || v_num);
end loop;


With thanks to my  colleague  Diana Veerman.

By | 2009-06-08T10:15:50+00:00 June 8th, 2009|Categories: Database|0 Comments

About the Author:

Leave A Comment