Oracle7 Server SQL Reference

ALTER PROCEDURE

Purpose

To recompile a stand-alone stored procedure.

Prerequisites

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

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the procedure's creation label or you must satisfy one of these criteria:

If the procedure's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the procedure's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the procedure's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

is the schema containing the procedure. If you omit schema, Oracle7 assumes the procedure is in your own schema.

procedure

is the name of the procedure to be recompiled.

COMPILE

causes Oracle7 to recompile the procedure. The COMPILE keyword is required.

Usage Notes

The ALTER PROCEDURE command and the ALTER FUNCTION command are quite similar. The following discussion of explicitly recompiling procedures also applies to functions.

You can use the ALTER PROCEDURE command to explicitly recompile a procedure that is invalid. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors and performance overhead.

When you issue an ALTER PROCEDURE statement, Oracle7 recompiles the procedure regardless of whether it is valid or invalid.

You can only use the ALTER PROCEDURE command to recompile a stand-alone procedure. To recompile a procedure that is part of a package, you must recompile the entire package using the ALTER PACKAGE command.

When you recompile a procedure, Oracle7 first recompiles objects upon which the procedure depends, if any of these objects are invalid. Oracle7 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 Oracle7 recompiles the procedure successfully, the procedure becomes valid. If recompiling the procedure results in compilation errors, then Oracle7 returns an error and the procedure remains invalid. You can then debug procedures using the predefined package DBMS_OUTPUT. For information on debugging procedures, see the "Using Procedures and Packages" chapter of the Oracle7 Server Application Developer's Guide. For information on how Oracle7 maintains dependencies among schema objects, including remote objects, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.

Note: This command does not change the declaration or definition of an existing procedure. To re-declare or redefine a procedure, you must use the CREATE PROCEDURE command with the OR REPLACE option.

Example

To explicitly recompile the procedure CLOSE_ACCT owned by the user HENRY, issue the following statement:

ALTER PROCEDURE henry.close_acct 
	COMPILE

If Oracle7 encounters no compilation errors while recompiling CLOSE_ACCT, CLOSE_ACCT becomes valid. Oracle7 can subsequently execute it without recompiling it at runtime. If recompiling CLOSE_ACCT results in compilation errors, Oracle7 returns an error and CLOSE_ACCT remains invalid.

Oracle7 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, Oracle7 recompiles it implicitly at runtime.