title: using pl/sql to update sequences
body: <p>We have a sql script to update a set of sequences after seed data populated our tables. The code below would not work:</p>
  
  <pre><code>declare
  cursor c1 is 
      select
        'select nvl(max(id),0) from '||uc.table_name sql_text,
        uc.table_name||'_SEQ' sequence_name 
      from
        user_constraints uc,
        user_cons_columns ucc
      where uc.constraint_type='P'
        and ucc.constraint_name = uc.constraint_name
        and ucc.column_name='ID'
        and uc.owner='ME';
  
  alter_sequence_text varchar2(1024);
  TYPE generic_cursor_type IS REF CURSOR;
  max_id number;
  c2 generic_cursor_type;
  begin
  for r1 in c1 loop
      open c2 for r1.sql_text;
      fetch c2 into max_id;
      close c2;
  
      if( max_id != 0 ) then
          dbms_output.put_line( 'seq name = '||r1.sequence_name );
          execute immediate 'alter sequence '||r1.sequence_name||' increment by '||to_char(max_id);
          dbms_output.put_line( 'max_id = '||to_char(max_id) );
          execute immediate 'select '||r1.sequence_name||'.nextval from dual';
          dbms_output.put_line( 'sequence value = '||to_char(next_id) );
          execute immediate 'alter sequence '||r1.sequence_name||' increment by 1';
          dbms_output.put_line( 'sequence: '||r1.sequence_name||' is at '||to_char(max_id+1) );
      end if;
  end loop;
  end;
  </code></pre>
  
  <p>After searching I found a reference that stated I needed to change the line:</p>
  
  <pre><code>execute immediate 'select '||r1.sequence_name||'.nextval from dual'
  </code></pre>
  
  <p>and add 'into next_id;' (of course declaring next_id appropriately) so the result would be:</p>
  
  <pre><code>execute immediate 'select '||r1.sequence_name||'.nextval from dual into next_id;
  </code></pre>
  
  <p>I've only dealt lightly with pl/sql and sql in general and am interested to know why this change was necessary to make the script work correctly.</p>
  
  <p>Thanks.</p>