execute immediate alter sequence not working

Question

I'm stuck on this pretty simple script. It isn't working like I expect it to.

declare
 st VARCHAR(1024);
begin
  for x in (SELECT sequence_name FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1000';
      execute immediate st;
      st := 'select ' || x.sequence_name ||  '.nextval from dual';
      execute immediate st;
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1';
      execute immediate st;
  end loop;
end;
/

When I run this it doesn't appear to work at all - all of my sequences just stay as they are, and they have not been incremented by a thousand by the dynamic statements. If I check nextval before and after the anonymous block, the difference is only 1, not 1001.

If I replace execute immediate with dbms_output.put_line and execute the generated commands manually the sequences are altered as I want.

What am I missing?

Answer 1

Both alter sequence statements are working, it's the increment in between that isn't happening. The nextval call in your loop is not being evaluated because the select statement isn't sending its output anywhere. From the documentation, a note that happens to refer to exactly what you are doing:

Note:
If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes.
For example, this statement never increments the sequence:

EXECUTE IMMEDIATE 'SELECT S.NEXTVAL FROM DUAL'

So you need to select that value into something:

declare
 st VARCHAR(1024);
 val number;
begin
  for x in (SELECT sequence_name FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1000';
      execute immediate st;
      st := 'select ' || x.sequence_name ||  '.nextval from dual';
      execute immediate st into val;
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1';
      execute immediate st;
  end loop;
end;
/

I've added a val variable, and an into val clause on the second execute immediate.

To demonstrate that it works now:

create sequence s42;

Sequence s42 created.

declare
 st VARCHAR(1024);
 n number;
begin
  for x in (SELECT sequence_name FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1000';
      execute immediate st;
      st := 'select ' || x.sequence_name ||  '.nextval from dual';
      execute immediate st into n;
      st := 'ALTER SEQUENCE ' || x.sequence_name ||  ' INCREMENT BY 1';
      execute immediate st;
  end loop;
end;
/

anonymous block completed

select s42.nextval from dual;

   NEXTVAL
----------
      1001 

Without the into clause, this came back with 1 rather than 1001, which is what you are seeing.

Answer 2

The restart start with syntax in 12c can simplify the steps:

create sequence test_sequence;

declare
 st VARCHAR(1024);
begin
  for x in (SELECT sequence_name, last_number FROM USER_SEQUENCES) loop
      st := 'ALTER SEQUENCE ' || x.sequence_name
          || ' RESTART START WITH ' || to_char(x.last_number+1000);
      execute immediate st;
  end loop;
end;
/

select test_sequence.nextval from dual;

NEXTVAL
-------
   1001