Rebuilding all normal indexes for a specific Oracle table

Question

We have a migration script that converts a LONG column to a LOB, and as mentioned in the Oracle migration guide, the index for the table now needs rebuilding.

Assuming the table name is MY_TABLE, I've been attempting to run this script:

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;

However, it fails with the following syntax error:

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 <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   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]

Even though this seems to match the syntax specified here: Database PL/SQL Language Reference

Is ALTER not a valid command to use in a loop?

Edit: At lad2025's suggestion, attempting to use EXECUTE IMMEDIATE like so:

5: LOOP
6:     execute immediate 'alter index ' || index_entry.index_name || ' rebuild';
7: END LOOP;

I receive:

ORA-06550: line 6, column 92:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem return
   returning <an exponent (**)> <> or != or ~= >= <= <> and or
   like like2 like4 likec between into using || bulk member
   submultiset

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)

Edit 2: The EXECUTE IMMEDIATE worked properly. The end-of-file issue was related to Liquibase executing my script, and my forgetting to define my <sql> block with:

<sql dbms="oracle" splitStatements="false">
                                    ^ defaults to true

Critically, Liquibase by default splits statements at the semicolon, and this needed to be turned off.

Answer 1

You cannot use DDL statements in PL/SQL block. Use Dynamic-SQL:

BEGIN
    ...
    EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD';
END

EDIT:

Try:

DECLARE
BEGIN
    FOR index_entry IN (select INDEX_NAME 
                        from user_indexes 
                        where  table_name='MY_TABLE' and
                         index_type='NORMAL')
    LOOP
        dbms_output.put_line('ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD'); 
        EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD';
    END LOOP;
END;
/

SqlFiddleDemo