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.

Solution 1

Here are a couple of possibilities. First, you would have to treat the SQL as dynamic SQL. Second, Oracle DDL statements cannot be run in a transaction (or, they terminate the current transaction and cannot themselves be rolled back). This may affect whether you can use them in stored procedures, or where you can use stored procedures that contain them.

If none of the above apply at all - there could easily be something else astray - I suggest posting some code.

Solution 2

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.

Solution 3

Documentation.

Passing Schema Object Names As Parameters

Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. You must build a string with a statement that includes the object names, then use EXECUTE IMMEDIATE to execute the statement:

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
/

Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the USING clause.

In addition, if you need to call a procedure whose name is unknown until runtime, you can pass a parameter identifying the procedure. For example, the following procedure can call another procedure (drop_table) by specifying the procedure name when executed.

CREATE PROCEDURE run_proc (proc_name IN VARCHAR2, table_name IN VARCHAR2) ASBEGIN
   EXECUTE IMMEDIATE 'CALL "' || proc_name || '" ( :proc_name )' using table_name;
END;
/

If you want to drop a table with the drop_table procedure, you can run the procedure as follows. Note that the procedure name is capitalized.

CREATE TABLE employees_temp AS SELECT last_name FROM employees;
BEGIN 
  run_proc('DROP_TABLE', 'employees_temp'); 
END;
/