set serveroutput on format wrap
DECLARE
cmd VARCHAR2(256);
obj varchar2(30);
compile_error exception;
pragma exception_init(compile_error,-24344);
procedure display_error is
begin
dbms_output.put_line ('****************************************');
dbms_output.put_line ('Error(s) during "'||cmd||'":');
for r in (select 'Line: '||line||', Column: '||position||'...'||text err
from user_errors
where name = obj) loop
dbms_output.put_line(r.err);
end loop;
dbms_output.put_line ('****************************************');
end;
BEGIN
FOR rec in (select 'Alter '
||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
||' '||object_name||' compile' cmd,
object_name obj
from user_objects where status = 'INVALID') LOOP
cmd := rec.cmd;
obj := rec.obj;
dbms_output.put_line(cmd);
begin
execute immediate cmd;
exception
when compile_error then
display_error;
end;
end loop;
END;
/
Alter TRIGGER MREPLICATE compile
****************************************
Error(s) during "Alter TRIGGER MREPLICATE compile":
Line: 2, Column: 17...PL/SQL: ORA-04052: error occurred when looking up remote object TESTNEW.T_REGION@YADA.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve service name
Line: 2, Column: 5...PL/SQL: SQL Statement ignored
****************************************
Alter PACKAGE VAC compile
****************************************
Error(s) during "Alter PACKAGE VAC compile":
Line: 8, Column: 36...PL/SQL: ORA-00942: table or view does not exist
Line: 8, Column: 22...PL/SQL: SQL Statement ignored
Line: 9, Column: 20...PLS-00364: loop index variable 'R' use is invalid
Line: 9, Column: 17...PL/SQL: Statement ignored
****************************************
Alter PACKAGE VACATION_LIABILITY compile
PL/SQL procedure successfully completed.
*************************************************************************************