set heading off
set feedback off
set echo off
set pages 0
DECLARE
 obj_number number := 0;
 cursor C1 is 
 SELECT 'ALTER ' ||DECODE (object_type,'PACKAGE BODY', 'PACKAGE',object_type)
      ||'  ' ||object_name ||' COMPILE' 
      ||DECODE (object_type,'PACKAGE BODY', ' BODY','') cmd
  FROM user_objects outer
 WHERE status = 'INVALID'
   AND object_type IN ('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','FUNCTION','TRIGGER')
   AND  ( object_type <>'PACKAGE BODY'
       OR NOT EXISTS ( SELECT NULL
                         FROM user_objects
                        WHERE object_name = outer.object_name
                          AND object_type = 'PACKAGE'
                          AND status = 'INVALID') )
 ORDER BY outer.object_name;
  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  loop_count number;
  my_err     number;
  validate   number;
  times    number := 1;
  ddl_ret    integer;
BEGIN
 loop_count := 0;
 -- To make sure we eventually stop, pick a max number of iterations
 loop
   SELECT count(*)
   into iterations
   FROM user_objects outer
   WHERE status = 'INVALID'
   AND object_type IN ('PACKAGE','PACKAGE BODY','VIEW','PROCEDURE','FUNCTION','TRIGGER')
   AND  ( object_type <>'PACKAGE BODY'
       OR NOT EXISTS ( SELECT NULL
                         FROM user_objects
                        WHERE object_name = outer.object_name
                          AND object_type = 'PACKAGE'
                          AND status = 'INVALID') )
   ORDER BY outer.object_name;
   
   if times >5 or iterations =0 then
    exit;
   end if;
   DDL_CURSOR := dbms_sql.open_cursor;
   OPEN C1;
   LOOP
     BEGIN
       FETCH C1 INTO ddl_statement;
       EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
     EXCEPTION when others then
       RAISE;
     END;
     begin
       dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
       ddl_ret := dbms_sql.execute(DDL_CURSOR);
     EXCEPTION WHEN OTHERS THEN
       null; -- ignore, and proceed.
     END;
     loop_count := loop_count + 1;
   END LOOP;
   dbms_sql.close_cursor(DDL_CURSOR);
   CLOSE C1;
   times := times + 1;
 end loop;
END;
/
exit

