title: Rebuilding all normal indexes for a specific Oracle table
body: <p>We have a migration script that converts a LONG column to a LOB, and as mentioned in the <a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_long_lob.htm#i1021732" rel="noreferrer">Oracle migration guide</a>, the index for the table now needs rebuilding.</p>
  
  <p>Assuming the table name is <code>MY_TABLE</code>, I've been attempting to run this script:</p>
  
  <pre><code>BEGIN
      FOR index_entry IN (
          select INDEX_NAME from user_indexes where table_name='MY_TABLE' and index_type='NORMAL'
      )
      LOOP
          ALTER INDEX index_entry.index_name REBUILD;
      END LOOP;
  END;
  </code></pre>
  
  <p>However, it fails with the following syntax error:</p>
  
  <pre><code>PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
  
     ( begin case declare exit for goto if loop mod null pragma
     raise return select update while with &lt;an identifier&gt;
     &lt;a double-quoted delimited-identifier&gt; &lt;a bind variable&gt; &lt;&lt;
     continue close current delete fetch lock insert open rollback
     savepoint set sql execute commit forall merge pipe purge
   [Failed SQL: BEGIN
                  FOR index_entry IN (
                      select INDEX_NAME from user_indexes where table_name='MY_TABLE' and index_type='NORMAL'
                  )
                  LOOP
                      ALTER INDEX index_entry.index_name REBUILD]
  </code></pre>
  
  <p>Even though this <em>seems</em> to match the syntax specified here: <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#CHDBJBJE" rel="noreferrer">Database PL/SQL Language Reference</a></p>
  
  <p>Is <code>ALTER</code> not a valid command to use in a loop?</p>
  
  <p><strong>Edit:</strong> At <em>lad2025</em>'s suggestion, attempting to use <code>EXECUTE IMMEDIATE</code> like so:</p>
  
  <pre><code>5: LOOP
  6:     execute immediate 'alter index ' || index_entry.index_name || ' rebuild';
  7: END LOOP;
  </code></pre>
  
  <p>I receive:</p>
  
  <pre><code>ORA-06550: line 6, column 92:
  PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
  
     * &amp; = - + ; &lt; / &gt; at in is mod remainder not rem return
     returning &lt;an exponent (**)&gt; &lt;&gt; or != or ~= &gt;= &lt;= &lt;&gt; and or
     like like2 like4 likec between into using || bulk member
     submultiset
  
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
  </code></pre>
  
  <p><strong>Edit 2:</strong> The <code>EXECUTE IMMEDIATE</code> worked properly. The end-of-file issue was related to Liquibase executing my script, and my forgetting to define my <code>&lt;sql&gt;</code> block with:</p>
  
  <pre><code>&lt;sql dbms="oracle" splitStatements="false"&gt;
                                      ^ defaults to true
  </code></pre>
  
  <p>Critically, Liquibase by default splits statements at the semicolon, and this needed to be turned off.</p>