Recompiling or Altering Invalid objects in Oracle

-- returns invalid objects and alter script to re-compile

  SELECT object_name,
         object_type,
         DECODE (
            object_type,
            'PACKAGE BODY', 'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;',
            'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' compile;')
            AS COMPILE_SCRIPT
    FROM user_objects
   WHERE status = 'INVALID'
ORDER BY object_type;



SQL> COLUMN OBJECT_NAME FORMAT A30
SQL> COLUMN OBJECT_TYPE FORMAT A30
SQL> COLUMN COMPILE_SCRIPT FORMAT A60
SQL> SET LINES 150
SQL> SET PAGES 50
SQL>   SELECT object_name,
  2           object_type,
  3           DECODE (
  4              object_type,
  5              'PACKAGE BODY', 'ALTER PACKAGE ' || OBJECT_NAME || ' COMPILE BODY;',
  6              'ALTER ' || OBJECT_TYPE || ' ' || OBJECT_NAME || ' compile;')
  7              AS COMPILE_SCRIPT
  8      FROM user_objects
  9     WHERE status = 'INVALID'
 10  ORDER BY object_type;

OBJECT_NAME                    OBJECT_TYPE                    COMPILE_SCRIPT
------------------------------ ------------------------------ ------------------------------------------------------------
F_GETEMP_COUNT                 FUNCTION                       ALTER FUNCTION F_GETEMP_COUNT compile;

SQL>