Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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:

ORA-04062 string of string has been changed
 
Cause: 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.
 
Action: 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 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.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.


Hit Counter