Alter current value of a sequence to the maximum value used in a column

We got a problem with an oracle sequence where the generated next value is already in the table. Therefore we had to modify the current value of the sequence in order to not to interfere with existing values.

Here's the sample code we have used to do that:

-- This will alter the value of the sequence
-- to the maximum value of the column which the sequence value
-- is entered


SET SERVEROUT ON


DECLARE
next_val NUMBER;
new_next_val NUMBER;
incr NUMBER;
max_table_val NUMBER;


v_code NUMBER;
v_errmsg VARCHAR2(64);


table_name VARCHAR2(100) := 'customer_tab';
column_name VARCHAR2(100) := 'customer_id';
sequence_name VARCHAR2(100) := 'CUSTOMER_ID_SEQ';


BEGIN

SAVEPOINT start_transaction;


    -- get the max value from the table that's using the sequence    
    EXECUTE IMMEDIATE 'select max(' || column_name || ') from '|| table_name into max_table_val;
    DBMS_OUTPUT.PUT_LINE('maximum value in table: ' || max_table_val);
   
    -- then read nextval from the sequence
    EXECUTE IMMEDIATE 'select ' || sequence_name || '.nextval from dual' into next_val;
    DBMS_OUTPUT.PUT_LINE('current next value of sequence: ' || next_val);


    -- calculate the desired next increment for the sequence
    incr := max_table_val - next_val;
    DBMS_OUTPUT.PUT_LINE('difference: ' || incr);
    
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || sequence_name || ' increment by ' || incr;
    EXECUTE IMMEDIATE 'select ' || sequence_name || '.nextval from dual' into new_next_val;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || sequence_name || ' increment by 1';
    DBMS_OUTPUT.PUT_LINE('new next value of sequence ' || new_next_val);


commit;


EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK to start_transaction;
    DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errmsg);    
end;
/
SET SERVEROUT OFF