compile invalid objects sorted parent-child wise (dependency wise)

compile invalid objects sorted parent-child wise (dependency wise)

set serveroutput on
declare
 v_invl_count number :=0;
 v_obj_type varchar2(32);
 v_obj_name varchar2(32);
 v_error_msg varchar2(1000);
begin
 select count(1)
   into v_invl_count
   from user_objects
  where object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALISED VIEW')
    and status = 'INVALID';

 if v_invl_count = 0 then
  dbms_output.put_line ('No invalid objects...');
 else
  for rec in (select distinct object_name,object_type
         from (select uo.status,uo.object_name,ud.name,uo.object_type,ud.type,ud.referenced_type,ud.referenced_name
                        from user_objects uo
            left join (select name,type,referenced_name,referenced_type
                                              from user_dependencies
                                             where referenced_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALISED VIEW')) ud
                                        on (uo.object_name = ud.name)
                                     where uo.object_type in ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALISED VIEW'))
                             where status = 'INVALID'
                        start with referenced_name is null
                                or referenced_name in ('STANDARD','DBMS_STANDARD')
          connect by nocycle prior object_name = referenced_name
                 order siblings by object_name)
  loop
   begin
    v_obj_type := rec.object_type;
    if rec.object_type = 'PACKAGE BODY' then
     dbms_output.put_line ('Query :-'||'alter package '||rec.object_name ||' compile body');
     execute immediate 'alter package '||rec.object_name ||' compile body';
    else
     dbms_output.put_line ('Query :-'||'alter '||rec.object_type||' '||rec.object_name ||' compile');
     execute immediate 'alter '||rec.object_type||' '||rec.object_name ||' compile';
    end if;
   exception
    when others then
     dbms_output.put_line ('Error1 :-'||sqlerrm);
   end;
  end loop;
 end if;
exception
 when others then
  dbms_output.put_line ('Error2 :-'||sqlerrm);
end;
/