Invalid Objects

---

Invalid objects, in Oracle Applications, are inevitable. Patching, cloning, upgrading, and sometimes just running applications cause invalid objects. Getting rid of most of them is fairly simple. $ORACLE_HOME/rdbms/admin/utlrp.sql will eliminate the majority of them. Those that are able to be validated can be gotten rid of by the following set of scripts. Create the scripts in Table 14.1, Table 14.2, and Table 14.3 where they can be easily run on any instance.

 set head off set pagesize 0 set linesize 132 set feedback off set termout off set echo off spool 2.sql select 'ALTER '||object_type||' '||object_name||' compile;'  STRING from user_objects where status='INVALID' and object_type in ('VIEW', 'PACKAGE', 'TRIGGER', 'PROCEDURE', 'FUNCTION') order by object_type desc / spool off set feedback on set termout on set echo on 

 set head off set pagesize 0 set linesize 132 set feedback off set termout off set echo off spool 4.sql select 'ALTER PACKAGE '||object_name||' compile body;' STRING from user_objects where status='INVALID' and object_type='PACKAGE BODY' / spool off set feedback on set termout on set echo on 

 select count(*) from all_objects where status = 'INVALID' 

Then from the SQL prompt, as in Table 14.4, you can call scripts 1.sql through 5.sql as follows, repeatedly until the output of 5.sql no longer produces a smaller number.

 SQL>@1 SQL>@2 SQL>@3 SQL>@4 SQL>@5 

---