ORA-04062: string of string has been changed tips
Oracle Error Tips by Stephanie F.
The Oracle docs note this on the
string of string has
Attempt to execute a stored procedure to serve an RPC stub which
specifies a timestamp or signature that is different from the current
timestamp/signature of the procedure.
Recompile the caller in order to pick up the new timestamp.
Oracle offers extensive information
regarding ORA-04062 occurring
- on client-side PL/SQL like Oracle
- server-side PL/SQL (across the
Here is a bit of background information
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,
procedure, the local PL/SQL engine needs to know if the reference is
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)
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
This error is reported if the local PL/SQL block cannot call the
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
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
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
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
Client tools, such as Developer, attempt to use SIGNATURE mode by
issuing 'ALTER SESSION' statements. Therefore, the init.ora parameter
is over-ridden for these products (provided users have the 'ALTER SESSION'
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
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
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:
Note: Without this, you always have to recompile forms or
reports if the connected users do not have the ALTER
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
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
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:
If the parameters to a procedure use %TYPE or %ROWTYPE, then a
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.
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
matches the timestamp stored in the locally compiled PL/SQL block. If
timestamps do not match, the local PL/SQL must be recompiled.
If the dependency mode is set to SIGNATURE, the local PL/SQL
still execute the remote PL/SQL block if its 'signature' is the same,
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
A description of the factors that the SIGNATURE depends on can be
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
overloaded version of an existing procedure is added, then the caller still
uses the original version until the caller is recompiled.