Script to compile Invalid objects - Tracking dependencies

begin
  FOR r IN (SELECT 'ALTER ' || decode(a.object_type,   'PACKAGE BODY',   'PACKAGE',   'TYPE BODY',   'TYPE', a.object_type) || ' ' || a.object_name || decode(a.object_type,   'JAVA CLASS',   ' RESOLVE',   ' COMPILE') || decode(a.object_type,   'PACKAGE BODY',   ' BODY',   'TYPE BODY',   'BODY') text
          FROM user_objects a,
              (SELECT MAX(LEVEL) dlevel,
               object_id
             FROM public_dependency START WITH object_id IN
              (SELECT object_id
               FROM user_objects
               WHERE status = 'INVALID'
                  and    object_type <> 'SYNONYM' )
            CONNECT BY object_id = PRIOR referenced_object_id
             GROUP BY object_id)b
          WHERE a.object_id = b.object_id(+)
           AND a.status = 'INVALID'
           and    object_type <> 'SYNONYM'
           ORDER BY b.dlevel DESC,a.object_name ASC )
  loop
  begin
    execute immediate r.text;
    exception
      when others then
        dbms_output.put_line (' Exception '||sqlcode||' - '||sqlerrm ||' - ' ||r.text);
  end;   
  end loop;
end;


rajesh@10GR2> begin
  2    FOR r IN (SELECT 'ALTER ' || decode(a.object_type,   'PACKAGE BODY',   'PACKAGE',   'TYPE BODY',   'TYPE', a.object_type) || ' ' || a.object_name || decode(a.object_type,   'JAVA CLASS',   ' RESOLVE',   ' COMPILE') || decode(a.object_type,   'PACKAGE BODY',   ' BODY',   'TYPE BODY',   'BODY') text
  3            FROM user_objects a,
  4                (SELECT MAX(LEVEL) dlevel,
  5                 object_id
  6               FROM public_dependency START WITH object_id IN
  7                (SELECT object_id
  8                 FROM user_objects
  9                 WHERE status = 'INVALID'
 10                    and    object_type <> 'SYNONYM' )
 11              CONNECT BY object_id = PRIOR referenced_object_id
 12               GROUP BY object_id)b
 13            WHERE a.object_id = b.object_id(+)
 14             AND a.status = 'INVALID'
 15             and    object_type <> 'SYNONYM'
 16             ORDER BY b.dlevel DESC,a.object_name ASC )
 17    loop
 18    begin
 19      execute immediate r.text;
 20      exception
 21        when others then
 22          dbms_output.put_line (' Exception '||sqlcode||' - '||sqlerrm ||' - ' ||r.text);
 23    end;
 24    end loop;
 25  end;
 26  /
 Exception -24344 - ORA-24344: success with compilation error - ALTER FUNCTION EMP_FUNC COMPILE
 Exception -24344 - ORA-24344: success with compilation error - ALTER FUNCTION SHOW_DATA COMPILE

PL/SQL procedure successfully completed.