CREATE OR REPLACE PROCEDURE q02brecompilar
IS

   /************************************************************************************
       *
       * q02brecompilar: Proceso que recompila todos los objetos inválidos
       *
       * Fecha de creación:   30/12/2013
       * Autor:               Ibermática
       * Proyecto:            q02b
       * Versión:             30/12/2013
       *
       *************************************************************************************/
      VSSQL                                             VARCHAR2 (1000);
      V_ESTADO                                          VARCHAR2 (7);
      V_NUM_INV_SIN_ERR                                 NUMBER;   -- Contabiliza los objetos inválidos que no han tenido error en la última compilación

	  CURSOR VIEWSINV
      IS
         SELECT OBJECT_NAME, OBJECT_TYPE
         FROM   USER_OBJECTS
         WHERE     
		  STATUS = 'INVALID' 
         AND    OBJECT_TYPE IN
                   ('VIEW', 'TRIGGER');
	  
      CURSOR OBJSINV
      IS
         SELECT OBJECT_NAME, OBJECT_TYPE
         FROM   USER_OBJECTS
         WHERE      
		   STATUS = 'INVALID' 
         AND    OBJECT_TYPE IN
                   ('VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE',
                    'PACKAGE BODY','TRIGGER');
   BEGIN
    DBMS_OUTPUT.ENABLE (1000000);
	  
	-- La primera vez se compilan todas las vistas y triggers que estén inválidos; así, a la hora de compilar los packages, no habrá problemas con las
	--referencias que estos tengan a las vistas.
	FOR cur_rec IN (SELECT object_name,
                         object_type
                  FROM   user_objects
                  WHERE      
			          OBJECT_TYPE IN
					('VIEW', 'TRIGGER')
					AND    status != 'VALID')
    LOOP
        BEGIN
            EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
					' "Q02B"."' || cur_rec.object_name || '" COMPILE';
      
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END LOOP;
    --packages
	FOR cur_rec IN (SELECT 
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   user_objects
                  WHERE    
				   object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 3)
    LOOP
        BEGIN
			IF cur_rec.object_type = 'PACKAGE' THEN
				EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
				' "Q02B"."' || cur_rec.object_name || '" COMPILE';
			ElSE
				EXECUTE IMMEDIATE 'ALTER PACKAGE "Q02B"."' || cur_rec.object_name || '" COMPILE BODY';
			END IF;
		EXCEPTION
			WHEN OTHERS THEN
				NULL;
		END;
    END LOOP;
	--resto de objetos
	FOR cur_rec IN (SELECT object_name,
                         object_type
                  FROM   user_objects
                  WHERE 
				   object_type IN  ('VIEW', 'FUNCTION', 'PROCEDURE', 'TRIGGER')
                  AND    status != 'VALID')
    LOOP
        BEGIN
			 EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
					' "Q02B"."' || cur_rec.object_name || '" COMPILE';
		EXCEPTION
			WHEN OTHERS THEN
				NULL;
		END;
    END LOOP;
	-- La primera vez se compilan todos los que estén inválidos; así, los que ahora queden inválidos de nuevo
    -- dejarán error en user_errors
    SELECT COUNT (1)
    INTO   V_NUM_INV_SIN_ERR
    FROM   USER_OBJECTS
    WHERE      
	 STATUS = 'INVALID'
    AND    OBJECT_TYPE IN
                ('VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY','TRIGGER');

    WHILE V_NUM_INV_SIN_ERR > 0
    LOOP
        --compilamos packages
		FOR cur_rec IN (SELECT 
                         object_name,
                         object_type,
                         DECODE(object_type, 'PACKAGE', 1,
                                             'PACKAGE BODY', 2, 2) AS recompile_order
                  FROM   user_objects
                  WHERE    
				   object_type IN ('PACKAGE', 'PACKAGE BODY')
                  AND    status != 'VALID'
                  ORDER BY 3)
		LOOP
			BEGIN
				IF cur_rec.object_type = 'PACKAGE' THEN
					EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
					' "Q02B"."' || cur_rec.object_name || '" COMPILE';
				ElSE
					EXECUTE IMMEDIATE 'ALTER PACKAGE "Q02B"."' || cur_rec.object_name || '" COMPILE BODY';
				END IF;
			EXCEPTION
			WHEN OTHERS THEN
				NULL;
			END;
		END LOOP;
		--resto de objetos
		FOR cur_rec IN (SELECT 
                         object_name,
                         object_type
                  FROM   user_objects
                  WHERE    
				   object_type IN  ('VIEW', 'FUNCTION', 'PROCEDURE', 'TRIGGER')
                  AND    status != 'VALID')
		LOOP
			BEGIN
				EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || 
					' "Q02B"."' || cur_rec.object_name || '" COMPILE';
			EXCEPTION
				WHEN OTHERS THEN
					NULL;
			END;
		END LOOP;
	

        -- Comprobar cuántos objetos inválidos no han dado errores en la última compilación: son los que deben
        -- quedar válidos al compilar
        SELECT COUNT (1)
        INTO   V_NUM_INV_SIN_ERR
        FROM   USER_OBJECTS
        WHERE      
		 STATUS = 'INVALID'
        AND    OBJECT_TYPE IN
                   ('VIEW', 'FUNCTION', 'PROCEDURE', 'PACKAGE',
                    'PACKAGE BODY','TRIGGER')
        AND    OBJECT_NAME NOT IN (SELECT NAME
                                    FROM   USER_ERRORS);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE ('Objetos inválidos: ');

    FOR OBJ IN OBJSINV
    LOOP
        DBMS_OUTPUT.PUT_LINE (OBJ.OBJECT_TYPE || '-' || OBJ.OBJECT_NAME);
    END LOOP;
   

END q02brecompilar;
/


