ALTER PROCEDURE
This statement is not supported in TimesTen Scaleout.
In TimesTen Classic:
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.
Usage with TimesTen Scaleout
This statement is not supported with TimesTen Scaleout.
SQL syntax
ALTER PROCEDURE [Owner.]ProcedureNameCOMPILE [
CompilerParametersClause
[...]] [REUSE SETTINGS]
Parameters
Parameter | Description |
---|---|
|
Name of the procedure to be recompiled. |
|
Required keyword that causes recompilation of the procedure. If the procedure does not compile successfully, use the |
|
Use this optional clause to specify a value for one of the PL/SQL persistent compiler parameters. The PL/SQL persistent compiler parameters are You can specify each parameter once in the statement. If you omit a parameter from this clause and you specify |
|
Use this optional clause to prevent TimesTen from dropping and reacquiring compiler switch settings. When you specify |
Description
-
The
ALTER PROCEDURE
statement does not change the declaration or definition of an existing procedure. To redeclare or redefine a procedure, use theCREATE 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
commandSHOW 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
for procedure query_emp
. Alter query_emp
by changing PLSQL_OPTIMIZE_LEVEL
to 3. Verify results.
Command> SELECT PLSQL_OPTIMIZE_LEVEL FROM user_plsql_object_settings WHERE name = 'QUERY_EMP'; < 2 > 1 row found. Command> ALTER PROCEDURE query_emp COMPILE PLSQL_OPTIMIZE_LEVEL = 3; Procedure altered. Command> SELECT PLSQL_OPTIMIZE_LEVEL FROM user_plsql_object_settings WHERE name = 'QUERY_EMP'; < 3 > 1 row found.
See also