Setting the value of a sequence in pl/sql in Oracle

In Oracle there is no direct way to set the value of a sequence once the sequence has been created. To set the sequence value to a specific value it is necessary to either drop and recreate the sequence, not forgetting all of the options specified in the create statement, or to increment the sequence by some positive or negative value to set the current value to the desired value.

To find out the current value of a sequence you can use the currval function on the sequence, but this will only return successfully if the sequence has already been initialized with a call to nextval. Another way to find the value for the sequence is to query the database as follows:

select sequence_name, last_number from user_sequences;

Below is sample pl/sql to set a sequence to the desired value, it defines two variables to store the current sequence and the desired sequence then alters the increment to the difference of those, increments the sequence then resets the sequence increment to the previous value. -


  procedure set_seq is
    l_max_row NUMBER(38,0) := 0;
    l_cur_seq NUMBER(38,0) := 0;
  begin

  --- Now change the sequence
   select max(id_col) into l_max_row from my_tab;
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by ' || (l_max_row-l_cur_seq) || ' nocache';
   select my_seq.nextval into l_cur_seq from dual;
   execute immediate 'alter sequence my_seq increment by 1 nocache';
   select my_seq.nextval into l_cur_seq from dual;