Example 6-14 Extracting KEY_VALUE and KEY_VALUE_NAME

-- Get the pre-12c KEY_VALUE and KEY_VALUE_NAME from an Enterprise Manager 13c
-- notification payload
-- parameters
--   IN Parameters:
--      event_msg : The event notification payload
--   OUT Parameters
--      key_value_name_out : the KEY_VALUE_NAME backward compitable to pre-12c 
--                           notification payload
--      key_value_out      : the KEY_VALUE backward compitable to pre-12c 
--                           notification payload
--
CREATE OR REPLACE PROCEDURE get_pre_12c_key_value(
          event_msg IN GC$NOTIF_EVENT_MSG,
          key_value_name_out OUT VARCHAR2,
          key_value_out OUT VARCHAR2)
IS

  l_key_columns MGMT_SHORT_STRING_ARRAY := MGMT_SHORT_STRING_ARRAY();
  l_key_column_values MGMT_MEDIUM_STRING_ARRAY := MGMT_MEDIUM_STRING_ARRAY();
  l_key_value VARCHAR2(1790) := NULL;
  l_num_keys NUMBER := 0;
  l_attrs gc$notif_event_attr_array;
  l_key_value_name VARCHAR2(512);
BEGIN
  l_attrs := event_msg.event_payload.event_attrs;
  key_value_name_out := NULL;
  key_value_out := NULL;

  IF l_attrs IS NOT NULL AND
     l_attrs.COUNT > 0
  THEN
    l_key_columns.extend(7);
    l_key_column_values.extend(7);
    FOR c IN 1..l_attrs.COUNT
    LOOP
      CASE l_attrs(c).name
        WHEN 'num_keys' THEN
          BEGIN
            l_num_keys := to_number(l_attrs(c).value);
          EXCEPTION
          WHEN OTHERS THEN
            -- should never happen, but guard against it l_num_keys := 0;
          END;
        WHEN 'key_value' THEN
          l_key_value := substr(l_attrs(c).nls_value,1,1290);
        WHEN 'key_column_1' THEN
          l_key_columns(1) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_2' THEN
          l_key_columns(2) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_3' THEN
          l_key_columns(3) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_4' THEN
          l_key_columns(4) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_5' THEN
          l_key_columns(5) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_6' THEN
          l_key_columns(6) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_7' THEN
          l_key_columns(7) := substr(l_attrs(c).nls_value,1,64);
        WHEN 'key_column_1_value' THEN
          l_key_column_values(1) := substr(l_attrs(c).nls_value,1,256);
        WHEN 'key_column_2_value' THEN
          l_key_column_values(2) := substr(l_attrs(c).nls_value,1,256);
        WHEN 'key_column_3_value' THEN
          l_key_column_values(3) := substr(l_attrs(c).nls_value,1,256);
        WHEN 'key_column_4_value' THEN
          l_key_column_values(4) := substr(l_attrs(c).nls_value,1,256);
        WHEN 'key_column_5_value' THEN
          l_key_column_values(5) := substr(l_attrs(c).nls_value,1,256);
        WHEN 'key_column_6_value' THEN
          l_key_column_values(6) := substr(l_attrs(c).nls_value,1,256);
        WHEN 'key_column_7_value' THEN
          l_key_column_values(7) := substr(l_attrs(c).nls_value,1,256);
        ELSE
          NULL;
      END CASE;
    END LOOP;

    -- get key_value and key_value_name when l_num_keys > 0 

    IF l_num_keys > 0
    THEN
      -- get key value name
      IF l_key_columns IS NULL OR l_key_columns.COUNT = 0
      THEN
        key_value_name_out := NULL;
      ELSE
        l_key_value_name := NULL;
        FOR i in l_key_columns.FIRST..l_num_keys
        LOOP
          IF i > 1
          THEN
            l_key_value_name := l_key_value_name || ';';
          END IF;
          l_key_value_name := l_key_value_name || l_key_columns(i);
        END LOOP;
        key_value_name_out := l_key_value_name;
      END IF;
            -- get key_value
      IF l_num_keys = 1
      THEN
        key_value_out := l_key_value;
      ELSE
        l_key_value := NULL;
        IF l_key_column_values IS NULL OR l_key_column_values.COUNT = 0
        THEN
          key_value_out := NULL;
        ELSE 
          FOR i in l_key_column_values.FIRST..l_num_keys
          LOOP
            IF i > 1
            THEN
              l_key_value := l_key_value || ';';
            END IF;
            l_key_value := l_key_value || l_key_column_values(i);
                        END LOOP;
          --  max length for key value in pre-12c = 1290
          key_value_out := substr(l_key_value,1,1290);
        END IF;
      END IF;
    END IF; -- l_num_keys > 0
  END IF;  -- l_attrs IS NOT NULL
END get_pre_12c_key_value;
/