Compile all invalid custom objects

Compile all invalid custom objects

I use this script on every installation of custom software to make sure all objects are compiled at the end.

SET SERVEROUTPUT ON SIZE 1000000
SET TERM ON
SELECT Status, Object_Type, Object_Name
FROM ALL_OBJECTS
WHERE Status = 'INVALID'
AND Object_Type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW')
;
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 3) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY','VIEW')
                  AND    status != 'VALID'
                  AND    (object_name like 'XX%'
    )
                  ORDER BY 4)
  LOOP
    dbms_output.put_line ('Compiling ' || cur_rec.object_type || ' ' || cur_rec.object_name);
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
            ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSIF cur_rec.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE BODY';
   ELSIF cur_rec.object_Type = 'VIEW' THEN
   EXECUTE IMMEDIATE 'ALTER VIEW "' || cur_rec.owner ||
            '"."' || cur_rec.object_name || '" COMPILE';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('ERROR ON ' || cur_rec.object_type || ' : ' || cur_rec.owner ||
                             ' : ' || cur_rec.object_name);       
    END;
  END LOOP;
END;
/
SELECT Status, Object_Type, Object_Name
FROM ALL_OBJECTS
WHERE Status = 'INVALID'
AND Object_Type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW')
;