set echo off
set feedback on
spool CDX_compfn.log
DECLARE
 
c  integer;
s  integer; 

cursor obj is
   select object_id from user_objects 
    where status='INVALID'
      and object_type != 'OBJECT BODY';

   ws_retc      INTEGER;

   TYPE t_tab IS TABLE OF user_objects.object_id%TYPE
        INDEX BY BINARY_INTEGER;

   ws_tab       t_tab;
   i            INTEGER := 0;
   ws_stmt      VARCHAR2(200);

BEGIN

   c := dbms_sql.open_cursor;

   FOR x in 1..3 LOOP

      FOR r in obj loop

         i := i + 1;
         ws_tab(i) := r.object_id;

      END LOOP;


      FOR j IN 1..i LOOP

         SELECT 'alter '
            || decode(object_type,'PACKAGE BODY','PACKAGE',object_type)
            || ' '
            || object_name
            || ' '
            || decode(object_type,'INDEX','REBUILD'
                                 ,'PACKAGE','COMPILE PACKAGE'
                                 ,'PACKAGE BODY','COMPILE BODY'
                                 ,'COMPILE')
          INTO ws_stmt
          FROM user_objects
         WHERE object_id = ws_tab(j);

         dbms_output.put_line(ws_stmt);

         BEGIN
            dbms_sql.parse(c,ws_stmt, 2);
            s := dbms_sql.execute(c);
         EXCEPTION
            when others then null;
         END;

      END LOOP;

      i := 0;
      ws_tab.DELETE;

   END LOOP;

   dbms_sql.close_cursor(c);

END;
/
spool off
