ORA-04062

ORA-4062 Explained (for Client and Server PL/SQL) [ID 73506.1]

Applies to:

PL/SQL - Version: 7.3.4.5 to 11.1.0.7 - Release: to 11.1
Information in this document applies to any platform.
Checked for relevance on 25-Feb-2010

Purpose

This article contains information about the ORA-4062 error which can occur when using client-side PL/SQL (Oracle Forms, Reports) or server-side PL/SQL across a database link.

Scope and Application

This article is intended for any level of user seeking an explanation of the error code.

ORA-4062 Explained (for Client and Server PL/SQL)

Background

ORA-4062 indicates that 'TIMESTAMP / SIGNATURE of NAME has been changed'.
When a local piece of PL/SQL references a remote package, function, or procedure, the local PL/SQL engine needs to know if the reference is still valid, or, if the remote procedure has changed.

The locally compiled PL/SQL code is 'dependent' on the remote code. The following two models can be used in Oracle to track this dependency:

TIMESTAMPS
-OR-
SIGNATURES

The method used is determined by the server initialization <Parameter:REMOTE_DEPENDENCIES_MODE>. This can be set at the instance (initSID.ora) or session (ALTER SESSION) level.

Additionally, we allow 'runtime binding' which allows client PLSQL to delay the actual binding up of a reference to a SCHEMA.OBJECT.

Timestamp

If the dependency mode is set to TIMESTAMP, the local PL/SQL block can only execute the remote PL/SQL block if the timestamp on the remote procedure matches the timestamp stored in the locally compiled PL/SQL block. If the timestamps do not match, the local PL/SQL must be recompiled.

Signature

If the dependency mode is set to SIGNATURE, the local PL/SQL block can  still execute the remote PL/SQL block if its 'signature' is the same, even if the timestamp has changed.

The 'signature' basically means the interface (procedure name, parameter types or modes) is the same, even if the underlying implementation has changed.

A description of the factors that the SIGNATURE depends on can be found in Chapter 7 of the Oracle Application Developers Guide in the section on Remote Dependencies (up to Version 10.1) or in Chapter 6 of the Oracle Database Concepts guide (Version 10.2 onwards). It is important to read this section because a few disadvantages exist to using a SIGNATURE dependency model.

The main disadvantage is that a few changes to a stored package / procedure require manual recompilation of the calling PL/SQL. For example, if an overloaded version of an existing procedure is added, then the caller still uses the original version until the caller is recompiled.

ORA-4062

This error is reported if the local PL/SQL block cannot call the remote procedure, since the timestamp or signature has changed on the remote end. A local recompilation may be required to make the call.

In the case of server to server calls, the local PL/SQL block is implicitly recompiled on the next call after an ORA-4062 error. In the case of client tools to server calls, the client Form or Report usually needs to be recompiled explicitly.

Recommendation

Oracle recommends that <Parameter:REMOTE_DEPENDENCIES_MODE> is set to SIGNATURE when client-side PL/SQL tools are used OR when server-side PL/SQL calls are used across database links. This reduces the chances of the ORA-4062 errors and the need for unnecessary recompilations, but note the restrictions discussed in the Documentation as mentioned above.

Client tools, such as Developer, attempt to use SIGNATURE mode by issuing 'ALTER SESSION' statements. Therefore, the init.ora parameter setting is over-ridden for these products (provided users have the 'ALTER SESSION' privilege).

Known Issues

Provided that REMOTE_DEPENDENCIES_MODE is set correctly, ORA-4062 errors should only be signalled if the signature of the procedure changes.
For example, if the remote procedure contains a definition MYPROC( A NUMBER ),and this is changed to MYPROC( A NUMBER, B NUMBER ), the signature has changed. Therefore, it is expected behaviour that any PL/SQL calling MYPROC must be recompiled.
Many Oracle tools which include a client-side PL/SQL engine issue an

'ALTER SESSION SET REMOTE_DEPENDENCIES_MODE=SIGNATURE'

statement to set the SIGNATURE dependency model. Errors from issuing this statement may be silently ignored. For example, if a user does not have the 'ALTER SESSION' privilege, then the REMOTE_DEPENDENCIES_MODE is left at the default mode (taken from the init.ora parameter setting).

Bug 5249142  ORA-04062: signature of package "SYS.UTL_RAW" has been changed

Additional Notes

If the parameters to a procedure use %TYPE or %ROWTYPE, then a change to the table on which the %TYPE or %ROWTYPE is based upon can change the signature of the procedure.

If the signature changes, an ORA-4062 error is correctly signaled. For client PL/SQL objects, changes to a signature require the client form or report to be recompiled. This is correct behaviour.

Conclusion

If, after reviewing the above steps, you believe you have a scenario where an ORA-4062 is being incorrectly signalled, then it is important to describe each step that leads to the error. Oracle Support Services requires a small test case to establish validity for the error.