ALTER PROCEDURE

The ALTER PROCEDURE statement is used to explicitly recompile standalone stored procedures.

To recompile a stored procedure that is part of a package, use the ALTER PACKAGE statement.

Prerequisites

If the stored procedure is in the SYS schema, you must connect as SYSDBA. Otherwise, the stored procedure must be in the current user's schema, or the user must have the ALTER ANY PROCEDURE system privilege.

Syntax

The syntax of alter_procedure is as follows:

ALTER PROCEDURE [ schema. ] procedure_name
{ procedure_compile_clause} ;

where procedure_compile_clause has the following syntax:

COMPILE [ DEBUG ] [ compiler_parameters_clause ... ] [ REUSE SETTINGS ]

Semantics

Examples

The following example explicitly recompiles the userlogin stored procedure owned by the sys user.

ALTER PROCEDURE sys.userlogin COMPILE;

If no compilation errors occur during the recompilation of userlogin, the stored procedure becomes valid. The database can then execute it without needing to recompile it at runtime.

If the recompilation of userlogin results in a compilation error, the database returns an error, and userlogin remains invalid.

The database also invalidates all objects that depend on userlogin. These objects include any stored procedures, functions, and package bodies that call userlogin. If these objects are subsequently referenced without being explicitly recompiled, the database will implicitly recompile them at runtime.