Problem Statement :
You have a Oracle sequence that is lagging behind a columns value. That column is supposed to have unique values and when it tries to derive the value using the sequence it fails. Hence you would like to have a quick and dirty method to advance the value of the sequence to match the column's value.
Solution :
declare
i INTEGER;
max_row_id INTEGER;
BEGIN
select MAX(<COLUMN_NAME>) INTO max_row_id
FROM <TABLE_NAME>;
DBMS_OUTPUT.PUT_LINE ('Max row id :' || max_row_id);
select <SEQUENCE_NAME>.nextval INTO i from dual;
DBMS_OUTPUT.PUT_LINE ('Starting with Sequence value :' || i);
loop
if i>=max_row_id then
EXIT;
else
select <SEQUENCE_NAME>.nextval INTO i from dual;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE ('Ending with Sequence value :' || i);
END;
i INTEGER;
max_row_id INTEGER;
BEGIN
select MAX(<COLUMN_NAME>) INTO max_row_id
FROM <TABLE_NAME>;
DBMS_OUTPUT.PUT_LINE ('Max row id :' || max_row_id);
select <SEQUENCE_NAME>.nextval INTO i from dual;
DBMS_OUTPUT.PUT_LINE ('Starting with Sequence value :' || i);
loop
if i>=max_row_id then
EXIT;
else
select <SEQUENCE_NAME>.nextval INTO i from dual;
end if;
end loop;
DBMS_OUTPUT.PUT_LINE ('Ending with Sequence value :' || i);
END;