set pages 0
set echo off
spool compile.tmp
prompt set echo on
select 'alter '||
       decode(object_type, 'PACKAGE BODY','PACKAGE',object_type) ||
       ' '||object_name||
       ' compile '||decode(object_type,'PACKAGE','PACKAGE',
                                       'PACKAGE BODY','body',null)
       ||chr(10)||'/'||chr(10)||'show errors'
from user_objects
where object_type in ('PACKAGE','PACKAGE BODY','VIEW',
                      'FUNCTION','TRIGGER','PROCEDURE')
and status = 'INVALID'
order by decode(object_type,'VIEW',1,
                            'FUNCTION',2,
                            'PROCEDURE',3,
                            'PACKAGE',4,
                            'PACKAGE BODY',5,
                            'TRIGGER',6,7)
/
prompt set echo off
spool off
@compile.tmp
spool compile.tmp
prompt set echo on
select 'alter '||
       decode(object_type, 'PACKAGE BODY','PACKAGE',object_type) ||
       ' '||object_name||
       ' compile '||decode(object_type,'PACKAGE','PACKAGE',
                                       'PACKAGE BODY','body',null)
       ||chr(10)||'/'||chr(10)||'show errors'
from user_objects
where object_type in ('PACKAGE','PACKAGE BODY','VIEW',
                      'FUNCTION','TRIGGER','PROCEDURE')
and status = 'INVALID'
order by decode(object_type,'VIEW',1,
                            'FUNCTION',2,
                            'PROCEDURE',3,
                            'PACKAGE',4,
                            'PACKAGE BODY',5,
                            'TRIGGER',6,7)
/
prompt set echo off
spool off
@compile.tmp
spool compile.tmp
prompt set echo on
select 'alter '||
       decode(object_type, 'PACKAGE BODY','PACKAGE',object_type) ||
       ' '||object_name||
       ' compile '||decode(object_type,'PACKAGE','PACKAGE',
                                       'PACKAGE BODY','body',null)
       ||chr(10)||'/'||chr(10)||'show errors'
from user_objects
where object_type in ('PACKAGE','PACKAGE BODY','VIEW',
                      'FUNCTION','TRIGGER','PROCEDURE')
and status = 'INVALID'
order by decode(object_type,'VIEW',1,
                            'FUNCTION',2,
                            'PROCEDURE',3,
                            'PACKAGE',4,
                            'PACKAGE BODY',5,
                            'TRIGGER',6,7)
/
prompt set echo off
spool off
@compile.tmp
spool compile.tmp
prompt set echo on
select 'alter '||
       decode(object_type, 'PACKAGE BODY','PACKAGE',object_type) ||
       ' '||object_name||
       ' compile '||decode(object_type,'PACKAGE','PACKAGE',
                                       'PACKAGE BODY','body',null)
       ||chr(10)||'/'||chr(10)||'show errors'
from user_objects
where object_type in ('PACKAGE','PACKAGE BODY','VIEW',
                      'FUNCTION','TRIGGER','PROCEDURE')
and status = 'INVALID'
order by decode(object_type,'VIEW',1,
                            'FUNCTION',2,
                            'PROCEDURE',3,
                            'PACKAGE',4,
                            'PACKAGE BODY',5,
                            'TRIGGER',6,7)
/
prompt set echo off
spool off
@compile.tmp
rem
set pagesize 50000
ttitle 'Invalid Objects'
column c format a70 heading 'Object_name(Object_type)'
select object_name||'('||object_type||')' c 
from user_objects where status='INVALID'
order by object_type,object_name;