rem ***************************************************************************
rem **
rem ** Compile.sql file 
rem **
rem ** Gera script para compilacao de objetos invalidos
rem **
rem ** Recebe os seguintes parametros
rem ** &1 - ORACLE_SID
rem ** &2 - origem dos arquivos de scripts
rem **
rem ***************************************************************************
set heading   off
set pagesize  0
set linesize  79
set verify    off
set feedback  off
set echo      off

spool &2\Compile_&1..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,
    sys.order_object_by_dependency 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
set feedback on
@&2\Compile_&1..sql
exit