Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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.

See Also:

ALTER FUNCTION

Prerequisites

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

Syntax

alter_procedure::=

Text description of statements_117.gif follows
Text description of alter_procedure


Semantics

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.

During recompilation, Oracle 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:

Oracle9i Database Concepts for information on how Oracle 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.

See Also:

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

REUSE SETTINGS

Specify REUSE SETTINGS to prevent Oracle from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation.

If you specify both DEBUG and REUSE SETTINGS, Oracle sets the persistently stored value of the PLSQL_COMPILER_FLAGS parameter to INTERPRETED, DEBUG. No other compiler switch values are changed.

See Also:

PL/SQL User's Guide and Reference and Oracle9i Application Developer's Guide - Fundamentals for more information on the interaction of the PLSQL_COMPILER_FLAGS parameter with the COMPILE clause

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 encounters no compilation errors while recompiling credit, credit becomes valid. Oracle can subsequently execute it without recompiling it at run time. If recompiling credit results in compilation errors, Oracle returns an error and credit remains invalid.

Oracle 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, Oracle recompiles it implicitly at run time.