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.

Hague

Dont "reset" a sequence, USE it using nextval. You probably dont NEED sequential ids separated by value of 1 (1,2,3,4,5...) for PK, just unique values (1,2,5,6,9,...). – Karakalpak

He doesn't want to reset it - his problem is that the sequence and the primary keys are out of sync (happens sometimes if you copy table content without adjusting the sequence accordingly), and therefore the sequence value is less than the maximum PK in the table. – Alexander

@frank: ok, got it. Suppose drop/recreate of the sequence with correct start val would work too, but I like your solution (nothing goes invalid) – Karakalpak

Don't reset a sequence using loop. Use the approach from this question instead: #51970 – Dramamine

Answer 1

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;

Alexander

Thanks a lot Frank. Yeah this is what happened the sequence and PK are out of Sync. Your loop seems do the trick. btw what's this default - 1 do? – Hague

It initializes the variable with value -1. – Alexander

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.

Mcgrew

I like this better than the loop option, because it will not generate lots of unnecessary redo. But I wonder, if executing DDL on the sequence is permissible, why not just drop and re-create it with a new start value? – Toboggan

Because dropping and re-creating it will lose all grants for this sequence? (Probably not a problem in this case, however) – Alexander

Is this going to work on a live database where there are other processes using the sequence? I'm afraid that once I set the increment by to 1000000 (for example), I'm going to have unnecessary large gaps in the sequence (one or two numbers are okay, but not several M). – Diedra

Answer 3

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;
    /

Anthropomorphosis