ALTER PROCEDURE

You can use the ALTER PROCEDURE statement to explicitly recompile an independent stored procedure.

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

Prerequisites

If the stored procedure to be recompiled is in the SYS schema, you must connect to the database with the SYSDBA role. Otherwise, the stored procedure must be located in your own schema, or you must have the ALTER ANY PROCEDURE system privilege.

Syntax

Syntax of alter_procedure:

ALTER PROCEDURE [ schema. ] procedure_name { procedure_compile_clause} ;

Syntax of procedure_compile_clause:

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

Semantics

Examples

• Explicitly recompile the userlogin procedure owned by the SYS user.

  ALTER PROCEDURE sys.userlogin COMPILE;
  

  In the preceding example, if the database does not encounter a compilation error when recompiling the userlogin procedure, the userlogin procedure takes effect. The database can directly run this procedure later without recompiling it at run time.

  If a compilation error occurs during the recompilation of the userlogin procedure, the database returns an error and the userlogin procedure becomes invalid.

  The database will also invalidate all objects that depend on the userlogin procedure, including any stored procedures, functions, and package bodies that call userlogin. If these objects are referenced later without being explicitly recompiled, the database will implicitly recompile the objects during running.

• Specify the proc1 and proc2 objects for compilation.

  // Debugged session: Create proc1 and proc2.
    CREATE OR REPLACE PROCEDURE proc1 IS
     BEGIN
        DBMS_OUTPUT.PUT_LINE('1');
     END;
     /
  
    CREATE OR REPLACE PROCEDURE proc2 IS
    BEGIN
       proc1;
    END;
    /
  
  // Debugging session: Go to the debug environment and compile proc1 and proc2.
   ALTER PROCEDURE proc1 COMPILE DEBUG;
   ALTER PROCEDURE proc2 COMPILE DEBUG;