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.
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:
Post a Comment