Page History

DECLARE
 ch_AlterTypes  VARCHAR2(1) := 'N';
 num_MaxRetries NUMBER := 0;
 num_Counter    NUMBER := 0;
 --
 num_Retries    NUMBER;

  PROCEDURE TRY_TO_ALTER_TYPE(
    vch_VC_TYPE_NAME                  IN ALL_OBJECTS.OBJECT_NAME%TYPE)
  IS
    num_Count                         NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO num_Count
    FROM  ALL_OBJECTS
    WHERE OBJECT_NAME = vch_VC_TYPE_NAME
    AND   OBJECT_TYPE = 'TYPE';
  
    IF num_Count > 0 THEN
      EXECUTE IMMEDIATE 'ALTER TYPE "' || vch_VC_TYPE_NAME || '" COMPILE';
    END IF;
  END TRY_TO_ALTER_TYPE;

BEGIN
  IF ch_AlterTypes = 'Y' THEN
    TRY_TO_ALTER_TYPE('equipment_bind');
    TRY_TO_ALTER_TYPE('equipment_binds');
    TRY_TO_ALTER_TYPE('HYDRA_ANYDATA');
    TRY_TO_ALTER_TYPE('HYDRA_ANYDATA_TABLE');
    TRY_TO_ALTER_TYPE('application_context');
    TRY_TO_ALTER_TYPE('deferred_task');
    TRY_TO_ALTER_TYPE('command_result');
    TRY_TO_ALTER_TYPE('command_results');
    TRY_TO_ALTER_TYPE('command');
    TRY_TO_ALTER_TYPE('commands');
    TRY_TO_ALTER_TYPE('profile_attribute');
    TRY_TO_ALTER_TYPE('profile_attributes');
    TRY_TO_ALTER_TYPE('profile');
    TRY_TO_ALTER_TYPE('profiles');
    TRY_TO_ALTER_TYPE('sys_msg_line');
    TRY_TO_ALTER_TYPE('sys_msg_lines');
    TRY_TO_ALTER_TYPE('system_message');
    TRY_TO_ALTER_TYPE('system_messages');
    TRY_TO_ALTER_TYPE('service');
    TRY_TO_ALTER_TYPE('services_list');
    TRY_TO_ALTER_TYPE('account_services');
    TRY_TO_ALTER_TYPE('LOG_MESSAGE');
  END IF;
  
  -- Выбираем список объектов
  FOR rl_Object IN (
    SELECT OWNER,
           OBJECT_NAME,
           OBJECT_TYPE
    FROM   ALL_OBJECTS A
    WHERE  OBJECT_TYPE IN ('PACKAGE', 'PACKAGE BODY', 'TRIGGER', 'VIEW')
    AND    OWNER  = 'AIS_NET'
    AND    STATUS = 'INVALID'
    ORDER BY OBJECT_TYPE, OBJECT_NAME)
  -- Движение по списку
  LOOP
    num_Retries := 0;
    num_Counter := num_Counter + 1;
  
    <<retry>>
  
    -- Компилим объект
    BEGIN
      -- Разные команды для пакетов ХП и остальных типов объектов
      IF rl_Object.OBJECT_TYPE = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE "' || rl_Object.OWNER || '"."' || rl_Object.OBJECT_NAME || '" COMPILE BODY';
      ELSE
        EXECUTE IMMEDIATE 'ALTER ' || rl_Object.OBJECT_TYPE || ' "' || rl_Object.OWNER || '"."' || rl_Object.OBJECT_NAME || '" COMPILE';
      END IF;
    EXCEPTION WHEN others THEN
      IF num_Retries < num_MaxRetries THEN
        num_Retries := num_Retries + 1;
        GOTO Retry;
      ELSE
        DBMS_OUTPUT.PUT_LINE('Ошибка: '|| rl_Object.OBJECT_TYPE || ' ' || rl_Object.OWNER || '.' || rl_Object.OBJECT_NAME);
      END IF;
    END;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Total processed: ' || num_Counter);
  
  IF ch_AlterTypes = 'Y' THEN
    SYS.FIX_HYDRA_OBJECT_TYPES;
  END IF;
END;
/