Rem drv: <create type="types" params="EM_REPOS_USER" pos="notification/notification_types.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/notification/em_notification_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:14 rbali Exp $
Rem
Rem em_notification_types.sql
Rem
Rem Copyright (c) 2010, 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/latest/notification/em_notification_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/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      02/03/25 - 
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    kalchand    04/08/15 - Type Dependency and Referenced Types Synonyms Dropped
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    bnam        03/03/10 - Created
Rem

DEFINE EM_REPOS_USER           = "&1"

-- Included the conent of this file @@&&EM_SQL_ROOT/core/11.2.0.1/notification/em_notification_types.sql &EM_REPOS_USER

rem
rem  TYPE EM_NOTIFY_EMAIL_CUST_OBJ
rem
rem  PURPOSE
rem     This type is for email customization template.
rem     get_email_cust_template functions return this type of object.
rem
rem  COLMUNS
rem     issue_type        - EM_EVENT_TYPE(1), EM_INCIDENT_TYPE(2), EM_PROBLEM_TYPE(3)
rem     event_class       - The name of event class 
rem     format            - LONG_EMAIL_DEVICE_TYPE(1)/SHORT_EMAIL_DEVICE_TYPE(5)
rem     subject           - email template for email subject
rem     body              - email template for email body
rem     created_by        - who created this template 
rem     is_out_of_box     - 1 if OOB template, otherwise 0 
rem     is_active         - 1 if this template is currently used, otherwise 0  
rem     last_updated_date - when updated this template  
rem     last_updated_by   - who updated this template  
rem  

CREATE OR REPLACE TYPE EM_NOTIFY_EMAIL_CUST_OBJ AS OBJECT
(
   issue_type NUMBER,
   event_class VARCHAR2(256),
   format NUMBER,
   subject NCLOB,
   body NCLOB,
   created_by VARCHAR2(64),
   is_out_of_box NUMBER,
   is_active NUMBER,
   last_updated_date Date,
   last_updated_by   VARCHAR(64)
);
/

create or replace type EM_NOTIFY_EMAIL_CUST_ARRAY as TABLE of EM_NOTIFY_EMAIL_CUST_OBJ;
/


rem
rem  TYPE EM_NOTIF_SUBSCRIBER_OBJ
rem
rem  PURPOSE
rem     This type is for email recipients.
rem     Rule engine will construct this type of object and pass to
rem     enqueue_event/incident/problem_deliveries procedures.
rem
rem  COLMUNS
rem     rule_set_name   - Ruleset name
rem     rule_name       - Rule name
rem     rule_owner      - Rule owner Rule engine should default it to 
rem                       RULESET_OWNER, if RULE_OWNER is NULL
rem     rule_set_id     - Rule Set ID  - It is required for hashing the 
rem                       deliveries to end users.
rem     repeat_count    - Positive value, if it is a repeat notification
rem     device_type     - Email (1) Pager (2)
rem     subscriber_type - To (1) CC (0)
rem     recipient_type  - (1) Email address (2) User Name (3) $TGT_OWNER$
rem                       (4) $SOURCE_OBJ_OWNER$ (5) $INCIDENT_OWNER$ (6) $PROBLEM_OWNER$
rem     recipient       - recipient email or user name
rem 
CREATE OR REPLACE TYPE EM_NOTIF_SUBSCRIBER_OBJ IS OBJECT (
  rule_set_name    VARCHAR2(256),   
  rule_name        VARCHAR2(256),   
  rule_owner       VARCHAR2(256),  
  rule_set_id      NUMBER,         
  repeat_count     NUMBER,         
  device_type      NUMBER(1),      
  subscriber_type  NUMBER(1),      
  recipient_type   NUMBER(1),      
  recipient        VARCHAR2(256),
  action_id NUMBER,
  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 
);
/

CREATE OR REPLACE TYPE EM_NOTIF_SUBSCRIBER_ARRAY as VARRAY(5000) of EM_NOTIF_SUBSCRIBER_OBJ;
/


rem
rem  TYPE EM_NOTIF_ADV_DEVICE_OBJ
rem
rem  PURPOSE
rem     This type is for advanced notification methods.
rem     Rule engine will construct this type of object and pass to
rem     enqueue_event/incident/problem_deliveries procedures.
rem
rem  COLMUNS
rem     rule_set_name   - Ruleset name
rem     rule_name       - Rule name
rem     rule_owner      - Rule owner Rule engine should default it to 
rem                       RULESET_OWNER, if RULE_OWNER is NULL
rem     repeat_count    - Positive value, if it is a repeat notification
rem     device_id       - Adv notification device id
rem     is_rule_visible - flag to indicate whether matching rule is hidden 
rem                       or visible
rem

CREATE OR REPLACE TYPE EM_NOTIF_ADV_DEVICE_OBJ IS OBJECT (
  rule_set_name    VARCHAR2(256),    
  rule_name        VARCHAR2(256),   
  rule_owner       VARCHAR2(256),   
  repeat_count     NUMBER,         
  device_id        NUMBER,
  is_rule_visible  NUMBER,
  action_id NUMBER,
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 
);
/

CREATE OR REPLACE TYPE EM_NOTIF_ADV_DEVICE_ARRAY as VARRAY(5000) of EM_NOTIF_ADV_DEVICE_OBJ;
/

rem
rem  TYPE EM_NOTIF_ADMIN_MSG
rem
rem  PURPOSE
rem     This is for admin messages for NotificationMgr.
rem     START/STOP queue, REFRESH gateways, etc
rem
rem  COLMUNS
rem     msg_id    - admin msg ID 
rem     param1    - queue_name if START/STOP_DEQUEUEINGa,
rem                 p_issue_type if REFRESH_EMAIL_TEMPLATE
rem     param2    - p_event_class if REFRESH_EMAIL_TEMPLATE
rem     param3    - p_format if REFRESH_EMAIL_TEMPLATE
rem     param4    - reserved for future use
rem     param5    - reserved for future use
rem 

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

CREATE OR REPLACE TYPE EM_NOTIF_ADMIN_MSG IS OBJECT
(
  msg_id               NUMBER,
  param1               VARCHAR2(256), 
  param2               VARCHAR2(256), 
  param3               VARCHAR2(256), 
  param4               VARCHAR2(256), 
  param5               VARCHAR2(256)  
);
/

grant execute on EM_NOTIF_ADMIN_MSG to sysman;

BEGIN
    SYSMAN.CUTOVER_SYNONYM('EM_NOTIF_ADMIN_MSG');
END;
/

rem
rem  TYPE EM_NOTIF_DELIVERY_PAYLOAD
rem
rem  PURPOSE
rem     This is notification delivery bus payload type.
rem
rem  COLMUNS
rem     admin_payload           - admin message payload
rem     event_instance_payload  - event instance payload
rem     incident_payload        - incident payload
rem     problem_payload         - problem payload
rem     issue_type              - EM_EVENT_TYPE(1), EM_INCIDENT_TYPE(2), 
rem                               EM_PROBLEM_TYPE(3)
rem     notification_type       - NORMAL/REPEAT/RETRY 
rem     is_ping                 - 1 if ping message, otherwise 0
rem     device_id               - notification device id
rem     device_type             - notification device type
rem     device_name             - notification device name
rem     to_names                - list of email addresses in To list
rem     cc_names                - list of email addresses in Cc list
rem     ruleset_id              - Ruleset ID
rem     ruleset_name            - Ruleset name
rem     rule_name               - Rule name
rem     rule_owner              - Rule owner 
rem     qname                   - delivery queue name 
rem     repeat_count            - Positive value, if it is a repeat notification
rem     requeue_guid            - requeue guid (retry)
rem

 
CREATE OR REPLACE TYPE EM_NOTIF_DELIVERY_PAYLOAD IS OBJECT
(
  admin_payload          EM_NOTIF_ADMIN_MSG,
  event_instance_payload EM_EVENT_OBJ,
  incident_payload       EM_INCIDENT_OBJ,
  problem_payload        EM_PROBLEM_OBJ,
  issue_type             NUMBER(2),
  notification_type      NUMBER(2),
  is_ping                NUMBER(1),
  device_id              NUMBER,
  device_type            NUMBER(2),
  device_name            VARCHAR2(132),
  to_names               MGMT_MEDIUM_STRING_ARRAY,
  cc_names               MGMT_MEDIUM_STRING_ARRAY,
  ruleset_id             NUMBER,
  ruleset_name           VARCHAR2(64),
  rule_name              VARCHAR2(64),
  rule_owner             VARCHAR2(256),
  qname                  VARCHAR2(30),
  repeat_count           NUMBER,
  requeue_guid           RAW(16)
);
/

grant execute on EM_NOTIF_DELIVERY_PAYLOAD to sysman;

BEGIN
    SYSMAN.CUTOVER_SYNONYM('EM_NOTIF_DELIVERY_PAYLOAD');
END;
/

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;


rem
rem  TYPE EM_NOTIF_PLSQL_10G_MSG
rem
rem  PURPOSE
rem     This is notification delivery bus payload type.
rem
rem  COLMUNS
rem     event_instance_payload  - event instance payload
rem     incident_payload        - incident payload
rem     notification_type       - NORMAL/REPEAT/RETRY 
rem     repeat_count            - Repeat count
rem     ruleset_name            - Ruleset name
rem     rule_name               - Rule name
rem     rule_owner              - Rule owner 
rem
CREATE OR REPLACE TYPE EM_NOTIF_PLSQL_10G_MSG IS OBJECT
(
  event_instance_payload  EM_EVENT_OBJ,
  incident_payload        EM_INCIDENT_OBJ,
  notification_type       NUMBER(2),
  repeat_count            NUMBER,
  ruleset_name            VARCHAR2(64),
  rule_name               VARCHAR2(64),
  rule_owner              VARCHAR2(256)
);
/


rem
rem  PURPOSE
rem     Create a notification delivery bus (AQ).
rem
rem  NOTE
rem     AQ table          - EM_NOTIFY_QTABLE           
rem     AQ payload type   - EM_NOTIF_DELIVERY_PAYLOAD           
rem     AQ name           - EM_NOTIFY_Q 
rem 
DECLARE
  l_next_step NUMBER := 1;
  l_failure_count NUMBER := 0;
BEGIN
  LOOP 
    BEGIN
      IF l_next_step = 1
      THEN
        DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => '&EM_REPOS_USER'||'.EM_NOTIFY_QTABLE',
                                       queue_payload_type => '&EM_REPOS_USER'||'_TYPES.EM_NOTIF_DELIVERY_PAYLOAD',
                                       sort_list => 'PRIORITY,ENQ_TIME',
                                       multiple_consumers => TRUE);
        l_next_step := 2;
      END IF;
      IF l_next_step = 2
      THEN
        DBMS_AQADM.CREATE_QUEUE (queue_name => '&EM_REPOS_USER'||'.EM_NOTIFY_Q',
                                 queue_table => '&EM_REPOS_USER'||'.EM_NOTIFY_QTABLE');
        l_next_step := 3;
      END IF;
      IF l_next_step = 3
      THEN
        DBMS_AQADM.START_QUEUE (queue_name => '&EM_REPOS_USER'||'.EM_NOTIFY_Q');
        EXIT;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        IF l_failure_count = 15
        THEN
          RAISE;
        END IF;
        --
        -- NOTE:
        --
        -- Rarely ORA-00060 is raised when executing the above statements.
        -- 
        -- This error occured for the first time in April, 2010. It was reported
        -- in EMPP_MAIN_GENERIC_100419 label [pid#4618399]. As a work around for 
        -- this intermittent error, one minute sleep was introduced to allow the db 
        -- kernel complete any pending activity for EM_NOTIF_DELIVERY_PAYLOAD
        -- data type. It recurred again with EMGC_MAIN_LINUX_100930 label
        -- regressions, reported as bug#10167568. Now the work around is
        -- changed to allow upto 5 failures. It sleeps for 1 minute after
        -- each failure, then reattempts the failed operations again.
        -- 
        -- The issue recured again in EMGC_MAIN_LINUX_101230 label. See PID#4882367.
        -- Increased the timeout to 15 minutes from 5 minutes by allowing upto 15 failures
        --
        DBMS_LOCK.SLEEP(60);
        l_failure_count := l_failure_count + 1;
    END;
  END LOOP;
END;
/

DROP TYPE EM_NOTIF_PLSQL_10G_MSG FORCE;

DECLARE
  TYPE l_dependency IS TABLE OF VARCHAR2(30);
  l_type_dependency l_dependency := l_dependency();
  type_exception EXCEPTION;
  PRAGMA EXCEPTION_INIT(type_exception, -22324);
BEGIN
   EXECUTE IMMEDIATE 'DROP SYNONYM EM_NOTIF_DELIVERY_PAYLOAD';
   EXECUTE IMMEDIATE q'[select DISTINCT(NAME) FROM DBA_DEPENDENCIES start with REFERENCED_NAME= 'EM_NOTIF_DELIVERY_PAYLOAD' and TYPE='TYPE' CONNECT BY NOCYCLE PRIOR NAME = REFERENCED_NAME and TYPE = 'TYPE']' BULK COLLECT INTO l_type_dependency;
   IF l_type_dependency.COUNT > 0 THEN
     FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
     BEGIN
        EXECUTE IMMEDIATE 'DROP SYNONYM ' || l_type_dependency(i);
        EXCEPTION
        WHEN OTHERS THEN
        IF (SQLCODE = -1434) THEN
          CONTINUE;
        ELSE
          SYSMAN.MGMT_EMDEP_LOGGER.log_message(1, 'EBR_UPGRADE', 'Failed to Drop Synonym ' || l_type_dependency(i), 'Error : ' || SQLERRM);
          RAISE;
        END IF;
     END;
     END LOOP;
   END IF;
   
   BEGIN
     execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES';
     execute immediate 'ALTER TYPE EM_NOTIF_DELIVERY_PAYLOAD MODIFY ATTRIBUTE RULESET_NAME VARCHAR2(256) CASCADE';
     execute immediate 'ALTER TYPE EM_NOTIF_DELIVERY_PAYLOAD MODIFY ATTRIBUTE RULE_NAME VARCHAR2(256) CASCADE';

     execute immediate q'[ALTER TYPE EM_NOTIF_DELIVERY_PAYLOAD ADD ATTRIBUTE (
	         DURATION_VALUE NUMBER) CASCADE]';
     execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN';
--     execute immediate 'CREATE OR REPLACE SYNONYM EM_NOTIF_DELIVERY_PAYLOAD FOR SYSMAN_TYPES.EM_NOTIF_DELIVERY_PAYLOAD';

     EXCEPTION
       WHEN type_exception THEN
       RAISE_APPLICATION_ERROR(-20202, 'Failed to Alter Type !!' || SQLERRM);
       WHEN OTHERS THEN
       RAISE;
   END;
END;
/

-- Included the conent of the following files

-- @@&&EM_SQL_ROOT/core/12.1.0.2/notification/em_notification_types_upgrade.sql
-- @@&&EM_SQL_ROOT/core/12.1.0.3/notification/em_notification_types.sql

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_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);

  BEGIN
      SYSMAN.CUTOVER_SYNONYM('EM_NOTIF_DELIVERY_PAYLOAD');
  END;    
  
execute_ddl('alter session set current_schema=SYSMAN');

END;
/

DECLARE
  TYPE l_dependency IS TABLE OF VARCHAR2(30);
  l_type_dependency l_dependency := l_dependency();
BEGIN
  EXECUTE IMMEDIATE q'[select DISTINCT(NAME) FROM DBA_DEPENDENCIES start with REFERENCED_NAME = 'EM_NOTIF_DELIVERY_PAYLOAD' and TYPE='TYPE' CONNECT BY NOCYCLE PRIOR NAME = REFERENCED_NAME and TYPE = 'TYPE']' BULK COLLECT INTO l_type_dependency;
  IF l_type_dependency.COUNT > 0 THEN
    FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
       BEGIN
          SYSMAN.CUTOVER_SYNONYM(l_type_dependency(i));
       END;
    END LOOP;
  END IF;
END;
/

@@&&EM_SQL_ROOT/core/12.1.0.4/notification/em_notification_types.sql


