ALTER PROCEDURE

Purpose

Use the ALTER PROCEDURE statement to explicitly recompile 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 (see ALTER PACKAGE).

Note:

This statement does not change the declaration or definition of an existing procedure. To redeclare or redefine a procedure, use the CREATE PROCEDURE statement with the OR REPLACE clause (see CREATE PROCEDURE).

The ALTER PROCEDURE statement is quite similar to the ALTER FUNCTION statement. Please refer to ALTER FUNCTION for more information.

Prerequisites

The procedure must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Syntax

alter_procedure::=

Description of alter_procedure.gif follows
Description of the illustration alter_procedure.gif

compiler_parameters_clause::=

Description of compiler_parameters_clause.gif follows
Description of the illustration compiler_parameters_clause.gif

Semantics

schema

Specify the schema containing the procedure. If you omit schema, then Oracle Database assumes the procedure is in your own schema.

procedure

Specify the name of the procedure to be recompiled.

COMPILE

Specify COMPILE to recompile the procedure. The COMPILE keyword is required. Oracle Database recompiles the procedure regardless of whether it is valid or invalid.

  • Oracle Database first recompiles objects upon which the procedure depends, if any of those objects are invalid.

  • Oracle Database also invalidates any local objects that depend upon the procedure, such as procedures that call the recompiled procedure or package bodies that define procedures that call the recompiled procedure.

  • If Oracle Database recompiles the procedure successfully, then the procedure becomes valid. If recompiling the procedure results in compilation errors, then Oracle Database returns an error and the procedure remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.

During recompilation, Oracle Database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE SETTINGS clause.

See Also:

Oracle Database Concepts for information on how Oracle Database maintains dependencies among schema objects, including remote objects and "Recompiling a Procedure: Example"

DEBUG

Specify DEBUG to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Specifying this clause is the same as specifying PLSQL_DEBUG = TRUE in the compiler_parameters_clause.

See Also:

Oracle Database Application Developer's Guide - Fundamentals for information on debugging procedures

compiler_parameters_clause

This clause has the same behavior for a procedure as it does for a function. Please refer to the ALTER FUNCTION compiler_parameters_clause.

REUSE SETTINGS

This clause has the same behavior for a procedure as it does for a function. Please refer to the ALTER FUNCTION clause REUSE SETTINGS.

Example

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

ALTER PROCEDURE hr.remove_emp
   COMPILE;

If Oracle Database encounters no compilation errors while recompiling credit, then credit becomes valid. Oracle Database can subsequently execute it without recompiling it at run time. If recompiling credit results in compilation errors, then Oracle Database returns an error and credit remains invalid.

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