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 = .logcompile_&v_input_un..sql
define v_compile_log_file = .logcompile_&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