Function to fix a sequence in oracle

If someone inserted some rows not using sequence and you have to increase the sequence value to use it again, here’s a procedure for it :

WARNING: the procedure assumes that the field that contains value generated through sequence is called ID!

create procedure move_sequence(sequence_name varchar2, table_name varchar2)

is
  przesuniecie number(19,0);
  max_id number(19,0);
  sequence_value number(19,0);
begin
  execute immediate 'select max(id) from ' || table_name into max_id;
  execute immediate 'select ' || sequence_name || '.nextval from dual' into sequence_value;
  
  execute immediate 'ALTER SEQUENCE ' || sequence_name || ' INCREMENT BY ' || (max_id - sequence_value + 1);
  execute immediate 'select ' || sequence_name || '.nextval from dual' into sequence_value;
  
  execute immediate 'ALTER SEQUENCE ' || sequence_name || ' INCREMENT BY ' || 1;
  
end;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s