ALTER PROCEDURE

The ALTER PROCEDURE statement recompiles a standalone stored procedure. Explicit recompilation eliminates the need for implicit runtime recompilation and prevents associated runtime compilation errors and performance overhead.

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

Required privilege

No privilege is required for the procedure owner.

ALTER ANY PROCEDURE for another user's procedure.

SQL syntax

ALTER PROCEDURE [Owner.]ProcedureName COMPILE
      [compiler_parameters_clause […]] 
      [REUSE SETTINGS]

Parameters

The ALTER PROCEDURE statement has the parameters:

Parameter	Description
`[Owner.]ProcedureName`	Name of the procedure to be recompiled.
`COMPILE`	Required keyword that causes recompilation of the procedure. If the procedure does not compile successfully, use the `ttIsql` command `SHOW ERRORS` to display the compiler error messages.
`compiler_parameters_clause`	Use this optional clause to specify a value for one of the PL/SQL persistent compiler parameters. The PL/SQL persistent compiler parameters are PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS and NLS_LENGTH_SEMANTICS. You can specify each parameter once in the statement. If you omit a parameter from this clause and you specify `REUSE SETTINGS`, then if a value was specified for the parameter in an earlier compilation, TimesTen uses that earlier value. If you omit a parameter and either you do not specify `REUSE SETTINGS` or no value has been specified for the parameter in an earlier compilation, then TimesTen obtains the value for the parameter from the session environment.
`REUSE SETTINGS`	Use this optional clause to prevent TimesTen from dropping and reacquiring compiler switch settings. When you specify `REUSE SETTINGS`, TimesTen preserves the existing settings and uses them for the compilation of any parameters for which values are not specified.

Description

The ALTER PROCEDURE statement does not change the declaration or definition of an existing procedure. To redeclare or redefine a procedure, use the CREATE PROCEDURE statement.
TimesTen first recompiles objects upon which the procedure depends, if any of those objects are invalid.
TimesTen also invalidates any objects that depend on the procedure, such as procedures that call the recompiled procedure or package bodies that define procedures that call the recompiled procedure.
If TimesTen recompiles the procedure successfully, then the procedure becomes valid. If recompiling the procedure results in compilation errors, then TimesTen returns an error and the procedure remains invalid. Use the ttIsql command SHOW ERRORS to display compilation errors.
During recompilation, TimesTen drops all persistent compiler settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE SETTINGS clause.

Examples

Query the system view USER_PLSQL_OBJECT_SETTINGS to check PLSQL_OPTIMIZE_LEVEL and PLSCOPE_SETTINGS for procedure query_emp. Alter query_emp by changing PLSQL_OPTIMIZE_LEVEL to 3. Verify results.

Command> SELECT PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS
       >  FROM user_plsql_object_settings WHERE  name = 'QUERY_EMP';
< 2, IDENTIFIERS:NONE >
1 row found.

Command> ALTER PROCEDURE query_emp COMPILE PLSQL_OPTIMIZE_LEVEL = 3;
 
Procedure altered.
 
Command> SELECT PLSQL_OPTIMIZE_LEVEL, PLSCOPE_SETTINGS
       >  FROM user_plsql_object_settings WHERE  name = 'QUERY_EMP';
< 3, IDENTIFIERS:NONE >
1 row found.