Error:
ORA-04061: existing state of has been invalidated ORA-04061: existing state of package "APPS.HR_TRANSACTION_WEB" has been invalidated ORA-04065: not executed, altered or dropped package "APPS.HR_TRANSACTION_WEB" ORA-06508: PL/SQL: could not find program unit being called.
Resolution:
The cause for this is the SGA (memory that is allocated by an Oracle Instance and is shared among Oracle processes). Any object in SGA referencing an object that is compiled become invalid during this compilation.
So in this case the process accessing the compiled object needs to be restarted. For example, in case of Oracle Workflows, this error can be rectified by restarting the Workflow Mailer ( or a Retry also helps). One more way is to bounce the database itself which ofcourse will be a last resort.
Workaround
The invalid object needs to be recompiled to become valid again. Oracle 10g includes a mechanism to recompile some invalid objects when they are accessed for the first time. However, this mechanism does not always work and errors of this sort can occur. If this error is encountered, the invalid packages and any invalid type bodies can be maually recompiled.
Be sure to start a new Oracle session after recompiling before verifying the recompiling solved the problem.
To find invalid objects owned by the user SDE, connect to Oracle with SQL*PLus and execute the following statement:
SELECT OBJECT_NAME, OBJECT_TYPE
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY')
AND STATUS = 'INVALID';
To recompile a type body, use the following command:
ALTER TYPE object_name COMPILE BODY;
To recompile a package body, use the following command:
ALTER PACKAGE object_name COMPILE BODY;
Once the above process is completed and still the issue is not resolved then we need to bounce the instance and restart the workflow process.
Error:
3146: Commit happened in activity/function 'HR_HRSSA_APPROVED_PRC:HR_COMMIT_TRANSACTION_FCT/HR_TRANSACTION_WEB.COMMIT_TRANSACTION'
Resolution:
Once we have updated the package we need to bounce the instance and restart the workflow process.
-------------------------------------------------------------------------------
Workflow Commands
Command to download workflow: WFLOAD apps/apps 0 Y DOWNLOAD workflow_name Internal_Name
Command to upload workflow: WFLOAD apps/apps 0 Y UPLOAD workflow_name
