Script to generate invalid Procedure , Package ,etc in Oracle Database

Following script generates the compilation sql's for the invalid objects

Spool the output in an xyz.sql and execute the xyz.sql

SET SERVEROUTPUT ON SIZE 1000000

BEGIN
  FOR cur_rec IN (SELECT owner,object_name,object_type,DECODE(object_type, 'PACKAGE', 1,'PACKAGE BODY', 2, 2) AS recompile_order
FROM   dba_objects WHERE  object_type IN ('PACKAGE', 'PACKAGE BODY') AND  owner not in ('SYS','SYSTEM','OUTLN') AND    status != 'VALID' ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PACKAGE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||  '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||  ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;



SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,object_name,object_type,DECODE(object_type, 'FUNCTION', 1,'FUNCTION', 2, 2) AS recompile_order
                  FROM   dba_objects WHERE  object_type IN ('FUNCTION', 'FUNCTION') AND  owner not in ('SYS','SYSTEM','OUTLN') AND    status != 'VALID' ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'FUNCTION' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER FUNCTION "' || cur_rec.owner ||  '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||  ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;



SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,object_name,object_type,DECODE(object_type, 'PROCEDURE', 1,'PROCEDURE', 2, 2) AS recompile_order
                  FROM   dba_objects WHERE  object_type IN ('PROCEDURE', 'PROCEDURE') AND  owner not in ('SYS','SYSTEM','OUTLN') AND    status != 'VALID' ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'PROCEDURE' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER PROCEDURE "' || cur_rec.owner ||  '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||  ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;



SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,object_name,object_type,DECODE(object_type, 'TRIGGER', 1,'TRIGGER', 2, 2) AS recompile_order
                  FROM   dba_objects WHERE  object_type IN ('TRIGGER', 'TRIGGER') AND  owner not in ('SYS','SYSTEM','OUTLN') AND    status != 'VALID' ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = 'TRIGGER' THEN
        EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
      ElSE
        EXECUTE IMMEDIATE 'ALTER TRIGGER "' || cur_rec.owner ||  '"."' || cur_rec.object_name || '" COMPILE BODY';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||  ' : ' || cur_rec.object_name);
    END;
  END LOOP;
END;