set echo off pagesize 0 feedback off

define v_input_un       = &1

define v_input_pw       = &2

define v_input_conn_str = &3

connect &v_input_un/&v_input_pw@&v_input_conn_str

define v_compile_command_file = .\log\compile_&v_input_un..sql

define v_compile_log_file     = .\log\compile_&v_input_un..log

 

spool &v_compile_command_file

prompt spool &v_compile_log_file

prompt show user

prompt set echo on feedback on pagesize 999

select   'alter ' || o.object_type ||  ' "'

         || o.object_name || '" compile;'

from     user_objects o

where    o.object_type not like '% BODY'

and      o.object_type not in ('UNDEFINED', 'JAVA CLASS')

and      o.status = 'INVALID'

order by decode(o.object_type

              , 'VIEW',      1

              , 'TYPE',      2

              , 'FUNCTION',  3

              , 'PROCEDURE', 4

              , 'PACKAGE',   5

              , 999)

       , o.object_name

/

 

select   'alter ' ||

         substr(o.object_type, 1, instr(o.object_type, ' ') - 1)

         ||  ' "'

         || o.object_name || '" compile body;'

from     user_objects o

where    o.object_type like '% BODY'

and      o.object_type not in ('UNDEFINED', 'JAVA CLASS')

and      o.status = 'INVALID'

order by decode(o.object_type

              , 'VIEW',      1

              , 'TYPE',      2

              , 'FUNCTION',  3

              , 'PROCEDURE', 4

              , 'PACKAGE',   5

              , 999)

       , o.object_name

/

 

prompt

prompt select * from user_errors order by 1, 2, 3

prompt /

prompt spool off spool off

@&v_compile_command_file