ALTER PROCEDURE Statement

The ALTER PROCEDURE statement explicitly recompiles a standalone stored procedure. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

To recompile a procedure that is part of a package, recompile the entire package using the "ALTER PACKAGE Statement").

The ALTER PROCEDURE statement is very similar to the ALTER FUNCTION statement. See "ALTER FUNCTION Statement" for more information.

Topics:

• Prerequisites

• Syntax

• Semantics

• Example

• Related Topics

Prerequisites

If the procedure is in the SYS schema, you must be connected as SYSDBA. Otherwise, the procedure must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Syntax

alter_procedure::=

compiler_parameters_clause::=

Semantics

schema

The name of the schema containing the procedure. The default is your own schema.

procedure

The name of the procedure to be recompiled.

COMPILE

Recompiles the procedure, whether it is valid or invalid.

First, if any of the objects upon which the procedure depends are invalid, the database recompiles them.

The database also invalidates any local objects that depend upon the procedure, such as subprograms that call the recompiled procedure or package bodies that define subprograms that call the recompiled procedure.

If the database recompiles the procedure successfully, then the procedure becomes valid. Otherwise, the database returns an error and the procedure remains invalid.

During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them after compilation. To avoid this process, specify the REUSE SETTINGS clause.

DEBUG

Has the same behavior for a procedure as it does for a function. See DEBUG.

compiler_parameters_clause

Has the same behavior for a procedure as it does for a function. See the ALTER FUNCTION compiler_parameters_clause.

REUSE SETTINGS

Has the same behavior for a procedure as it does for a function. See REUSE SETTINGS.

Example

Recompiling a Procedure: Example To explicitly recompile the procedure remove_emp owned by the user hr, issue this statement:

ALTER PROCEDURE hr.remove_emp COMPILE;

If the database encounters no compilation errors while recompiling remove_emp, then remove_emp becomes valid. The database can subsequently run it without recompiling it at run time. If recompiling remove_emp results in compilation errors, then the database returns an error and remove_emp remains invalid.

the database also invalidates all dependent objects. These objects include any procedures, functions, and package bodies that call remove_emp. If you subsequently reference one of these objects without first explicitly recompiling it, then the database recompiles it implicitly at run time.

Related Topics

• "CREATE PROCEDURE Statement"

• "DROP PROCEDURE Statement"