rem *********************************************************
rem * file:    comp_all.sql
rem * purpose: compile all database stored objects
rem * to use:  log in using the appropriate account then
rem *          execute this script using the following syntax:
rem *
rem *            SQL> @comp_all
rem *
rem * NOTE:    You should not have to run this script more
rem *          than once since it uses
rem *          order_object_by_dependency table to compile
rem *          objects in the proper order.  Any
rem *          compilation errors generated should be
rem *          investigated.
rem *********************************************************

set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
set feedback off

spool comp_all.sql

select
    decode( OBJECT_TYPE, 'PACKAGE BODY',
    'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
    'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
    dba_objects a,
    (select max(level) dlevel, object_id from public_dependency 
connect by object_id = prior referenced_object_id 
group by object_id) b
where
    A.OBJECT_ID = B.OBJECT_ID(+) and
    STATUS = 'INVALID' and
    OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
                      'TRIGGER', 'VIEW' )
order by
    DLEVEL DESC,
    OBJECT_TYPE,
    OBJECT_NAME;

spool off