ORA-04062: string of string has been changed tips Oracle Error Tips by Stephanie F.

 

The Oracle docs note this on the ora-04062 error:

Oracle offers extensive information regarding ORA-04062 occurring

• on client-side PL/SQL like Oracle Forms, Reports
• server-side PL/SQL (across the database link)

Here is a bit of background information concerning ORA-04062:

NOTE: See additional information on timestamps and signatures at the bottom  of this page

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 (Oracle releases V7.2 and prior)
-OR-
SIGNATURES (Oracle releases V7.3 and onwards)

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, 7.3.4 and 8.0.4 onwards allow 'runtime binding' which
allows client PLSQL to delay the actual binding up of a reference to
a SCHEMA.OBJECT

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.

Oracle offers easy to follow recommendations for resolving ORA-04062:

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 Application Developers Guide 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).

For Developer 2000 (release 2.0 onwards), we also recommend that you use
database versions 7.3.4 or 8.0.4 (or higher) 'to take advantage of 'runtime
binding'.

There are a few problems concerning ORA-04062, which may help you diagnose the error faster:

Provided that REMOTE_DEPENDENCIES_MODE is set correctly, ORA-4062
errors should only be signaled if the signature of the procedure
changes.

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.

Also important to note is that a bug exists which is know to cause ORA-04062 unnecessarily.  Internet sources offer the following information about the ORA-04062 bug:

The following scenario describes how this bug affects Oracle Forms
and shows a simple workaround. Recompile the INVALID procedure
on the remote server.

1. Set the database to use the SIGNATURE dependency model:

REMOTE_DEPENDENCIES_MODE=SIGNATURE

Note: Without this, you always have to recompile forms or
reports if the connected users do not have the ALTER
SESSION privilege.

2. Compile a form calling a known procedure. For example:

FORM-A calls PROC_1 in the database

3. Run FORM-A.

4. Recompile PROC_1 on the server:

ALTER PROCEDURE PROC_1 RECOMPILE.

This adjusts the timestamp, but not the signature.

5. Run FORM-A. This should not report an error since the signature
has not changed.

6. Modify PROC_1 to depend on TAB_1. For example:

PROC_1 does a select from TAB_1

7. Recompile PROC_1 and FORM_A.

8. Run FORM-A.

9. ALTER TABLE TAB_1 and add a new column. This causes PROC_1 to be
INVALIDATED since it is referencing an object which has changed.

10. Run FORM-A.

If the bug is present, the form will raise an ORA-4062
error at this point since the called procedure is INVALID.

If the bug is fixed in the release you are using, the form
call will cause an automatic recompilation of PROC_1, restoring its
signature and hence generating no error.

11. To avoid the bug, compile all dependent procedures on the server
after TAB_1 has been changed. Provided the procedure definition
does not change, you should not need to recompile the form.

Effect for Server Calls using PL/SQL
------------------------------------

The bug scenario for a server call to a remote procedure is similar
to the description above.

1. Create PROC_1 which performs a select * from TAB_1 on REMOTEDB.

2. Call PROC_1 from LOCALDB.

3. Alter TAB_1 on REMOTEDB to add a new column.

4. If the bug is present, re-executing the block mentioned in step 2 above
raises an ORA-4062 error. A second execution of this block succeeds.

5. The workaround is to recompile PROC_1 after altering table
TAB_1 on REMOTEDB.

If you have not yet resolved ORA-04062, these last bits of information may help:

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 behavior.

---

More info:

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
the Oracle8 or "Oracle8i Application Developers Guide" in the section
on Remote Dependencies. 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.