Rem drv: <create type="pkgbodys" pos="beacon/beacon_pkgbodys.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_pkgbodys.sql /st_emcore_10.2.0.4.2db11.2/2 2009/02/26 20:39:36 jsadras Exp $
Rem
Rem collections_pkgbodys.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      collections_pkgbodys.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    jsadras     11/19/08 - remove obsolete code
Rem    jsadras     09/23/08 - Bug:7425928, remove execute immediate
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    jsadras     01/20/05 - bug_4133682,mgmt_metric.get_metric_guid
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jsadras     09/23/04 - deprecate run_collections 
Rem    rpinnama    09/13/04 - Remove is_repsoitory check as we are joining 
Rem                           mgmt_collections_rep 
Rem    aholser     05/17/04 - better tracing 
Rem    aholser     05/14/04 - 
Rem    lgloyd      11/06/03 - shadow rep collections table 
Rem    lgloyd      11/05/03 - 
Rem    lgloyd      11/03/03 - performance work 
Rem    dcawley     09/12/03 - Check for invalid eval func 
Rem    streddy     07/10/03 - Added message and message_nlsid
Rem    aholser     03/26/03 - set error level on collection errors
Rem    dcawley     02/18/03 - Add delete_thresholds
Rem    ancheng     02/14/03 - target version fix
Rem    dcawley     01/28/03 - Fix metric versioning
Rem    vnukal      01/15/03 - changes for agent collection metadata -streddy
Rem    dcawley     09/23/02 - Update last_load_time
Rem    skini       07/12/02 - Change in target_name column size
Rem    dcawley     06/04/02 - Remove example evaluation procedure.
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

----------------------
-- The package body --
----------------------

CREATE OR REPLACE PACKAGE BODY EMD_COLLECTION
IS


--
-- PURPOSE
--   Function to format a default message to add to a severity when
--   no message is provided in the associated threshold. See
--   FORMAT_SEVERITY_MESSAGE for more details.
--
-- PARAMETERS
--   V_METRIC_NAME - name of the metric 
--   V_METRIC_COLUMN - name of the metric column
--   V_METRIC_LABEL - label of the metric 
--   V_COLUMN_LABEL - label of the metric column
--   V_KEY_VALUE - name of the key column value
--   V_THRESHOLD - the threshold that was above or below
--   V_VALUE - the value of the metric
--   V_TYPE - the type of the severity
--            0 - CLEAR
--            1 - WARNING
--            2 - CRITICAL
FUNCTION FORMAT_DFLT_SEVERITY_MESSAGE(v_metric_name   IN VARCHAR2,
                                 v_metric_column IN VARCHAR2,
                                 v_metric_label  IN VARCHAR2,
                                 v_column_label  IN VARCHAR2,
                                 v_key_value     IN VARCHAR2,
                                 v_threshold     IN VARCHAR2,
                                 v_value         IN VARCHAR2,
                                 v_type          IN NUMBER)
RETURN VARCHAR2
IS
  msg VARCHAR2(4000);
BEGIN

  IF v_column_label IS NOT NULL AND v_column_label <> ' '
  THEN
    msg := v_column_label;
  ELSIF v_metric_column IS NOT NULL AND v_metric_column <> ' '
  THEN
    msg := v_metric_column;
  ELSIF v_metric_label IS NOT NULL AND v_metric_label <> ' '
  THEN
    msg := v_metric_label;
  ELSE
    msg := v_metric_name;  
  END IF;

  IF v_key_value IS NOT NULL AND v_key_value <> ' '
  THEN
    msg := msg || ' for ' || v_key_value;
  END IF;

  IF v_type = 0
  THEN
    msg := msg || ' is below the warning threshold (' || 
           v_threshold ||'). Current value: ' || v_value;
  ELSIF v_type = 1
  THEN
    msg := msg || ' exceeded the warning threshold (' || 
           v_threshold ||'). Current value: ' || v_value;  
  ELSIF v_type = 2
  THEN
    msg := msg || ' exceeded the critical threshold (' || 
           v_threshold ||'). Current value: ' || v_value;  
  END IF;  

  RETURN msg;
  
END FORMAT_DFLT_SEVERITY_MESSAGE;

--
-- PURPOSE
--   Returns an URL encoding string
--   The only characters that are encoded are '&', '%', and ';'
-- Used by FORMAT_SEVERITY_MESSAGE
--
FUNCTION URL_ENCODE_STRING(v_string IN VARCHAR2)
  RETURN VARCHAR2
IS
   l_mod_string VARCHAR2(4000) := v_string;
BEGIN
   IF (v_string IS NULL) THEN
     RETURN NULL;
   END IF;

   IF (INSTR(l_mod_string, '&') != 0) THEN
      l_mod_string := REPLACE(l_mod_string, '&', '%26');
   END IF;

   IF (INSTR(l_mod_string, '%') != 0) THEN
      l_mod_string := REPLACE(l_mod_string, '%', '%25');
   END IF;

   IF (INSTR(l_mod_string, ';') != 0) THEN
      l_mod_string := REPLACE(l_mod_string, ';', '%3b');
   END IF;

   RETURN l_mod_string;

END URL_ENCODE_STRING;

--
-- PURPOSE
--   Appends a  value to nls_params
--   Used by FORMAT_SEVERITY_MESSAGE
--
PROCEDURE APPEND_MESSAGE_PARAM(v_nls_params IN OUT VARCHAR2,
                               v_param IN VARCHAR2)
IS
BEGIN
   IF (v_nls_params IS NULL) THEN
     v_nls_params := v_param;
   ELSE
     v_nls_params := v_nls_params || '&' || v_param;
   END IF;
END;


-- PURPOSE
--   Appends nlsid to nls_params
--   Used by FORMAT_SEVERITY_MESSAGE
PROCEDURE APPEND_MESSAGE_PARAM_NLSID(v_nls_params IN OUT VARCHAR2,
                                     v_nlsid IN VARCHAR2)
IS
BEGIN
   IF (v_nlsid IS NULL) THEN
     RETURN; 
   END IF;

   IF (v_nls_params IS NULL) THEN
     v_nls_params := v_nlsid;
   ELSE
     v_nls_params := v_nls_params || ';' || v_nlsid;
   END IF;
END;

-- PURPOSE
--   Returns NLSID for a metric
FUNCTION GET_METRIC_NLSID(v_target_guid IN RAW,
                          v_metric_name IN VARCHAR2)
 RETURN VARCHAR2
IS  
  l_nlsid  VARCHAR2(64);
BEGIN
  -- Get the NLS ID from the mgmt_metrics table
  SELECT m.metric_label_nlsid
    INTO l_nlsid
    FROM mgmt_targets t,  mgmt_metrics m
   WHERE t.target_guid = v_target_guid
    AND  m.target_type = t.target_type
    AND  m.metric_name = v_metric_name
    AND  m.metric_column = ' '
    AND  m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ');

   RETURN l_nlsid;
EXCEPTION
   -- nlsid not defined
   WHEN NO_DATA_FOUND THEN
     RETURN NULL;
END;

-- PURPOSE
--   Returns NLSID for a metric/column
FUNCTION GET_METRIC_COLUMN_NLSID(v_target_guid IN RAW,
                                v_metric_name IN VARCHAR2,
                                v_metric_column IN VARCHAR2)
 RETURN VARCHAR2
IS  
  l_nlsid  VARCHAR2(64);
BEGIN
  -- Get the NLS ID from the mgmt_metrics table
  SELECT m.column_label_nlsid
    INTO l_nlsid
    FROM mgmt_targets t,  mgmt_metrics m
   WHERE t.target_guid = v_target_guid
    AND  m.target_type = t.target_type
    AND  m.metric_name = v_metric_name
    AND  m.metric_column = v_metric_column
    AND  m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ');

   RETURN l_nlsid;
EXCEPTION
   -- nlsid not defined
   WHEN NO_DATA_FOUND THEN
     RETURN NULL;
END;



--
-- PURPOSE
--   Builds the message for a severity. This implementation is based on
--   how agent formats the message based on a templated message specified in 
--   the metadata file. See nmecc.c for agent side implementation.
--
PROCEDURE FORMAT_SEVERITY_MESSAGE(v_target_guid   IN RAW,
                                  v_metric_name   IN VARCHAR2,
                                  v_metric_column IN VARCHAR2,
                                  v_metric_label  IN VARCHAR2,
                                  v_column_label  IN VARCHAR2,
                                  v_key_value     IN VARCHAR2,
                                  v_warning_threshold IN VARCHAR2,
                                  v_critical_threshold IN VARCHAR2,
                                  v_num_occurences IN NUMBER,
                                  v_value         IN VARCHAR2,
                                  v_sev_type      IN NUMBER,
                                  v_msg_template  IN VARCHAR2,
                                  v_metric_result IN MGMT_METRIC_RESULTS,
                                  v_message       OUT VARCHAR2,
                                  v_nls_params    OUT VARCHAR2)
IS
  l_idx1 NUMBER := 0;
  l_idx2 NUMBER := 0;
  l_start_pos NUMBER := 1;
  l_keyword VARCHAR2(64);
  l_value VARCHAR2(256);
  l_nlsid VARCHAR2(64);
  l_threshold VARCHAR2(256);
BEGIN

  dbms_output.put_line('FORMAT_SEVERITY_MESSAGE:' ||v_metric_name || '/' || v_metric_column ||
                       '/msg=' || v_msg_template);

  IF (v_msg_template IS NULL) THEN
    l_threshold := v_warning_threshold;
    IF (v_sev_type = 2) THEN
      l_threshold := v_critical_threshold;
    END IF;
    v_message := FORMAT_DFLT_SEVERITY_MESSAGE(v_metric_name, v_metric_column, 
                                              v_metric_label, v_column_label,
                                              v_key_value, l_threshold,
                                              v_value, v_sev_type);
    RETURN;
  END IF;

  l_idx1 := INSTR(v_msg_template, '%', l_idx2 + 1, 1);
 
  v_message := '';
  v_nls_params := NULL;
  
  WHILE (l_idx1 <> 0) LOOP
    l_nlsid := NULL;

    l_idx2 := INSTR(v_msg_template, '%', l_idx1+1, 1);

    -- End the loop if the end % is missing.
    IF (l_idx2 = 0) THEN
       GOTO end_loop;
    END IF;

    -- Copy over the template data to output message
    IF (l_start_pos < l_idx1) THEN
       v_message := v_message || 
          SUBSTR(v_msg_template, l_start_pos, (l_idx1 - l_start_pos));
    END IF;

    l_start_pos := l_idx2 + 1;

    l_keyword := SUBSTR(v_msg_template, l_idx1+1, (l_idx2 - l_idx1-1));

    IF (l_keyword IS NULL) THEN
       l_value := '%';
    ELSIF (l_keyword = 'value') THEN
       l_value := v_value;
    ELSIF (l_keyword = 'target') THEN
       SELECT target_name INTO l_value
         FROM mgmt_targets
        WHERE target_guid = v_target_guid;
    ELSIF (l_keyword = 'metric_id') THEN
       l_value := v_metric_name;
       l_nlsid := get_metric_nlsid(v_target_guid, v_metric_name);
    ELSIF (l_keyword = 'warning_threshold') THEN
       l_value := v_warning_threshold;
    ELSIF (l_keyword = 'critical_threshold') THEN
       l_value := v_critical_threshold;
    ELSIF (l_keyword = 'num_of_occur') THEN
       l_value := v_num_occurences;
    ELSIF (l_keyword = 'severity') THEN
       IF (v_sev_type = 0) THEN
          l_value := 'CLEAR';
       ELSIF (v_sev_type = 1) THEN
          l_value := 'WARNING';
       ELSIF (v_sev_type = 2) THEN
          l_value := 'CRITICAL';
       ELSE
          l_value := 'UNDEFINED';
       END IF;
    ELSIF (l_keyword = 'threshold') THEN
       IF (v_sev_type = 0 OR v_sev_type = 1) THEN
         l_value := v_warning_threshold;
       ELSE
         l_value := v_critical_threshold;
       END IF;
    ELSIF (l_keyword = 'columnName') THEN
       l_value := v_column_label;
       l_nlsid := 
         get_metric_column_nlsid(v_target_guid, v_metric_name, v_metric_column);
    ELSIF (l_keyword = 'keyValue') THEN
       l_value := v_key_value;
    ELSE
       l_value := '%' || l_keyword;
       -- Check if the keyword is some other column name
       -- within the same metric
       FOR i in 1..v_metric_result.COUNT LOOP
           IF (v_metric_result(i).metric_column = l_keyword) THEN
              l_value := v_metric_result(i).metric_value;
           END IF;
       END LOOP;
    END IF;

    dbms_output.put_line('keyword = ' || l_keyword || ';value = ' || l_value);

    -- Substitute the keyword with the value
    v_message := v_message || l_value;
    append_message_param(v_nls_params, url_encode_string(l_value));

    -- Also add the value to the NLS params
    IF (l_nlsid IS NOT NULL) THEN
      append_message_param_nlsid(v_nls_params, url_encode_string(l_nlsid));
    END IF;

    l_idx1 := INSTR(v_msg_template, '%', l_idx2+1, 1);

  END LOOP;

<<end_loop>>
   IF (l_start_pos < LENGTH(v_msg_template)) THEN
      v_message := v_message || SUBSTR(v_msg_template, l_start_pos);
   END IF;

   dbms_output.put_line('FORMAT_SEVERITY_MESSAGE: message = ' || v_message 
                         || ' params = ' || v_nls_params);

END FORMAT_SEVERITY_MESSAGE;



-- PURPOSE
--   To determine and run all scheduled collections
PROCEDURE RUN_COLLECTIONS
IS
  iSQL           VARCHAR2(4000);
  metric_result  MGMT_METRIC_RESULTS;  
  key_val        VARCHAR2(64) := ' ';
  tkey_val       VARCHAR2(64) := ' ';
  mguid          RAW(16);
  num_values     NUMBER := 0;
  tname          MGMT_TARGETS.target_name%TYPE;
  ttype          MGMT_TARGETS.target_type%TYPE;
  last_sev_code  NUMBER := 0;
  new_sev_code   NUMBER := 0;
  mtype          NUMBER := 0;
  warn_op        NUMBER;
  warn_threshold VARCHAR2(256);
  crit_op        NUMBER;
  crit_threshold VARCHAR2(256);
  m_label        VARCHAR2(64);
  c_label        VARCHAR2(64);
  start_time     DATE;
  end_time       DATE;
  last_time      DATE;
  num_oc         NUMBER := 1;
  num_WAs        NUMBER := 0;
  num_CAs        NUMBER := 0;
  message_template VARCHAR2(4000);
  message        VARCHAR2(4000);
  nls_params     VARCHAR2(4000);
  message_nlsid  VARCHAR2(64);
  perf_start     DATE;      
  duration       NUMBER;
  cnt            NUMBER := 0;
  CURSOR collections IS
    SELECT c.target_guid, c.metric_guid, c.store_metric, c.schedule,
      c.coll_name, m.metric_name, m.eval_func
    FROM MGMT_METRIC_COLLECTIONS_REP r, MGMT_METRIC_COLLECTIONS c, MGMT_METRICS m 
    WHERE c.suspended = 0 AND
          (c.last_collected_timestamp IS NULL
            OR  c.last_collected_timestamp + c.schedule / 1440 < SYSDATE) AND
          c.metric_guid = m.metric_guid AND
          r.target_guid = c.target_guid AND
          r.metric_guid = c.metric_guid AND
          r.coll_name = c.coll_name; 
          
BEGIN

  raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,
    'emd_collections deprecated please use mgmt_collection') ;

END RUN_COLLECTIONS;

--
-- PURPOSE         
--   To add the thresholds for a given collection for one or more columns 
--   and/or one or more key values of a metric
--
PROCEDURE ADD_THRESHOLDS(v_target_guid     IN RAW, 
                         v_metric_name     IN VARCHAR2,
                         v_collection_name IN VARCHAR2, 
                         v_thresholds      IN MGMT_THRESHOLDS,
                         v_delete          IN NUMBER DEFAULT 0)
IS
  ttype   VARCHAR2(64);
  mguid   RAW(16);
  key_val VARCHAR2(64);
BEGIN

  -- Check if the old thresholds need to be deleted
  IF v_delete = 1
  THEN
    DELETE_ALL_THRESHOLDS(v_target_guid, v_metric_name, v_collection_name);
  END IF;

  -- Get the target type
  SELECT target_type INTO ttype FROM MGMT_TARGETS 
  WHERE target_guid = v_target_guid;

  -- Add each threshold
  FOR i IN v_thresholds.FIRST..v_thresholds.LAST
  LOOP  
    -- Get the metric GUID
    mguid := mgmt_metric.get_metric_guid(p_target_type=>ttype,
                                         p_metric_name=>v_metric_name,
                                         p_metric_column=>
                                           v_thresholds(i).metric_column);

    IF v_thresholds(i).key_column_value IS NULL
    THEN
      key_val := ' ';
    ELSE
      key_val := v_thresholds(i).key_column_value;  
    END IF;

    -- Add the threshold
    INSERT INTO MGMT_METRIC_THRESHOLDS (target_guid, metric_guid, 
                                        coll_name, key_value, 
                                        warning_operator, warning_threshold, 
                                        critical_operator, critical_threshold,
                                        num_occurences, 
                                        message, message_nlsid)
    VALUES (v_target_guid, mguid, v_collection_name, key_val,
            v_thresholds(i).warning_operator, 
            v_thresholds(i).warning_threshold, 
            v_thresholds(i).critical_operator, 
            v_thresholds(i).critical_threshold,
            v_thresholds(i).num_occurrences,
            v_thresholds(i).message,
            v_thresholds(i).message_nlsid);
  END LOOP;
            
END ADD_THRESHOLDS;

-- PURPOSE         
--   To delete all thresholds for a number of collections
PROCEDURE DELETE_THRESHOLDS(v_collections IN MGMT_COLLECTION_IDS)
IS
  tguid RAW(16);
BEGIN
  IF v_collections IS NOT NULL AND v_collections.COUNT > 0
  THEN
    FOR i IN v_collections.FIRST..v_collections.LAST
    LOOP
      SELECT target_guid INTO tguid FROM MGMT_TARGETS
      WHERE target_name = v_collections(i).target_name AND
            target_type = v_collections(i).target_type;

      DELETE FROM MGMT_METRIC_THRESHOLDS
      WHERE target_guid = tguid AND 
            coll_name = v_collections(i).collection_name AND
            metric_guid IN (SELECT metric_guid FROM MGMT_METRICS 
                             WHERE metric_name = v_collections(i).metric_name 
                               AND target_type = v_collections(i).target_type);
    END LOOP;
  END IF;

END DELETE_THRESHOLDS;

-- PURPOSE         
--   To delete all thresholds for a collection
PROCEDURE DELETE_ALL_THRESHOLDS(v_target_guid     IN RAW, 
                                v_metric_name     IN VARCHAR2,
                                v_collection_name IN VARCHAR2)
IS
BEGIN
  
  DELETE FROM MGMT_METRIC_THRESHOLDS
  WHERE target_guid = v_target_guid AND coll_name = v_collection_name AND
        metric_guid IN (SELECT metric_guid FROM MGMT_METRICS 
                        WHERE metric_name = v_metric_name AND 
                              target_type=(SELECT target_type FROM MGMT_TARGETS 
                                           WHERE target_guid = v_target_guid));
        
END DELETE_ALL_THRESHOLDS;
 
-- PURPOSE
--   To start a collection
PROCEDURE START_COLLECTION(v_target_guid     IN RAW,
                           v_metric_name     IN VARCHAR2,
                           v_collection_name IN VARCHAR2,
                           v_store_metric    IN VARCHAR2, 
                           v_schedule        IN NUMBER DEFAULT 15,
                           v_schedule_ex     IN VARCHAR2 DEFAULT ' ')
IS
  mguid  RAW(16);
  is_rep NUMBER;
BEGIN

  -- Get the metric name and column
  SELECT m.metric_guid, DECODE(metric_type, 5, 1, 6, 1, 7, 1, 0) 
  INTO mguid, is_rep 
  FROM MGMT_TARGETS t, MGMT_METRICS m
  WHERE t.target_guid = v_target_guid
    AND m.metric_name = v_metric_name
    AND m.metric_column = ' '
    AND t.target_type = m.target_type
    AND m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');

  INSERT INTO MGMT_METRIC_COLLECTIONS (target_guid, metric_guid, coll_name,
                                       is_repository, store_metric, schedule, 
                                       schedule_ex)
  VALUES (v_target_guid, mguid, v_collection_name, is_rep, v_store_metric, 
          v_schedule, v_schedule_ex);

END START_COLLECTION;

-- PURPOSE
--   To modify a collection
PROCEDURE MODIFY_COLLECTION(v_target_guid     IN RAW, 
                            v_metric_name     IN VARCHAR2,
                            v_collection_name IN VARCHAR2, 
                            v_store_metric    IN VARCHAR2, 
                            v_schedule        IN NUMBER DEFAULT 15,
                            v_schedule_ex     IN VARCHAR2 DEFAULT ' ')
IS
  mguid RAW(16);
BEGIN

  SELECT m.metric_guid INTO mguid
  FROM MGMT_TARGETS t, MGMT_METRICS m
  WHERE t.target_guid = v_target_guid
    AND m.metric_name = v_metric_name
    AND m.metric_column = ' '
    AND t.target_type = m.target_type
    AND m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');

  UPDATE MGMT_METRIC_COLLECTIONS SET store_metric = v_store_metric,
                                     schedule = v_schedule
  WHERE target_guid = v_target_guid AND metric_guid = mguid AND
        coll_name = v_collection_name;
    
END MODIFY_COLLECTION;

-- PURPOSE
--   Procedure to describe a collection
PROCEDURE DESCRIBE_COLLECTION(v_target_guid         IN RAW,
                              v_metric_name         IN VARCHAR2,
                              v_collection_name     IN VARCHAR2,
                              v_eval_proc           OUT VARCHAR2,
                              v_schedule            OUT NUMBER,
                              v_schedule_ex         OUT VARCHAR2,
                              v_store_metric        OUT VARCHAR2,
                              v_key_column          OUT VARCHAR2,
                              v_thresholds          OUT MGMT_THRESHOLDS)
IS                              
  mguid RAW(16);
BEGIN

  SELECT m.metric_guid, eval_func, key_column 
  INTO mguid, v_eval_proc, v_key_column
  FROM MGMT_TARGETS t, MGMT_METRICS m
  WHERE t.target_guid = v_target_guid
    AND m.metric_name = v_metric_name
    AND m.metric_column = ' '
    AND t.target_type = m.target_type
    AND m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');

  SELECT schedule, schedule_ex, store_metric 
  INTO v_schedule, v_schedule_ex, v_store_metric
  FROM MGMT_METRIC_COLLECTIONS
  WHERE target_guid = v_target_guid AND metric_guid = mguid
    AND coll_name = v_collection_name;
        
  BEGIN
    SELECT MGMT_THRESHOLD(thr.key_value, met.metric_column, 
                          thr.warning_operator, thr.warning_threshold, 
                          thr.critical_operator, thr.critical_threshold,
                          thr.num_occurences, thr.message, thr.message_nlsid)
    BULK COLLECT INTO v_thresholds
    FROM MGMT_METRIC_THRESHOLDS thr, MGMT_METRICS met
    WHERE thr.target_guid = v_target_guid AND 
          thr.coll_name = v_collection_name AND
          thr.metric_guid = met.metric_guid AND
          met.metric_name = v_metric_name
    ORDER BY met.metric_column, thr.key_value;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- Do nothing
      NULL;
  END;
  
END DESCRIBE_COLLECTION;

-- PURPOSE
--   To verify if a collection exists on the given metric.
FUNCTION HAS_COLLECTION(v_target_guid IN RAW,
                        v_metric_name IN VARCHAR2)
RETURN BOOLEAN
IS
  mguid  RAW(16);  
  result NUMBER(2);
BEGIN

  SELECT m.metric_guid INTO mguid
  FROM MGMT_TARGETS t, MGMT_METRICS m
  WHERE t.target_guid = v_target_guid
    AND m.metric_name = v_metric_name
    AND m.metric_column = ' '
    AND t.target_type = m.target_type
    AND m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');

  SELECT count(coll_name) INTO result
  FROM MGMT_METRIC_COLLECTIONS
  WHERE target_guid = v_target_guid AND metric_guid = mguid;

  IF result > 0 THEN
    return TRUE;
  END IF;

  return FALSE;

END HAS_COLLECTION;

-- PURPOSE
--   To stop a collection
PROCEDURE STOP_COLLECTION(v_target_guid     IN RAW, 
                          v_metric_name     IN VARCHAR2,
                          v_collection_name IN VARCHAR2)
IS
  mguid  RAW(16);  
BEGIN

  SELECT m.metric_guid INTO mguid
  FROM MGMT_TARGETS t, MGMT_METRICS m
  WHERE t.target_guid = v_target_guid
    AND m.metric_name = v_metric_name
    AND m.metric_column = ' '
    AND t.target_type = m.target_type
    AND m.type_meta_ver = t.type_meta_ver
    AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ')
    AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ')
    AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ')
    AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ')
    AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ');

  -- Delete the collection
  DELETE FROM MGMT_METRIC_COLLECTIONS 
  WHERE target_guid = v_target_guid AND metric_guid = mguid AND
        coll_name = v_collection_name;

END STOP_COLLECTION;          

PROCEDURE STOP_COLLECTION(v_target_name     IN VARCHAR2, 
                          v_target_type     IN VARCHAR2,
                          v_metric_name     IN VARCHAR2,
                          v_collection_name IN VARCHAR2)
IS
  tguid RAW(16);
BEGIN
  -- Get the target GUID
  SELECT target_guid INTO tguid FROM MGMT_TARGETS
  WHERE target_name = v_target_name AND target_type = v_target_type ;

  STOP_COLLECTION(tguid, v_metric_name, v_collection_name);

END;

-- PURPOSE
--   To stop a collection
--
PROCEDURE STOP_COLLECTIONS(v_collections IN MGMT_COLLECTION_IDS,
                           v_delete_thresholds IN NUMBER)
IS
  tguid RAW(16);
BEGIN

  IF v_collections IS NOT NULL AND v_collections.COUNT > 0
  THEN
    FOR i IN v_collections.FIRST..v_collections.LAST
    LOOP
      STOP_COLLECTION(v_collections(i).target_name,
                      v_collections(i).target_type,
                      v_collections(i).metric_name,
                      v_collections(i).collection_name);

      IF v_delete_thresholds = 1
      THEN
 
        SELECT target_guid INTO tguid FROM MGMT_TARGETS
        WHERE target_name = v_collections(i).target_name AND
              target_type = v_collections(i).target_type;

        DELETE_ALL_THRESHOLDS(tguid, v_collections(i).metric_name,
                              v_collections(i).collection_name);
      END IF;

    END LOOP;
  END IF;

END STOP_COLLECTIONS;

-- PURPOSE
--   To suspend or resume a collection
--
PROCEDURE SUSPEND_COLLECTION(v_target_guid     IN RAW,
                             v_metric_name     IN VARCHAR2,
                             v_collection_name IN VARCHAR2,
                             v_suspend         IN NUMBER)
IS
  ttype  VARCHAR2(64);
  mguid  RAW(16);
BEGIN

  -- Get the target type
  SELECT target_type INTO ttype FROM MGMT_TARGETS
  WHERE target_guid = v_target_guid;

  SELECT metric_guid INTO mguid FROM MGMT_METRICS
  WHERE target_type = ttype AND metric_name = v_metric_name AND
        metric_column = ' ';

  -- Update the collection
  UPDATE MGMT_METRIC_COLLECTIONS SET suspended = v_suspend
  WHERE target_guid = v_target_guid AND metric_guid = mguid AND
        coll_name = v_collection_name;

END SUSPEND_COLLECTION;

-- PURPOSE
--   To suspend or resume all collections for a target
--
PROCEDURE SUSPEND_COLLECTION(v_target_guid     IN RAW,
                             v_suspend         IN NUMBER)
IS
  ttype  VARCHAR2(64);
  mguid  RAW(16);
BEGIN

  -- Update the collection
  UPDATE MGMT_METRIC_COLLECTIONS SET suspended = v_suspend
  WHERE target_guid = v_target_guid; 

END SUSPEND_COLLECTION;

-- PURPOSE         
--   To get a list of key values that have thresholds defined on them 
FUNCTION GET_KEY_VALUES_WITH_THRESHOLDS(v_target_guid   IN RAW, 
                                        v_metric_guid   IN RAW)
RETURN SMP_EMD_STRING_ARRAY
IS
  key_vals SMP_EMD_STRING_ARRAY;
BEGIN
        
  SELECT DISTINCT key_value BULK COLLECT INTO key_vals 
  FROM MGMT_METRIC_THRESHOLDS
  WHERE key_value IS NOT NULL AND key_value <> ' '
    AND target_guid = v_target_guid AND metric_guid IN
     (SELECT metric_guid FROM mgmt_metrics
        WHERE metric_name = (SELECT metric_name FROM mgmt_metrics 
                         WHERE metric_guid = v_metric_guid));

  RETURN key_vals;
    
END GET_KEY_VALUES_WITH_THRESHOLDS; 


END EMD_COLLECTION;
/
show errors