SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;' FROM ALL_OBJECTS WHERE STATUS = 'INVALID' AND OWNER = 'SCOTT' AND OBJECT_TYPE != 'PACKAGE BODY';

SELECT 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;' FROM ALL_OBJECTS WHERE STATUS = 'INVALID' AND OWNER = 'SCOTT' AND OBJECT_TYPE = 'PACKAGE BODY';

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql