The ALTER
PROCEDURE
statement explicitly recompiles 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).
Note:
This statement does not change the declaration or definition of an existing procedure. To redeclare or redefine a standalone stored procedure, use the CREATE PROCEDURE Statement with theOR
REPLACE
clause.The ALTER
PROCEDURE
statement is very similar to the ALTER
FUNCTION
statement. See ALTER FUNCTION Statement for more information.
The procedure must be in your own schema or you must have ALTER
ANY
PROCEDURE
system privilege.
Keyword and Parameter Descriptions
Specify the schema containing the procedure. If you omit schema
, then the database assumes the procedure is in your own schema.
Specify the name of the procedure to be recompiled.
Specify COMPILE
to recompile the procedure. The COMPILE
keyword is required. the database recompiles the procedure regardless of whether it is valid or invalid.
the database first recompiles objects upon which the procedure depends, if any of those objects are invalid.
the 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 the database recompiles the procedure successfully, then the procedure becomes valid. If recompiling the procedure results in compilation errors, then the 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, the 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:
Recompiling a Procedure: ExampleSpecify 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 Advanced Application Developer's Guide for information about debugging proceduresThis clause has the same behavior for a procedure as it does for a function. See the ALTER
FUNCTION
compiler_parameters_clause.
This clause has the same behavior for a procedure as it does for a function. See the ALTER
FUNCTION
clause REUSE SETTINGS.
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 the database encounters no compilation errors while recompiling remove_emp
, then remove_emp
becomes valid. the database can subsequently execute it without recompiling it at run time. If recompiling remove_emp
results in compilation errors, then the database returns an error and remove_emp
remains invalid.
the database also invalidates all dependent objects. These objects include any procedures, functions, and package bodies that call remove_emp
. If you subsequently reference one of these objects without first explicitly recompiling it, then the database recompiles it implicitly at run time.