Checked for relevance by ATG-ICM Subject Matter Expert (SME): 10-May-2007
- goal: How to resolve invalid objects in a database
- fact: Oracle Application Object Library
- fact: ADCOMPSC.pls
fix:
1. Determine what objects are invalid in a database.
Connect as system manager:
sqlplus system/manager
Run following select:
SQL> SELECT owner,object_type,COUNT(*) FROM DBA_OBJECTS
WHERE status='INVALID'
GROUP BY owner,object_type;
Output should be similar to below:
OWNER OBJECT_TYPE COUNT(*)
------------------------------------------------------------------
APPDEMO PACKAGE BODY 1
APPS_APPDEMO PACKAGE BODY 34
APPS_APPDEMO PROCEDURE 1
APPS_APPDEMO TRIGGER 5
APPS_APPDEMO VIEW 142
MFG_BRW VIEW 2
NOETIX_SYS FUNCTION 1
NOETIX_SYS VIEW 9
OO_DS TRIGGER 9
OO_SCHED TRIGGER 1
OWA PACKAGE 3
Note: Use USER_OBJECTS or DBA_OBJECTS in your query and not ALL_OBJECTS.
i.e USER_OBJECTS - returns objects owned by the user (schema) you
are connected as.
DBA_OBJECTS - returns every object in the Database.
2. Compile current invalid objects.
Use a script. called ADCOMPSC.pls, within Applications, to compile INVALID
objects.
The order in which to compile Invalid Objects in schemas is:-
SYS, SYSTEM, APPS, others.
(APPS_DDL and APPS_ARRAY_DDL should exist in all schemas)
Login to a UNIX session as the applmgr user (owner of the applications files)
$cd $AD_TOP/sql
sqlplus x/y @adcompsc.pls
%
If an ORA-1555 error is encountered while running adcompsc.pls, just restart
the
script.
* Use aderrchk.sql to record the remaining INVALID objects (same syntax as
adcompsc.pls). You may like to send the aderrchk.sql to a file using the spool
command in sqlplus.
e.g. sqlplus x/y @aderrchk.sql %
3. Review the errors after running ADCOMPSC.pls.
To fix INVALID objects, a sequence of steps is required.
The REAL PROBLEM IS TO DETERMINE WHAT WENT WRONG !!! (Invalid objects symptom)
A sample of what aderrchk.sql output may look like :
OBJECT LINE ERROR
----------------------------------------------------------------------------
PACKAGE BODY ARP_AUTO_BANK_CLEAR 300 PLS-00306: wrong number or types of
arguments in call to 'REVERSE'
Generally it is a PACKAGE BODY that is INVALID, where the PACKAGE (spec)
only becomes invalid if there is a spelling/syntax mistake.
The PACKAGE BODY contains the actual PL/SQL code that runs.
4) Esatblish who owns the invalid object.
If the object is in the Wrong Schema, that could be the reason for it being
INVALID.
Remember the different Applications Database Architectures.
i.e. Release 10.7 and 11.X
Fixing INVALID objects falls into 1 of 2 methods, fixing for Packages and
fixing for Views.
FOR VERSIONS - goto step XIII.
============
FOR PACKAGES - do all the following steps in our systems as well to compare
============ Versions.
I) OBTAINING THE VERSION OF THE PACKAGE & PACKAGE BODIES.
Connect as apps apps:
sqlplus apps/apps
Run following select:
SQL> SELECT text
FROM user_source
WHERE name='ARP_AUTO_BANK_CLEAR' ( where name='&1')
AND line = 2; ( line between 1 and 10)
Should get output similar to below:
TEXT
--------------------------------------------------------------------------
/* $Header: ceabrcrs.pls 10.35 96/12/23 13:36:15 kpigg ship $ */
/* $Header: ceabrcrb.pls 10.62 97/01/13 15:21:05 bcarrol ship $ */
If this does not work, verify.
the package exists in the database in
DBA_OBJECTS, or line 2 may not be the 'Header' line.
II) CHECK WHAT IS THE HIGHEST VERSION UNDER THE APPL_TOP.
ceabrcrs.pls & ceabrcrb.pls have 'ce' so they belong under $CE_TOP
This is not alway conclusive.
III) WHERE IS THE MOST RECENT VERSION OF THE SOURCE FILES.
$CE_TOP/admin/sql (10.7 and above) (Base Install Version)
> 'patch' - directory for REL 11 & above > patchsc - for REL 10.7
$CE_TOP/patch/110/sql (11.x)
$ cd $CE_TOP
$ find . -name ceabrcr* -print
./admin/sql/ceabrcrb.pls
./admin/sql/ceabrcrs.pls
./patch/110/sql/ceabrcrs.pls
./patch/110/sql/ceabrcrb.pls
./patch/110/sql/ceabrcrb.plsO
./patch/110/sql/ceabrcrs.plsO
** When you do not have the source file name, some things to try **
a) Look in our system in 'USER_SOURCE'
b) Look in GSX/Techrep to see if you can get the Source file name.
c) OR, Choose which product you think it belongs to :
$ cd $AR_TOP/admin/sql
$ grep -i ARP_AUTO_BANK_CLEAR * | grep -i package
$ grep -i ARP_AUTO_BANK_CLEAR * | grep -i create
$ grep -i ARP_AUTO_BANK_CLEAR * | more
$ grep -i package * | grep -i create (all created package objects)
In this example remember ARP_AUTO_BANK_CLEAR seems to point to AR,
but it actually belongs to CE, so try some different combinations.
IV) WHAT IS THE CURRENT VERSION, OR WHAT VERSION ARE THERE.
$ grep Header ./admin/sql/ceabrcr*
|
./admin/sql/ceabrcrb.pls:
/* $Header: ceabrcrb.pls 10.59 96/11/05 19:26:48 porting ship $ */
./admin/sql/ceabrcrs.pls:
/* $Header: ceabrcrs.pls 10.34 96/11/05 19:26:49 porting ship $ */
Package (specification) ceabrcrs.pls ('s.pls' verifies that this script. creates the Package Spec) 10.34
Package Body (body) ceabrcrb.pls ('b.pls' verifies that this script. creates the Package Body) 10.59
V) CHECK ALL OTHER VERSIONS.
$ grep Header ./patch/110/sql/ceabrcr*
This will show you all versions of the file. From our systems, try to
determine which Version the customer has loaded into their Database.
This can tell us two things:
a) They are running the latest version which means that reloading
ARP_AUTO_BANK_CLEAR may not solve the problem.
(good to try to reload ARP_AUTO_BANK_CLEAR)
b) What occured on the customers system to Invalidate the Object.
- They may be running from the 'admin/sql' level which is incorrect.
They should be using the version from the patch/110/sql. This
occurs when the customer has re-run autoinstall and not re-applied
all patches.
- Are they running on a higher version than they should be on ?
VI) TRY RELOADING THE MOST RECENT VERSION OF THE ARP_AUTO_BANK_CLEAR PACKAGE.
(you need to know if this is correct).
$ cd $CE_TOP/patch/110/sql
$ sqlplus apps/apps @ceabrcrs.pls (sometimes you need to check for
parameters)
package created
$ sqlplus apps/apps @ceabrcrb.pls
Package Body created
If running the above scripts results in a Warning : package body created with
errors then it's Invalid.
Try to compile the package manually :
Connect as apps apps:
sqlplus apps/apps
Compile both the Spec & body:
SQL> ALTER PACKAGE ARP_AUTO_BANK_CLEAR COMPILE;
Compile only the body:
SQL> ALTER PACKAGE ARP_AUTO_BANK_CLEAR COMPILE BODY;
Compiling Package Spec will only report error, 'Warning...'
Check for errors:
SQL> SHOW ERROR PACKAG BODY ARP_AUTO_BANK_CLEAR
Should get output similar to below:
OBJECT LINE ERROR
------------------------------------------------------------------------
PACKAGE BODY ARP_AUTO_BANK_CLEAR PLS-00306: wrong number or types of
arguments in .
call to 'REVERSE'
From the above, we see the same error existing.
VII) WHAT DOES THE ERROR REFER TO - 'REVERSE'.
This does not look as a Typical name for an Application object.
So what we do is search in the ARP_AUTO_BANK_CLEAR package body source file
for references to 'REVERSE' to see what we are really after.
$ grep -i REVERSE ceabrcrb.pls
ARP_CASHBOOK.reverse( p_cr_id => cash_receipt_id,
So 'REVERSE' refers to the object ARP_CASHBOOK
VIII) DETERMINE WHAT TYPE OF OBJECT ARP_CASHBOOK IS.
DBA_OBJECTS
Connect as system manager:
sqlplus system/manager
Run following select:
SQL> SELECT owner,object_type,status
FROM DBA_OBJECTS
WHERE object_name='ARP_CASHBOOK'; (object_name='&1';)
Should get output similar to below:
OWNER OBJECT_TYPE STATUS
----------------------------------------------------------------
APPS_APPDEMO PACKAGE INVALID
APPS_APPDEMO PACKAGE BODY INVALID
IX) CHECK WHAT VERSION OF ARP_CASHBOOK IS LOADED INTO THE DATABASE.
Connect as apps apps:
sqlplus apps/apps
Run following select:
SQL> SELECT text
FROM user_source
WHERE name='ARP_CASHBOOK'( where name='&1')
AND line = 2; ( line between 1 and 10)
Should get output similar to below:
TEXT
----------------------------------------------------------------------
/*$Header: ARRECBKS.pls 70.15 96/12/25 21:18:39 osteinme ship $*/
/*$Header: ARRECBKB.pls 70.25 96/12/25 21:18:02 osteinme ship $*/
X) CHECK WHAT ARRECBK* FILES EXIST ON THE SYSTEM AND THEIR VERSIONS.
$ cd $AR_TOP
$ find . -name ARRECBK* -print
./admin/sql/ARRECBKB.pls
./admin/sql/ARRECBKS.pls
./patch/110/sql/ARRECBKS.pls
./patch/110/sql/ARRECBKB.pls
./patch/110/sql/ARRECBKB.plsO
./patch/110/sql/ARRECBKS.plsO
$ grep Header ./patch/110/sql/ARRECBK*
$ grep Header ./admin/sql/ARRECBK*
./patch/110/sql/ARRECBKB.pls:
/*$Header: ARRECBKB.pls 70.30 97/05/27 14:24:52 vsmith ship $*/
./patch/110/sql/ARRECBKB.plsO:
/*$Header: ARRECBKB.pls 70.25 96/12/25 21:18:02 osteinme ship $*/
./patch/110/sql/ARRECBKS.pls:
/*$Header: ARRECBKS.pls 70.17 97/03/27 12:29:29 vsmith ship $*/
./patch/110/sql/ARRECBKS.plsO:
/*$Header: ARRECBKS.pls 70.15 96/12/25 21:18:39 osteinme ship $*/
So the Database has the Lower Versions from the O files.
From this, it may appear the customer has not applied a patch...
XI) LOAD THE NEWEST VERSION
Check in our system 1st, or look at the file history.
$ cd $AR_TOP/patch/110/sql
$ sqlplus apps/apps @ARRECBKS.pls
$ sqlplus apps/apps @ARRECBKB.pls
XII) TEST TO SEE IF ARP_AUTO_BANK_CLEAR WILL COMPILE.
Connect as apps apps:
sqlplus apps/apps
Run following select:
SQL> ALTER PACKAGE ARP_AUTO_BANK_CLEAR COMPILE BODY;
Package body altered:
SQL> SHOW ERROR PACKAGE BODY ARP_AUTO_BANK_CLEAR
no rows.
Fixed.
XIII) INVALID VIEWS
Depending on the Errors
- Who owns the view ? DBA_OBJECTS
- Is the View in the correct schema, Applications Architecture.
- Get the View TEXT.
Connect as apps apps
sqlplus apps/apps
Run following select
SQL> SELECT text
FROM user_views
WHERE view_name='&1';
This may only show 80 characters so:
SQL> SET LONG 30000
Re-run query
SQL> /
- Relate the View TEXT back to the error.
Hint : Usually the last column in the View is missing or Invalid.
Check the table in from clause, the last column in the table to see if it fits.
Invalid column: will need to check each column from each table.
Missing column: check the base table or is the View a wrong
version or release.
Table or view does not exist:
Need to check all the Objects referenced in the.
FROM or
WHERE clauses to see they are correct.
Need to check from the Schema the view is run from.
Can be a missing Object or missing Grant or Synonym.
Look in DBA_OBJECTS for the missing object, that will assist
you to determine if its a Grant/Synonym problem.
Other objects on the system are INVALID:
Sometimes tables have columns added but the View still has
the old definition. Look for the SQL script.
(Customer may not have applied a patch ?)..
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-751433/，如需轉載，請註明出處，否則將追究法律責任。
