Rem  drv: <migrate type="schema_upgrade" version="12.2.0.0"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/emSDK/notification/sdk_notification_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem sdk_notification_types.sql
Rem
Rem Copyright (c) 2015, 2025, Oracle and/or its affiliates.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/emSDK/notification/sdk_notification_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/emSDK/notification/sdk_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      sdk_notification_types.sql
Rem
Rem    DESCRIPTION
Rem      Upgrades to notification types for 13.1 release.
Rem
Rem    NOTES
Rem      According to EBR Wiki (http://dbdev.us.oracle.com/twiki/bin/view/Smpdev/EBRReadinessFAQ)
Rem      updates to types under SYSMAN require drop and re-create operations.
Rem
Rem    BEGIN SQL_FILE_METADATA 
Rem    SQL_SOURCE_FILE: emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/emSDK/notification/sdk_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
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    sreddy      09/29/15 - fix bug#21909571; also include fix for bug#21609914
Rem    jricoir     06/18/15 - Fix bug#21271033: intermittent ORA-04043 error
Rem                           when dropping gc$notif_event_payload
Rem    jricoir     06/01/15 - Created
Rem

DECLARE
l_sql VARCHAR2(4000);
l_created_type_names SYSMAN.MGMT_SHORT_STRING_ARRAY := SYSMAN.MGMT_SHORT_STRING_ARRAY();
TYPE l_dependency IS TABLE OF VARCHAR2(30);
l_type_dependency l_dependency := l_dependency();
MODULE_NAME CONSTANT VARCHAR2(20) := 'EM_NOTIFY' ;
SCHEMA_SYSMAN_TYPES CONSTANT VARCHAR2(12) := 'SYSMAN_TYPES';

--
-- PURPOSE
--   Recompiles invalid dependent objects before retrying
--
PROCEDURE recompile_dependendents(p_type_name IN VARCHAR2)
IS
l_compile_obj varchar2(19);
l_compile_subobj varchar2(19);
l_compile_cmd    varchar2(256);
BEGIN
  -- Look at all of the invalid objects and log them using a debug-level logger command
  FOR invalid_obj_rec IN (SELECT * 
                            FROM all_objects 
                           WHERE status <> 'VALID'
                            AND object_name IN (SELECT name 
                                                  FROM all_dependencies 
                                                  WHERE referenced_name = p_type_name)
                            AND owner = SCHEMA_SYSMAN_TYPES)
  LOOP 
    SYSMAN.MGMT_EMDEP_LOGGER.log_message(p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_DEBUG,
                                         p_component_name => MODULE_NAME,
                                         p_message_id => SQLCODE,
                                         p_message => 'Invalid objects: '  ||
                                         invalid_obj_rec.owner || ' ' ||
                                         invalid_obj_rec.object_name || ' ' ||
                                         invalid_obj_rec.object_type || ' ' ||
                                         invalid_obj_rec.status || ' ' ||
                                         to_char(invalid_obj_rec.last_ddl_time,'yyyymmdd hh24:mi:ss') );

    -- try to recompile
    IF invalid_obj_rec.object_type in ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION','TRIGGER') 
    THEN
      l_compile_obj := case invalid_obj_rec.object_type when 'PACKAGE BODY' then  'PACKAGE' else invalid_obj_rec.object_type end;
      l_compile_subobj := case invalid_obj_rec.object_type when 'PACKAGE BODY' then 'BODY' else null end;
      l_compile_cmd := 'ALTER ' || l_compile_obj || ' ' || invalid_obj_rec.owner || '.' || invalid_obj_rec.object_name || ' COMPILE ' || l_compile_subobj;
      BEGIN
        EXECUTE IMMEDIATE l_compile_cmd;
        SYSMAN.MGMT_EMDEP_LOGGER.log_message(p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_DEBUG,
                                          p_component_name => MODULE_NAME,
                                          p_message_id => SQLCODE,
                                          p_message => 'RECOMPILED OBJECT : '  || l_compile_cmd);
      EXCEPTION 
        -- If there are problems recompiling the dependent objects, log a severe error and stop trying
        WHEN OTHERS THEN 
          SYSMAN.MGMT_EMDEP_LOGGER.log_message(p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_SEVERE,
                                               p_component_name => MODULE_NAME,
                                               p_message_id => SQLCODE,
                                               p_message => 'ERROR: COULD NOT EXECUTE : '  || l_compile_cmd);
        RAISE;
      END;
    END IF;
  END LOOP;
END recompile_dependendents;

-- Utility for determining if max time allowed has elapsed
FUNCTION max_time_elapsed(p_type_name IN VARCHAR2,
                          p_failure1_count IN NUMBER,
                          p_failure2_count IN NUMBER, 
                          p_failure3_count IN NUMBER,
                          p_start_time IN NUMBER,
                          p_max_time_in_seconds IN NUMBER)
RETURN BOOLEAN
IS
l_elapsed_time NUMBER;
BEGIN
  l_elapsed_time := (dbms_utility.get_time -  p_start_time)/100;  

  IF l_elapsed_time >= p_max_time_in_seconds
  THEN
    -- Log an error for the failed operation
    SYSMAN.MGMT_EMDEP_LOGGER.log_message
      (p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_SEVERE, 
       p_component_name => MODULE_NAME, 
       p_message_id => SQLCODE, 
       p_message => 'Failed to recover from intermittent errors while altering type: ' || 
                    p_type_name ||
                    ' [ORA-04020 count: ' || p_failure1_count ||
                    ' ][ORA-22324 count: ' || p_failure2_count || ']' ||
                    ' ][ORA-22933 count: ' || p_failure3_count || ']' ||
                    ' elapsed_time: ' || round(l_elapsed_time, 2));

    RETURN TRUE;
  END IF;

  RETURN FALSE;
END max_time_elapsed;

--
-- PURPOSE
-- internal function for events type upgrade retry
-- when error is encountered
--
PROCEDURE schema_upgrade_with_retry(p_alter_type_ddl IN VARCHAR2,
                                    p_type_name IN VARCHAR2)
IS
l_max_elapsed_time NUMBER := 900;
l_start_time NUMBER :=0;
l_elapsed_time NUMBER;
l_failure1_count NUMBER := 0;
l_failure2_count NUMBER := 0;
l_failure3_count NUMBER := 0;
deadlock_detected EXCEPTION;
compilation_error EXCEPTION;
table_error EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -4020);
PRAGMA EXCEPTION_INIT(compilation_error,-22324);
PRAGMA EXCEPTION_INIT(table_error,-22933);
BEGIN
  l_start_time :=  DBMS_UTILITY.GET_TIME;
  LOOP
    BEGIN
      EXECUTE IMMEDIATE p_alter_type_ddl;

      -- Log a warning message if an exception occurred in the past to indicate completion of operation
      IF l_failure1_count + l_failure2_count + l_failure3_count > 0
      THEN
        l_elapsed_time := (dbms_utility.get_time -  l_start_time)/100;  

        SYSMAN.MGMT_EMDEP_LOGGER.log_message
            (p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_WARNING, 
             p_component_name => MODULE_NAME, 
             p_message_id => SQLCODE, 
             p_message => 'Recovered from intermittent errors while altering type: ' || p_type_name ||
                          ' [ORA-04020 count: ' || l_failure1_count ||
                          ' ][ORA-22324 count: ' || l_failure2_count || ']' ||
                          ' ][ORA-22933 count: ' || l_failure3_count || ']' ||
                          ' elapsed_time: ' || round(l_elapsed_time, 2));
      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.
      --
      WHEN deadlock_detected THEN

        -- Log a warning message for the first exception raised
        IF l_failure1_count = 0
        THEN
          SYSMAN.MGMT_EMDEP_LOGGER.log_message
              (p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_WARNING, 
               p_component_name => MODULE_NAME, 
               p_message_id => SQLCODE, 
               p_message => 'Encountered ORA-04020 exception, while altering type: ' || p_type_name || '. Will try to recover by retrying the operation periodically for a short period of time.') ;
        END IF;

        l_failure1_count := l_failure1_count + 1;
  
        IF max_time_elapsed(p_type_name, l_failure1_count, l_failure2_count, l_failure3_count,
                            l_start_time, l_max_elapsed_time)
        THEN
          RAISE;
        END IF;

      -- has not been compiled properly.  Try to recompile those dependent objects before retrying the original TYPE alteration.
      WHEN compilation_error THEN

        -- Log a warning message for the first exception raised
        IF l_failure2_count = 0
        THEN
          SYSMAN.MGMT_EMDEP_LOGGER.log_message
              (p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_WARNING,
               p_component_name => MODULE_NAME,
               p_message_id => SQLCODE,
               p_message => SQLERRM ||' Encountered ORA-22324 exception, while altering type: ' || p_type_name || '. Will try to recover by retrying the operation periodically for a short period of time.') ;
        END IF;

        l_failure2_count := l_failure2_count + 1;

        recompile_dependendents(p_type_name);

        IF max_time_elapsed(p_type_name, l_failure1_count, l_failure2_count, l_failure3_count,
                            l_start_time, l_max_elapsed_time)
        THEN
          RAISE;
        END IF;

      -- has not been compiled properly.  Try to recompile those dependent objects before retrying the original TYPE alteration.
      WHEN table_error THEN

        -- Log a warning message for the first exception raised
        IF l_failure3_count = 0
        THEN
          SYSMAN.MGMT_EMDEP_LOGGER.log_message
              (p_log_level => SYSMAN.MGMT_EMDEP_LOGGER.G_LOG_LEVEL_WARNING,
               p_component_name => MODULE_NAME,
               p_message_id => SQLCODE,
               p_message => SQLERRM ||' Encountered ORA-22933 exception, while altering type: ' || p_type_name || '. Will try to recover by retrying the operation periodically for a short period of time.') ;
        END IF;

        l_failure3_count := l_failure3_count + 1;

        recompile_dependendents(p_type_name);

        IF max_time_elapsed(p_type_name, l_failure1_count, l_failure2_count, l_failure3_count,
                            l_start_time, l_max_elapsed_time)
        THEN
          RAISE;
        END IF;

      -- 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 schema_upgrade_with_retry ;

PROCEDURE drop_synonym(p_synonym IN VARCHAR2)
IS
BEGIN
  EXECUTE IMMEDIATE 'DROP SYNONYM ' || p_synonym;
EXCEPTION WHEN OTHERS THEN 
  IF (SQLCODE = -1434) 
  THEN
    NULL;
  ELSE
    SYSMAN.MGMT_EMDEP_LOGGER.log_message(1, 'EBR_UPGRADE', 'Failed to Drop Synonym ' || p_synonym, 'Error : ' || SQLERRM);
    RAISE;
  END IF;
END drop_synonym;

-- creates a type under SYSMAN_TYPES schema if doesn't exist
PROCEDURE create_type(p_type_name IN VARCHAR2,
                      p_type_creation_sql IN VARCHAR2,
                      p_created_type_names IN OUT NOCOPY SYSMAN.MGMT_SHORT_STRING_ARRAY)
IS
l_exists NUMBER;
BEGIN
  select count(1)
    into l_exists
    from all_objects
   where upper(object_name) = upper(p_type_name)
     and object_type = 'TYPE'
     and owner = 'SYSMAN_TYPES';

  -- type doesn't exist, create it
  IF l_exists = 0
  THEN
    schema_upgrade_with_retry(p_alter_type_ddl => p_type_creation_sql,
                              p_type_name => p_type_name);
    schema_upgrade_with_retry(p_alter_type_ddl => 'GRANT EXECUTE ON ' || p_type_name || ' TO sysman',
                              p_type_name => p_type_name);
    p_created_type_names.extend(1);
    p_created_type_names(p_created_type_names.COUNT) := p_type_name;
  END IF;
END create_type;

-- creates the synonyms under SYSMAN schema and drops the types if they exist
PROCEDURE create_synonyms(p_type_names IN SYSMAN.MGMT_SHORT_STRING_ARRAY)
IS
l_exists NUMBER;
BEGIN
  FOR i IN REVERSE 1..p_type_names.COUNT
  LOOP
    select count(1)
      into l_exists
      from all_objects
     where upper(object_name) = upper(p_type_names(i))
       and object_type = 'TYPE'
       and owner = 'SYSMAN';

    -- type exists under SYSMAN schema, drop it
    IF l_exists > 0
    THEN
      schema_upgrade_with_retry(p_alter_type_ddl => 'DROP TYPE SYSMAN.' || p_type_names(i) || ' FORCE',
                                p_type_name => p_type_names(i));
    END IF;

    select count(1)
      into l_exists
      from all_objects
     where upper(object_name) = upper(p_type_names(i))
       and object_type = 'SYNONYM'
       and owner = 'SYSMAN';

    -- synonym doesn't exist under SYSMAN schema, create it
    IF l_exists = 0
    THEN
      schema_upgrade_with_retry(p_alter_type_ddl => 'CREATE OR REPLACE SYNONYM SYSMAN.' || p_type_names(i) || ' FOR ' || p_type_names(i),
                                p_type_name => p_type_names(i));
    END IF;
  END LOOP;
END create_synonyms;

BEGIN

  --
  -- Step #1: create types under SYSMAN_TYPES schema
  --
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES';

  -- gc$notif_event_attr
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_event_attr AS OBJECT (' ||
           '    name                        VARCHAR2(64),' ||
           '    value                       VARCHAR2(4000),' ||
           '    nls_value                   VARCHAR2(4000)' ||
           ')';
  create_type(p_type_name => 'gc$notif_event_attr',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_event_attr_array
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_event_attr_array AS VARRAY(35) OF gc$notif_event_attr';
  create_type(p_type_name => 'gc$notif_event_attr_array',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_udtp
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_udtp AS OBJECT (' ||
           '    name                        VARCHAR2(64),' ||
           '    value                       VARCHAR2(1024),' ||
           '    label                       VARCHAR2(256),' ||
           '    nls_id                      VARCHAR2(64)' ||
           ')';
  create_type(p_type_name => 'gc$notif_udtp',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_udtp_array
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_udtp_array AS VARRAY(20) OF gc$notif_udtp';
  create_type(p_type_name => 'gc$notif_udtp_array',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_target
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_target AS OBJECT (' ||
           '    target_guid                 RAW(16),' ||
           '    target_name                 VARCHAR2(256),' ||
           '    target_owner                VARCHAR2(256),' ||
           '    target_lifecycle_status     VARCHAR2(1024),' ||
           '    target_version              VARCHAR2(64),' ||
           '    target_type                 VARCHAR2(128),' ||
           '    target_timezone             VARCHAR2(64),' ||
           '    host_name                   VARCHAR2(256),' ||
           '    target_url                  VARCHAR2(4000),' ||
           '    udtp_array                  gc$notif_udtp_array' ||
           ')';
  create_type(p_type_name => 'gc$notif_target',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_source
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_source AS OBJECT (' ||
           '    source_guid             RAW(16),' ||
           '    source_type             VARCHAR2(120),' ||
           '    source_name             VARCHAR2(256),' ||
           '    source_owner            VARCHAR2(256),' ||
           '    source_sub_type         VARCHAR2(256),' ||
           '    source_url              VARCHAR2(4000)' ||
           ')';
  create_type(p_type_name => 'gc$notif_source',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_source_info
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_source_info AS OBJECT(' ||
           '    target                      gc$notif_target,' ||
           '    source                      gc$notif_source' ||
           ')';
  create_type(p_type_name => 'gc$notif_source_info',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_source_info_array
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_source_info_array AS VARRAY(200) OF gc$notif_source_info';
  create_type(p_type_name => 'gc$notif_source_info_array',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_corrective_action_job
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_corrective_action_job AS OBJECT (' ||
           '    job_guid                        RAW(16),' ||
           '    job_name                        VARCHAR2(128),' ||
           '    job_owner                       VARCHAR2(256),' ||
           '    job_type                        VARCHAR2(256),' ||
           '    job_status                      VARCHAR2(64),' ||
           '    job_status_code                 NUMBER,' ||
           '    job_step_output                 VARCHAR2(4000),' ||
           '    job_execution_guid              RAW(16),' ||
           '    job_state_change_guid           RAW(16),' ||
           '    occurred_date                   DATE' ||
           ')';
  create_type(p_type_name => 'gc$notif_corrective_action_job',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_event_context
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_event_context AS OBJECT (' ||
           '    name                        VARCHAR2(256),' ||
           '    type                        NUMBER(1),' ||
           '    value                       NUMBER,' ||
           '    string_value                VARCHAR2(4000)' ||
           ')';
  create_type(p_type_name => 'gc$notif_event_context',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_event_context_array
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_event_context_array AS VARRAY(200) OF gc$notif_event_context';
  create_type(p_type_name => 'gc$notif_event_context_array',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$category_string_array
  l_sql := 'CREATE OR REPLACE TYPE gc$category_string_array AS VARRAY(10) OF VARCHAR2(64)';
  create_type(p_type_name => 'gc$category_string_array',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_issue_summary
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_issue_summary AS OBJECT(' ||
           '    id                      NUMBER,' ||
           '    severity                VARCHAR2(128),' ||
           '    severity_code           VARCHAR2(32),' ||
           '    priority                VARCHAR2(128),' ||
           '    priority_code           VARCHAR2(32),' ||
           '    status                  VARCHAR2(64),' ||
           '    escalation_level        VARCHAR2(2),' ||
           '    owner                   VARCHAR2(256),' ||
           '    acknowledged_by_owner   NUMBER(1)' ||
           ')';
  create_type(p_type_name => 'gc$notif_issue_summary',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_event_payload
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_event_payload AS OBJECT (' ||
           '    event_instance_guid         RAW(16),' ||
           '    event_sequence_guid         RAW(16),' ||
           '    target                      gc$notif_target,' ||
           '    source                      gc$notif_source,' ||
           '    event_attrs                 gc$notif_event_attr_array,' ||
           '    corrective_action           gc$notif_corrective_action_job,' ||
           '    event_type                  VARCHAR2(20),' ||
           '    event_name                  VARCHAR2(512),' ||
           '    event_msg                   VARCHAR2(4000),' ||
           '    reported_date               DATE,' ||
           '    occurrence_date             DATE, ' ||
           '    severity                    VARCHAR2(128),' ||
           '    severity_code               VARCHAR2(32),' ||
           '    assoc_incident              gc$notif_issue_summary,' ||
           '    action_msg                  VARCHAR2(4000),' ||
           '    rca_detail                  VARCHAR2(4000),' ||
           '    event_context_data          gc$notif_event_context_array,' ||
           '    categories                  gc$category_string_array,' ||
           '    category_codes              gc$category_string_array,' ||
           '    total_occurrence_count      NUMBER,' ||
           '    current_occurrence_count    NUMBER,' ||
           '    current_first_occur_date    DATE,' ||
           '    current_last_occur_date     DATE,' ||
           '    rca_status                  VARCHAR2(128)' ||
           ')';
  create_type(p_type_name => 'gc$notif_event_payload',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_issue_attrs
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_issue_attrs AS OBJECT(' ||
           '    id                          NUMBER(16), ' ||
           '    severity                    VARCHAR2(128), ' ||
           '    severity_code               VARCHAR2(32),' ||
           '    priority                    VARCHAR2(128),' ||
           '    priority_code               VARCHAR2(32),' ||
           '    status                      VARCHAR2(32),' ||
           '    escalation_level            NUMBER(1),' ||
           '    owner                       VARCHAR2(256),' ||
           '    acknowledged_by_owner       NUMBER(1),' ||
           '    creation_date               DATE,' ||
           '    closed_date                 DATE,' ||
           '    categories                  gc$category_string_array,' ||
           '    category_codes              gc$category_string_array,' ||
           '    source_info_arr             gc$notif_source_info_array, ' ||
           '    last_modified_by            VARCHAR2(256),' ||
           '    last_updated_date           DATE' ||
           ')';
  create_type(p_type_name => 'gc$notif_issue_attrs',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_incident_payload
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_incident_payload AS OBJECT (' ||
           '    incident_attrs              gc$notif_issue_attrs,' ||
           '    assoc_event_count           NUMBER,' ||
           '    ticket_status               VARCHAR2(64),' ||
           '    ticket_id                   VARCHAR2(128),' ||
           '    ticket_url                  VARCHAR2(4000),' ||
           '    assoc_problem               gc$notif_issue_summary,' ||
           '    sr_id                       VARCHAR2(64),' ||
           '    sr_url                      VARCHAR2(4000),' ||
           '    assoc_problem_key          VARCHAR2(850),' ||
           '    adr_incident_id            NUMBER(20),' ||
           '    adr_ecid                   VARCHAR2(64),' ||
           '    adr_impact                 VARCHAR2(512),' ||
           '    rca_status                 VARCHAR2(128),' ||
           '    rca_details                VARCHAR2(4000)' ||
           ')';
  create_type(p_type_name => 'gc$notif_incident_payload',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_problem_payload
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_problem_payload AS OBJECT(' ||
           '    problem_attrs               gc$notif_issue_attrs,' ||
           '    problem_key                 VARCHAR2(850),' ||
           '    assoc_incident_count        NUMBER, ' ||
           '    sr_id                       VARCHAR2(64),' ||
           '    sr_url                      VARCHAR2(4000),' ||
           '    bug_id                      VARCHAR2(64)' ||
           ')';
  create_type(p_type_name => 'gc$notif_problem_payload',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_update_string_array
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_update_string_array AS VARRAY(16) OF VARCHAR2(4000)';
  create_type(p_type_name => 'gc$notif_update_string_array',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_msg_info
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_msg_info AS OBJECT(' ||
           '    notification_type           VARCHAR2(32),' ||
           '    repeat_count                NUMBER,' ||
           '    ruleset_name                VARCHAR2(256),' ||
           '    rule_name                   VARCHAR2(256),' ||
           '    rule_owner                  VARCHAR2(256),' ||
           '    message                     VARCHAR2(4000),' ||
           '    message_url                 VARCHAR2(4000),' ||
           '    user_comment                VARCHAR2(4000),' ||
           '    updates                     gc$notif_update_string_array' ||
           ')';
  create_type(p_type_name => 'gc$notif_msg_info',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  -- gc$notif_event_msg
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_event_msg AS OBJECT(' ||
           '    event_payload               gc$notif_event_payload,' ||
           '    msg_info                    gc$notif_msg_info' ||
           ')';
  create_type(p_type_name => 'gc$notif_event_msg',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);


  -- gc$notif_incident_msg
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_incident_msg AS OBJECT(' ||
           '    incident_payload            gc$notif_incident_payload,' ||
           '    msg_info                    gc$notif_msg_info ' ||
           ')';
  create_type(p_type_name => 'gc$notif_incident_msg',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);


  -- gc$notif_problem_msg
  l_sql := 'CREATE OR REPLACE TYPE gc$notif_problem_msg AS OBJECT(' ||
           '    problem_payload             gc$notif_problem_payload,' ||
           '    msg_info                    gc$notif_msg_info ' ||
           ')';
  create_type(p_type_name => 'gc$notif_problem_msg',
              p_type_creation_sql => l_sql,
              p_created_type_names => l_created_type_names);

  --
  -- Step #2: create the synonyms under SYSMAN schema for the newly created types
  --
  create_synonyms(l_created_type_names);

  --
  -- Step #3: upgrade types with new 13.1 fields
  --

  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYSMAN';

  drop_synonym('GC$NOTIF_EVENT_PAYLOAD');

  EXECUTE IMMEDIATE q'[select DISTINCT(NAME) FROM DBA_DEPENDENCIES 
    start with REFERENCED_NAME='GC$NOTIF_EVENT_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
      drop_synonym(l_type_dependency(i));
    END LOOP;  
  END IF;

  l_sql := 'ALTER TYPE SYSMAN_TYPES.GC$NOTIF_EVENT_PAYLOAD ADD ATTRIBUTE (
              REPORTED_STATE VARCHAR2(256)
            ) CASCADE NOT INCLUDING TABLE DATA';
  schema_upgrade_with_retry(p_alter_type_ddl => l_sql,
                            p_type_name => 'GC$NOTIF_EVENT_PAYLOAD');

  EXECUTE IMMEDIATE 'CREATE OR REPLACE NONEDITIONABLE SYNONYM GC$NOTIF_EVENT_PAYLOAD FOR SYSMAN_TYPES.GC$NOTIF_EVENT_PAYLOAD';

  FOR i IN l_type_dependency.first .. l_type_dependency.last LOOP
    EXECUTE IMMEDIATE 'CREATE OR REPLACE NONEDITIONABLE SYNONYM ' || l_type_dependency(i) || ' FOR SYSMAN_TYPES.' || l_type_dependency(i);
  END LOOP;
END;
/


