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