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.



No comments:

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error. SQL> alter tablespace DATA  add datafile '/u01/data/data15.dbf...