REM
/*
 * CompileBadObjects.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */

Column cnt New_Value _cnt FORMAT A12
COLUMN txt       FORMAT A200

set echo off termout off 
Select LTRIM(TO_CHAR(count(*),'999,999,999')) cnt from all_objects where status='INVALID';
set termout on
Prompt
PROMPT Compile bad PL/SQL objects (there are currently &&_cnt invalid objects)
Prompt

set heading off PAGES 0 TRIMSPOOL ON feedback off echo off scan on verify off def on
ACCEPT object_owner PROMPT "Enter value for Object Owner: "
ACCEPT object_name  PROMPT "Enter value for Object Name: "
ACCEPT type         PROMPT "Enter value for Type: "

select
  'ALTER '||o.object_type||' '||o.owner||'.'||o.object_name||' COMPILE;' txt
from ALL_objects o
where o.owner like UPPER('&&object_owner')
--and (user='SYS' or o.owner<>'SYS')
and o.owner!='SYS'
and o.object_name like UPPER('&&object_name')
and o.object_type like UPPER('&&type')
and o.object_type NOT IN ('PACKAGE BODY','TYPE BODY','SYNONYM')
--and o.object_type <> 'VIEW'
and status = 'INVALID'
union all
select
  'ALTER PACKAGE '
  ||' '||o.owner||'.'||o.object_name||' COMPILE BODY;' txt
from all_objects o
where o.owner like UPPER('&&object_owner')
--and (user='SYS' or o.owner<>'SYS')
and o.owner!='SYS'
and o.object_name like UPPER('&&object_name')
and o.object_type='PACKAGE BODY'
and o.object_type like UPPER('&&type')
and status = 'INVALID'
and exists (
  select 0
  from all_objects 
  where owner = o.owner
  and object_name = o.object_name
  and object_type = 'PACKAGE'
  and status = 'VALID'
)
union all
select
  'ALTER TYPE '
  ||' '||o.owner||'.'||o.object_name||' COMPILE BODY;' txt
from all_objects o
where o.owner like UPPER('&&object_owner')
--and (user='SYS' or o.owner<>'SYS')
and o.owner!='SYS'
and o.object_name like UPPER('&&object_name')
and o.object_type ='TYPE BODY'
and o.object_type like UPPER('&&type')
and o.object_type <> 'VIEW'
and status = 'INVALID'
and exists (
  select 0
  from all_objects 
  where owner = o.owner
  and object_name = o.object_name
  and object_type = 'TYPE'
  and status = 'VALID'
)
UNION ALL
SELECT 'SELECT 1 FROM '||object_name||' WHERE 1=2;' txt
FROM all_objects o WHERE STATUS='INVALID'
AND object_type = 'SYNONYM'
AND o.object_type like UPPER('&&type')
AND owner LIKE UPPER('&&object_owner')

SPOOL CompileBadObjects.TMP
/

set termout off
set def off
undef _cnt
undef _cnt2
prompt set echo off 
prompt set termout off 
prompt undef _cnt
prompt Column cnt2 New_Value _cnt2 FORMAT A12
prompt Select LTRIM(TO_CHAR(count(*),'999,999,999')) cnt2 from all_objects where status='INVALID'
prompt /
prompt set termout on
prompt Prompt
prompt PROMPT There are now &&_cnt2 invalid objects
prompt undef _cnt2
set def on
prompt Prompt
set termout on

spool off
set feedback on heading On PAGES 24

undef type
undef object_owner
undef object_name
undef _cnt
undef 1
undef 2
undef 3
Clear Col

Prompt
Prompt You now have a file called CompileBadObjects.TMP to run...
Prompt

SET DOC off
/* SUMMARY - Generate a file of commands to try and fix invalid objects by approporiate recompilation.
 *
 * DETAIL - generate the commands that are most likely to correctly re-compile objects that are maked as invalid.
 *          
 *          Note that the commands are not generated in dependency order and so there may be a need to run the script multiple times
 *          to get everything that will compile cleanly to compile. Also, note that compilation of certain objects can cause other, dependent
 *          objects to go invalid as a result (e.g. if you compile a package specification then any package bodies that reference it (or triggers
 *          or views..) will go invalid as a result.
 *
 *          A count of the number of invalid objects before and after the commands are run is given so that it is clear as to what the effect
 *          has been.
 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM