ALTER FUNCTION

This statement is not supported in TimesTen Scaleout.

In TimesTen Classic:

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

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

Required privilege

No privilege is required for the PL/SQL function owner.

ALTER ANY PROCEDURE for another user's function.

Usage with TimesTen Scaleout

This statement is not supported with TimesTen Scaleout.

SQL syntax

ALTER FUNCTION [Owner.]FunctionName COMPILE
      [CompilerParametersClause [...]] 
      [REUSE SETTINGS]

Parameters

Parameter Description

[Owner.]FunctionName

Name of the function to be recompiled.

COMPILE

Required keyword that causes recompilation of the function. If the function does not compile successfully, use the ttIsql command SHOW ERRORS to display the compiler error messages.

CompilerParametersClause

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 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 FUNCTION statement does not change the declaration or definition of an existing function. To redeclare or redefine a function, use the CREATE FUNCTION statement.

  • TimesTen first recompiles objects upon which the function depends, if any of those objects are invalid.

  • TimesTen also invalidates any objects that depend on the function, such as functions that call the recompiled function or package bodies that define functions that call the recompiled function.

  • If TimesTen recompiles the function successfully, then the function becomes valid. If recompiling the function results in compilation errors, then TimesTen returns an error and the function 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.

See also