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 .
Detect:
select owner, object_name, object_type, status from all_objects where owner = 'OQ' and status <> 'VALID';ORSELECT 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.
XXXXXXX(Add more links as needed for other relevant Be Communities content.)XXXXXXX