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

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;