Thursday, April 29, 2010

ORA-04068 Errors from User-Written and Oracle Packages

ORA-04068 Errors from User-Written and Oracle Packages



Causes of ORA-4068 Errors


-------------------------



1) A dependent object was altered through a DDL statement.



When a package is compiled, all copies in the shared pool are

flagged as invalid. The next invocation of the package sees

this flag set, and goes to get a new copy.



If the package is now invalid, cannot compile, or relied on

a package state (i.e., package level variables), then this error

occurs because the current copy of the package is no longer valid

and must be thrown out.



2) The package was changed or recompiled (both DDL) and the package

being used contains package level variables.



Same as above. When a package dependency is altered through

DDL statements (DROP, CREATE, ALTER, ...), this package is

flagged as invalid through cascade invalidation.



3) A package relied on another package that had no body, and during

execution, the call failed.



When a package is compiled, it only looks for the specification.

During execution, it calls a non-existent routine and throws an

error. This error then invalidates the package.



Another variation is if the procedure being called is not defined

in the package body and possibly as a standalone routine.



4) A remote dependent object has been altered through a DDL statement.

This can occur between database instances or from Forms or Reports

to a database instance.



The default remote dependency model uses the Timestamp model, and when

an execution of a procedure takes place, the remote object's timestamp

is validated, thus forcing invalidation on the local package.



To check for these situations, several SQL statements can be run:



a. To check the package's last compile:



SELECT object_name, object_type, owner, status, last_ddl_time FROM

dba_objects WHERE object_name = '';



For example:



SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM

2 dba_objects WHERE object_name = 'DBMS_SQL';



OBJECT_NAME

------------------------------------------------------------------------

OBJECT_TYPE OWNER STATUS LAST_DDL_

------------- ------------------------------ ------- ---------

DBMS_SQL

PACKAGE SYS VALID 13-JUL-99



DBMS_SQL

PACKAGE BODY SYS VALID 13-JUL-99



DBMS_SQL

SYNONYM PUBLIC VALID 13-JUL-99



SQL>



b. To check the dependent objects last alteration:



SELECT object_name, object_type, owner, status, last_ddl_time FROM

dba_objects WHERE ( object_name, object_type ) IN ( SELECT

referenced_name, referenced_type FROM dba_dependencies WHERE name =

'' );



For example:



SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM

2 dba_objects WHERE ( object_name, object_type ) IN ( SELECT

3 referenced_name, referenced_type FROM dba_dependencies WHERE name =

4 'DBMS_SQL' );



OBJECT_NAME

-----------------------------------------------------------------------------

OBJECT_TYPE OWNER STATUS LAST_DDL_

------------- ------------------------------ ------- ---------

DBMS_SQL

PACKAGE SYS VALID 13-JUL-99



DBMS_SYS_SQL

PACKAGE SYS VALID 13-JUL-99



STANDARD

PACKAGE SYS VALID 13-JUL-99





SQL>



c. To check for existing errors on package:



SELECT name, type, text FROM dba_errors WHERE name = '';



For example:



SQL> SELECT name, type, text FROM dba_errors WHERE name = 'DBMS_SQL';



no rows selected



SQL>





Solutions for ORA-4068 Errors

-----------------------------



1) Simply re-execute the package.



For example:



Session 1: Create the package and body for package p_pack:



SQL> create or replace package p_pack as

2 p_var varchar2(1);

3 procedure p;

4 end p_pack;

5 /



Package created.



SQL> create or replace package body p_pack as

2 procedure p is

3 begin

4 p_var := 1;

5 end;

6 end p_pack;

7 /



Package body created.



SQL>



Session 2: Execute the package:



SQL> exec p_pack.p



PL/SQL procedure successfully completed.



SQL>



Session 1: Recreate the package and body:



SQL> create or replace package p_pack as

2 p_var varchar2(1);

3 procedure p;

4 end p_pack;

5 /



Package created.



SQL> create or replace package body p_pack as

2 procedure p is

3 begin

4 p_var := 1;

5 end;

6 end p_pack;

7 /



Package body created.



SQL>



Session 2: Re-execute the package:



SQL> exec p_pack.p

begin p_pack.p; end;



*

ERROR at line 1:

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package "SCOTT.P_PACK" has been invalidated

ORA-04065: not executed, altered or dropped package "SCOTT.P_PACK"

ORA-06508: PL/SQL: could not find program unit being called

ORA-06512: at line 1





SQL> exec p_pack.p



PL/SQL procedure successfully completed.



SQL>



As background, when the ORA-4068 is raised, ORACLE will throw away all

existing instantiations of the package. When the package (more properly,

the subprogram referring to the package) is re-executed, ORACLE will

re-instantiate the package automatically (if possible), which

typically will succeed, and re-execution of the subprogram will succeed.



An important proviso is that the ORA-4068 error must be unhandled on exit

from the subprogram in order for this solution to work. It's only when an

_unhandled_ ORA-4068 is returned by PL/SQL to ORACLE that the needed

deinstantiations take place.

(The ORA-4068 may be handled in the subprogram and various actions

taken in the handler, but the error must be reraised in order for it to be

unhandled on exit from PL/SQL to get the desired deinstantiations.)





2) Attempt to recompile the package by using the ALTER PACKAGE command.



For example:



SQL> ALTER PACKAGE DBMS_SQL COMPILE;



Package altered.



SQL>



3) Verify that proper execute permissions have been provided. In PL/SQL

stored program units, roles are disabled prior to the release of Oracle

8i. Oracle 8i definers rights follow the previous release model of

requiring explicit permission to the object.



In Oracle 8i, if invoker's rights are set on the routine, then execution

is done with roles enabled, so permission could be granted explicitly to

the schema executing or through a role. For additional information,

refer to Note:162489.1 entitled "Invokers Rights Procedure Executed by Definers Rights Procedures".









For example:



SQL> SELECT owner, table_name, privilege FROM dba_tab_privs WHERE

2 table_name = 'DBMS_SQL' AND ( grantee = 'SCOTT' OR grantee = 'PUBLIC' );



OWNER TABLE_NAME

------------------------------ ------------------------------

PRIVILEGE

----------------------------------------

SYS DBMS_SQL

EXECUTE





SQL>



4) If errors exist (check DBA_ERRORS or USER_ERRORS views with above

query), then take the appropriate action to correct the errors.



If the package is a system package that comes with the Oracle server,

the scripts are located in $ORACLE_HOME/rdbms/admin. Most packages have

their own .sql and .plb script to build the specification and body (see

below for names).



System packages that come with the Oracle server, as well as other Oracle

products, typically need to be owned by a particular schema. In the case

of the Oracle server DBMS packages, these need to be owned by SYS. If these

packages are not owned by SYS, some packages start getting 'ORA-6509 PL/SQL

ICD vector missing for this package' errors.



5) If duplicate SYS owned objects exist, clean them up. Refer to

Note:1030426.6, entitled "HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY

SYS AND SYSTEM SCHEMA", for information on this.





$ORACLE_HOME/rdbms/admin Packages

---------------------------------

System Package Name Package Spc Package Bdy Owner

------------------- ----------- ----------- -----

DBMS_ALERT dbmsalrt.sql prvtalrt.plb SYS

DBMS_APPLICATION_INFO dbmsapin.sql prvtapin.plb SYS

DBMS_AQ dbmsaq.plb prvtaq.plb SYS

DBMS_AQADM dbmsaqad.sql prvtaqad.plb SYS

DBMS_CRYPTO_TOOLKIT dbmsoctk.sql prvtoctk.plb SYS

DBMS_DDL dbmsutil.sql prvtutil.plb SYS

DBMS_DEBUG dbmspb.sql prvtpb.plb SYS

DBMS_DEFER dbmsdefr.sql prvtdefr.plb SYS

DBMS_DEFER_QUERY dbmsdefr.sql prvtdefr.plb SYS

DBMS_DEFER_SYS dbmsdefr.sql prvtdefr.plb SYS

DBMS_DESCRIBE dbmsdesc.sql prvtdesc.plb SYS

DBMS_DISTRIBUTED_TRUST_ADMIN

dbmstrst.sql prvttrst.plb SYS

DBMS_HS dbmshs.sql prvths.plb SYS

DBMS_IOT dbmsiotc.sql prvtiotc.plb SYS

DBMS_JOB dbmsjob.sql prvtjob.plb SYS

DBMS_LOB dbmslob.sql prvtlob.plb SYS

DBMS_LOCK dbmslock.sql prvtlock.plb SYS

DBMS_LOGMNR dbmslm.sql prvtlm.plb SYS

DBMS_LOGMNR_D dbmslmd.sql dbmslmd.sql SYS

DBMS_OFFLINE_OG dbmsofln.sql prvtofln.plb SYS

DBMS_OFFLINE_SNAPSHOT dbmsofsn.sql prvtofsn.plb SYS

DBMS_ORACLE_TRACE_AGENT

dbmsotrc.sql prvtotrc.plb SYS

DBMS_ORACLE_TRACE_USER

dbmsotrc.sql prvtotrc.plb SYS

DBMS_OUTPUT dbmsotpt.sql prvtotpt.plb SYS

DBMS_PCLXUTIL dbmsutil.sql prvtutil.plb SYS

DBMS_PIPE dbmspipe.sql prvtpipe.sql SYS

DBMS_RANDOM dbmsrand.sql dbmsrand.sql SYS

DBMS_RECTIFIER_DIFF dbmsrctf.sql prvtrctf.plb SYS

DBMS_REFRESH dbmssnap.sql prvtsnap.plb SYS

DBMS_REPAIR dbmsrpr.sql prvtrpr.plb SYS

DBMS_REPCAT dbmshrep.sql prvtbrep.plb SYS

DBMS_REPCAT_ADMIN prvthdmn.plb prvtbdmn.plb SYS

DBMS_REPCAT_INSTANTIATE

dbmsrint.sql prvtbrnt.plb SYS

DBMS_REPCAT_RGT dbmsrgt.sql prvtbrgt.plb SYS

DBMS_REPUTIL dbms_gen.sql prvtgen.plb SYS

DBMS_RESOURCE_MANAGER dbmsrmad.sql prvtrmad.plb SYS

DBMS_RESOURCE_MANAGER_PRIVS

dbmsrmpr.sql prvtrmpr.plb SYS

DBMS_RLS dbmsrlsa.sql prvtrlsa.plb SYS

DBMS_ROWID dbmsutil.sql prvtutil.plb SYS

DBMS_SESSION dbmsutil.sql prvtutil.plb SYS

DBMS_SHARED_POOL dbmspool.sql prvtpool.plb SYS

DBMS_SNAPSHOT dbmssnap.sql prvtsnap.plb SYS

DBMS_SPACE dbmsutil.sql prvtutil.plb SYS

DBMS_SPACE_ADMIN dbmsspc.sql prvtspad.plb SYS

DBMS_SQL dbmssql.sql prvtsql.plb SYS

DBMS_STATS dbmsstat.sql prvtstat.plb SYS

DBMS_TRACE dbmspbt.sql prvtpbt.plb SYS

DBMS_TRANSACTION dbmsutil.sql prvtutil.plb SYS

DBMS_UTILITY dbmsutil.sql prvtutil.plb SYS

OUTLN_PKG dbmsol.sql prvtol.plb SYS

UTL_COLL utlcoll.sql prvtcoll.plb SYS

UTL_FILE utlfile.sql prvtfile.plb SYS

UTL_HTTP utlhttp.sql prvthttp.plb SYS

UTL_RAW utlraw.sql prvtrawb.plb SYS

UTL_REF utlref.sql prvtref.plb SYS





References

----------



"Oracle7 Server Application Developer's Guide", (A32536-1)



"Oracle8 Server Application Developer's Guide", (A54642-01)



"Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5",

(A68003-01)





Related Articles

----------------



Note:19857.1 OERR: ORA 4068 "existing state of packages%s%s%s has been

discarded"



Note:19854.1 OERR: ORA 4065 "not executed, altered or dropped %s"



Note:19850.1 OERR: ORA 4061 "existing state of %s has been invalidated"



Note:20065.1 OERR: ORA 6508 "PL/SQL: could not find program unit being

called"



Note:1012129.102 ORA-4068,4067,6508 When Executing a Procedure Using PL/SQL



Note:117118.1 Errors Running or Compiling DBMS_RANDOM or DBMS_CRYPTO_TOOLKIT



Note:1030426.6 HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM

SCHEMA



Note:1012129.102 ORA-4068,4067,6508 WHEN EXECUTING A PROCEDURE USING PLSQL

OERR: ORA 603 "ORACLE server session terminated by fatal error"

Error: ORA 603
Text: ORACLE server session terminated by fatal error
-------------------------------------------------------------------------------
Cause: An ORACLE server session is in an unrecoverable state.
Action: Login to ORACLE again so a new server session will be created

*** Important: The notes below are for experienced users - See Note:22080.1

Explanation:
A severe error occurred so this session died.
This is a fairly generic error that can be raised in many places
so the error itself is of little use.

Diagnosis:
See the server side trace file and alert log. There is usually another
error on the error stack to show why the session died.
Eg: ORA 1092 - Instance terminated.
The stack trace should show what we were doing when we died but this is
often a side effect of some other session dying .

How to Analyze Problems Related to Internal Errors (ORA-600) and CoreDumps (ORA-7445) using My Oracle Support

Check the following link

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=TROUBLESHOOTING&id=260459.1

R12 / POXPOPDOI - 'ORA-01422' Error While Running 'Import StandardPurchase Orders'

Symptoms
On Release 12.0, the “Import Standard Purchase Orders" concurrent program fails with the following error:


ERROR
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure init_sys_parameters.0
ORA-01422: exact fetch returns more than requested number of rows in Package
po.plsql.PO_PDOI_PVT. Procedure init_startup_values.10
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure start_process.50
User-Defined Exception in Package po.plsql.PO_PDOI_Concurrent. Procedure POXPDOI.30

Steps To Reproduce:
1. Populate the interface table with the PO details.
2. Navigate to Requests -> Run -> Single request.
3. Select 'Import Standard Purchase Orders'.



Solution
To implement the solution, please execute the following steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:

Update fnd_concurrent_programs
set multi_org_category = 'S'
where concurrent_program_name='POXPOPDOI';

3. Commit the transaction using 'commit' command.

4. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:
Select multi_org_category from fnd_concurrent_programs where concurrent_program_name='POXPOPDOI';
-- should return a value 'S'.

5. Confirm that the data is corrected, run the "Import Standard Purchase Orders" concurrent program.

6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.


For More refer here

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=457628.1

R12: Error 95353 When Running Create Accounting Program

Symptoms
When running Create Accounting using SLA with the profile option FA: Use Workflow Account Generation set to Yes at the Site level, the following error occurs:

ERROR
Error Number Error Message
95353 Subledger Accounting was unable to derive an accounting code combination using the account derivation rule Assets Book Default Account for Offset owned by Oracle. Please review the account derivation rule and make sure it derives a valid accounting flexfield combination for the source values passed for the transaction.

FND logging of the accounting program reveals: 'ERROR: XLA_AP_CCID_NULL'




Solution
1. Alter the XXX Generate Default Account process including sub processes XXX: Generate Book Level Account and XXX: Generate Category Level Account, exchanging all NLS Accounting flexfield names used in it with the English name used in the XXX Accounting Flexfield.

2. Save a copy of the altered process on the desktop (FA_ACCOUNTS.wft) and save it also in the database. In the Account Generation Processes window, choose the XXX Generate Default Account process for the FA Account Generator and save it.

3. Run Generate Accounts.

4. Verify that the default AAD (Application Accounting Definition) is still valid.

5. Run Create Accounting.

6. To apply the solution on other instances, it will only be necessary to load FA_ACCOUNTS.wft into the database and perform steps 2 to 5.


for more information refer here
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=PROBLEM&id=553091.1

Output post processor Related issues

To implement the solution, please execute the following steps
=============================================================

1. Shutdown all the services.

2. Log in as applmgr
cd to $FND_TOP/patch/115/sql
Run the script: afdcm037.sql

3. Relink FNDSM and FNDLIBR executables as mentioned below:

$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"

4. Run cmclean.sql

5. Start up the Services and retest.


some services= = output post processor
======================================

C_AQCT_SVC - C AQCART Service
Debug_Service - Debug Service
FNDCPOPP - Output Post Processor
WFALSNRSVC - Workflow Agent Listener Service
WFMLRSVC - Workflow Mailer Service
WFWSSVC - Workflow Document Web Services Service
XDP_APPL_SVC - SFM Application Monitoring Service
XDP_CTRL_SVC - SFM Controller Service
XDP_Q_EVENT_SVC - SFM Event Manager Queue Service
XDP_Q_FA_SVC - SFM Fulfillment Actions Queue Service
XDP_Q_FE_READY_SVC - SFM Fulfillment Element Ready Queue Service
XDP_Q_IN_MSG_SVC - SFM Inbound Messages Queue Service
XDP_Q_ORDER_SVC - SFM Order Queue Service
XDP_Q_TIMER_SVC - SFM Timer Queue Service
XDP_Q_WI_SVC - SFM Work Item Queue Service
XDP_SMIT_SVC - SFM SM Interface Test Service
The following symptoms have been identified in regards to this issue.

Friday, April 23, 2010

Copy Function fails with FNDFS Error after a fresh installation

After a fresh installation of Oracle E-Business Suite Release 12 , the Tools -> Copy Functionality fails with the following error message

An error occurred while attempting to establish an Applications File Server connection with the node FNDFS_. There may be a network configuration problem, or the TNS listener on node FNDFS_ may not be running. Please contact your system administrator.

The Following aspects have been verified

a) All the entries in tnsnames.ora is correct
b) Tnsping to all the entries in tnsnames.ora return successful results

Solution
Ensure that the following profile options have the Desired value

a) "RRA: Enabled"/FS_ENABLED should be set to "Yes"

b) "RRA: Service Prefix"/FS_SVC_PREFIX should be null-ed out , no spaces allowed

After making the changes, Log-out and Login back and retest the issue.

Command to do active duplicate for Oracle Database

1. First login to target server 2. Validate tns connectivity between Source DB and Target DB 3. Prepare and validate space availability 4. S...