Month: January 2021

Sometimes the value in an Oracle Column is based on the value of an Oracle Sequence. This is a quick and easy way to make a unique key. The column value is usually automatically determined using a table trigger.

However, it can sometimes get out of sync. Here’s the code to fix it:

DECLARE
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  LOOP
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

Credit goes to: https://stackoverflow.com/questions/6099108/best-way-to-reset-an-oracle-sequence-to-the-next-value-in-an-existing-column