Script to Compile all invalid Objects in the Data base.

DECLARE
   l_schema                                          VARCHAR2 (240) := '&Schema_name';
   l_include_java                                    INTEGER := 0;
   l_max_passes                                      INTEGER := 10;
   l_debug_flag                                      INTEGER := 0;
   -- Above four variables can be used as parameters if defining a procedure for the Anonymous block
   l_alter_string                                    VARCHAR2 (4000);
   c_cursor                                          INTEGER;
   l_ret_code                                        INTEGER;
   l_object_count                                    INTEGER;
   l_pass                                            INTEGER;
   l_message                                         VARCHAR2 (8000);
   l_status                                          NUMBER;

   CURSOR c_objects_dtls (
      c_schema                   IN             VARCHAR2
   )
   IS
      SELECT   owner
             , object_type
             , object_name
             , DECODE (object_type, 'TYPE', 1, 'OPERATOR', 2, 'PACKAGE', 3, 4) obj_type
          FROM all_objects
         WHERE (status = 'INVALID')
           AND (   c_schema IS NULL
                OR owner = UPPER (c_schema))
           AND object_type IN
                  ('DIMENSION', 'FUNCTION', 'INDEXTYPE', 'JAVA CLASS', 'JAVA SOURCE', 'MATERIALIZED VIEW', 'OPERATOR'
                 , 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER', 'TYPE', 'TYPE BODY', 'VIEW')
      ORDER BY obj_type;
BEGIN
   c_cursor                                                 := SYS.DBMS_SQL.open_cursor;
   l_pass                                                   := 1;

   LOOP
      l_object_count                                           := 0;

      FOR c_objects_dtls_rec IN c_objects_dtls (l_schema)
      LOOP
         IF (c_objects_dtls_rec.object_type = 'PACKAGE')
         THEN
            l_alter_string                                           :=
               'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name
               || '" COMPILE PACKAGE';
         ELSIF (c_objects_dtls_rec.object_type = 'PACKAGE BODY')
         THEN
            l_alter_string                                           :=
                'ALTER PACKAGE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE')
         THEN
            l_alter_string                                           :=
                  'ALTER TYPE "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE SPECIFICATION';
         ELSIF (c_objects_dtls_rec.object_type = 'TYPE BODY')
         THEN
            l_alter_string                                           :=
                   'ALTER TYPE "' || c_objects_dtls_rec.owner || '"."' || c_objects_dtls_rec.object_name || '" COMPILE BODY';
         ELSE
            l_alter_string                                           :=
                  'ALTER '
               || c_objects_dtls_rec.object_type
               || ' "'
               || c_objects_dtls_rec.owner
               || '"."'
               || c_objects_dtls_rec.object_name
               || '" COMPILE';
         END IF;

         IF (    l_include_java <> 1
             AND c_objects_dtls_rec.object_type LIKE '%JAVA%')
         THEN
            l_alter_string                                           := NULL;
         END IF;

         IF (l_alter_string IS NOT NULL)
         THEN
            l_object_count                                           := l_object_count + 1;
            l_message                                                :=
                  'CompileAllObjects pass '
               || TO_CHAR (l_pass, '99999')
               || ' processing object '
               || TO_CHAR (l_object_count, '99999')
               || ' ['
               || l_alter_string
               || ']';

            BEGIN
               DBMS_SQL.parse (c_cursor, l_alter_string, DBMS_SQL.native);
               l_ret_code                                               := DBMS_SQL.EXECUTE (c_cursor);
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_message                                                :=
                                                                  l_message || ' => ERROR # ' || SQLCODE || ' - ' || SQLERRM;
                  NULL;
            END;

            IF (l_debug_flag <> 0)
            THEN
               DBMS_PIPE.pack_message (LENGTH (l_message));
               DBMS_PIPE.pack_message (l_message);
               l_status                                                 :=
                                                      DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
            END IF;
         END IF;
      END LOOP;

      EXIT WHEN l_object_count = 0
            OR l_pass >= l_max_passes;
      l_pass                                                   := l_pass + 1;
   END LOOP;

   DBMS_SQL.close_cursor (c_cursor);

   IF l_debug_flag <> 0
   THEN
      l_message                                                :=
            '*** The Invalid Object Compilation is Completed'
         || TO_CHAR (l_object_count)
         || ' objects in '
         || l_pass
         || ' passes ***';
      DBMS_PIPE.pack_message (LENGTH (l_message));
      DBMS_PIPE.pack_message (l_message);
      l_status                                                 :=
                                                       DBMS_PIPE.send_message ('plsql_debug', DBMS_PIPE.maxwait, 1024 * 1024);
   END IF;
END;
/