Oracle Stored Procedure with Alter command

Question

I am trying to build an Oracle stored procedure which will accept a table name as a parameter. The procedure will then rebuild all indexes on the table.

My problem is I get an error while using the ALTER command from a stored procedure, as if PLSQL does not allow that command.

Answer 1

Use the execute immediate statement to execute DDL inside PL/SQL.

create procedure RebuildIndex(index_name varchar2) as
begin
   execute immediate 'alter index ' || index_name || ' rebuild';
end;

I tested this code; it works.