PL/SQL Alter Procedure
The PL/SQL ALTER PROCEDURE statement is used 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.
Note: This statement does not change the declaration or definition of an existing procedure.
PL/SQL Alter Procedure Syntax
To explicitly recompile a standalone stored procedure, use the following syntax:
Syntax
ALTER PROCEDURE procedure_name
COMPILE;
Where:
procedure_name: The name of the procedure_name.
COMPILE: Specify COMPILE to recompile the procedure.
PL/SQL Alter Procedure example
The following PL/SQL show how to explicitly recompile a standalone stored procedure:
Example
ALTER PROCEDURE CreateUser
COMPILE;
PL/SQL Call Altered Procedure example
The following PL/SQL show how to call above created procedure using
EXECUTE and in PL/SQL block:
Example
EXECUTE CreateUser;
OR
BEGIN
CreateUser(10001, 'Ramesh', 'Malhotra');
DBMS_OUTPUT.PUT_LINE('User created successfully');
END;