Just curious: why all the code when changing next number in a sequence?

[Claus Pedersen]

I have a sequence with next number 10 and want to change it to 20. I use the editor and press View SQL to view the SQL. I see the following:

alter sequence MY_SEQUENCE increment by 10 nocache;
select MY_SEQUENCE.nextval from dual;
alter sequence MY_SEQUENCE increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select MY_SEQUENCE.currval into LastValue from dual;
    exit when LastValue >= 20 - 1;
    select MY_SEQUENCE.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence MY_SEQUENCE increment by 1 cache 20;

Why do you have the PL/SQL code with the loop? It is because the sequence is defined with cache 20?

 

[Marco Kalter]

To change the next value, you have to read the sequence a number of times until the specified value is reached. Hence the loop.

 

[Claus Pedersen]

Yes, but the first three statements "alter ... increment by 10", "... MY_SEQUENCE.nextval ...", "alter ... increment by 1" sets the sequence at 20, so in this case there is no need for the loop