CREATE OR REPLACE PROCEDURE COMPILE_INVALID_OBJECTS(
   P_OWNER  ALL_OBJECTS.OWNER%TYPE,
   P_OBJECT ALL_OBJECTS.OBJECT_NAME%TYPE DEFAULT NULL,
   P_CMD_SQL VARCHAR2 DEFAULT NULL)
IS
   V_OBJECT_NAME USER_OBJECTS.OBJECT_NAME%TYPE;
   V_CMD_SQL VARCHAR2(400);
   
	CURSOR INVALID_OBJECTS IS 
      SELECT O.OBJECT_NAME, 'ALTER '||O.OBJECT_TYPE||' '||P_OWNER||'.'||O.OBJECT_NAME||' COMPILE' AS CMD_SQL 
        FROM ALL_OBJECTS O
       WHERE O.OWNER = P_OWNER
         AND O.OBJECT_TYPE IN ('PROCEDURE', 'VIEW', 'FUNCTION', 'TRIGGER')
         AND O.STATUS = 'INVALID';

   CURSOR INVALID_DEPENDENCIES IS 
      SELECT D.REFERENCED_NAME, 'ALTER '||D.REFERENCED_TYPE||' '||P_OWNER||'.'||D.REFERENCED_NAME||' COMPILE' AS CMD_SQL
        FROM ALL_DEPENDENCIES D, ALL_OBJECTS O
       WHERE D.OWNER = P_OWNER
         AND D.NAME = V_OBJECT_NAME
         AND D.REFERENCED_TYPE IN ('PROCEDURE', 'VIEW', 'FUNCTION', 'TRIGGER')
         AND O.OWNER = D.OWNER
         AND O.OBJECT_NAME = D.REFERENCED_NAME
         AND O.STATUS = 'INVALID';
BEGIN

	IF P_OBJECT IS NULL THEN
		OPEN INVALID_OBJECTS;
		LOOP
			FETCH INVALID_OBJECTS INTO V_OBJECT_NAME, V_CMD_SQL;
			EXIT WHEN INVALID_OBJECTS%NOTFOUND;
         
			COMPILE_INVALID_OBJECTS(P_OWNER, V_OBJECT_NAME, V_CMD_SQL);
		END LOOP;
	ELSE
		OPEN INVALID_DEPENDENCIES;
		LOOP
			FETCH INVALID_DEPENDENCIES INTO V_OBJECT_NAME, V_CMD_SQL;
			EXIT WHEN INVALID_DEPENDENCIES%NOTFOUND;
         
			COMPILE_INVALID_OBJECTS(P_OWNER, V_OBJECT_NAME, V_CMD_SQL);
		END LOOP;
      
		BEGIN
			EXECUTE IMMEDIATE P_CMD_SQL;
		EXCEPTION
			WHEN OTHERS 
			THEN 
				NULL;
		END;	
	END IF;
END;
/