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..
declare
v_num number;
v_max number;
—
v_table varchar2(30) := ‘table_x’;
v_id varchar2(10) := ‘s_id’;
v_seq varchar2(15) := ‘s_seq1’;
begin
—
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
loop
execute immediate
‘select ‘|| v_seq ||’.nextval
from dual’
into v_num;
dbms_output.put_line(‘seq is now:’ || v_num);
end loop;
—
end;
With thanks to my colleague Diana Veerman.
Leave A Comment