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.

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

Solution 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.

Comments

[Dave Costa]

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?

[user729147]

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?

[Frank Schmitt]

It initializes the variable with value -1.

[Frank Schmitt]

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

[Nauman Aslam]

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).