set serveroutput on
declare
   l_status             varchar2(10) := 'INVALID';
   l_no_invalid_objects number       := 0;
   cursor objects is
      select
         object_type, count(*) cnt, count (case when status = 'VALID' then 1 end) valid_objects,
         count (case when status = 'INVALID' then 1 end) invalid_objects
      from user_objects
      group by object_type
      order by object_type;
   cursor invalid_objects is
      select
        object_name,
        status,
        object_type,
        'ALTER '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
        ||' '||object_name||' COMPILE '||decode(object_type,'PACKAGE BODY','BODY') exec_string
      from
        user_objects
      where
        status = l_status
        and object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','VIEW');
begin
   for io in invalid_objects loop
      begin
         execute immediate io.exec_string;
      exception
         when others then
            l_no_invalid_objects := 1;
      end;
   end loop;
   dbms_output.put_line(chr(10)||'Objects summary'||chr(10));
   for o in objects loop
      dbms_output.put_line(rpad(o.cnt,5,' ')||' '||rpad(o.object_type, 20 ,' ')||rpad(' Valid: '||o.valid_objects,16,' ')||'  Invalid: '|
|o.invalid_objects);
   end loop;
   if (l_no_invalid_objects = 0) then
      dbms_output.put_line(chr(10)||'ORACLE - All procedures, packages, functions, and views are VALID.');
   else
      dbms_output.put_line(chr(10)||'ORACLE - INVALID database objects were found.  Contact support, or');
      dbms_output.put_line('query the user_objects table for more information.');
   end if;
end;
/