How to change the Oracle Sequence using loop?

Question

Hope someone can help. When I tried to insert something into a table it give me error saying the primary key is already existed. So I need to reset my sequence so that it is always max(id)+1.

The table is called 'People' with 2 columns (ID, Name). The sequence is called SEQ.

I am thinking of doing a loop. To run select SEQ.nextval from dual for n times. this n= max(id)-SEQ.currval

Wwill this work? and how Can I put it into the syntax?

Thanks a lot.

Answer 1

With this one you can synchronize the sequence whatever it is forward or behind the max of the ID.

Just need to change the parameters in the final of the code.

    declare
      procedure SYNC_SEQUENCE
        (  P_IN_SEQ          in     varchar2
         , P_IN_TABLE        in     varchar2
         , P_IN_ID           in     varchar2
        )
        is
          LV_MAXVAL          number  := 0;
          LV_CURRVAL         number  := -1;
          LV_AUX NUMBER;
        begin

          execute immediate
              'select max('||P_IN_ID||')
                 from '||P_IN_TABLE   into LV_MAXVAL;
          execute immediate 
              'select '||P_IN_SEQ||'.nextval
                from dual ' into LV_CURRVAL;             

          if LV_MAXVAL < LV_CURRVAL then
            LV_AUX := (LV_CURRVAL - LV_MAXVAL);
            execute immediate 
                 'ALTER SEQUENCE '||P_IN_SEQ||' INCREMENT BY -'||LV_AUX;
            execute immediate 
                 'SELECT '||P_IN_SEQ||'.NEXTVAL FROM dual' INTO LV_AUX;
            execute immediate 
                 'ALTER SEQUENCE '||P_IN_SEQ||' INCREMENT BY 1';
          end if;

          while LV_CURRVAL < LV_MAXVAL
          loop
             execute immediate 
                'select '||P_IN_SEQ||'.nextval
                  from dual ' into LV_CURRVAL;
          end loop;
        end SYNC_SEQUENCE;

    begin
      SYNC_SEQUENCE('MY_SEQUENCIE_NAME','MY_TABLE_NAME','MY_FIELD_ID_NAME');
    end;
    /

Answer 2

If this is a one off, you can use the alter sequence alter sequence sequenceName increment by val ; whereas val is +1 to the maximum then call get nextVal, then set the increment back to 1.

I threw the below together to show you how it can be done without looping.

create sequence changeValue start with 18 increment by 1 nocache ;
select changeValue.nextval from dual ;
/

NEXTVAL                
---------------------- 
18  



set serveroutput on
declare
 maxVal     number := 24 ;
 curVal     number ;
 diffVal      number ;
 incrementVal number ;


 procedure alterSequence(seqName in varchar2, incVal in number) as
    s varchar2(500);
    begin
       s := 'alter sequence ' || seqName || ' increment by  ' || incVal ;
       dbms_output.put_line(s);
       execute immediate s;
    end alterSequence;
begin
    --(done in 11gr2 so if in earlier version select into)
     curVal := changeValue.currval ;
    dbms_output.put_line('curValue=>' || curVal );
    diffVal :=  maxVal - curVal ;
    dbms_output.put_line('diffVal=>' || diffVal );

    alterSequence ( 'changeValue' , diffVal + 1 );
    incrementVal   := changeValue.nextval ;
    dbms_output.put_line('incrementVal=>' || incrementVal );
    alterSequence ( 'changeValue' , 1 );
    curVal := changeValue.currval ;
    dbms_output.put_line('curValue=>' || curVal ); 
end ;
/


curValue=>18
diffVal=>6
alter sequence changeValue increment by  7
incrementVal=>25
alter sequence changeValue increment by  1
curValue=>25

---

or better yet, as @Dave suggests, just drop and recreate the sequence with the acceptable Start With value.

Answer 3

declare
  l_MaxVal  pls_integer;
  l_Currval pls_integer default - 1;
begin
  select max(id)
    into l_MaxVal
    from people;
  while l_Currval < l_Maxval
  loop
    select my_seq.nextval
      into l_Currval
      from dual;
  end loop;
end;