The ALTER FUNCTION statement explicitly recompiles an invalid standalone stored function. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.


This statement does not change the declaration or definition of an existing function. To redeclare or redefine a standalone stored function, use the CREATE FUNCTION Statement with the OR REPLACE clause.


The function must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.



Description of the illustration alter_function.gif


Description of the illustration compiler_parameters_clause.gif

Keyword and Parameter Descriptions


Specify the schema containing the function. If you omit schema, then the database assumes the function is in your own schema.


Specify the name of the function to be recompiled.


Specify COMPILE to cause the database to recompile the function. The COMPILE keyword is required. If the database does not compile the function successfully, then you can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.

During recompilation, the database drops all persistent compiler switch settings, retrieves them again from the session, and stores them at the end of compilation. To avoid this process, specify the REUSE SETTINGS clause.


Specify DEBUG to instruct the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. Specifying this clause has the same effect as specifying PLSQL_DEBUG = TRUE in the compiler_parameters_clause.


Use this clause to specify a value for one of the PL/SQL persistent compiler parameters. The value of these initialization parameters at the time of compilation is stored with the unit's metadata. You can learn the value of such a parameter by querying the appropriate *_PLSQL_OBJECT_SETTINGS view. The PL/SQL persistent parameters are PLSQL_OPTIMIZE_LEVEL, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_WARNINGS, PLSQL_CCFLAGS, and NLS_LENGTH_SEMANTICS.

You can specify each parameter only once in each statement. Each setting is valid only for the current library unit being compiled and does not affect other compilations in this session or system. To affect the entire session or system, you must set a value for the parameter using the ALTER SESSION or ALTER SYSTEM statement.

If you omit any parameter from this clause and you specify REUSE SETTINGS, then if a value was specified for the parameter in an earlier compilation of this library unit, the database uses that earlier value. If you omit any parameter and either you do not specify REUSE SETTINGS or no value has been specified for the parameter in an earlier compilation, then the database obtains the value for that parameter from the session environment.

Restriction on the compiler_parameters_clause  You cannot set a value for the PLSQL_DEBUG parameter if you also specify DEBUG, because both clauses set the PLSQL_DEBUG parameter, and you can specify a value for each parameter only once.

See Also:


Specify REUSE SETTINGS to prevent Oracle from dropping and reacquiring compiler switch settings. With this clause, Oracle preserves the existing settings and uses them for the recompilation of any parameters for which values are not specified elsewhere in this statement.

For backward compatibility, the database sets the persistently stored value of the PLSQL_COMPILER_FLAGS initialization parameter to reflect the values of the PLSQL_CODE_TYPE and PLSQL_DEBUG parameters that result from this statement.

See Also:


Recompiling a Function: Example To explicitly recompile the function get_bal owned by the sample user oe, issue the following statement:


If the database encounters no compilation errors while recompiling get_bal, then get_bal becomes valid. the database can subsequently execute it without recompiling it at run time. If recompiling get_bal results in compilation errors, then the database returns an error, and get_bal remains invalid.

the database also invalidates all objects that depend upon get_bal. If you subsequently reference one of these objects without explicitly recompiling it first, then the database recompiles it implicitly at run time.

Related Topics