Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/notification/em_notification_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem em_notification_types.sql
Rem
Rem Copyright (c) 2013, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/notification/em_notification_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/notification/em_notification_types.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      em_notification_types.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    sreddy      03/21/13 - Created
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/12.1.0.3/notification/em_notification_types.sql 
Rem    SQL_SHIPPED_FILE: 
Rem    SQL_PHASE: 
Rem    SQL_STARTUP_MODE: NORMAL 
Rem    SQL_IGNORABLE_ERRORS: NONE 
Rem    SQL_CALLING_FILE: 
Rem    END SQL_FILE_METADATA


DECLARE

l_sql VARCHAR2(4000);

-- Executes the submitted DDL. Handles ORA-04020 gracefully.
PROCEDURE EXECUTE_DDL(p_statement IN VARCHAR2)
IS
  l_max_sleep_time NUMBER := 900;
  l_sleep_time NUMBER := 10;
  l_total_sleep_time NUMBER := 0;
  l_failure_count NUMBER := 0;
  MODULE_NAME CONSTANT VARCHAR2(20) := 'EM_NOTIFY' ;
  deadlock_detected EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock_detected, -4020);
BEGIN
  LOOP
    BEGIN
      EXECUTE IMMEDIATE p_statement;

      -- Log a warning message if an exception occurred in the past to indicate completion of operation
      IF l_failure_count > 0
      THEN
        MGMT_EMDEP_LOGGER.log_message
          (p_log_level => MGMT_EMDEP_LOGGER.G_LOG_LEVEL_WARNING, 
           p_component_name => MODULE_NAME, 
           p_message_id => SQLCODE, 
           p_message => 'Recovered from ORA-04020 exception after ' || l_failure_count ||
                        ' retries in ' || l_total_sleep_time || ' seconds. Statement=[' ||
                        p_statement || ']');
      END IF;

      -- If the code reaches here, type changes have been completed, so exit the loop.
      EXIT;

    EXCEPTION 
      --
      -- Rarely ORA-04020 is raised when executing the 'alter type' statements.
      -- pid#7144356 has tracked this issue.
      --
      -- As a work around for this intermittent error, the statements will be
      -- reattempted after a brief sleep. Total time is capped at 15 minutes.
      --
      WHEN deadlock_detected THEN

        -- Log a warning message for the first exception raised
        IF l_failure_count = 0
        THEN
          MGMT_EMDEP_LOGGER.log_message
              (p_log_level => MGMT_EMDEP_LOGGER.G_LOG_LEVEL_WARNING, 
               p_component_name => MODULE_NAME, 
               p_message_id => SQLCODE, 
               p_message => 'Encountered ORA-04020 exception. Will try to recover by retrying the operation periodically for a short period of time. Statement=['||p_statement||']') ;
        END IF;

        IF l_total_sleep_time >= l_max_sleep_time
        THEN
          -- Log an error for the failed operation
          MGMT_EMDEP_LOGGER.log_message
              (p_log_level => MGMT_EMDEP_LOGGER.G_LOG_LEVEL_SEVERE, 
               p_component_name => MODULE_NAME, 
               p_message_id => SQLCODE, 
               p_message => 'Failed to recover from ORA-04020 exception after ' || l_failure_count || 
                          ' attempts in ' || l_total_sleep_time || ' seconds. Statement=['||
                          p_statement||']');

          -- Raise the error to exit
          RAISE;
        END IF;

        l_failure_count := l_failure_count + 1;

        -- Sleep for a short period before reattempting
        DBMS_LOCK.SLEEP(l_sleep_time);

        l_total_sleep_time := l_total_sleep_time + l_sleep_time;

      -- NOTE: Ensure that other exceptions are raised, per recommendation from schema review team.
      -- PLSQL engine intermittently fails to raise other exceptions without the explict the block below.
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;
END EXECUTE_DDL;

BEGIN
  l_sql := 'ALTER TYPE EM_NOTIF_SUBSCRIBER_OBJ 
            ADD CONSTRUCTOR FUNCTION EM_NOTIF_SUBSCRIBER_OBJ
            (
              rule_set_name    IN VARCHAR2,
              rule_name        IN VARCHAR2,
              rule_owner       IN VARCHAR2,
              rule_set_id      IN NUMBER,
              repeat_count     IN NUMBER,
              device_type      IN NUMBER,
              subscriber_type  IN NUMBER,
              recipient_type   IN NUMBER,
              recipient        IN VARCHAR2
             )
             RETURN SELF AS RESULT CASCADE';

  execute_ddl(l_sql);

  l_sql := 'ALTER TYPE EM_NOTIF_SUBSCRIBER_OBJ ADD ATTRIBUTE (action_id NUMBER) CASCADE';

  execute_ddl(l_sql);

  l_sql := 'ALTER TYPE EM_NOTIF_ADV_DEVICE_OBJ
            ADD CONSTRUCTOR FUNCTION EM_NOTIF_ADV_DEVICE_OBJ
            (
             rule_set_name    IN VARCHAR2,
             rule_name        IN VARCHAR2,
             rule_owner       IN VARCHAR2,
             repeat_count     IN NUMBER,
             device_id        IN NUMBER,
             is_rule_visible  IN NUMBER
            )
            RETURN SELF AS RESULT CASCADE';

  execute_ddl(l_sql);

  l_sql := 'ALTER TYPE EM_NOTIF_ADV_DEVICE_OBJ ADD ATTRIBUTE (action_id NUMBER) CASCADE';

  execute_ddl(l_sql);

  l_sql := 'ALTER TYPE EM_NOTIF_DELIVERY_PAYLOAD 
            ADD CONSTRUCTOR FUNCTION EM_NOTIF_DELIVERY_PAYLOAD
            (
              admin_payload          IN EM_NOTIF_ADMIN_MSG,
              event_instance_payload IN EM_EVENT_OBJ,
              incident_payload       IN EM_INCIDENT_OBJ,
              problem_payload        IN EM_PROBLEM_OBJ,
              issue_type             IN NUMBER,
              notification_type      IN NUMBER,
              is_ping                IN NUMBER,
              device_id              IN NUMBER,
              device_type            IN NUMBER,
              device_name            IN VARCHAR2,
              to_names               IN MGMT_MEDIUM_STRING_ARRAY,
              cc_names               IN MGMT_MEDIUM_STRING_ARRAY,
              ruleset_id             IN NUMBER,
              ruleset_name           IN VARCHAR2,
              rule_name              IN VARCHAR2,
              rule_owner             IN VARCHAR2,
              qname                  IN VARCHAR2,
              repeat_count           IN NUMBER,
              requeue_guid           IN RAW,
              duration_value         IN NUMBER
            )
            RETURN SELF AS RESULT CASCADE';

  execute_ddl('alter session set current_schema=SYSMAN_TYPES');
  execute_ddl(l_sql);

  l_sql := 'ALTER TYPE EM_NOTIF_DELIVERY_PAYLOAD ADD 
            ATTRIBUTE 
            (request_id NUMBER,
             was_retried NUMBER(1)
            ) CASCADE';

  execute_ddl(l_sql);

execute_ddl('create or replace noneditionable synonym SYSMAN.EM_NOTIF_DELIVERY_PAYLOAD for EM_NOTIF_DELIVERY_PAYLOAD');

execute_ddl('alter session set current_schema=SYSMAN');

END;
/

