	Product(s):	APM Implementation and Performance Management
	Version(s):	6+
	Environment:	N\A
	Area:	N/A
	Subarea:	N\A

Problem

The following query return records (invalid Stored Procedures)

select owner, object_name, object_type, status from all_objects where owner = 'OQ' and status <> 'VALID';

Examples:

OQ.ENBL_ASSET_FNL_POSN_CONSTR Site
OQ.HIERARCHY_PATH_BUILDER Asset
OQ.UPD_INSTLD_CMPNTS_FUNCPOSITION Asset • OQ.UPDATEINDICATORACCUMULATOR Indicator • OQ.BUILDINDICATORHIERARCHY Indicator

The above will be affected when deploying customizations on Asset.

Adding a column, altering or rebuilding an index, will cause stored procedure to become invalid if these changes are to dependencies of the stored procedures .

Solution

Detect:

select owner, object_name, object_type, status from all_objects where owner = 'OQ' and status <> 'VALID';

OR

SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID'; --it will give count of invalid objects.
SELECT object_type, count(*) from user_objects where status = 'INVALID' group by object_type;
SELECT owner, object_type, object_name FROM all_objects WHERE status = 'INVALID'

•DBA_OBJECTS : All objects in the database
•USER_OBJECTS : All objects owned by the user
•ALL_OBJECTS : All objects owned by the user and on which the user has been granted privileges

Fix:

Run proc_apponly_ivara.sql and proc_common.sql found in the Oracle directory on the application server. This will recompile all stored procedures.

Issue 079913 created.

Oracle - Invalid Stored Procedure need to be recompiled

Problem

Solution

See Also