-- **********************************************************************
--
-- **********************************************************************
   CREATE OR REPLACE PROCEDURE AB39A.RECOMPILA_SCHEMA
   IS
      l_user              VARCHAR2 (30);
      l_inv_count         PLS_INTEGER;
      l_inv_index_count   PLS_INTEGER;
      l_inv_count_prev    PLS_INTEGER;
      l_max_ciclo         PLS_INTEGER   := 5;
      b_hay_compilar      BOOLEAN;
      l_contador          PLS_INTEGER;
   BEGIN
      DBMS_OUTPUT.put_line
                   ('*******************************************************');
      DBMS_OUTPUT.put_line ('[INI] RECOMPILA_SCHEMA');
      DBMS_OUTPUT.put_line
                   ('*******************************************************');
      -- Obtener el nombre del usuario
      l_user := USER;
      l_contador := 1;

      SELECT COUNT (*)
        INTO l_inv_count
        FROM user_objects
       WHERE status = 'INVALID';

      DBMS_OUTPUT.put_line ('[INI] Objetos invalidos: ' || l_inv_count);

      SELECT COUNT (*)
        INTO l_inv_index_count
        FROM user_indexes
       WHERE status = 'UNUSABLE';

      DBMS_OUTPUT.put_line ('[INI] Indices no usables: ' || l_inv_index_count);
      b_hay_compilar := (l_inv_count + l_inv_index_count) > 0;

      WHILE b_hay_compilar
      LOOP
         DBMS_OUTPUT.put_line
                   ('*******************************************************');
         DBMS_OUTPUT.put_line (   '==> PASE  '
                               || l_contador
                               || ' - [INI] Objetos invalidos: '
                               || l_inv_count
                               || ' - Indices no usables: '
                               || l_inv_index_count
                              );
         DBMS_OUTPUT.put_line
                    ('-------------------------------------------------------');
         DBMS_OUTPUT.put_line (   '==> PASE  '
                               || l_contador
                               || ' - Fase 1: [INI] dbms_utility.compile_schema'
                              );
         DBMS_UTILITY.compile_schema (USER, FALSE);

         SELECT COUNT (*)
           INTO l_inv_count
           FROM user_objects
          WHERE status = 'INVALID';

         SELECT COUNT (*)
           INTO l_inv_index_count
           FROM user_indexes
          WHERE status = 'UNUSABLE';

         l_inv_count := l_inv_count + l_inv_index_count;
         DBMS_OUTPUT.put_line (   '==> PASE  '
                               || l_contador
                               || ' - Fase 1: [FIN] Invalidos/No usables: '
                               || l_inv_count
                              );
         DBMS_OUTPUT.put_line
                    ('-------------------------------------------------------');
         DBMS_OUTPUT.put_line (   '==> PASE  '
                               || l_contador
                               || ' - Fase 2: [INI] compilacion manual via ALTER'
                              );

         -- Elementos que debe ser recompilados
         FOR reg IN (SELECT   DECODE (object_type,
                                      'PACKAGE BODY', 'PACKAGE',
                                      'TYPE BODY', 'TYPE',
                                      object_type
                                     ) tipo,
                              DECODE (object_type,
                                      'PACKAGE BODY', 'BODY',
                                      'PACKAGE', 'PACKAGE',
                                      'TYPE BODY', 'BODY',
                                      'TYPE', 'TYPE',
                                      ' '
                                     ) sufijo,
                              object_type,
                              object_name
                         FROM user_objects
                        WHERE status = 'INVALID'
                          AND object_type IN
                                 ('FUNCTION',
                                  'PACKAGE',
                                  'PACKAGE BODY',
                                  'PROCEDURE',
                                  'TRIGGER',
                                  'VIEW',
                                  'SYNONYM'
                                 )
                     ORDER BY object_type,
                              object_name)
         LOOP
            DBMS_OUTPUT.put_line ('--------------------');
            DBMS_OUTPUT.put_line (   'Compilando '
                                  || reg.object_name
                                  || ' ['
                                  || reg.object_type
                                  || ']'
                                 );

            BEGIN
               EXECUTE IMMEDIATE    'alter '
                                 || reg.tipo
                                 || ' '
                                 || reg.object_name
                                 || ' compile '
                                 || reg.sufijo;
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (   'ERROR compilando '
                                        || reg.object_name
                                        || ' - '
                                        || SQLERRM
                                       );

                  FOR regerr IN (SELECT   NAME,
                                          TYPE,
                                          SEQUENCE,
                                          line,
                                          POSITION,
                                          text
                                     FROM user_errors
                                    WHERE NAME = reg.object_name
                                 ORDER BY SEQUENCE)
                  LOOP
                     DBMS_OUTPUT.put_line (   regerr.NAME
                                           || ': Lin. '
                                           || TO_CHAR (regerr.line, '09')
                                           || ' - Pos. '
                                           || TO_CHAR (regerr.POSITION, '09')
                                           || ' - '
                                           || regerr.text
                                          );
                  END LOOP;
            END;
         END LOOP;

         -- Objetos que deben se reconstriudos
         DBMS_OUTPUT.put_line
                          (   '==> PASE  '
                           || l_contador
                           || ' - Fase 3: [INI] recostruccion manual via rebuild'
                          );

         FOR reg IN (SELECT   index_name,
                              index_type,
                              table_name
                         FROM user_indexes
                        WHERE status = 'UNUSABLE'
                     ORDER BY index_name)
         LOOP
            DBMS_OUTPUT.put_line ('--------------------');
            DBMS_OUTPUT.put_line (   'Reconstruyendo '
                                  || reg.index_name
                                  || ' ['
                                  || reg.index_type
                                  || ']'
                                 );

            BEGIN
               EXECUTE IMMEDIATE    'alter index '
                                 || reg.index_name
                                 || ' rebuild ';
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (   'ERROR reconstruyendo '
                                        || reg.index_name
                                        || ' - '
                                        || SQLERRM
                                       );

                  FOR regerr IN (SELECT   NAME,
                                          TYPE,
                                          SEQUENCE,
                                          line,
                                          POSITION,
                                          text
                                     FROM user_errors
                                    WHERE NAME = reg.index_name
                                 ORDER BY SEQUENCE)
                  LOOP
                     DBMS_OUTPUT.put_line (   regerr.NAME
                                           || ': Lin. '
                                           || TO_CHAR (regerr.line, '0009')
                                           || ' - Pos. '
                                           || TO_CHAR (regerr.POSITION, '009')
                                           || ' - '
                                           || regerr.text
                                          );
                  END LOOP;
            END;
         END LOOP;

         l_inv_count_prev := l_inv_count;

         -- Obtener el numero de objetos invalidos
         SELECT COUNT (*)
           INTO l_inv_count
           FROM user_objects
          WHERE status = 'INVALID';

         SELECT COUNT (*)
           INTO l_inv_index_count
           FROM user_indexes
          WHERE status = 'UNUSABLE';

         l_inv_count := l_inv_count + l_inv_index_count;
         DBMS_OUTPUT.put_line ('--------------------');
         DBMS_OUTPUT.put_line (   '==> PASE  '
                               || l_contador
                               || ' - Fase 2: [FIN] Objetos invalidos: '
                               || l_inv_count
                               || ']'
                              );
-- Condiciones de fin de ciclo
         l_contador := l_contador + 1;
         b_hay_compilar :=
                (l_contador < l_max_ciclo)
            AND (l_inv_count_prev > l_inv_count)
            AND (l_inv_count > 0);
      END LOOP;

      IF l_inv_count > 0
      THEN
      DBMS_OUTPUT.put_line
                    ('*******************************************************');

         FOR reg IN (SELECT   *
                         FROM user_objects
                        WHERE status = 'INVALID'
                     ORDER BY object_type,
                              object_name)
         LOOP
            DBMS_OUTPUT.put_line (   'Invalido: '
                                  || reg.object_name
                                  || ' ['
                                  || reg.object_type
                                  || ']'
                                 );
         END LOOP;

      DBMS_OUTPUT.put_line
                    ('*******************************************************');
      END IF;

      DBMS_OUTPUT.put_line
                    ('*******************************************************');
      DBMS_OUTPUT.put_line ('[FIN] RECOMPILA_SCHEMA');
      DBMS_OUTPUT.put_line
                    ('*******************************************************');
   END RECOMPILA_SCHEMA;
   
/

SHOW ERRORS;



set serveroutput on

declare

begin

	RECOMPILA_SCHEMA;
end;

/


commit;
exit;