Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, May 23, 2008

Markview error: ORA-04062: timestamp of package "SFGWY.SFOA_AP_UTIL" has been changed

We had an issue with 170 Systems Markview on one of our environments where the invoices were going into the error queue. Following error was thrown:

ORA-04062: timestamp of package "SFGWY.SFOA_AP_UTIL" has been changed

After a lot of time and research, Akhilesh and Subbu came across this thread in Oracle forums. After going through this they changed the value of initialization parameter from



The error stopped coming after this momentarily. The error was finally solved after they bounced the database.

I found this some good writeup on this parameter here, which I am pasting below:

* If the initialization parameter file contains the following specification:


Then only timestamps are used to resolve dependencies (if this is not explicitly overridden dynamically).
* If the initialization parameter file contains the following parameter specification:


Then signatures are used to resolve dependencies (if this not explicitly overridden dynamically).
* You can alter the mode dynamically by using the DDL statements. For example, this example alters the dependency model for the current session:


Thise example alters the dependency model systemwide after startup:

If the REMOTE_DEPENDENCIES_MODE parameter is not specified, either in the init.ora parameter file or using the ALTER SESSION or ALTER SYSTEM DDL statements, then timestamp is the default value. Therefore, unless you explicitly use the REMOTE_DEPENDENCIES_MODE parameter, or the appropriate DDL statement, your server is operating using the timestamp dependency model.


* If you change the default value of a parameter of a remote procedure, then the local procedure calling the remote procedure is not invalidated. If the call to the remote procedure does not supply the parameter, then the default value is used. In this case, because invalidation/recompilation does not automatically occur, the old default value is used. If you want to see the new default values, then you must recompile the calling procedure manually.
* If you add a new overloaded procedure in a package (a new procedure with the same name as an existing one), then local procedures that call the remote procedure are not invalidated. If it turns out that this overloading results in a rebinding of existing calls from the local procedure under the timestamp mode, then this rebinding does not happen under the signature mode, because the local procedure does not get invalidated. You must recompile the local procedure manually to achieve the new rebinding.
* If the types of parameters of an existing packaged procedure are changed so that the new types have the same shape as the old ones, then the local calling procedure is not invalidated or recompiled automatically. You must recompile the calling procedure manually to get the semantics of the new type.

Dependency Resolution

When REMOTE_DEPENDENCIES_MODE = TIMESTAMP (the default value), dependencies among program units are handled by comparing timestamps at runtime. If the timestamp of a called remote procedure does not match the timestamp of the called procedure, then the calling (dependent) unit is invalidated and must be recompiled. In this case, if there is no local PL/SQL compiler, then the calling application cannot proceed.

In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL compiler, then recompilation happens automatically when the calling procedure is run.

When REMOTE_DEPENDENCIES_MODE = SIGNATURE, the recorded timestamp in the calling unit is first compared to the current timestamp in the called remote unit. If they match, then the call proceeds. If the timestamps do not match, then the signature of the called remote subprogram, as recorded in the calling subprogram, is compared with the current signature of the called subprogram. If they do not match (using the criteria described in the section "When Does a Signature Change?"), then an error is returned to the calling session.
Suggestions for Managing Dependencies

Follow these guidelines for setting the REMOTE_DEPENDENCIES_MODE parameter:

* Server-side PL/SQL users can set the parameter to TIMESTAMP (or let it default to that) to get the timestamp dependency mode.
* Server-side PL/SQL users can choose to use the signature dependency mode if they have a distributed system and they want to avoid possible unnecessary recompilations.
* Client-side PL/SQL users should set the parameter to SIGNATURE. This allows:
1. Installation of new applications at client sites, without the need to recompile procedures.
2. Ability to upgrade the server, without encountering timestamp mismatches.
* When using signature mode on the server side, add new procedures to the end of the procedure (or function) declarations in a package specification. Adding a new procedure in the middle of the list of declarations can cause unnecessary invalidation and recompilation of dependent procedures.

conn / as sysdba

set linesize 121
col name format a40
col value format a40

SELECT name, value
FROM gv$parameter
WHERE name = 'remote_dependencies_mode';

1 comment:

PL SQL basico said...

good blog, im taking samples from your site, Im preparing a video blog