Rem drv:
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/template/template_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/4 2009/03/18 02:29:03 bram Exp $
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/template/template_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/4 2009/03/18 02:29:03 bram Exp $
Rem
Rem template_pkgbody.sql
Rem
Rem Copyright (c) 2005, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem template_pkgbody.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem paachary 03/12/09 - Fixing perf issues
Rem paachary 12/18/08 - Adding CA support during create template
Rem paachary 12/18/08 - Adding CA support during create template
Rem paachary 12/20/08 - XbranchMerge paachary_support_ca from
Rem st_emcore_10.2.0.1.0
Rem paachary 11/26/08 - Fixing create template backend API
Rem paachary 11/27/08 - XbranchMerge paachary_bug-7592717 from
Rem st_emcore_10.2.0.1.0
Rem paachary 09/17/08 - Backport paachary_bug-7258269 from
Rem st_emcore_10.2.0.1.0
Rem nqureshi 04/18/07 - XbranchMerge kmanicka_pdp5 from main
Rem pratagar 12/07/05 - Delete UDM and 2 Col SQL UDM Support.
Rem pratagar 07/25/06 - Backport pratagar_bug-4653111 from main
Rem kmanicka 05/10/06 -
Rem rpinnama 09/28/05 - Do not fetch remote or multi-column UDMs
Rem gsbhatia 07/18/05 - Fix repmgr header
Rem rpinnama 07/06/05 - Fix bug 4467255: Skip over metrics/policies that
Rem are not applicable.
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem rpinnama 06/09/05 - rpinnama_bug-4012083
Rem rpinnama 05/03/05 - Created
Rem
CREATE OR REPLACE PACKAGE BODY em_template
AS
DB_CREDS_UDM_PROP CONSTANT VARCHAR2(10) := 'DBCredsUDM';
HOST_UDM_CREDS_PROP CONSTANT VARCHAR2(12) := 'HostUDMCreds';
STR_VALUE_PROP CONSTANT VARCHAR2(8) := 'StrValue';
NUM_VALUE_PROP CONSTANT VARCHAR2(8) := 'NumValue';
PASSWORD_PROP CONSTANT VARCHAR2(8) := 'password';
USERNAME_PROP CONSTANT VARCHAR2(8) := 'username';
-- Generate template guid
FUNCTION generate_template_guid(p_target_type IN VARCHAR2,
p_template_name IN VARCHAR2)
RETURN RAW
IS
l_template_guid MGMT_TEMPLATES.template_guid%TYPE;
BEGIN
l_template_guid := DBMS_OBFUSCATION_TOOLKIT.md5(
input => UTL_RAW.cast_to_raw('ora$template' || ';' ||
p_target_type || ';'||
p_template_name));
RETURN l_template_guid;
END generate_template_guid;
-- Generate template copy API
FUNCTION generate_template_copy_guid (
p_target_type IN VARCHAR2,
p_template_name IN VARCHAR2,
p_target_name IN VARCHAR2 DEFAULT ' ',
p_copy_req_guid IN RAW)
RETURN RAW
IS
l_target_name MGMT_TARGETS.target_name%TYPE;
l_template_copy_guid MGMT_TEMPLATE_COPIES.template_copy_guid%TYPE;
BEGIN
l_target_name := NVL(p_target_name, ' ');
l_template_copy_guid := DBMS_OBFUSCATION_TOOLKIT.md5(
input => UTL_RAW.cast_to_raw('ora$template_copy' || ';' ||
p_target_type || ';'||
p_template_name || ';'||
l_target_name || ';'||
p_copy_req_guid));
RETURN l_template_copy_guid;
END generate_template_copy_guid;
FUNCTION get_ca_name(p_object_guid IN RAW,
p_object_type IN NUMBER,
p_ca_id IN RAW)
RETURN VARCHAR2
IS
l_ca_name mgmt_job.job_name%TYPE;
l_ca_scope mgmt_corrective_action.ca_scope%TYPE;
BEGIN
IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT) THEN
BEGIN
SELECT j.job_name
INTO l_ca_name
FROM MGMT_JOB j, MGMT_CORRECTIVE_ACTION ca
WHERE j.job_id = ca.job_id
AND ca.job_id = p_ca_id
AND j.is_corrective_action = 1
AND ca.ca_scope = MGMT_CA.CA_SCOPE_TARGET_TYPE;
EXCEPTION
WHEN OTHERS THEN
l_ca_name := NULL;
END;
ELSIF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN
BEGIN
SELECT j.job_name
INTO l_ca_name
FROM MGMT_JOB j, MGMT_CORRECTIVE_ACTION ca
WHERE j.job_id = ca.job_id
AND ca.job_id = p_ca_id
AND j.is_corrective_action = 1
AND ca.ca_scope = MGMT_CA.CA_SCOPE_TARGET
AND ca.ca_target_guid = p_object_guid;
EXCEPTION
WHEN OTHERS THEN
l_ca_name := NULL;
END;
ELSIF (p_object_type IN (MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE,
MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY,
MGMT_GLOBAL.G_OBJECT_TYPE_MNTR_SET_COPY) ) THEN
IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE) THEN
l_ca_scope := MGMT_CA.CA_SCOPE_TEMPLATE;
ELSIF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY) THEN
l_ca_scope := MGMT_CA.CA_SCOPE_TEMPLATE_COPY;
ELSE
l_ca_scope := MGMT_CA.CA_SCOPE_TEMPLATE_COPY;
END IF;
BEGIN
SELECT j.job_name
INTO l_ca_name
FROM MGMT_JOB j, MGMT_CORRECTIVE_ACTION ca
WHERE j.job_id = ca.job_id
AND ca.job_id = p_ca_id
AND j.is_corrective_action = 1
AND ca.ca_scope = l_ca_scope
AND ca.ca_template_guid = p_object_guid;
EXCEPTION
WHEN OTHERS THEN
l_ca_name := NULL;
END;
END IF;
RETURN l_ca_name;
END get_ca_name;
PROCEDURE get_object_metric_settings(p_object_guid IN RAW,
p_object_type IN NUMBER,
p_metric_list OUT MGMT_MNTR_METRIC_ARRAY)
IS
l_param_val_list MGMT_POLICY_PARAM_VAL_ARRAY;
l_key_col_cond_list MGMT_POLICY_KEY_COL_COND_ARRAY;
l_key_cfg_list MGMT_POLICY_KEY_VAL_ARRAY;
l_met_info EM_METRIC.METRIC_INFO_REC;
l_key_parts MGMT_MEDIUM_STRING_ARRAY;
l_key_part_oper NUMBER;
l_proc_name VARCHAR2(32) := 'get_object_metric_settings';
BEGIN
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Getting metric settings for object = ' ||
p_object_guid || ' type = ' || p_object_type,
G_MODULE_NAME) ;
END IF ;
p_metric_list := MGMT_MNTR_METRIC_ARRAY();
FOR met_rec IN (SELECT pa.policy_guid, m.metric_name, m.metric_column,
pa.coll_name, pa.is_enabled, pa.add_or_delete
FROM mgmt_policy_assoc pa,
(SELECT DISTINCT metric_guid, target_type,
metric_name, metric_column
FROM mgmt_metrics
WHERE NVL(remote, 0) = 0) m
WHERE pa.object_guid = p_object_guid
AND pa.object_type = p_object_type
AND pa.policy_guid = m.metric_guid
AND pa.policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
ORDER BY pa.policy_guid, pa.coll_name)
LOOP
-- Get metric info
IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN
EM_METRIC.get_metric_info_for_target(
p_metric_guid=> met_rec.policy_guid,
p_target_guid => p_object_guid,
p_metric_info => l_met_info);
ELSE
EM_METRIC.get_metric_info(
p_metric_guid=> met_rec.policy_guid,
p_metric_info => l_met_info);
END IF;
IF (l_met_info.metric_cols.COUNT > 0) THEN
-- Add the metric only if it is applicable
l_key_cfg_list := MGMT_POLICY_KEY_VAL_ARRAY();
FOR cfg_rec IN (SELECT key_value, key_operator, eval_order, is_exception,
has_active_baseline, prevent_override, crit_action_job_id,
warn_action_job_id, info_action_job_id, fixit_job,
simultaneous_actions, importance, num_occurrences, is_push,
condition_operator, message, message_nlsid, clear_message,
clear_message_nlsid
FROM mgmt_policy_assoc_cfg
WHERE object_guid = p_object_guid
AND policy_guid = met_rec.policy_guid
AND coll_name = met_rec.coll_name
ORDER BY eval_order)
LOOP
l_param_val_list := MGMT_POLICY_PARAM_VAL_ARRAY();
FOR param_rec IN (SELECT param_name, crit_threshold, warn_threshold, info_threshold
FROM mgmt_policy_assoc_cfg_params
WHERE object_guid = p_object_guid
AND policy_guid = met_rec.policy_guid
AND coll_name = met_rec.coll_name
AND key_value = cfg_rec.key_value
AND key_operator = cfg_rec.key_operator
ORDER BY param_name)
LOOP
l_param_val_list.extend(1);
l_param_val_list(l_param_val_list.count) := MGMT_POLICY_PARAM_VAL.NEW(
p_param_name => param_rec.param_name,
p_crit_threshold => param_rec.crit_threshold,
p_warn_threshold => param_rec.warn_threshold,
p_info_threshold => param_rec.info_threshold);
END LOOP;
-- Construct the key val object
l_key_col_cond_list := MGMT_POLICY_KEY_COL_COND_ARRAY();
IF (l_met_info.num_keys = 0) THEN
l_key_col_cond_list := NULL;
ELSIF (l_met_info.num_keys = 1) THEN
l_key_col_cond_list.extend(1);
l_key_col_cond_list(l_key_col_cond_list.COUNT) :=
MGMT_POLICY_KEY_COL_COND.NEW(
p_key_value => cfg_rec.key_value,
p_has_wildcard => cfg_rec.key_operator,
p_key_column_name => NULL);
ELSE
-- Fetch and populate key parts from composite key table
-- Special case the ' '
IF (cfg_rec.key_value = ' ') THEN
FOR key_ctr IN 1..l_met_info.num_keys
LOOP
l_key_col_cond_list.extend(1);
l_key_col_cond_list(l_key_col_cond_list.COUNT) :=
MGMT_POLICY_KEY_COL_COND.NEW(
p_key_value => ' ',
p_has_wildcard => 0,
p_key_column_name => l_met_info.key_cols(key_ctr) );
END LOOP;
ELSE
BEGIN
SELECT MGMT_MEDIUM_STRING_ARRAY(key_part1_value,
key_part2_value, key_part3_value,
key_part4_value, key_part5_value)
INTO l_key_parts
FROM mgmt_metrics_composite_keys comp_keys
WHERE comp_keys.composite_key = cfg_rec.key_value
AND comp_keys.target_guid = p_object_guid;
EXCEPTION
WHEN OTHERS THEN
l_key_parts := MGMT_MEDIUM_STRING_ARRAY();
END;
FOR key_ctr IN 1..l_met_info.num_keys
LOOP
l_key_part_oper := 0;
IF (BITAND(cfg_rec.key_operator, POWER(2, key_ctr-1) ) =
POWER(2, key_ctr-1) ) THEN
l_key_part_oper := 1;
END IF;
l_key_col_cond_list.extend(1);
l_key_col_cond_list(l_key_col_cond_list.COUNT) :=
MGMT_POLICY_KEY_COL_COND.NEW(
p_key_value => l_key_parts(key_ctr),
p_has_wildcard => l_key_part_oper,
p_key_column_name => l_met_info.key_cols(key_ctr) );
END LOOP;
END IF; -- if key_value = ' '
END IF; -- num_keys
l_key_cfg_list.extend(1);
l_key_cfg_list(l_key_cfg_list.COUNT) := MGMT_POLICY_KEY_VAL.NEW(
p_key_value => l_key_col_cond_list,
p_is_exception => cfg_rec.is_exception,
p_prevent_override => cfg_rec.prevent_override,
p_crit_job_name => get_ca_name(p_object_guid, p_object_type,
cfg_rec.crit_action_job_id),
p_warn_job_name => get_ca_name(p_object_guid, p_object_type,
cfg_rec.warn_action_job_id),
p_info_job_name => get_ca_name(p_object_guid, p_object_type,
cfg_rec.info_action_job_id),
p_agent_fixit => cfg_rec.fixit_job,
p_simult_actions => cfg_rec.simultaneous_actions,
p_importance => cfg_rec.importance,
p_num_occurrences => cfg_rec.num_occurrences,
p_is_push => cfg_rec.is_push,
p_condition_operator => cfg_rec.condition_operator,
p_message => cfg_rec.message,
p_message_nlsid => cfg_rec.message_nlsid,
p_clear_message => cfg_rec.clear_message,
p_clear_message_nlsid => cfg_rec.clear_message_nlsid,
p_param_values => l_param_val_list);
END LOOP;
p_metric_list.extend(1);
p_metric_list(p_metric_list.COUNT) := MGMT_MNTR_METRIC.NEW(
p_metric_name => l_met_info.metric_name,
p_metric_column => met_rec.metric_column,
p_coll_name => met_rec.coll_name,
p_is_enabled => met_rec.is_enabled,
p_key_val_list => l_key_cfg_list,
p_add_or_delete => met_rec.add_or_delete);
END IF; -- If l_met_info.metric_cols.COUNT > 0
END LOOP;
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Exit. Got metric settings for object = ' ||
p_object_guid || ' type = ' || p_object_type,
G_MODULE_NAME) ;
END IF ;
END get_object_metric_settings;
PROCEDURE get_object_policy_settings(p_object_guid IN RAW,
p_object_type IN NUMBER,
p_policy_list OUT MGMT_MNTR_POLICY_ARRAY)
IS
l_param_val_list MGMT_POLICY_PARAM_VAL_ARRAY;
l_key_col_cond_list MGMT_POLICY_KEY_COL_COND_ARRAY;
l_key_cfg_list MGMT_POLICY_KEY_VAL_ARRAY;
l_met_info EM_METRIC.METRIC_INFO_REC;
l_policy_name mgmt_policies.policy_name%type;
l_metric_guid mgmt_metrics.metric_guid%TYPE;
l_key_parts MGMT_MEDIUM_STRING_ARRAY;
l_key_part_oper NUMBER;
l_proc_name VARCHAR2(32) := 'get_object_policy_settings';
BEGIN
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Getting policy settings for object = ' ||
p_object_guid || ' type = ' || p_object_type,
G_MODULE_NAME) ;
END IF ;
p_policy_list := MGMT_MNTR_POLICY_ARRAY();
FOR pol_rec IN (SELECT policy_guid, coll_name, is_enabled
FROM mgmt_policy_assoc
WHERE object_guid = p_object_guid
AND object_type = p_object_type
AND policy_type = MGMT_GLOBAL.G_TYPE_POLICY
ORDER BY policy_guid, coll_name)
LOOP
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Getting ' ||
' policy = [' || pol_rec.policy_guid || ']' ||
' coll = [' || pol_rec.coll_name || ']' ||
' enb = [' || pol_rec.is_enabled || ']',
G_MODULE_NAME) ;
END IF ;
SELECT policy_name, metric_guid
INTO l_policy_name, l_metric_guid
FROM mgmt_policies
WHERE policy_guid = pol_rec.policy_guid;
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Got metric guid.' ||
' policy name = [' || l_policy_name || ']' ||
' metric_guid = [' || l_metric_guid || ']',
G_MODULE_NAME) ;
END IF ;
-- Get metric info
IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN
EM_METRIC.get_metric_info_for_target(
p_metric_guid=> l_metric_guid,
p_target_guid => p_object_guid,
p_metric_info => l_met_info);
ELSE
EM_METRIC.get_metric_info(
p_metric_guid=> l_metric_guid,
p_metric_info => l_met_info);
END IF;
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Got metric details.' ||
' metric name = [' || l_met_info.metric_name || ']' ||
' num keys = [' || l_met_info.num_keys || ']',
G_MODULE_NAME) ;
END IF ;
l_key_cfg_list := MGMT_POLICY_KEY_VAL_ARRAY();
FOR cfg_rec IN (SELECT key_value, key_operator, eval_order, is_exception,
has_active_baseline, prevent_override, crit_action_job_id,
warn_action_job_id, info_action_job_id, fixit_job,
simultaneous_actions, importance, num_occurrences, is_push,
condition_operator, message, message_nlsid, clear_message,
clear_message_nlsid
FROM mgmt_policy_assoc_cfg
WHERE object_guid = p_object_guid
AND policy_guid = pol_rec.policy_guid
AND coll_name = pol_rec.coll_name
ORDER BY eval_order)
LOOP
l_param_val_list := MGMT_POLICY_PARAM_VAL_ARRAY();
FOR param_rec IN (SELECT param_name, crit_threshold, warn_threshold, info_threshold
FROM mgmt_policy_assoc_cfg_params
WHERE object_guid = p_object_guid
AND policy_guid = pol_rec.policy_guid
AND coll_name = pol_rec.coll_name
AND key_value = cfg_rec.key_value
AND key_operator = cfg_rec.key_operator
ORDER BY param_name)
LOOP
l_param_val_list.extend(1);
l_param_val_list(l_param_val_list.count) := MGMT_POLICY_PARAM_VAL.NEW(
p_param_name => param_rec.param_name,
p_crit_threshold => param_rec.crit_threshold,
p_warn_threshold => param_rec.warn_threshold,
p_info_threshold => param_rec.info_threshold);
END LOOP;
-- Construct the key val object
l_key_col_cond_list := MGMT_POLICY_KEY_COL_COND_ARRAY();
IF (l_met_info.num_keys = 0) THEN
l_key_col_cond_list := NULL;
ELSIF (l_met_info.num_keys = 1) THEN
l_key_col_cond_list.extend(1);
l_key_col_cond_list(l_key_col_cond_list.COUNT) :=
MGMT_POLICY_KEY_COL_COND.NEW(
p_key_value => cfg_rec.key_value,
p_has_wildcard => cfg_rec.key_operator,
p_key_column_name => NULL);
ELSE
-- Fetch and populate key parts from composite key table
IF (cfg_rec.key_value = ' ') THEN
l_key_col_cond_list := NULL;
ELSE
BEGIN
SELECT MGMT_MEDIUM_STRING_ARRAY(key_part1_value,
key_part2_value, key_part3_value,
key_part4_value, key_part5_value)
INTO l_key_parts
FROM mgmt_metrics_composite_keys comp_keys
WHERE comp_keys.composite_key = cfg_rec.key_value
AND comp_keys.target_guid = p_object_guid;
EXCEPTION
WHEN OTHERS THEN
l_key_parts := MGMT_MEDIUM_STRING_ARRAY();
END;
FOR key_ctr IN 1..l_met_info.num_keys
LOOP
l_key_part_oper := 0;
IF (BITAND(cfg_rec.key_operator, POWER(2, key_ctr-1) ) =
POWER(2, key_ctr-1) ) THEN
l_key_part_oper := 1;
END IF;
l_key_col_cond_list.extend(1);
l_key_col_cond_list(l_key_col_cond_list.COUNT) :=
MGMT_POLICY_KEY_COL_COND.NEW(
p_key_value => l_key_parts(key_ctr),
p_has_wildcard => l_key_part_oper,
p_key_column_name => l_met_info.key_cols(key_ctr) );
END LOOP;
END IF; -- If keyvalue = ' '
END IF; -- If num_keys > 1
l_key_cfg_list.extend(1);
l_key_cfg_list(l_key_cfg_list.COUNT) := MGMT_POLICY_KEY_VAL.NEW(
p_key_value => l_key_col_cond_list,
p_is_exception => cfg_rec.is_exception,
p_prevent_override => cfg_rec.prevent_override,
p_crit_job_name => get_ca_name(p_object_guid, p_object_type,
cfg_rec.crit_action_job_id),
p_warn_job_name => get_ca_name(p_object_guid, p_object_type,
cfg_rec.warn_action_job_id),
p_info_job_name => get_ca_name(p_object_guid, p_object_type,
cfg_rec.info_action_job_id),
p_agent_fixit => cfg_rec.fixit_job,
p_simult_actions => cfg_rec.simultaneous_actions,
p_importance => cfg_rec.importance,
p_num_occurrences => cfg_rec.num_occurrences,
p_is_push => cfg_rec.is_push,
p_condition_operator => cfg_rec.condition_operator,
p_message => cfg_rec.message,
p_message_nlsid => cfg_rec.message_nlsid,
p_clear_message => cfg_rec.clear_message,
p_clear_message_nlsid => cfg_rec.clear_message_nlsid,
p_param_values => l_param_val_list);
END LOOP;
p_policy_list.extend(1);
p_policy_list(p_policy_list.COUNT) := MGMT_MNTR_POLICY.NEW(
p_policy_name => l_policy_name,
p_coll_name => pol_rec.coll_name,
p_is_enabled => pol_rec.is_enabled,
p_key_val_list => l_key_cfg_list);
END LOOP;
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Exit. Got policy settings for object = ' ||
p_object_guid || ' type = ' || p_object_type,
G_MODULE_NAME) ;
END IF ;
END get_object_policy_settings;
PROCEDURE get_object_coll_settings(p_object_guid IN RAW,
p_object_type IN NUMBER,
p_coll_list OUT MGMT_MNTR_COLLECTION_ARRAY)
IS
l_metric_name mgmt_metrics.metric_name%TYPE;
l_is_transposed mgmt_metrics.is_transposed%TYPE;
l_prop_list MGMT_COLL_PROP_ARRAY;
l_met_list MGMT_COLL_METRIC_ARRAY;
l_cred_list MGMT_CRED_ARRAY;
l_cred_row_list MGMT_CRED_ROW_ARRAY;
l_coll_sched MGMT_COLL_SCHEDULE_OBJ;
l_proc_name VARCHAR2(32) := 'get_object_coll_settings';
BEGIN
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Enter. Getting collection settings for object = ' ||
p_object_guid || ' type = ' || p_object_type,
G_MODULE_NAME) ;
END IF ;
p_coll_list := MGMT_MNTR_COLLECTION_ARRAY();
FOR coll_rec IN (SELECT coll_name, is_enabled, store_metric,
upload_frequency, schedule_ex,
frequency_code, start_time, end_time,
execution_hours, execution_minutes, interval,
months, days
FROM mgmt_collections
WHERE object_guid = p_object_guid
AND object_type = p_object_type
ORDER BY coll_name)
LOOP
l_met_list := MGMT_COLL_METRIC_ARRAY();
FOR met_rec IN (SELECT DISTINCT cmt.metric_guid, m.metric_name, m.is_transposed
FROM mgmt_collection_metric_tasks cmt, mgmt_metrics m
WHERE NVL(m.remote, 0) = 0
AND cmt.target_guid = p_object_guid
AND cmt.coll_name = coll_rec.coll_name
AND cmt.metric_guid = m.metric_guid
ORDER BY cmt.metric_guid)
LOOP
l_is_transposed := met_rec.is_transposed;
l_prop_list := MGMT_COLL_PROP_ARRAY();
-- Get collection properties
FOR prop_rec IN (SELECT property_name, property_value
FROM mgmt_coll_item_properties
WHERE object_guid = p_object_guid
AND metric_guid = met_rec.metric_guid
AND object_type = p_object_type
AND coll_name = coll_rec.coll_name
ORDER BY property_name)
LOOP
l_prop_list.extend(1);
l_prop_list(l_prop_list.count) := MGMT_COLL_PROP.NEW(
p_name => prop_rec.property_name,
p_value => prop_rec.property_value);
END LOOP; -- prop_rec
-- Get collection credentials
l_cred_list := MGMT_CRED_ARRAY();
FOR cred_rec IN (SELECT credential_set_name, credential_guid
FROM mgmt_collection_credentials
WHERE target_guid = p_object_guid
AND metric_guid = met_rec.metric_guid
AND coll_name = coll_rec.coll_name
ORDER BY credential_set_name)
LOOP
l_cred_row_list := MGMT_CRED_ROW_ARRAY();
SELECT MGMT_CRED_ROW_RECORD(credential_set_column, decrypt(credential_value))
BULK COLLECT INTO l_cred_row_list
FROM mgmt_credentials2
WHERE credential_guid = cred_rec.credential_guid
ORDER BY credential_set_column;
l_cred_list.extend(1);
l_cred_list(l_cred_list.count) := MGMT_CRED_RECORD.NEW(NULL,
cred_rec.credential_set_name, l_cred_row_list);
END LOOP;
l_met_list.extend(1);
l_met_list(l_met_list.count) := MGMT_COLL_METRIC.NEW(
p_metric_name => met_rec.metric_name,
p_property_list => l_prop_list,
p_credentials => l_cred_list);
END LOOP; -- met_rec
IF (l_met_list.COUNT > 0) THEN
-- Create the collection only if it has metrics,
-- Collections for remote metrics and multi-column UDM metrics
-- are not created.
l_coll_sched := MGMT_COLL_SCHEDULE_OBJ.NEW(
p_frequency_code => coll_rec.frequency_code,
p_execution_hours => coll_rec.execution_hours,
p_execution_minutes => coll_rec.execution_minutes,
p_interval => coll_rec.interval,
p_months => coll_rec.months,
p_days => coll_rec.days,
p_start_time => coll_rec.start_time,
p_end_time => coll_rec.end_time);
p_coll_list.extend(1);
p_coll_list(p_coll_list.COUNT) := MGMT_MNTR_COLLECTION.NEW(
p_coll_name => coll_rec.coll_name,
p_is_enabled => coll_rec.is_enabled,
p_is_transposed => l_is_transposed,
p_store_metric => coll_rec.store_metric,
p_upload_frequency => coll_rec.upload_frequency,
p_schedule_ex => coll_rec.schedule_ex,
p_schedule => l_coll_sched,
p_metric_list => l_met_list);
END IF;
END LOOP; -- coll_rec
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(l_proc_name||' Exit. Got collection settings for object = ' ||
p_object_guid || ' type = ' || p_object_type,
G_MODULE_NAME) ;
END IF ;
END get_object_coll_settings;
PROCEDURE get_object_settings(p_object_guid IN RAW,
p_object_type IN NUMBER,
p_metric_list OUT MGMT_MNTR_METRIC_ARRAY,
p_policy_list OUT MGMT_MNTR_POLICY_ARRAY,
p_coll_list OUT MGMT_MNTR_COLLECTION_ARRAY)
IS
BEGIN
-- Get metric list
get_object_metric_settings(p_object_guid, p_object_type, p_metric_list);
-- Get policy list
get_object_policy_settings(p_object_guid, p_object_type, p_policy_list);
-- Get collection list
get_object_coll_settings(p_object_guid, p_object_type, p_coll_list);
END get_object_settings;
-- Private API - copy_target_settings
-- IN Parameters
-- p_target_type
-- p_source_target_name
-- OUT Parameters
-- p_metric_list
-- p_policy_list
-- p_collection_list
-- p_message_array
-- Description
-- First make a call to the mgmt_monitoring.get_target_settings SDK API.
-- Get all the below mentioned info for the provided target_type AND target_name combination.
-- p_metric_list
-- p_policy_list
-- p_collection_list
PROCEDURE copy_target_settings(p_target_type IN VARCHAR2,
p_source_target_name IN VARCHAR2,
p_metric_list OUT MGMT_MNTR_METRIC_ARRAY,
p_policy_list OUT MGMT_MNTR_POLICY_ARRAY,
p_collection_list OUT MGMT_MNTR_COLLECTION_ARRAY,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
IS
l_msg_cntr PLS_INTEGER := 0;
BEGIN
-- First make a call to the mgmt_monitoring.get_target_settings SDK API.
-- Get all the below mentioned info for the provided target_type AND target_name combination.
-- p_metric_list
-- p_policy_list
-- p_collection_list
mgmt_monitoring.get_target_settings ( p_target_type => p_target_type,
p_target_name => p_source_target_name,
p_metric_list => p_metric_list,
p_policy_list => p_policy_list,
p_collection_list => p_collection_list);
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Successfully got the target settings for target '||p_source_target_name||':'||p_target_type;
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception when retreiving target settings '||sqlerrm;
END copy_target_settings;
-- Private API - create_cas
-- IN Parameters
-- p_target_type
-- p_target_name
-- p_template_name
-- OUT Parameters
-- Description
-- Associate the CAs from the source tgt to the template.
PROCEDURE create_cas(p_template_name IN mgmt_templates.template_name%TYPE,
p_target_name IN mgmt_targets.target_name%TYPE,
p_target_type IN mgmt_targets.target_type%TYPE)
IS
l_target_guid mgmt_targets.target_guid%TYPE;
l_template_guid mgmt_templates.template_guid%TYPE;
l_job_id RAW(16);
BEGIN
l_target_guid := mgmt_target.get_target_guid(target_name_in => p_target_name,
target_type_in => p_target_type);
l_template_guid := mgmt_template.get_template_guid(p_target_type => p_target_type,
p_template_name => p_template_name);
FOR tgt_cas IN (SELECT crit_action_job_id, warn_action_job_id,
info_action_job_id, policy_guid ,key_value, coll_name, key_operator
FROM mgmt_policy_assoc_cfg
WHERE object_guid = l_target_guid
ORDER BY policy_guid, coll_name, eval_order)
LOOP
BEGIN
mgmt_job_engine.create_templ_ca_from_target_ca
(p_template_name => p_template_name,
p_target_type => p_target_type,
p_source_ca_id => tgt_cas.info_action_job_id,
p_ca_job_id_out => l_job_id);
UPDATE mgmt_policy_assoc_cfg
SET info_action_job_id = l_job_id
WHERE object_guid = l_template_guid
AND policy_guid = tgt_cas.policy_guid
AND key_value = tgt_cas.key_value;
MGMT_JOB_ENGINE.increment_ca_ref_count(l_job_id);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
mgmt_job_engine.create_templ_ca_from_target_ca
(p_template_name => p_template_name,
p_target_type => p_target_type,
p_source_ca_id => tgt_cas.warn_action_job_id,
p_ca_job_id_out => l_job_id);
UPDATE mgmt_policy_assoc_cfg
SET warn_action_job_id = l_job_id
WHERE object_guid = l_template_guid
AND policy_guid = tgt_cas.policy_guid
AND key_value = tgt_cas.key_value;
MGMT_JOB_ENGINE.increment_ca_ref_count(l_job_id);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
BEGIN
mgmt_job_engine.create_templ_ca_from_target_ca
(p_template_name => p_template_name,
p_target_type => p_target_type,
p_source_ca_id => tgt_cas.crit_action_job_id,
p_ca_job_id_out => l_job_id);
UPDATE mgmt_policy_assoc_cfg
SET crit_action_job_id = l_job_id
WHERE object_guid = l_template_guid
AND policy_guid = tgt_cas.policy_guid
AND key_value = tgt_cas.key_value;
MGMT_JOB_ENGINE.increment_ca_ref_count(l_job_id);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END LOOP;
END create_cas;
-- Private API - filter_udm_tgts
-- IN Parameters
-- p_udm_list
-- p_udm_cred_list
-- OUT Parameters
-- p_invalid_udm_tgt_list
-- p_valid_udm_tgt_list
-- p_message_array
-- Description
-- Filter out all the valid AND invalid targets user provided in the credentail array.
PROCEDURE filter_udm_tgts( p_valid_tgt_list IN MGMT_TARGET_ARRAY,
p_udm_cred_list IN UDM_CRED_ARRAY,
p_invalid_udm_tgt_list OUT MGMT_TARGET_ARRAY,
p_valid_udm_tgt_list OUT UDM_CRED_ARRAY,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
IS
l_msg_cntr PLS_INTEGER := 0;
BEGIN
SELECT MGMT_TARGET_OBJ(target_name,
target_type)
BULK COLLECT INTO p_invalid_udm_tgt_list
FROM TABLE(CAST(p_udm_cred_list AS UDM_CRED_ARRAY))
WHERE (target_type, target_name)
NOT IN
(SELECT target_type, target_name
FROM TABLE(CAST(p_valid_tgt_list AS MGMT_TARGET_ARRAY)));
SELECT /*+ CARDINALITY(a 10) CARDINALITY(b 10) */ UDM_CRED_OBJ( a.target_name,
a.target_type,
a.collection_name,
a.username,
a.password)
BULK COLLECT INTO p_valid_udm_tgt_list
FROM TABLE(CAST(p_udm_cred_list AS UDM_CRED_ARRAY)) a,
TABLE(CAST(p_valid_tgt_list AS MGMT_TARGET_ARRAY)) b
WHERE a.target_type = b.target_type
AND a.target_name = b.target_name;
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception during filtering invalid targets in UDM credentials array: '||sqlerrm;
END filter_udm_tgts;
-- Private API - get_udms_for_monitoring_std
-- IN Parameters
-- p_cs_guid
-- p_target_type
-- OUT Parameters
-- COLLECTION_ARRAY
-- p_message_array
-- Description
-- Get all the UDMs (Collections) present in the database for that particular monitoring std.
FUNCTION get_udms_for_monitoring_std( p_cs_guid IN mgmt_templates.template_guid%TYPE,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
RETURN COLLECTION_ARRAY
IS
l_coll_name_array COLLECTION_ARRAY := COLLECTION_ARRAY();
l_msg_cntr PLS_INTEGER := 0;
BEGIN
-- Get all the UDMs (Collections) present in the database for that particular monitoring std.
SELECT COLLECTION_OBJ(mpa.coll_name,
to_char(add_or_delete),
mm.metric_name,
mm.metric_column)
BULK COLLECT INTO l_coll_name_array
FROM mgmt_policy_assoc mpa,
mgmt_metrics mm
WHERE mpa.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE --for template only
AND object_guid = p_cs_guid
AND mm.metric_column IN (STR_VALUE_PROP, NUM_VALUE_PROP)
AND mpa.policy_guid = mm.metric_guid;
RETURN l_coll_name_array;
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception during UDM fetching operation '||sqlerrm;
END get_udms_for_monitoring_std;
-- Private API - prepare_udm_cred
-- IN Parameters
-- p_udm_list
-- p_coll_array
-- OUT Parameters
-- MGMT_COLLECTION_CRED_ARRAY
-- p_message_array
-- Description
-- Prepare the MGMT_COLLECTION_CRED_ARRAY array required for passing into the apply monitoring std API.
-- This API will populate all the credentials required for the UDMs.
FUNCTION prepare_udm_cred(p_udm_list IN UDM_CRED_ARRAY,
p_coll_array IN MGMT_COLLECTION_CRED_ARRAY,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
RETURN MGMT_COLLECTION_CRED_ARRAY
IS
cred_count PLS_INTEGER := 0;
l_msg_cntr PLS_INTEGER := 0;
p_coll_temp_array MGMT_COLLECTION_CRED_ARRAY := MGMT_COLLECTION_CRED_ARRAY();
BEGIN
p_coll_temp_array := p_coll_array;
IF (p_coll_array IS NOT NULL AND p_coll_array.COUNT > 0)
THEN
FOR i IN 1..p_coll_array.COUNT
LOOP
p_coll_temp_array(i).credential := MGMT_CRED_RECORD(null, null, null);
p_coll_temp_array(i).credential.user_name := null;
IF (p_coll_temp_array(i).target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE)
THEN
p_coll_temp_array(i).credential.credential_set_name := HOST_UDM_CREDS_PROP;
ELSIF (p_coll_temp_array(i).target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE
OR p_coll_temp_array(i).target_type = MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE)
THEN
p_coll_temp_array(i).credential.credential_set_name := DB_CREDS_UDM_PROP;
END IF;
p_coll_temp_array(i).credential.pdp_data := null;
p_coll_temp_array(i).credential.creds := MGMT_CRED_ROW_ARRAY();
cred_count := 0;
IF (p_udm_list.COUNT >0 AND p_udm_list IS NOT NULL)
THEN
FOR j IN 1..p_udm_list.COUNT
LOOP
IF (p_udm_list(j).collection_name = p_coll_array(i).collection_name)
THEN
cred_count := cred_count + 1;
p_coll_temp_array(i).credential.creds.EXTEND;
p_coll_temp_array(i).credential.creds(cred_count) := MGMT_CRED_ROW_RECORD(USERNAME_PROP, p_udm_list(j).username);
cred_count := cred_count + 1;
p_coll_temp_array(i).credential.creds.EXTEND;
p_coll_temp_array(i).credential.creds(cred_count) := MGMT_CRED_ROW_RECORD(PASSWORD_PROP, p_udm_list(j).password);
EXIT;
END IF;
END LOOP;
END IF;
END LOOP;
END IF;
RETURN p_coll_temp_array;
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception during preparing UDM credentials: '||sqlerrm;
END prepare_udm_cred;
-- Private API - filter_udms
-- IN Parameters
-- p_valid_udm_tgt_list
-- p_udms_list
-- OUT Parameters
-- p_udm_invalid_list
-- p_udm_missing_list
-- p_udm_mfd_list
-- p_message_array
-- Description
-- Filter out all the invalid udms the user provided in the credentail array.
-- This includes:
-- list of udms which are invalid
-- list of udms not present in udm_list from db
-- udm list with MFD flag set
PROCEDURE filter_udms(p_valid_udm_tgt_list IN UDM_CRED_ARRAY,
p_udms_list IN COLLECTION_ARRAY,
p_udm_invalid_list OUT VARCHAR2_TABLE,
p_udm_missing_list OUT VARCHAR2_TABLE,
p_udm_mfd_list OUT VARCHAR2_TABLE,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
IS
l_msg_cntr PLS_INTEGER := 0;
BEGIN
-- This query will provide the list of udms which are invalid
SELECT collection_name
BULK COLLECT INTO p_udm_invalid_list
FROM TABLE(CAST(p_valid_udm_tgt_list AS UDM_CRED_ARRAY))
WHERE collection_name
NOT IN
(SELECT collection_name
FROM TABLE(CAST(p_udms_list AS COLLECTION_ARRAY)));
-- This query will provide the list of udms not present in udm_list from db
SELECT collection_name
BULK COLLECT INTO p_udm_missing_list
FROM TABLE(CAST(p_udms_list AS COLLECTION_ARRAY))
WHERE collection_name
NOT IN
(SELECT collection_name
FROM TABLE(CAST(p_valid_udm_tgt_list AS UDM_CRED_ARRAY)));
-- This query will provide the udm list with MFD flag set
SELECT collection_name
BULK COLLECT INTO p_udm_mfd_list
FROM TABLE(CAST(p_valid_udm_tgt_list AS UDM_CRED_ARRAY))
WHERE collection_name
IN
(SELECT collection_name
FROM TABLE(CAST(p_udms_list AS COLLECTION_ARRAY))
WHERE add_or_delete=1);
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception during filter out invalid UDMs: '||sqlerrm;
END filter_udms;
-- Private API - get_valid_udms
-- IN Parameters
-- p_valid_udm_tgt_list
-- p_udms_list
-- OUT Parameters
-- p_coll_array
-- p_udm_valid_list
-- p_message_array
-- Description
-- This API provides the actual udm list.
PROCEDURE get_valid_udms( p_valid_udm_tgt_list IN UDM_CRED_ARRAY,
p_udms_list IN COLLECTION_ARRAY,
p_coll_array OUT MGMT_COLLECTION_CRED_ARRAY,
p_udm_valid_list OUT UDM_CRED_ARRAY,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
IS
l_msg_cntr PLS_INTEGER := 0;
BEGIN
-- This query will provide the actual udm list
SELECT /*+ CARDINALITY(a 10) CARDINALITY(b 10) */ MGMT_COLLECTION_CRED_RECORD(a.target_name,
a.target_type,
b.metric_name,
b.metric_column,
a.collection_name,
null),
UDM_CRED_OBJ(a.target_name,
a.target_type,
a.collection_name,
a.username,
a.password)
BULK COLLECT INTO p_coll_array,
p_udm_valid_list
FROM TABLE(CAST(p_valid_udm_tgt_list AS UDM_CRED_ARRAY)) a,
TABLE(CAST(p_udms_list AS COLLECTION_ARRAY)) b
WHERE a.collection_name = b.collection_name
AND b.add_or_delete=0;
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception during filter out valid UDMs: '||sqlerrm;
END get_valid_udms;
-- Private API - populate_msg_array
-- IN Parameters
-- p_na_targets_list
-- p_invalid_targets_list
-- p_invalid_mode_targets_list
-- p_udm_invalid_list
-- p_udm_missing_list
-- p_udm_mfd_list
-- p_invalid_udm_tgt_list
-- p_invalid_priv_tgts
-- OUT Parameters
-- p_message_array
-- Description
-- This API populates the message array.
PROCEDURE populate_msg_array(p_na_targets_list IN MGMT_TARGET_ARRAY,
p_invalid_targets_list IN MGMT_TARGET_ARRAY,
p_invalid_mode_targets_list IN MGMT_TARGET_ARRAY,
p_udm_invalid_list IN VARCHAR2_TABLE,
p_udm_missing_list IN VARCHAR2_TABLE,
p_udm_mfd_list IN VARCHAR2_TABLE,
p_invalid_udm_tgt_list IN MGMT_TARGET_ARRAY,
p_invalid_priv_tgts IN MGMT_TARGET_ARRAY,
p_message_array IN OUT SMP_EMD_STRING_ARRAY)
IS
l_msg_cntr NUMBER := p_message_array.COUNT;
BEGIN
IF ( p_na_targets_list IS NOT NULL AND p_na_targets_list.COUNT > 0)
THEN
FOR i in 1..p_na_targets_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Target '||p_na_targets_list(i).target_name||':'||p_na_targets_list(i).target_type||' is not applicable';
END LOOP;
END IF;
IF ( p_invalid_targets_list IS NOT NULL AND p_invalid_targets_list.COUNT > 0)
THEN
FOR i in 1..p_invalid_targets_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Target '||p_invalid_targets_list(i).target_name||':'||p_invalid_targets_list(i).target_type||' is invalid';
END LOOP;
END IF;
IF ( p_invalid_mode_targets_list IS NOT NULL AND p_invalid_mode_targets_list.COUNT > 0)
THEN
FOR i in 1..p_invalid_mode_targets_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Target '||p_invalid_mode_targets_list(i).target_name||':'||p_invalid_targets_list(i).target_type||' is in invalid mode';
END LOOP;
END IF;
IF ( p_udm_invalid_list IS NOT NULL AND p_udm_invalid_list.COUNT > 0)
THEN
FOR i in 1..p_udm_invalid_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'UDM '||p_udm_invalid_list(i)||' is not present in the repository.';
END LOOP;
END IF;
IF ( p_udm_missing_list IS NOT NULL AND p_udm_missing_list.COUNT > 0)
THEN
FOR i in 1..p_udm_missing_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'UDM '||p_udm_missing_list(i)||' is missing in the UDM List.';
END LOOP;
END IF;
IF ( p_udm_mfd_list IS NOT NULL AND p_udm_mfd_list.COUNT > 0)
THEN
FOR i in 1..p_udm_mfd_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'UDM '||p_udm_mfd_list(i)||' is marked for delete in the monitoring template.';
END LOOP;
END IF;
IF ( p_invalid_udm_tgt_list IS NOT NULL AND p_invalid_udm_tgt_list.COUNT > 0)
THEN
FOR i in 1..p_invalid_udm_tgt_list.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Target '||p_invalid_udm_tgt_list(i).target_name||':'||p_invalid_udm_tgt_list(i).target_type||' is invalid in the UDM Cred List.';
END LOOP;
END IF;
IF ( p_invalid_priv_tgts IS NOT NULL AND p_invalid_priv_tgts.COUNT > 0)
THEN
FOR i in 1..p_invalid_priv_tgts.COUNT
LOOP
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'User does not have proper privelege on target or the target does not exist: '||p_invalid_priv_tgts(i).target_name||':'||p_invalid_priv_tgts(i).target_type||'.';
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception during populating the msg array: '||sqlerrm;
END;
-- Private API - get_targets_with_privs
-- IN Parameters
-- p_priv_name
-- p_targets
-- OUT Parameters
-- MGMT_TARGET_ARRAY
-- Description
-- This API returns targets list with valid input priv.
FUNCTION get_targets_with_privs( p_priv_name IN VARCHAR2,
p_targets IN MGMT_TARGET_ARRAY)
RETURN MGMT_TARGET_ARRAY
IS
TYPE tgtrefcur IS REF CURSOR;
l_tgtcur tgtrefcur;
p_tmp_tgts MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_tgt_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
l_user_name VARCHAR2(100) := mgmt_user.get_current_em_user;
BEGIN
-- Convert the p_targets into Guids
SELECT /*+ CARDINALITY(t 50) */ mt.target_guid
BULK COLLECT INTO l_tgt_guids
FROM mgmt_targets mt,
TABLE(CAST(p_targets AS MGMT_TARGET_ARRAY)) t
WHERE mt.target_name = t.target_name
AND mt.target_type = t.target_type;
l_tgtcur := MGMT_USER.HAS_PRIV( user_name_in => l_user_name,
priv_name_in => p_priv_name,
target_guids_in => l_tgt_guids);
l_tgt_guids.DELETE;
LOOP
FETCH l_tgtcur
BULK COLLECT INTO l_tgt_guids;
EXIT WHEN l_tgtcur%NOTFOUND;
END LOOP;
IF (l_tgt_guids IS NOT NULL AND l_tgt_guids.COUNT > 0)
THEN
SELECT /*+ CARDINALITY(tgts 50) */ MGMT_TARGET_OBJ(target_name, target_type)
BULK COLLECT INTO p_tmp_tgts
FROM mgmt_targets mt,
TABLE(CAST(l_tgt_guids AS MGMT_USER_GUID_ARRAY)) tgts
WHERE mt.target_guid = tgts.column_value;
END IF;
RETURN p_tmp_tgts;
END get_targets_with_privs;
-- Private API - get_targets_with_privs
-- IN Parameters
-- p_priv_name
-- p_targets
-- OUT Parameters
-- p_valid_targets_out
-- p_invalid_targets_out
-- Description
-- This API returns targets list with valid AND invalid input priv.
PROCEDURE get_targets_with_privs( p_priv_name IN VARCHAR2,
p_targets_in IN MGMT_TARGET_ARRAY,
p_valid_targets_out OUT MGMT_TARGET_ARRAY,
p_invalid_targets_out OUT MGMT_TARGET_ARRAY)
IS
BEGIN
p_valid_targets_out := get_targets_with_privs( p_priv_name => p_priv_name,
p_targets => p_targets_in);
SELECT /*+ CARDINALITY(tgtslistin 10) */ MGMT_TARGET_OBJ(target_name, target_type)
BULK COLLECT INTO p_invalid_targets_out
FROM TABLE(CAST(p_targets_in AS MGMT_TARGET_ARRAY)) tgtslistin
WHERE tgtslistin.target_name IS NOT NULL
AND tgtslistin.target_type IS NOT NULL
AND NOT EXISTS ( SELECT /*+ CARDINALITY(validtgts 10) */ '1'
FROM TABLE(CAST(p_valid_targets_out AS MGMT_TARGET_ARRAY)) validtgts
WHERE validtgts.target_name = tgtslistin.target_name
AND validtgts.target_type = tgtslistin.target_type
AND validtgts.target_name IS NOT NULL
AND validtgts.target_type IS NOT NULL);
END get_targets_with_privs;
-- Public API - create_template
-- IN Parameters
-- p_target_type
-- p_source_target_name
-- p_template_name
-- p_description
-- p_is_out_of_box
-- OUT Parameters
-- p_message_array
-- p_return_status - 1 => success
-- 0 => failure
-- Description
-- This API copies all the metric settings of the source target and creates a monitoring template
-- with the name provided.
PROCEDURE create_template( p_target_type IN VARCHAR2,
p_source_target_name IN VARCHAR2,
p_template_name IN VARCHAR2,
p_description IN VARCHAR2 DEFAULT '',
p_is_out_of_box IN NUMBER DEFAULT 0,
p_message_array OUT SMP_EMD_STRING_ARRAY,
p_return_status OUT NUMBER)
IS
l_metric_list MGMT_MNTR_METRIC_ARRAY := MGMT_MNTR_METRIC_ARRAY();
l_policy_list MGMT_MNTR_POLICY_ARRAY := MGMT_MNTR_POLICY_ARRAY();
l_collection_list MGMT_MNTR_COLLECTION_ARRAY := MGMT_MNTR_COLLECTION_ARRAY();
l_temp_coll_list MGMT_MNTR_COLLECTION_ARRAY := MGMT_MNTR_COLLECTION_ARRAY();
l_access_list MGMT_TEMPLATE_ACCESS_ARRAY := MGMT_TEMPLATE_ACCESS_ARRAY();
flag NUMBER := 0;
l_msg_cntr PLS_INTEGER := 0;
l_new_cntr PLS_INTEGER := 0;
l_priv NUMBER := 0;
l_desc mgmt_templates.description%TYPE;
BEGIN
p_return_status := 0;
p_message_array := SMP_EMD_STRING_ARRAY();
SELECT COUNT(*)
INTO flag
FROM mgmt_targets
WHERE target_name = p_source_target_name
AND target_type = p_target_type;
IF (flag > 0) THEN
l_priv := MGMT_USER.HAS_PRIV( user_name_in => MGMT_USER.get_current_em_user,
priv_name_in => 'VIEW_TARGET',
target_name_in => p_source_target_name,
target_type_in => p_target_type);
IF (l_priv = MGMT_USER.USER_DOES_NOT_HAVE_PRIV)
THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'User does not have required privilege on the target '
||p_source_target_name||':'||p_target_type;
p_return_status := 0;
ELSE
copy_target_settings(p_target_type => p_target_type,
p_source_target_name => p_source_target_name,
p_metric_list => l_metric_list,
p_policy_list => l_policy_list,
p_collection_list => l_collection_list,
p_message_array => p_message_array);
IF (p_is_out_of_box = 1)
THEN
l_desc := 'This is a Public Monitoring Template. '||p_description;
l_access_list.EXTEND;
l_access_list(1) := MGMT_TEMPLATE_ACCESS
('PUBLIC',
MGMT_USER.VIEW_TEMPLATE);
ELSE
l_desc := p_description;
l_access_list := NULL;
END IF;
IF (l_collection_list IS NOT NULL AND l_collection_list.COUNT > 0)
THEN
FOR i IN 1..l_collection_list.COUNT
LOOP
IF (l_collection_list(i).metric_list IS NOT NULL AND l_collection_list(i).METRIC_LIST.COUNT > 0)
THEN
FOR j IN 1..l_collection_list(i).metric_list.COUNT
LOOP
l_new_cntr := l_new_cntr + 1;
l_temp_coll_list.EXTEND;
l_temp_coll_list(l_new_cntr) := mgmt_mntr_collection(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
l_temp_coll_list(l_new_cntr).is_enabled := l_collection_list(i).is_enabled ;
l_temp_coll_list(l_new_cntr).is_transposed := l_collection_list(i).is_transposed ;
l_temp_coll_list(l_new_cntr).store_metric := l_collection_list(i).store_metric ;
l_temp_coll_list(l_new_cntr).upload_frequency := l_collection_list(i).upload_frequency ;
l_temp_coll_list(l_new_cntr).schedule_ex := l_collection_list(i).schedule_ex ;
l_temp_coll_list(l_new_cntr).schedule := l_collection_list(i).schedule ;
l_temp_coll_list(l_new_cntr).metric_list := mgmt_coll_metric_array();
l_temp_coll_list(l_new_cntr).metric_list.extend;
l_temp_coll_list(l_new_cntr).metric_list(1) := mgmt_coll_metric(NULL, NULL, NULL);
IF (l_temp_coll_list(l_new_cntr).is_transposed = 0)
THEN
l_temp_coll_list(l_new_cntr).coll_name := l_collection_list(i).metric_list(j).metric_name;
ELSE
l_temp_coll_list(l_new_cntr).coll_name := l_collection_list(i).coll_name;
END IF;
l_temp_coll_list(l_new_cntr).metric_list(1).metric_name := l_collection_list(i).metric_list(j).metric_name ;
l_temp_coll_list(l_new_cntr).metric_list(1).property_list := l_collection_list(i).metric_list(j).property_list;
l_temp_coll_list(l_new_cntr).metric_list(1).credentials := l_collection_list(i).metric_list(j).credentials;
END LOOP;
ELSE
l_new_cntr := l_new_cntr + 1;
l_temp_coll_list.EXTEND;
l_temp_coll_list(l_new_cntr) := mgmt_mntr_collection(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
l_temp_coll_list(l_new_cntr).is_enabled := l_collection_list(i).is_enabled ;
l_temp_coll_list(l_new_cntr).is_transposed := l_collection_list(i).is_transposed ;
l_temp_coll_list(l_new_cntr).store_metric := l_collection_list(i).store_metric ;
l_temp_coll_list(l_new_cntr).upload_frequency := l_collection_list(i).upload_frequency ;
l_temp_coll_list(l_new_cntr).schedule_ex := l_collection_list(i).schedule_ex ;
l_temp_coll_list(l_new_cntr).schedule := l_collection_list(i).schedule ;
l_temp_coll_list(l_new_cntr).coll_name := l_collection_list(i).coll_name;
l_temp_coll_list(l_new_cntr).metric_list := mgmt_coll_metric_array();
l_temp_coll_list(l_new_cntr).metric_list.extend;
l_temp_coll_list(l_new_cntr).metric_list(1) := mgmt_coll_metric(NULL, NULL, NULL);
END IF;
END LOOP;
END IF;
-- Invoke the mgmt_template.create_template SDK API with the above parameters.
mgmt_template.create_template( p_target_type => p_target_type,
p_template_name => p_template_name,
p_description => l_desc,
p_is_public => p_is_out_of_box,
p_metric_list => l_metric_list,
p_policy_list => l_policy_list,
p_collection_list => l_temp_coll_list,
p_access_list => l_access_list);
-- Call the Create CA to add the CAs from the source target to the created template.
create_cas(p_template_name => p_template_name,
p_target_name => p_source_target_name,
p_target_type => p_target_type);
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Monitoring Template '||p_template_name||
' created successfully';
p_return_status := 1;
END IF;
ELSE
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Target '||p_source_target_name||':'||p_target_type||
' is invalid. Creation of Monitoring Template aborted.';
p_return_status := 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_return_status := 0;
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception During Create '||sqlerrm||
'.Creation of Monitoring Template aborted.';
END create_template;
-- Public API - apply_template
-- IN Parameters
-- p_target_type
-- p_template_name
-- p_apply_option
-- p_dest_targets_list
-- p_udm_cred_array
-- OUT Parameters
-- p_message_array
-- p_return_status - 1 => success
-- 0 => failure
-- Description
-- This API applies the monitoring std to the list of valid targets from the list of targets
-- specified by the user.
PROCEDURE apply_template( p_template_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_apply_option IN NUMBER := 2,
p_dest_targets_list IN MGMT_TARGET_ARRAY,
p_udm_cred_array IN UDM_CRED_ARRAY DEFAULT NULL,
p_message_array OUT SMP_EMD_STRING_ARRAY,
p_return_status OUT NUMBER)
IS
l_valid_targets MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_invalid_priv_tgts MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_valid_tgts_list MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_na_targets_list MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_invalid_targets_list MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_invalid_udm_tgt_list MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_invalid_mode_targets_list MGMT_TARGET_ARRAY := MGMT_TARGET_ARRAY();
l_udms_list COLLECTION_ARRAY := COLLECTION_ARRAY();
l_coll_array MGMT_COLLECTION_CRED_ARRAY := MGMT_COLLECTION_CRED_ARRAY();
l_udm_invalid_list VARCHAR2_TABLE := VARCHAR2_TABLE();
l_udm_missing_list VARCHAR2_TABLE := VARCHAR2_TABLE();
l_udm_valid_list UDM_CRED_ARRAY := UDM_CRED_ARRAY();
l_udm_mfd_list VARCHAR2_TABLE := VARCHAR2_TABLE();
l_udm_cred_list MGMT_COLLECTION_CRED_ARRAY := MGMT_COLLECTION_CRED_ARRAY();
l_valid_udm_tgt_list UDM_CRED_ARRAY := UDM_CRED_ARRAY();
l_apply_option_array MGMT_INTEGER_ARRAY :=MGMT_INTEGER_ARRAY();
l_msg_cntr PLS_INTEGER := 0;
l_is_out_of_box mgmt_templates.is_public%TYPE;
l_std_guid mgmt_templates.template_guid%TYPE;
flag NUMBER := 0;
l_priv VARCHAR2(10) := NULL;
BEGIN
p_return_status := 0;
p_message_array := SMP_EMD_STRING_ARRAY();
BEGIN
l_std_guid := mgmt_template.get_template_guid( p_target_type => p_target_type,
p_template_name => p_template_name);
flag := mgmt_user.has_priv(mgmt_user.get_current_em_user,
mgmt_user.VIEW_TEMPLATE,
l_std_guid);
IF (flag = 1)
THEN
-- When the control comes here, it is assumed that the supplied std AND target type combination is a valid one.
IF (p_dest_targets_list IS NOT NULL AND p_dest_targets_list.COUNT > 0)
THEN
-- So process this before calling the get_targets_info.
-- So, what we send to get_targets_info is a valid tgt list, atleast from the
-- user model perspective. For apply / synchronize operation, the logged-in user needs to have
-- atleast MANAGE_TARGET_METRICS priv on all the destination targets.
get_targets_with_privs(MGMT_USER.OPERATOR_TARGET,
p_dest_targets_list,
l_valid_targets,
l_invalid_priv_tgts);
--Give a call to get_targets_info AND get the list of valid AND invalid targets.
mgmt_template.get_targets_info
(
p_template_target_type => p_target_type,
p_tgt_name_type_list => l_valid_targets,
p_targets_list => l_valid_tgts_list,
p_na_targets_list => l_na_targets_list,
p_invalid_targets_list => l_invalid_targets_list,
p_invalid_mode_targets_list => l_invalid_mode_targets_list
);
-- filter invalid targets from UDM cred list
filter_udm_tgts(l_valid_tgts_list,
p_udm_cred_array,
l_invalid_udm_tgt_list,
l_valid_udm_tgt_list,
p_message_array);
-- Before processing the UDMs Cred list, get all the UDMs present in this monitoring standard.
l_udms_list := get_udms_for_monitoring_std( l_std_guid,
p_message_array);
-- filter invalid udms from UDM cred list
filter_udms(l_valid_udm_tgt_list,
l_udms_list,
l_udm_invalid_list,
l_udm_missing_list,
l_udm_mfd_list,
p_message_array);
get_valid_udms( l_valid_udm_tgt_list,
l_udms_list,
l_coll_array,
l_udm_valid_list,
p_message_array);
l_udm_cred_list := prepare_udm_cred(l_udm_valid_list,
l_coll_array,
p_message_array);
populate_msg_array(l_na_targets_list,
l_invalid_targets_list,
l_invalid_mode_targets_list,
l_udm_invalid_list,
l_udm_missing_list,
l_udm_mfd_list,
l_invalid_udm_tgt_list,
l_invalid_priv_tgts,
p_message_array);
-- Call the apply monitoring template mgmt API
IF (l_valid_tgts_list.COUNT > 0)
THEN
l_apply_option_array.EXTEND;
l_apply_option_array(1) := p_apply_option;
mgmt_template.apply_template(p_template_name => p_template_name,
p_target_type => p_target_type,
p_destination_list => l_valid_tgts_list,
p_copy_common_only_flags => l_apply_option_array,
p_coll_creds => l_udm_cred_list);
FOR i in 1..l_valid_tgts_list.COUNT
LOOP
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Apply Operation has been successfully submitted for '||
l_valid_tgts_list(i).target_type||':'||
l_valid_tgts_list(i).target_name;
END LOOP;
p_return_status := 1;
ELSE
p_return_status := 0;
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'All the targets provided in the list are invalid. Apply Operation Aborted.';
END IF;
ELSE
p_return_status := 0;
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Destination targets list is empty. Apply Operation Aborted.';
END IF;
ELSE
p_return_status := 0;
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'User does not have necessary privilege to apply the Monitoring Template :'||p_template_name||'. Apply Operation Aborted.';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_return_status := 0;
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Monitoring Template '||p_template_name||':'||p_target_type||' does not exist. Apply Operation Aborted.';
WHEN OTHERS THEN
l_msg_cntr := p_message_array.COUNT;
p_message_array.EXTEND;
l_msg_cntr := l_msg_cntr + 1;
p_message_array(l_msg_cntr) := 'Exception '||sqlerrm;
p_return_status := 0;
END;
END apply_template;
END em_template;
/
show errors