ALTER PROCEDURE

Purpose

Use the ALTER PROCEDURE statement to explicitly recompile a stand-alone 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.

See Also: ALTER FUNCTION

Prerequisites

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

Syntax

Keywords and Parameters

schema

Specify the schema containing the procedure. If you omit schema, Oracle 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 recompiles the procedure regardless of whether it is valid or invalid.

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

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

  See Also: Oracle8i Concepts for information on how Oracle maintains dependencies among schema objects, including remote objects

DEBUG

Specify DEBUG to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.

See Also: Oracle8i Application Developer's Guide - Fundamentals for information on debugging procedures

Example

Recompiling a Procedure Example

To explicitly recompile the procedure close_acct owned by the user henry, issue the following statement:

ALTER PROCEDURE henry.close_acct
   COMPILE; 

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

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