& Continuous Learning

If you are ever working with a database where data is consistently being imported, dropped, and/or restored; sooner or later you will find yourself in the situation where the primary key of a table is no longer synchronized with the sequence used to generate it.

If you have the appropriate permissions, this is easy to fix. Say you need to advance the sequence by 500, you just execute the following sql:

alter sequence MY_SEQ increment by 500;
select MY_SEQ.nextval from dual;
alter sequence MY_SEQ increment by 1;

What do you do if you don't have the correct permissions to do this? You can always call up the dba and ask him. If you're impatient, you can also do a little pl/sql programming to accomplish the same thing. All you want to do is just repeatedly select the next value until the sequence value is higher that the current key value.

DECLARE
  current_value NUMBER;
  seq_val NUMBER;
BEGIN
  select max(MY_TABLE.id) into current_val from MY_TABLE;
  while current_val >= seq_val
  loop
    select MY_SEQ.nextval into seq_val from dual;
  end loop;
END;

Of course, if you encounter this situation once, chances are that you will encounter it many times. Next, you can create a function that takes the table name, key column name, and sequence name and then executes dynamically created sql to fix the problem.

create or replace procedure INCREMENT_SEQ(IN TABLE_NAME, IN ID_COLUMN_NAME, IN SEQUENCE_NAME) is
DECLARE
  current_value NUMBER;
  seq_val NUMBER := -1;
BEGIN
  execute immediate 'select max(' || TABLE_NAME || '.' || ID_COLUMN_NAME || ') into current value from ' || TABLE_NAME;
  while current_val >= seq_val
  loop
    execute immediate 'select ' || SEQUENCE_NAME || ".nextval into seq_val from dual';
  end loop;
END INCREMENT_SEQ;

Now, you should be able to fix any sequence/key mismatches simply by calling the procedure.

INCREMENT_SEQ('MY_TABLE', 'ID', 'MY_SEQ');