[SOLVED] Resetting the Last_NUMBER in sequence by looking up a value from a table

Resetting the Last_NUMBER in sequence by looking up a value from a table

I have a sequence called SEQ_SYNTAX. I need to set the last_number value of this sequence by looking up a value from another table called INCREMENTS. I can get the value from increments table by doing SELECT LASTVAL FROM INCREMENTS WHERE TABLE = 'SYNTAX';

How do I write a simple SQL script that can set that sequence value? say if the lastval from increments table where table = 'SYNTAX' is 3000, I want to set the last_number for sequence SEQ_SYNTAX to be 3000.

Thank you.

Solution

As of 12c, you can simply reset the starting value. Doing it programmatically will require PL/SQL so we have to use EXECUTE IMMEDIATE since an ALTER SEQUENCE is a DDL:

   DECLARE
     var_sequence_name varchar2(128) := 'SEQ_SYNTAX';
     var_new_value integer;
   BEGIN
     SELECT MAX(lastval)
       INTO var_new_value
       FROM increments
      WHERE "TABLE" = 'SYNTAX';
      
     IF var_new_value > 0
     THEN
       EXECUTE IMMEDIATE 'alter sequence "'||var_sequence_name||'" restart start with '||v_new_value;
     END IF;
   END;

If you are pre-12c you will have to drop and recreate the sequence if the new value is lower than the current value, or dummy-advance with a script until it reaches the new value if the new value is larger. That was a hassle, so we're grateful for the 12c enhancement.