Recompile Invalid Objects Script

Mit folgendem SQL alle invaliden PL/SQL-Objekte in der Datenbank gesucht und es wird versucht, sie neu zu compilieren:

 BEGIN
   FOR cur_rec IN (SELECT owner,
                          object_name,
                          object_type,
                          DECODE(object_type, 'PACKAGE', 1,
                                              'PACKAGE BODY', 2, 2) AS sort
                   FROM   dba_objects
                   WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE',
                                          'TRIGGER', 'VIEW')
                   AND    status != 'VALID'
                   ORDER BY 4)
   LOOP
     BEGIN
       IF cur_rec.object_type != 'PACKAGE BODY' THEN
         EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
             ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
         DBMS_OUTPUT.put_line('Compiled: '||cur_rec.object_type || ' : ' || cur_rec.owner ||
                              ' : ' || cur_rec.object_name);
       ElSE
         EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
             '"."' || cur_rec.object_name || '" COMPILE BODY';
         DBMS_OUTPUT.put_line('Compiled: '||cur_rec.object_type || ' : ' || cur_rec.owner ||
                              ' : ' || cur_rec.object_name);
       END IF;
     EXCEPTION
       WHEN OTHERS THEN
         DBMS_OUTPUT.put_line('ERROR: '||cur_rec.object_type || ' : ' || cur_rec.owner ||
                              ' : ' || cur_rec.object_name);
     END;
   END LOOP;
 END;
 /