PLS-00905: object APPS.APPS is invalid

Today development team has reported an issue that when they are trying to compile any package which
have apps.<> schema reference thrown an error:

SQL> alter package XX_XXXX_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY XX_XXXX_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1130/16  PL/SQL: Statement ignored
1130/16  PLS-00905: object APPS.APPS is invalid
1379/16  PL/SQL: Statement ignored
1379/16  PLS-00905: object APPS.APPS is invalid
1563/16  PL/SQL: Statement ignored
1563/16  PLS-00905: object APPS.APPS is invalid
1791/16  PL/SQL: Statement ignored
1791/16  PLS-00905: object APPS.APPS is invalid
SQL>


Cause: 
An invalid package specification or stored subprogram was referenced.
A package specification or stored subprogram is invalid if its source code or
any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled.

Action: 
Find out what invalidated the package specification or stored subprogram,
then make sure that Oracle can recompile it without errors.
Now we should make sure that we have created the package in the right place.

Steps-1.

 Package code I have compiled successfully in other instances like QA,
however got the error in dev instance. If we remove schema reference (apps) then it’s compiling from dev also
but as per coding standard it has good practice to use apps.<>.

Steps-2.

 Now we should make sure that we have created the package in the right place.

Connect with apps schema :-

[Dev@oracle ~]$ sqlplus apps/apps

SQL > select owner, object_name, object_type
  from dba_objects
 where object_name='XX_XXXX_PKG'
 order by 1,2,3;

Output- Perfect no issue in place.
owner            object_name      object_type
APPS            XX_XXXX_PKG       PACKAGE
APPS            XX_XXXX_PKG       PACKAGE BODY


Steps-3.

I have checked the compiler log again and found APPS.APPS that means some developer mistakenly crated the apps
name package on apps schema. That may be our case.

Now check :

SQL > select owner, object_name, object_type
  from dba_objects
 where OBJECT_NAME='APPS'
 order by 1,2,3;

found one object created with name apps.

Steps-4.

Now we should drop apps object

SQL > drop package apps;

Steps-5.

Try compiling the package in dev instance again.

I hope issue fix.

Inventory load failed

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:

[ora@oracle OPatch]$ opatch lsinventory

Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2019, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/db/tech_st/12.1.0.2
Central Inventory : /u01/app/db/oraInventory
   from           : /u01/app/db/tech_st/12.1.0.2/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /u01/app/db/tech_st/12.1.0.2/cfgtoollogs/opatch/opatchAM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73


Solution


[ora@oracle ~]$ cd $ORACLE_HOME/oui/bin
[ora@oracle bin]$ pwd
/u01/app/db/tech_st/12.1.0.2/oui/bin

[ora@oracle bin]$ vi attachHome.sh

[ora@oracle bin]$ ./attachHome.sh

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 8191 MB    Passed
The inventory pointer is located at /etc/oraInst.loc

'AttachHome' was successful.

Retest issue.

[ora@oracle OPatch]$ opatch lsinventory

Issue has been resolved.