Script: Synchronizing sequences to max(value)

Today I needed to synchronise several sequences with the max(value) existing in the primary key column. I did a quick google search to see if I found a script which could do this for me, but didn’t find anything (well didn’t spend to long either). So I wrote a script instead.

SET serveroutput ON echo ON;

DECLARE
   l_table_name   VARCHAR2(30) := '<table_name>';
   l_column_name  VARCHAR2(30) := '<column_name>';
   l_seq_name     VARCHAR2(30) := '<sequence_name>';
                  
   l_max_id       NUMBER;
   l_now_id       NUMBER;
   l_new_id       NUMBER;
   l_last_id      NUMBER;
   l_sql1         VARCHAR2(2000);
   l_sql2         VARCHAR2(2000);
   l_step         NUMBER;

BEGIN
   -- Find max(value) for table.column
   execute immediate 'select max('||l_column_name||') from ' || l_table_name into l_max_id;

   -- Get the current value of the sequence
   execute immediate 'select '||l_seq_name||'.nextval from dual' into l_now_id;

   -- Check if sequence is already in sync
   if (l_max_id <= l_now_id) then
         dbms_output.put_line('-- INFO -------------------------');
         dbms_output.put_line('MAX ID : ' || l_max_id);
         dbms_output.put_line('NOW ID : ' || l_now_id);
         dbms_output.put_line('STATUS : Allready in sync');
         dbms_output.put_line('---------------------------------');
         return;
   end if;

   -- Calculate the which number to increment the sequence
   l_step := l_max_id - l_now_id;

   -- Make sequence value increase to needed value
   l_sql1 := 'ALTER SEQUENCE '||l_seq_name||' INCREMENT BY ' || l_step;
   execute immediate l_sql1;
   execute immediate 'select '||l_seq_name||'.nextval from dual' into l_new_id;

   -- Reset the sequence back to increment by 1
   l_sql2 := 'ALTER SEQUENCE '||l_seq_name||' INCREMENT BY 1';
   execute immediate l_sql2;

   -- Verify correct NEXTVAL
   execute immediate 'select '||l_seq_name||'.nextval from dual' into l_last_id;

   -- Print the result
   dbms_output.put_line('--------------------------------------------------------------------------------------------');
   dbms_output.put_line('MAX ID : ' || l_max_id);
   dbms_output.put_line('NOW ID : ' || l_now_id);
   dbms_output.put_line('NEW ID : ' || l_new_id);
   dbms_output.put_line('STEP   : ' || l_step);
   dbms_output.put_line('SQL1   : ' || l_sql1);
   dbms_output.put_line('SQL2   : ' || l_sql2);
   dbms_output.put_line('LAST   : ' || l_last_id);
   dbms_output.put_line('--------------------------------------------------------------------------------------------');
END;
/

Post a Comment

Your email is never published nor shared. Required fields are marked *