Restore all normal indexes for a specific Oracle table

Question

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

Assuming the table name MY_TABLE, I tried 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 does not work 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]

Although this looks like the syntax specified here: PL / SQL Database Reference

Is an ALTERinvalid command to use in a loop?

Edit: On a lad2025 suggestion trying to use EXECUTE IMMEDIATEas follows:

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

I get:

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)

2: EXECUTE IMMEDIATE . Liquibase, script, <sql> :

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

, Liquibase , .

Answer 1

DDL PL/SQL. 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