Rem drv: Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/policy/policy_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/2 2009/04/27 22:10:50 bram Exp $ Rem Rem policy_pkgbody.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem policy_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem bram 04/24/09 - Backport jsadras_rfi_backport_6686041_10.2.0.5 Rem from st_emcore_10.2.0.1.0 Rem jsadras 05/05/08 - bug:7010693, sec risk in delete policy Rem rrawat 07/24/07 - Backport rrawat_bug-5357750 from main Rem rrawat 07/12/07 - Bug-5357750 Rem dgiaimo 05/03/07 - Fixing bug 6030187 Rem dgiaimo 05/18/07 - Backport dgiaimo_bug-6030187 from main Rem groyal 08/05/06 - fixing get_post_exemp info Rem sthiruna 07/28/06 - Copying 10.2.0.3 Config Standard changes from Rem EMCORE_MAIN_LINUX Rem pratagar 01/09/06 - Delete UDM and 2 Col SQL UDM Support. Rem pratagar 07/25/06 - Backport pratagar_bug-4653111 from main Rem rpinnama 07/26/06 - Backport rpinnama_bug-5098097 from main Rem rpinnama 07/10/06 - Bug 5098097 : Fix CA upgrade issue in meta_ver Rem update callback Rem neearora 03/29/06 - Bug 5108394. Adding rowset handler for deleting Rem rows from MGMT_POLICY_ASSOC_CFG and Rem MGMT_POLICY_ASSOC_CFG_PARAMS Rem neearora 07/16/06 - Backport neearora_bug-5108394 from main Rem pmodi 12/01/05 - Backport pmodi_bug-4640899 from main Rem aragarwa 04/06/06 - Create CS policy only for policy_type=2. Rem kchiasso 02/23/06 - change owner to author Rem niramach 01/25/06 - Update the create_cs_policy procedure. Rem niramach 01/11/06 - 10.3 Work Start: Add create_cs_policy procedure. Rem pmodi 11/21/05 - Bug:4740910 - Call proc for deletion Rem pmodi 11/21/05 - Bug:4640899 - Remove object_type from query Rem pmodi 12/01/05 - Backport pmodi_bug-4740910 from main Rem pmodi 11/21/05 - Bug:4740910 - Call proc for deletion Rem rpinnama 09/08/05 - Fix 4591107 : Use different name if templ and Rem tgt CA names are same but not equivalent. Rem rpinnama 09/01/05 - Add delete_policy_data API Rem jsadras 08/25/05 - add more diagnostic messages for tracking policy Rem association errors Rem jsadras 08/18/05 - add category prop change support Rem jsadras 08/07/05 - add condition to update_policy Rem jsadras 08/02/05 - Bug:4529040, clear alerts on key exceptions Rem jsadras 07/29/05 - Rem rpinnama 07/27/05 - Fix 4495845 : Support a proc to remove CA Rem associations Rem rpinnama 07/26/05 - Fix 4504659 : Special case transposed metrics in Rem get_config_key_details call Rem jsadras 07/14/05 - null threshold Rem scgrover 07/07/05 - add extended sql trace Rem rzazueta 07/06/05 - Fix 4435559 Rem gsbhatia 07/01/05 - New repmgr header impl Rem rpinnama 06/06/05 - Support default CAs Rem pmodi 06/10/05 - Bug:4406767 -New proc for key-val deletion Rem rpinnama 06/22/05 - Fix 4448414 : Copy target CAs properly to Rem template copy Rem rpinnama 06/15/05 - Fix 4340418: Re-associate policies on detecting Rem version change Rem rpinnama 05/23/05 - Create target CA from target type CA Rem rpinnama 05/23/05 - Change the key default to ' ' instead of '%' Rem rpinnama 05/19/05 - Support CA copy from default to target Rem niramach 05/13/05 - clear_expired_suppressions is moved from em_policy_ui Rem to em_policy since it is no longer called from UI code. Rem rpinnama 04/15/05 - Add dump routines Rem groyal 02/16/05 - Fix problem with compliance score computation Rem rpinnama 03/14/05 - Replace ALL_ZERO_GUID with target_type guid Rem jaysmith 03/08/05 - catch missing policy in get_config_key_details Rem rpinnama 02/23/05 - Use separate coll name for src and dest. Rem niramach 02/04/05 - exempt->suppress conversions. Rem jsadras 02/09/05 - check condition operator Rem rpinnama 01/10/05 - Copy only repository policies Rem rpinnama 01/06/05 - Add target type addition callback Rem skini 12/16/04 - Create CA with correct initial refcounts Rem skini 11/30/04 - Fix get_config_key_details for thresholds Rem rpinnama 12/02/04 - Add support for clear_message and Rem clear_message_nlsid Rem rpatti 11/29/04 - fix copy_object_policy_assoc Rem rpinnama 11/22/04 - Add policy type to assoc table Rem rpinnama 11/18/04 - Add support for UDM Rem jsadras 10/19/04 - policy_timing Rem rpinnama 10/18/04 - Start eval order from 0. Rem groyal 10/13/04 - Move ui procedures to policy_ui_pkgbody Rem rpinnama 10/10/04 - Replace template_ca with mntr_ca. Rem jsadras 10/07/04 - max(importance) Rem rpinnama 10/06/04 - Add remove API for policy eval tables Rem rpinnama 10/05/04 - For zero key metrics, support ' ', 0 for all Rem else case Rem rpinnama 10/01/04 - Rem groyal 09/29/04 - Update clear_expired_exemptions Rem rpinnama 09/30/04 - Add copy_object_policy_assoc Rem rpinnama 09/28/04 - Support CAs Rem rpinnama 09/23/04 - Rename template_override as prevent_override Rem shuberma 09/20/04 - Adding clear_expired_exemptions. Rem shuberma 09/03/04 - Add refresh_policy_eval_summ. Rem rpinnama 09/02/04 - Add get_config_key_details procedure Rem rpinnama 08/27/04 - Add add_target/delete_target callbacks Rem rpinnama 08/19/04 - Support is_push Rem rpinnama 08/16/04 - Add APIs to update policy eval details Rem rpinnama 08/12/04 - Provide update* API Rem rpinnama 08/11/04 - Make simultaction NUMBER. Rem rpinnama 08/04/04 - Add corrective action API Rem rpinnama 07/28/04 - Add more internal APIs Rem rpinnama 07/26/04 - rpinnama_add_policy_api Rem rpinnama 07/23/04 - Created Rem CREATE OR REPLACE PACKAGE BODY em_policy AS -- Some constants that define the default buckets for compliance score calculation. CRITICAL_HIGH_LOW CONSTANT INTEGER := 0; CRITICAL_HIGH_HIGH CONSTANT INTEGER := 25; CRITICAL_MEDIUM_LOW CONSTANT INTEGER := 26; CRITICAL_MEDIUM_HIGH CONSTANT INTEGER := 50; CRITICAL_LOW_LOW CONSTANT INTEGER := 51; CRITICAL_LOW_HIGH CONSTANT INTEGER := 75; WARNING_HIGH_LOW CONSTANT INTEGER := 66; WARNING_HIGH_HIGH CONSTANT INTEGER := 75; WARNING_MEDIUM_LOW CONSTANT INTEGER := 76; WARNING_MEDIUM_HIGH CONSTANT INTEGER := 85; WARNING_LOW_LOW CONSTANT INTEGER := 86; WARNING_LOW_HIGH CONSTANT INTEGER := 95; INFORMATION_HIGH_LOW CONSTANT INTEGER := 95; INFORMATION_HIGH_HIGH CONSTANT INTEGER := 96; INFORMATION_MEDIUM_LOW CONSTANT INTEGER := 97; INFORMATION_MEDIUM_HIGH CONSTANT INTEGER := 98; INFORMATION_LOW_LOW CONSTANT INTEGER := 99; INFORMATION_LOW_HIGH CONSTANT INTEGER := 99; PROCEDURE add_policy( p_policy_guid IN RAW, p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_metric_guid IN RAW, p_start_type_meta_ver IN VARCHAR2 DEFAULT '1.0', p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL, p_policy_type IN NUMBER DEFAULT 1, p_policy_label_nlsid IN VARCHAR2 DEFAULT NULL, p_description IN VARCHAR2 DEFAULT NULL, p_description_nlsid IN VARCHAR2 DEFAULT NULL, p_author IN VARCHAR2 DEFAULT 'ORACLE', p_auto_enable IN NUMBER DEFAULT 0, p_cs_consider_percentage IN NUMBER DEFAULT 0, p_impact IN VARCHAR2 DEFAULT NULL, p_impact_nlsid IN VARCHAR2 DEFAULT NULL, p_recommendation IN VARCHAR2 DEFAULT NULL, p_recommendation_nlsid IN VARCHAR2 DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL, p_condition_type IN NUMBER DEFAULT 1, p_condition IN VARCHAR2 DEFAULT NULL, p_condition_operator IN VARCHAR2 DEFAULT NULL, p_detailed_url_link IN VARCHAR2 DEFAULT NULL, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL, p_owner IN VARCHAR2 DEFAULT '', p_last_updated_by IN VARCHAR2 DEFAULT NULL) IS l_owner mgmt_policies.owner%TYPE; BEGIN l_owner := NVL(p_owner, ''); INSERT INTO mgmt_policies ( policy_guid, policy_name, metric_guid, target_type, start_type_meta_ver, end_type_meta_ver, policy_type, policy_label_nlsid, description, description_nlsid, author, auto_enable, cs_consider_percentage, impact, impact_nlsid, recommendation, recommendation_nlsid, violation_level, condition_type, condition, condition_operator, detailed_url_link, message, message_nlsid, clear_message, clear_message_nlsid, owner, created_date, last_updated_date, last_updated_by ) VALUES ( p_policy_guid, p_policy_name, p_metric_guid, p_target_type, p_start_type_meta_ver, p_end_type_meta_ver, p_policy_type, p_policy_label_nlsid, p_description, p_description_nlsid, p_author, p_auto_enable, p_cs_consider_percentage, p_impact, p_impact_nlsid, p_recommendation, p_recommendation_nlsid, p_violation_level, p_condition_type, p_condition, p_condition_operator, p_detailed_url_link, p_message, p_message_nlsid, p_clear_message, p_clear_message_nlsid, l_owner, SYSDATE, SYSDATE, p_last_updated_by ); END add_policy; PROCEDURE update_policy( p_policy_guid IN RAW, p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_metric_guid IN RAW DEFAULT NULL, p_start_type_meta_ver IN VARCHAR2 DEFAULT NULL, p_end_type_meta_ver IN VARCHAR2 DEFAULT NULL, p_policy_type IN NUMBER DEFAULT NULL, p_policy_label_nlsid IN VARCHAR2 DEFAULT NULL, p_description IN VARCHAR2 DEFAULT NULL, p_description_nlsid IN VARCHAR2 DEFAULT NULL, p_author IN VARCHAR2 DEFAULT NULL, p_auto_enable IN NUMBER DEFAULT NULL, p_cs_consider_percentage IN NUMBER DEFAULT NULL, p_impact IN VARCHAR2 DEFAULT NULL, p_impact_nlsid IN VARCHAR2 DEFAULT NULL, p_recommendation IN VARCHAR2 DEFAULT NULL, p_recommendation_nlsid IN VARCHAR2 DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL, p_condition_type IN NUMBER DEFAULT NULL, p_condition IN VARCHAR2 DEFAULT NULL, p_condition_operator IN VARCHAR2 DEFAULT NULL, p_detailed_url_link IN VARCHAR2 DEFAULT NULL, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL, p_owner IN VARCHAR2 DEFAULT NULL, p_last_updated_by IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_policies SET metric_guid = NVL(p_metric_guid, metric_guid), start_type_meta_ver = NVL(p_start_type_meta_ver, start_type_meta_ver), end_type_meta_ver = NVL(p_end_type_meta_ver, end_type_meta_ver), policy_type = NVL(p_policy_type, policy_type), policy_label_nlsid = NVL(p_policy_label_nlsid, policy_label_nlsid), description = NVL(p_description, description), description_nlsid = NVL(p_description_nlsid, description_nlsid), author = NVL(p_author, author), auto_enable = NVL(p_auto_enable, auto_enable), cs_consider_percentage = NVL(p_cs_consider_percentage, cs_consider_percentage), impact = NVL(p_impact, impact), impact_nlsid = NVL(p_impact_nlsid, impact_nlsid), recommendation = NVL(p_recommendation, recommendation), recommendation_nlsid = NVL(p_recommendation_nlsid, recommendation_nlsid), violation_level = NVL(p_violation_level, violation_level), condition = NVL(p_condition, condition), condition_type = NVL(p_condition_type, condition_type), condition_operator = NVL(p_condition_operator, condition_operator), detailed_url_link = NVL(p_detailed_url_link, detailed_url_link), message = NVL(p_message, message), message_nlsid = NVL(p_message_nlsid, message_nlsid), clear_message = NVL(p_clear_message, clear_message), clear_message_nlsid = NVL(p_clear_message_nlsid, clear_message_nlsid), owner = NVL(p_owner, owner), last_updated_by = NVL(p_last_updated_by, last_updated_by) WHERE policy_guid = p_policy_guid; END update_policy; PROCEDURE remove_policy( p_policy_guid IN RAW) IS BEGIN DELETE FROM mgmt_policies WHERE policy_guid = p_policy_guid; END remove_policy; -- Policy parameter API PROCEDURE add_policy_parameter(p_policy_guid IN RAW, p_param_name IN VARCHAR2, p_param_name_nlsid IN VARCHAR2, p_param_type IN NUMBER DEFAULT 0) IS BEGIN INSERT INTO mgmt_policy_parameters (policy_guid, param_name, param_name_nlsid, param_type) VALUES (p_policy_guid, p_param_name, p_param_name_nlsid, p_param_type); END add_policy_parameter; PROCEDURE update_policy_parameter(p_policy_guid IN RAW, p_param_name IN VARCHAR2, p_param_name_nlsid IN VARCHAR2 DEFAULT NULL, p_param_type IN NUMBER DEFAULT NULL) IS BEGIN UPDATE mgmt_policy_parameters SET param_name_nlsid = NVL(p_param_name_nlsid, param_name_nlsid), param_type = NVL(p_param_type, param_type) WHERE policy_guid = p_policy_guid AND param_name = p_param_name; END update_policy_parameter; PROCEDURE remove_policy_parameter( p_policy_guid IN RAW, p_param_name IN VARCHAR2) IS BEGIN DELETE FROM mgmt_policy_parameters WHERE policy_guid = p_policy_guid AND param_name = p_param_name; END remove_policy_parameter; PROCEDURE remove_policy_parameters( p_policy_guid IN RAW, p_param_name_list IN MGMT_POLICY_PARAM_DEF_ARRAY DEFAULT NULL) IS BEGIN IF ( (p_param_name_list IS NOT NULL) AND (p_param_name_list.COUNT > 0) ) THEN FOR param_ctr IN p_param_name_list.FIRST..p_param_name_list.LAST LOOP remove_policy_parameter(p_policy_guid, p_param_name_list(param_ctr).param_name); END LOOP; ELSE DELETE FROM mgmt_policy_parameters WHERE policy_guid = p_policy_guid; END IF; END remove_policy_parameters; PROCEDURE add_policy_bind_var(p_policy_guid IN RAW, p_bind_column_name IN VARCHAR2, p_bind_column_type IN NUMBER DEFAULT 1) IS BEGIN INSERT INTO mgmt_policy_bind_vars (policy_guid, bind_column_name, bind_column_type) VALUES (p_policy_guid, p_bind_column_name, p_bind_column_type); END add_policy_bind_var; PROCEDURE remove_policy_bind_vars(p_policy_guid IN RAW) IS BEGIN DELETE FROM mgmt_policy_bind_vars WHERE policy_guid = p_policy_guid; END remove_policy_bind_vars; -- Violation context definition API PROCEDURE add_viol_ctxt_def(p_policy_guid IN RAW, p_column_name IN VARCHAR2, p_metric_guid IN RAW, p_column_position IN NUMBER DEFAULT 0, p_is_hidden IN NUMBER DEFAULT 0, p_url_link_type IN NUMBER DEFAULT 0, p_url_link_template IN VARCHAR2 DEFAULT NULL) IS BEGIN INSERT INTO mgmt_policy_viol_ctxt_def (policy_guid, column_name, metric_guid, column_position, is_hidden, url_link_type, url_link_template) VALUES (p_policy_guid, p_column_name, p_metric_guid, p_column_position, p_is_hidden, p_url_link_type, p_url_link_template); END add_viol_ctxt_def; PROCEDURE update_viol_ctxt_def(p_policy_guid IN RAW, p_column_name IN VARCHAR2, p_metric_guid IN RAW DEFAULT NULL, p_column_position IN NUMBER DEFAULT NULL, p_is_hidden IN NUMBER DEFAULT NULL, p_url_link_type IN NUMBER DEFAULT NULL, p_url_link_template IN VARCHAR2 DEFAULT NULL) IS BEGIN -- Note: Metric GUID cannot be updated. -- As metric_guid is derived from metric_column. UPDATE mgmt_policy_viol_ctxt_def SET column_position = NVL(p_column_position, column_position), is_hidden = NVL(p_is_hidden, is_hidden), url_link_type = NVL(p_url_link_type, url_link_type), url_link_template = NVL(p_url_link_template, url_link_template) WHERE policy_guid = p_policy_guid AND column_name = p_column_name; END update_viol_ctxt_def; PROCEDURE remove_viol_ctxt_defs(p_policy_guid IN RAW) IS BEGIN DELETE FROM mgmt_policy_viol_ctxt_def WHERE policy_guid = p_policy_guid; END remove_viol_ctxt_defs; PROCEDURE add_policy_type_version(p_policy_guid IN RAW, p_type_meta_ver IN VARCHAR2) IS BEGIN INSERT INTO mgmt_policy_type_versions (policy_guid, type_meta_ver) VALUES (p_policy_guid, p_type_meta_ver); END add_policy_type_version; -- Deletes one or all the policy type version rows for a given policy PROCEDURE remove_policy_type_version(p_policy_guid IN RAW, p_type_meta_ver IN VARCHAR2 DEFAULT NULL) IS BEGIN DELETE FROM mgmt_policy_type_versions WHERE policy_guid = p_policy_guid AND type_meta_ver = NVL(p_type_meta_ver, type_meta_ver); END remove_policy_type_version; PROCEDURE add_policy_assoc(p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_object_type IN NUMBER DEFAULT 1, p_policy_type IN NUMBER DEFAULT 1, p_is_enabled IN NUMBER DEFAULT 1, p_add_or_delete IN NUMBER DEFAULT 0) IS BEGIN INSERT INTO mgmt_policy_assoc (object_guid, policy_guid, coll_name, object_type, policy_type, is_enabled, add_or_delete) VALUES (p_object_guid, p_policy_guid, NVL(p_coll_name, ' '), p_object_type, p_policy_type, p_is_enabled, p_add_or_delete); END add_policy_assoc; PROCEDURE update_policy_assoc(p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_is_enabled IN NUMBER DEFAULT NULL) IS BEGIN UPDATE mgmt_policy_assoc SET is_enabled = NVL(p_is_enabled, is_enabled) WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name); END update_policy_assoc; PROCEDURE remove_policy_assocs(p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL) IS BEGIN DELETE FROM mgmt_policy_assoc WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name); END remove_policy_assocs; PROCEDURE add_policy_assoc_cfg( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT 0, p_eval_order IN NUMBER DEFAULT 1, p_is_exception IN NUMBER DEFAULT 0, p_has_active_baseline IN NUMBER DEFAULT 0, p_prevent_override IN NUMBER DEFAULT 0, p_crit_action_job_id IN RAW DEFAULT NULL, p_warn_action_job_id IN RAW DEFAULT NULL, p_info_action_job_id IN RAW DEFAULT NULL, p_fixit_job IN VARCHAR2 DEFAULT NULL, p_simultaneous_actions IN NUMBER DEFAULT 1, p_importance IN NUMBER DEFAULT NULL, p_num_occurrences IN NUMBER DEFAULT 1, p_is_push IN NUMBER DEFAULT 0, p_condition_operator IN NUMBER DEFAULT 0, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL) IS BEGIN INSERT INTO mgmt_policy_assoc_cfg (object_guid, policy_guid, coll_name, 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) VALUES (p_object_guid, p_policy_guid, NVL(p_coll_name, ' '), NVL(p_key_value, ' '), p_key_operator, p_eval_order, p_is_exception, p_has_active_baseline, p_prevent_override, p_crit_action_job_id, p_warn_action_job_id, p_info_action_job_id, p_fixit_job, p_simultaneous_actions, p_importance, p_num_occurrences, p_is_push, p_condition_operator, p_message, p_message_nlsid, p_clear_message, p_clear_message_nlsid); IF p_is_exception = MGMT_GLOBAL.G_TRUE THEN EM_SEVERITY.clear_excepted_key (p_target_guid=>p_object_guid, p_policy_guid=>p_policy_guid, p_cfg_coll_name=>p_coll_name, p_cfg_key_value=>p_key_value, p_cfg_key_operator=>p_key_operator ) ; END IF ; END add_policy_assoc_cfg; PROCEDURE update_policy_assoc_cfg( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT 0, p_eval_order IN NUMBER DEFAULT NULL, p_is_exception IN NUMBER DEFAULT NULL, p_has_active_baseline IN NUMBER DEFAULT NULL, p_prevent_override IN NUMBER DEFAULT NULL, p_crit_action_job_id IN RAW DEFAULT NULL, p_warn_action_job_id IN RAW DEFAULT NULL, p_info_action_job_id IN RAW DEFAULT NULL, p_fixit_job IN VARCHAR2 DEFAULT NULL, p_simultaneous_actions IN NUMBER DEFAULT NULL, p_importance IN NUMBER DEFAULT NULL, p_num_occurrences IN NUMBER DEFAULT NULL, p_is_push IN NUMBER DEFAULT NULL, p_condition_operator IN NUMBER DEFAULT NULL, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_policy_assoc_cfg SET eval_order = NVL(p_eval_order, eval_order), is_exception = NVL(p_is_exception, is_exception), has_active_baseline = NVL(p_has_active_baseline, has_active_baseline), prevent_override = NVL(p_prevent_override, prevent_override), crit_action_job_id = NVL(p_crit_action_job_id, crit_action_job_id), warn_action_job_id = NVL(p_warn_action_job_id, warn_action_job_id), info_action_job_id = NVL(p_info_action_job_id, info_action_job_id), fixit_job = NVL(p_fixit_job, fixit_job), simultaneous_actions = NVL(p_simultaneous_actions, simultaneous_actions), importance = NVL(p_importance, importance), num_occurrences = NVL(p_num_occurrences, num_occurrences), is_push = NVL(p_is_push, is_push), condition_operator = NVL(p_condition_operator, condition_operator), message = NVL(p_message, message), message_nlsid = NVL(p_message_nlsid, message_nlsid), clear_message = NVL(p_clear_message, clear_message), clear_message_nlsid = NVL(p_clear_message_nlsid, clear_message_nlsid) WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name AND key_value = p_key_value AND key_operator = p_key_operator; END update_policy_assoc_cfg; PROCEDURE remove_policy_assoc_cas( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_key_value IN VARCHAR2 DEFAULT NULL, p_key_operator IN NUMBER DEFAULT NULL) IS l_update_rec NUMBER := 0; TYPE job_ids IS TABLE OF mgmt_job.job_id%TYPE ; l_crit_job_ids job_ids ; l_warn_job_ids job_ids ; l_info_job_ids job_ids ; BEGIN -- if policy_guid is NULL , it means all associations for the policy -- if object_guid is NULL , it means all associations for the object -- if both object/policy are not null, then only for the particular -- object policy IF p_policy_guid IS NOT NULL AND p_object_guid IS NOT NULL THEN SELECT crit_action_job_id,warn_action_job_id,info_action_job_id BULK COLLECT INTO l_crit_job_ids,l_warn_job_ids,l_info_job_ids FROM mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name) AND key_value = NVL(p_key_value, key_value) AND key_operator = NVL(p_key_operator, key_operator) AND ( crit_action_job_id IS NOT NULL OR warn_action_job_id IS NOT NULL OR info_action_job_id IS NOT NULL ) ORDER BY coll_name, eval_order ; ELSIF p_object_guid IS NOT NULL THEN SELECT crit_action_job_id,warn_action_job_id,info_action_job_id BULK COLLECT INTO l_crit_job_ids,l_warn_job_ids,l_info_job_ids FROM mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND ( crit_action_job_id IS NOT NULL OR warn_action_job_id IS NOT NULL OR info_action_job_id IS NOT NULL ) ORDER BY coll_name, eval_order ; ELSIF p_policy_guid IS NOT NULL THEN SELECT crit_action_job_id,warn_action_job_id,info_action_job_id BULK COLLECT INTO l_crit_job_ids,l_warn_job_ids,l_info_job_ids FROM mgmt_policy_assoc_cfg WHERE policy_guid = p_policy_guid AND ( crit_action_job_id IS NOT NULL OR warn_action_job_id IS NOT NULL OR info_action_job_id IS NOT NULL ) ORDER BY coll_name, eval_order ; END IF ; IF l_crit_job_ids IS NULL OR l_crit_job_ids.COUNT = 0 THEN RETURN ; END IF ; -- Iterate through the policy assocs FOR i IN l_crit_job_ids.FIRST..l_crit_job_ids.LAST LOOP IF (l_crit_job_ids(i) IS NOT NULL) THEN MGMT_JOB_ENGINE.decrement_ca_ref_count(l_crit_job_ids(i)) ; END IF; IF (l_warn_job_ids(i) IS NOT NULL) THEN MGMT_JOB_ENGINE.decrement_ca_ref_count(l_warn_job_ids(i)) ; END IF; IF (l_info_job_ids(i) IS NOT NULL) THEN MGMT_JOB_ENGINE.decrement_ca_ref_count(l_info_job_ids(i)) ; END IF; END LOOP; -- This update is not required. -- When the CA reference counter becomes zero, the CA is deleted -- and the references are cleared by calling handle_delete_ca -- policy_guid IS set as NULL only from target delete, in which case -- we will delete the record anyway, so no need to update IF p_policy_guid IS NOT NULL AND p_object_guid IS NOT NULL THEN UPDATE mgmt_policy_assoc_cfg SET crit_action_job_id = NULL, warn_action_job_id = NULL, info_action_job_id = NULL WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name) AND key_value = NVL(p_key_value, key_value) AND key_operator = NVL(p_key_operator, key_operator) AND ( crit_action_job_id IS NOT NULL OR warn_action_job_id IS NOT NULL OR info_action_job_id IS NOT NULL ) ; END IF ; END remove_policy_assoc_cas; PROCEDURE remove_policy_assoc_cfgs( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_key_value IN VARCHAR2 DEFAULT NULL, p_key_operator IN NUMBER DEFAULT NULL) IS BEGIN DELETE FROM mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name) AND key_value = NVL(p_key_value, key_value) AND key_operator = NVL(p_key_operator, key_operator); END remove_policy_assoc_cfgs; PROCEDURE add_policy_assoc_cfg_param( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT 0, p_param_name IN VARCHAR2 DEFAULT ' ', p_crit_threshold IN VARCHAR2 DEFAULT ' ', p_warn_threshold IN VARCHAR2 DEFAULT ' ', p_info_threshold IN VARCHAR2 DEFAULT ' ') IS BEGIN INSERT INTO mgmt_policy_assoc_cfg_params (object_guid, policy_guid, coll_name, key_value, key_operator, param_name, crit_threshold, warn_threshold, info_threshold) VALUES (p_object_guid, p_policy_guid, NVL(p_coll_name, ' '), NVL(p_key_value, ' '), p_key_operator, p_param_name, p_crit_threshold, p_warn_threshold, p_info_threshold); END add_policy_assoc_cfg_param; PROCEDURE update_policy_assoc_cfg_param( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT NULL, p_param_name IN VARCHAR2 DEFAULT NULL, p_crit_threshold IN VARCHAR2 DEFAULT NULL, p_warn_threshold IN VARCHAR2 DEFAULT NULL, p_info_threshold IN VARCHAR2 DEFAULT NULL) IS BEGIN UPDATE mgmt_policy_assoc_cfg_params SET crit_threshold = p_crit_threshold, warn_threshold = p_warn_threshold, info_threshold = p_info_threshold WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name AND key_value = p_key_value AND key_operator = p_key_operator; END update_policy_assoc_cfg_param; PROCEDURE remove_policy_assoc_cfg_params( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_key_value IN VARCHAR2 DEFAULT NULL, p_key_operator IN NUMBER DEFAULT NULL, p_param_name IN VARCHAR2 DEFAULT NULL) IS BEGIN DELETE FROM mgmt_policy_assoc_cfg_params WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name) AND key_value = NVL(p_key_value, key_value) AND key_operator = NVL(p_key_operator, key_operator) AND param_name = NVL(p_param_name, param_name); END remove_policy_assoc_cfg_params; -- COMPLIANCE_SCORE function calculates the compliance score given the violation_level, number of non-exempt violations, -- total number of rows and the importance. These values are the first 4 arguments. Other arguments are optimal and are used -- to adjust the 9 bucket sizes. -- FUNCTION compliance_score( p_max_violation_level INTEGER, -- MGMT_GLOBAL.CRITICAL, WARNING, INFORMATIONAL p_non_exempt_violations_logged INTEGER, -- 0-? p_rows_evaluated INTEGER, p_importance INTEGER := MGMT_GLOBAL.G_IMPORTANCE_NORMAL, p_chl INTEGER := CRITICAL_HIGH_LOW, -- 1-100 p_chh INTEGER := CRITICAL_HIGH_HIGH , -- 1-100 p_cml INTEGER := CRITICAL_MEDIUM_LOW , -- 1-100 p_cmh INTEGER := CRITICAL_MEDIUM_HIGH, -- 1-100 p_cll INTEGER := CRITICAL_LOW_LOW, -- 1-100 p_clh INTEGER := CRITICAL_LOW_HIGH, -- 1-100 p_whl INTEGER := WARNING_HIGH_LOW, -- 1-100 p_whh INTEGER := WARNING_HIGH_HIGH, -- 1-100 p_wml INTEGER := WARNING_MEDIUM_LOW, -- 1-100 p_wmh INTEGER := WARNING_MEDIUM_HIGH, -- 1-100 p_wll INTEGER := WARNING_LOW_LOW, -- 1-100 p_wlh INTEGER := WARNING_LOW_HIGH, -- 1-100 p_ihl INTEGER := INFORMATION_HIGH_LOW, -- 1-100 p_ihh INTEGER := INFORMATION_HIGH_HIGH , -- 1-100 p_iml INTEGER := INFORMATION_MEDIUM_LOW , -- 1-100 p_imh INTEGER := INFORMATION_MEDIUM_HIGH, -- 1-100 p_ill INTEGER := INFORMATION_LOW_LOW, -- 1-100 p_ilh INTEGER := INFORMATION_LOW_HIGH -- 1-100 ) RETURN INTEGER IS type t_hilo is varray(2) of integer; type t_lookuprow is varray(3) of t_hilo; type t_lookuptab is varray(3) of t_lookuprow; -- Setup the lookup table using the bucket ranges passed in. l_thetab t_lookuptab := t_lookuptab(t_lookuprow(t_hilo(p_ihl, p_ihh), t_hilo(p_iml,p_imh), t_hilo(p_ill, p_ilh)), t_lookuprow(t_hilo(p_whl, p_whh), t_hilo(p_wml, p_wmh), t_hilo(p_wll, p_wlh)), t_lookuprow(t_hilo(p_chl, p_chh), t_hilo(p_cml, p_cmh), t_hilo(p_cll, p_clh))); l_fraction NUMBER; l_hirange INTEGER; l_lorange INTEGER; l_severity INTEGER := p_max_violation_level; l_importance INTEGER := p_importance; BEGIN -- Cannot assume any mapping between these globals and indexes in table. IF p_max_violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL THEN l_severity := 1; ELSE IF p_max_violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING THEN l_severity := 2; ELSE IF p_max_violation_level > 3 THEN l_severity := 3; END IF; END IF; END IF; IF p_importance = MGMT_GLOBAL.G_IMPORTANCE_LOW THEN l_importance := 3; ELSE IF p_importance = MGMT_GLOBAL.G_IMPORTANCE_NORMAL THEN l_importance := 2; ELSE l_importance := 1; -- HIGH END IF; END IF; -- Get the high and low ends of the bucket given the severity and the importance indexes. l_hirange := l_thetab(l_severity)(l_importance)(2); l_lorange := l_thetab(l_severity)(l_importance)(1); -- If there are zero non exempt violations then 100 percent compliant. IF p_non_exempt_violations_logged = 0 then return 100; end if; -- If the high and low ends of the range is the same then use this value -- as the score IF l_hirange = l_lorange then return l_hirange; end if; -- If for some reason, there are fewer rows than violations, then assume 1 to 1 (100percent violating) IF p_rows_evaluated < p_non_exempt_violations_logged then l_fraction := 1; ELSE l_fraction := p_non_exempt_violations_logged /p_rows_evaluated; end if; -- Return score. RETURN l_hirange - (l_hirange-l_lorange)*l_fraction; END compliance_score; -- get_post_exempt_info -- -- Purpose: -- This procedure returns the compliance score, non_exempt_violations and -- max violations level to reflect changes in exemptions. -- -- Parameters -- p_target_guid : part of ID of eval summ -- p_policy_guid : part of ID of eval summ -- p_call_name : part of ID of eval summ. -- p_rows_evaluated : if known, the number of rows evaluated. Otherwise null. -- p_importance : if known, the importance of the policy, otherwise null. -- p_max_violation_level -- p_non_exempt_violations_logged -- p_compliance_score -- -- Exceptions -- None expected. -- Notes: -- PROCEDURE get_post_exempt_info( p_target_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 := ' ', p_rows_evaluated IN NUMBER := NULL, p_importance IN NUMBER := NULL, p_max_violation_level OUT NUMBER, p_non_exempt_violations_logged OUT NUMBER, p_compliance_score OUT NUMBER ) AS l_importance NUMBER := p_importance; l_rows_evaluated NUMBER := p_rows_evaluated; BEGIN -- Get the number of non-exempt violations and max level given the target-policy pair. If -- There are non, then max violation level is clear and count is 0. SELECT NVL(MAX(violation_level),MGMT_GLOBAL.G_SEVERITY_CLEAR), COUNT(*) INTO p_max_violation_level, p_non_exempt_violations_logged FROM mgmt_current_violation WHERE exempt_code = MGMT_GLOBAL.G_SUPPRESS_NONE AND target_guid = p_target_guid AND policy_guid = p_policy_guid; -- Look up the importance for this association. If association doesn't -- exist, assume normal. IF l_importance IS NULL THEN BEGIN SELECT max(importance) INTO l_importance FROM mgmt_policy_assoc_cfg WHERE is_exception = MGMT_GLOBAL.G_FALSE AND object_guid = p_target_guid AND policy_guid = p_policy_guid; EXCEPTION WHEN NO_DATA_FOUND THEN l_importance := MGMT_GLOBAL.G_IMPORTANCE_NORMAL; END; END IF; -- Get the rows evaluated from the eval summary. If for some reason there are -- no rows, then no need to update. BEGIN IF l_rows_evaluated IS NULL THEN SELECT rows_evaluated INTO l_rows_evaluated FROM mgmt_policy_assoc_eval_summ WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name; END IF; p_compliance_score := COMPLIANCE_SCORE( p_max_violation_level=>p_max_violation_level, p_non_exempt_violations_logged=>p_non_exempt_violations_logged, p_rows_evaluated=>l_rows_evaluated, p_importance=>l_importance ); EXCEPTION WHEN OTHERS THEN NULL; END; RETURN; END get_post_exempt_info; -- update_policy_eval_summ -- -- Purpose -- Update the policy evaluation summary -- PROCEDURE update_policy_eval_summ( p_target_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_evaluation_date IN DATE DEFAULT NULL, p_rows_evaluated IN NUMBER DEFAULT NULL, p_violations_logged IN NUMBER DEFAULT NULL, p_non_exempt_violations IN NUMBER DEFAULT NULL, p_max_violation_level IN NUMBER DEFAULT NULL, p_compliance_score IN NUMBER DEFAULT NULL) IS l_non_exempt_violations NUMBER; l_max_violation_level NUMBER; l_compliance_score NUMBER; BEGIN IF p_non_exempt_violations IS NULL OR p_max_violation_level IS NULL OR p_compliance_score IS NULL THEN get_post_exempt_info( p_target_guid=>p_target_guid, p_policy_guid=>p_policy_guid, p_coll_name=>p_coll_name, p_rows_evaluated=>p_rows_evaluated, p_max_violation_level=>l_max_violation_level, p_non_exempt_violations_logged=>l_non_exempt_violations, p_compliance_score=>l_compliance_score ); END IF; UPDATE mgmt_policy_assoc_eval_summ SET last_evaluation_date = NVL(p_evaluation_date, SYSDATE), rows_evaluated = NVL(p_rows_evaluated, 0), total_violations_logged = NVL(p_violations_logged, total_violations_logged), non_exempt_violations_logged = NVL(p_non_exempt_violations, l_non_exempt_violations), max_violation_level = NVL(p_max_violation_level, l_max_violation_level), compliance_score = NVL(p_compliance_score, l_compliance_score) WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name ; IF SQL%NOTFOUND THEN INSERT INTO mgmt_policy_assoc_eval_summ (target_guid, policy_guid, coll_name, last_evaluation_date, rows_evaluated, total_violations_logged, non_exempt_violations_logged, max_violation_level, compliance_score) values (p_target_guid,p_policy_guid,p_coll_name, p_evaluation_date, p_rows_evaluated, p_violations_logged, NVL(p_non_exempt_violations, l_non_exempt_violations), NVL(p_max_violation_level, l_max_violation_level), NVL(p_compliance_score, l_compliance_score)); END IF; END update_policy_eval_summ; PROCEDURE remove_policy_eval_summ( p_target_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('remove_policy_eval_summ:Enter',G_MODULE_NAME); END IF ; DELETE FROM mgmt_policy_assoc_eval_summ WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('remove_policy_eval_summ:Exit Deleted='||SQL%ROWCOUNT, G_MODULE_NAME); END IF ; END remove_policy_eval_summ; -- update_compliance_score -- -- Purpose: -- This procedure updates the compliance score, non_exempt_violations and -- max violations level to reflect changes in exemptions. Compliance score is -- only calculated for the target-policy pair and not on individual columns, so -- col_name is ' '. -- -- Parameters -- p_target_guid : part of ID of eval summ -- p_policy_guid : part of ID of eval summ -- -- Exceptions -- None expected. -- Notes: -- PROCEDURE update_compliance_score( p_target_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 := ' ' ) AS l_max_violation_level NUMBER; l_non_exempt_violations_logged NUMBER; l_importance NUMBER; l_row_to_update ROWID; l_rows_evaluated NUMBER; l_compliance_Score NUMBER; BEGIN BEGIN get_post_exempt_info( p_target_guid=>p_target_guid, p_policy_guid=>p_policy_guid, p_coll_name=>p_coll_name, p_max_violation_level=>l_max_violation_level, p_non_exempt_violations_logged=>l_non_exempt_violations_logged, p_compliance_score=>l_compliance_score ); UPDATE mgmt_policy_assoc_eval_summ SET max_violation_level = l_max_violation_level, non_exempt_violations_logged = l_non_exempt_violations_logged, compliance_score = l_compliance_score WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name; EXCEPTION WHEN OTHERS THEN NULL; END; END update_compliance_score; PROCEDURE update_policy_eval_detail( p_target_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_result_key_value IN VARCHAR2 DEFAULT ' ', p_crit_counter IN NUMBER DEFAULT NULL, p_warn_counter IN NUMBER DEFAULT NULL, p_info_counter IN NUMBER DEFAULT NULL, p_evaluation_date IN DATE DEFAULT NULL, p_last_error_message IN VARCHAR DEFAULT NULL, p_cfg_key_value IN VARCHAR2 DEFAULT ' ', p_cfg_key_operator IN NUMBER DEFAULT 0) IS BEGIN UPDATE mgmt_policy_assoc_eval_details SET crit_counter = NVL(p_crit_counter, crit_counter), warn_counter = NVL(p_warn_counter, warn_counter), info_counter = NVL(p_info_counter, info_counter), last_error_message = NVL(p_last_error_message, last_error_message), last_evaluation_date = p_evaluation_date WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name AND result_key_value = p_result_key_value; IF SQL%NOTFOUND THEN INSERT INTO mgmt_policy_assoc_eval_details (target_guid, policy_guid, coll_name, result_key_value, crit_counter,warn_counter,info_counter, last_evaluation_date,last_error_message, cfg_key_value, cfg_key_operator) VALUES (p_target_guid, p_policy_guid, p_coll_name, p_result_key_value, NVL(p_crit_counter, 0), NVL(p_warn_counter, 0), NVL(p_info_counter, 0), p_evaluation_date, p_last_error_message, p_cfg_key_value, p_cfg_key_operator); END IF; END update_policy_eval_detail; -- Removes policy eval details PROCEDURE remove_policy_eval_details( p_target_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_result_key_value IN VARCHAR2 DEFAULT NULL) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('remove_policy_eval_details:Enter key='|| p_result_key_value, G_MODULE_NAME); END IF ; DELETE FROM mgmt_policy_assoc_eval_details WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name) AND result_key_value = NVL(p_result_key_value, result_key_value); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('remove_policy_eval_details:Exit Deleted='||SQL%ROWCOUNT, G_MODULE_NAME); END IF ; END remove_policy_eval_details; PROCEDURE add_composite_key_entry( p_object_guid IN RAW, p_composite_key_guid IN RAW, p_key_part1_value IN VARCHAR2, p_key_part2_value IN VARCHAR2, p_key_part3_value IN VARCHAR2, p_key_part4_value IN VARCHAR2, p_key_part5_value IN VARCHAR2) IS BEGIN UPDATE mgmt_metrics_composite_keys SET key_part1_value = p_key_part1_value, key_part2_value = p_key_part2_value, key_part3_value = p_key_part3_value, key_part4_value = p_key_part4_value, key_part5_value = p_key_part5_value WHERE target_guid = p_object_guid AND composite_key = p_composite_key_guid; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO mgmt_metrics_composite_keys (target_guid, composite_key, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value) VALUES (p_object_guid, p_composite_key_guid, p_key_part1_value, p_key_part2_value, p_key_part3_value, p_key_part4_value, p_key_part5_value); END IF; END add_composite_key_entry; PROCEDURE add_composite_key_entry( p_object_guid IN RAW, p_composite_key_guid IN RAW, p_key_part_list IN mgmt_medium_string_array DEFAULT NULL) IS l_key_part1_value mgmt_metrics_composite_keys.key_part1_value%TYPE; l_key_part2_value mgmt_metrics_composite_keys.key_part2_value%TYPE; l_key_part3_value mgmt_metrics_composite_keys.key_part3_value%TYPE; l_key_part4_value mgmt_metrics_composite_keys.key_part4_value%TYPE; l_key_part5_value mgmt_metrics_composite_keys.key_part5_value%TYPE; BEGIN IF (p_key_part_list.EXISTS(1)) THEN l_key_part1_value := p_key_part_list(1); END IF; IF (p_key_part_list.EXISTS(2)) THEN l_key_part2_value := p_key_part_list(2); END IF; IF (p_key_part_list.EXISTS(3)) THEN l_key_part3_value := p_key_part_list(3); END IF; IF (p_key_part_list.EXISTS(4)) THEN l_key_part4_value := p_key_part_list(4); END IF; IF (p_key_part_list.EXISTS(5)) THEN l_key_part5_value := p_key_part_list(5); END IF; add_composite_key_entry(p_object_guid, p_composite_key_guid, l_key_part1_value, l_key_part2_value, l_key_part3_value, l_key_part4_value, l_key_part5_value); END add_composite_key_entry; PROCEDURE get_composite_key_parts( p_object_guid IN RAW, p_composite_key_guid IN RAW, p_key_part_list OUT NOCOPY mgmt_medium_string_array) IS BEGIN p_key_part_list := mgmt_medium_string_array(); p_key_part_list.extend(5); BEGIN SELECT key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value INTO p_key_part_list(1), p_key_part_list(2), p_key_part_list(3), p_key_part_list(4), p_key_part_list(5) FROM mgmt_metrics_composite_keys WHERE target_guid = p_object_guid AND composite_key = p_composite_key_guid; EXCEPTION WHEN NO_DATA_FOUND THEN p_key_part_list(1) := NULL; p_key_part_list(2) := NULL; p_key_part_list(3) := NULL; p_key_part_list(4) := NULL; p_key_part_list(5) := NULL; END; END get_composite_key_parts; PROCEDURE copy_composite_key_entry(p_src_object_guid IN RAW, p_dest_object_guid IN RAW, p_composite_key_guid IN RAW) IS l_key_part1_value mgmt_metrics_composite_keys.key_part1_value%TYPE; l_key_part2_value mgmt_metrics_composite_keys.key_part2_value%TYPE; l_key_part3_value mgmt_metrics_composite_keys.key_part3_value%TYPE; l_key_part4_value mgmt_metrics_composite_keys.key_part4_value%TYPE; l_key_part5_value mgmt_metrics_composite_keys.key_part5_value%TYPE; l_key_found NUMBER; BEGIN l_key_found := 0; BEGIN SELECT key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value INTO l_key_part1_value, l_key_part2_value, l_key_part3_value, l_key_part4_value, l_key_part5_value FROM mgmt_metrics_composite_keys WHERE target_guid = p_src_object_guid AND composite_key = p_composite_key_guid; l_key_found := 1; EXCEPTION WHEN NO_DATA_FOUND THEN l_key_found := 0; END; IF (l_key_found > 0) THEN add_composite_key_entry(p_dest_object_guid, p_composite_key_guid, l_key_part1_value, l_key_part2_value, l_key_part3_value, l_key_part4_value, l_key_part5_value); END IF; END copy_composite_key_entry; PROCEDURE add_object_policy_assoc( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_object_type IN NUMBER DEFAULT 1, p_policy_type IN NUMBER DEFAULT 1, p_is_enabled IN NUMBER DEFAULT 1, p_policy_val_list IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL, p_add_or_delete IN NUMBER DEFAULT 0, p_metric_info IN EM_METRIC.METRIC_INFO_REC DEFAULT NULL) IS l_key_val MGMT_POLICY_KEY_VAL; l_param_val MGMT_POLICY_PARAM_VAL; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_key_part_list mgmt_medium_string_array; l_metric_info EM_METRIC.METRIC_INFO_REC; l_key_value mgmt_policy_assoc_cfg.key_value%TYPE; l_key_operator mgmt_policy_assoc_cfg.key_operator%TYPE; l_proc_name CONSTANT VARCHAR2(30) := 'add_object_policy_assoc' ; l_crit_job_id MGMT_JOB.job_id%TYPE; l_warn_job_id MGMT_JOB.job_id%TYPE; l_info_job_id MGMT_JOB.job_id%TYPE; l_add_or_delete mgmt_policy_assoc.add_or_delete%TYPE; has_prevent_override NUMBER; l_prevent_override NUMBER DEFAULT 0; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Adding assoc for object = ' || p_object_guid || ' Policy = ' || p_policy_guid, G_MODULE_NAME) ; END IF ; -- see if metric information is passed, if so use it IF p_metric_info.metric_guid IS NOT NULL THEN l_metric_guid := p_metric_info.metric_guid ; l_metric_info := p_metric_info ; END IF ; l_add_or_delete := p_add_or_delete; -- Object Type 2 is for Target. IF (p_add_or_delete = MGMT_GLOBAL.G_TRUE AND p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN IF ((p_policy_val_list IS NOT NULL) AND (p_policy_val_list.COUNT > 0) ) THEN has_prevent_override := 0; FOR key_ctr IN p_policy_val_list.FIRST..p_policy_val_list.LAST LOOP l_key_val := p_policy_val_list(key_ctr); IF (l_key_val.prevent_override = 1) THEN has_prevent_override := 1; EXIT; END IF; END LOOP; IF (has_prevent_override = 0) THEN EMD_MNTR.delete_udm (p_target_guid => p_object_guid, p_metric_guid => p_policy_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name => p_coll_name ); RETURN; ELSE l_add_or_delete := MGMT_GLOBAL.G_FALSE; -- As prevent override flag for a key in a UDM is high. --We can not delete it. Reset the p_add_or_delete to 0. END IF; END IF; END IF; EM_POLICY.add_policy_assoc( p_object_guid => p_object_guid, p_policy_guid => p_policy_guid, p_coll_name => p_coll_name, p_object_type => p_object_type, p_policy_type => p_policy_type, p_is_enabled => p_is_enabled, p_add_or_delete => l_add_or_delete); IF (p_policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC) THEN IF p_metric_info.metric_guid IS NULL THEN -- For metric thresholds, get the table metric guid from the metric column guid SELECT metric_guid INTO l_metric_guid FROM mgmt_metrics m WHERE (target_type,metric_name) IN (SELECT target_type, metric_name FROM mgmt_metrics WHERE metric_guid = p_policy_guid AND ROWNUM = 1) AND metric_column = ' ' AND ROWNUM = 1 ; END IF ; ELSE -- For policies, get the metric guid on which the policy is defined. SELECT metric_guid INTO l_metric_guid FROM mgmt_policies WHERE policy_guid = p_policy_guid; END IF; -- Get key details of the metric IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN IF p_metric_info.metric_guid IS NULL THEN EM_METRIC.get_metric_info_for_target(l_metric_guid, p_object_guid, l_metric_info); END IF ; ELSE EM_METRIC.get_metric_info(l_metric_guid, l_metric_info); END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Got metric_info for metric = ' || l_metric_info.metric_name, G_MODULE_NAME) ; END IF ; IF ((p_policy_val_list IS NOT NULL) AND (p_policy_val_list.COUNT > 0) ) THEN FOR key_ctr IN p_policy_val_list.FIRST..p_policy_val_list.LAST LOOP l_key_val := p_policy_val_list(key_ctr); l_key_operator := 0; get_key_details(l_key_val.key_value, l_metric_info, l_key_value, l_key_part_list, l_key_operator); IF ( (l_metric_info.num_keys > 1) AND (l_key_value <> ' ') ) THEN add_composite_key_entry(p_object_guid, l_key_value, l_key_part_list); END IF; IF p_policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC AND l_key_val.condition_operator IS NULL THEN l_key_val.condition_operator := MGMT_GLOBAL.G_THRESHOLD_GT ; END IF; -- -- Get corrective action IDs. -- l_info_job_id := get_ca_id(p_ca_name => l_key_val.info_action_name, p_object_guid => p_object_guid, p_object_type => p_object_type, p_incr_ref => MGMT_GLOBAL.G_TRUE); l_warn_job_id := get_ca_id(p_ca_name => l_key_val.warn_action_name, p_object_guid => p_object_guid, p_object_type => p_object_type, p_incr_ref => MGMT_GLOBAL.G_TRUE); l_crit_job_id := get_ca_id(p_ca_name => l_key_val.crit_action_name, p_object_guid => p_object_guid, p_object_type => p_object_type, p_incr_ref => MGMT_GLOBAL.G_TRUE); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Adding policy assoc cfg for ' || ' order = ' || (key_ctr -1) || ' object = ' || p_object_guid || ' policy = ' || p_policy_guid || ' coll = [' || p_coll_name || '] key_val = [' || l_key_value || '] oper = ' || l_key_operator, G_MODULE_NAME) ; END IF ; IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE) THEN l_prevent_override := 0; ELSE l_prevent_override := l_key_val.prevent_override; END IF; -- Start eval order from 0, as that how agent sends it EM_POLICY.add_policy_assoc_cfg( p_object_guid => p_object_guid, p_policy_guid => p_policy_guid, p_coll_name => p_coll_name, p_key_value => l_key_value, p_key_operator => l_key_operator, p_eval_order => (key_ctr -1), p_is_exception => l_key_val.is_exception, p_has_active_baseline => 0, -- By default, we will not have baselines.. p_prevent_override => l_prevent_override, p_crit_action_job_id => l_crit_job_id, p_warn_action_job_id => l_warn_job_id, p_info_action_job_id => l_info_job_id, p_fixit_job => l_key_val.agent_fixit, p_simultaneous_actions => l_key_val.simultaneous_actions, p_importance => l_key_val.importance, p_num_occurrences => l_key_val.num_occurrences, p_is_push => l_key_val.is_push, p_condition_operator => l_key_val.condition_operator, p_message => l_key_val.message, p_message_nlsid => l_key_val.message_nlsid, p_clear_message => l_key_val.clear_message, p_clear_message_nlsid => l_key_val.clear_message_nlsid); IF (l_key_val.param_values IS NOT NULL) AND (l_key_val.param_values.COUNT > 0) THEN FOR param_ctr IN l_key_val.param_values.FIRST..l_key_val.param_values.LAST LOOP l_param_val := l_key_val.param_values(param_ctr); EM_POLICY.add_policy_assoc_cfg_param( p_object_guid => p_object_guid, p_policy_guid => p_policy_guid, p_coll_name => p_coll_name, p_key_value => l_key_value, p_key_operator => l_key_operator, p_param_name => l_param_val.param_name, p_crit_threshold => l_param_val.crit_threshold, p_warn_threshold => l_param_val.warn_threshold, p_info_threshold => l_param_val.info_threshold); END LOOP; END IF; END LOOP; END IF; END add_object_policy_assoc; PROCEDURE copy_all_policy_assocs( p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dest_object_guid IN RAW, p_dest_object_type IN NUMBER, p_ca_creds IN MGMT_MNTR_CA_ARRAY DEFAULT NULL) IS l_proc_name VARCHAR2(32) := 'copy_all_policy_assocs'; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Enter', G_MODULE_NAME) ; EMDW_LOG.DEBUG(l_proc_name || ' Params : ' || ' src guid = ' || p_src_object_guid || ' src type = ' || p_src_object_type || ' dest guid = ' || p_dest_object_guid || ' dest type = ' || p_dest_object_type, G_MODULE_NAME); END IF; FOR assoc_rec IN (SELECT pa.policy_guid, pa.coll_name, pa.policy_type, pa.is_enabled, pa.add_or_delete FROM mgmt_policy_assoc pa WHERE pa.object_guid = p_src_object_guid AND pa.object_type = p_src_object_type) LOOP EM_POLICY.copy_object_policy_assoc( p_src_object_guid => p_src_object_guid, p_src_object_type => p_src_object_type, p_dest_object_guid => p_dest_object_guid, p_dest_object_type => p_dest_object_type, p_policy_guid => assoc_rec.policy_guid, p_policy_type => assoc_rec.policy_type, p_src_coll_name => assoc_rec.coll_name, p_is_enabled => assoc_rec.is_enabled, p_add_or_delete => assoc_rec.add_or_delete); END LOOP; -- assoc_rec LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Exit', G_MODULE_NAME) ; END IF; END copy_all_policy_assocs; -- Copy object policy assoc config from src object to dest object PROCEDURE copy_object_policy_assoc( p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dest_object_guid IN RAW, p_dest_object_type IN NUMBER, p_policy_guid IN RAW, p_policy_type IN NUMBER DEFAULT 1, p_src_coll_name IN VARCHAR2 DEFAULT NULL, p_dest_coll_name IN VARCHAR2 DEFAULT NULL, p_is_enabled IN NUMBER DEFAULT 1, p_ca_creds IN MGMT_MNTR_CA_ARRAY DEFAULT NULL, p_add_or_delete IN NUMBER DEFAULT 0) IS l_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_info EM_METRIC.METRIC_INFO_REC; l_target_type mgmt_metrics.target_type%TYPE; l_metric_name mgmt_metrics.metric_name%TYPE; l_dest_coll_name mgmt_policy_assoc.coll_name%TYPE; l_crit_job_id MGMT_JOB.job_id%TYPE; l_warn_job_id MGMT_JOB.job_id%TYPE; l_info_job_id MGMT_JOB.job_id%TYPE; l_proc_name VARCHAR2(32) := 'copy_object_policy_assoc '; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Enter Params : ' || ' src guid = ' || p_src_object_guid || ' src type = ' || p_src_object_type || ' dest guid = ' || p_dest_object_guid || ' dest type = ' || p_dest_object_type || ' policy guid = ' || p_policy_guid || ' policy type = ' || p_policy_type || ' src coll = [' || p_src_coll_name || ']' || ' dest coll = [' || p_dest_coll_name || ']' || ' enabled = [' || p_is_enabled || ']', G_MODULE_NAME); END IF; -- If dest coll_name is NULL, use the src coll_name l_dest_coll_name := NVL(p_dest_coll_name, p_src_coll_name); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Using dest coll [' || l_dest_coll_name || ']', G_MODULE_NAME) ; END IF; EM_POLICY.add_policy_assoc( p_object_guid => p_dest_object_guid, p_policy_guid => p_policy_guid, p_coll_name => l_dest_coll_name, p_object_type => p_dest_object_type, p_policy_type => p_policy_type, p_is_enabled => p_is_enabled, p_add_or_delete => p_add_or_delete); IF (p_policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC) THEN -- For metric thresholds, get the table metric guid from the metric column guid SELECT target_type, metric_name INTO l_target_type, l_metric_name FROM mgmt_metrics m WHERE metric_guid = p_policy_guid AND ROWNUM = 1 ; SELECT metric_guid INTO l_metric_guid FROM mgmt_metrics m WHERE target_type = l_target_type AND metric_name = l_metric_name AND metric_column = ' ' AND ROWNUM = 1 ; ELSE -- For policies, get the metric guid on which the policy is defined. SELECT metric_guid INTO l_metric_guid FROM mgmt_policies WHERE policy_guid = p_policy_guid; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Looked up metric guid ' || l_metric_guid, G_MODULE_NAME) ; END IF; -- Get key details of the metric EM_METRIC.get_metric_info(l_metric_guid, l_metric_info); FOR cfg_rec IN (SELECT coll_name, 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_src_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_src_coll_name, coll_name) ORDER BY coll_name, eval_order) LOOP EM_POLICY.copy_object_policy_assoc_cfg( p_src_object_guid => p_src_object_guid, p_src_object_type => p_src_object_type, p_dest_object_guid => p_dest_object_guid, p_dest_object_type => p_dest_object_type, p_policy_guid => p_policy_guid, p_src_coll_name => p_src_coll_name, p_dest_coll_name => cfg_rec.coll_name, p_src_key_value => cfg_rec.key_value, p_src_key_oper => cfg_rec.key_operator, p_dest_eval_order => cfg_rec.eval_order, -- Maintain the same eval order on dest p_metric_info => l_metric_info, p_ca_creds => p_ca_creds); END LOOP; -- cfg_rec LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Exit', G_MODULE_NAME) ; END IF; END copy_object_policy_assoc; PROCEDURE copy_object_policy_assoc_cfg( p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dest_object_guid IN RAW, p_dest_object_type IN NUMBER, p_policy_guid IN RAW, p_src_coll_name IN VARCHAR2 DEFAULT NULL, p_dest_coll_name IN VARCHAR2 DEFAULT NULL, p_src_key_value IN VARCHAR2 DEFAULT ' ', p_src_key_oper IN NUMBER DEFAULT 0, p_dest_eval_order IN NUMBER DEFAULT 0, p_metric_info IN EM_METRIC.METRIC_INFO_REC DEFAULT NULL, p_ca_creds IN MGMT_MNTR_CA_ARRAY DEFAULT NULL) IS l_crit_job_id MGMT_JOB.job_id%TYPE; l_warn_job_id MGMT_JOB.job_id%TYPE; l_info_job_id MGMT_JOB.job_id%TYPE; l_proc_name VARCHAR2(32) := 'copy_object_policy_assoc '; BEGIN IF ( (p_metric_info.num_keys > 1) AND (p_src_key_value <> ' ') ) THEN EM_POLICY.copy_composite_key_entry(p_src_object_guid, p_dest_object_guid, p_src_key_value); END IF; FOR cfg_rec IN (SELECT coll_name, 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_src_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_src_coll_name, coll_name) AND key_value = p_src_key_value AND key_operator = p_src_key_oper ORDER BY coll_name, eval_order) LOOP -- copy_ca_id ( l_info_job_id := copy_ca_id( p_src_object_guid, p_src_object_type, p_dest_object_guid, p_dest_object_type, cfg_rec.info_action_job_id, p_ca_creds); l_warn_job_id := copy_ca_id( p_src_object_guid, p_src_object_type, p_dest_object_guid, p_dest_object_type, cfg_rec.warn_action_job_id, p_ca_creds); l_crit_job_id := copy_ca_id( p_src_object_guid, p_src_object_type, p_dest_object_guid, p_dest_object_type, cfg_rec.crit_action_job_id, p_ca_creds); EM_POLICY.add_policy_assoc_cfg( p_object_guid => p_dest_object_guid, p_policy_guid => p_policy_guid, p_coll_name => p_dest_coll_name, p_key_value => p_src_key_value, p_key_operator => p_src_key_oper, p_eval_order => p_dest_eval_order, p_is_exception => cfg_rec.is_exception, p_has_active_baseline => cfg_rec.has_active_baseline, p_prevent_override => cfg_rec.prevent_override, p_crit_action_job_id => l_crit_job_id, p_warn_action_job_id => l_warn_job_id, p_info_action_job_id => l_info_job_id, p_fixit_job => cfg_rec.fixit_job, p_simultaneous_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); -- TODO : Can replace it with INSERT INTO SELECT FROM FOR param_rec IN (SELECT param_name, crit_threshold, warn_threshold, info_threshold FROM mgmt_policy_assoc_cfg_params WHERE object_guid = p_src_object_guid AND policy_guid = p_policy_guid AND coll_name = p_src_coll_name AND key_value = p_src_key_value AND key_operator = p_src_key_oper) LOOP EM_POLICY.add_policy_assoc_cfg_param( p_object_guid => p_dest_object_guid, p_policy_guid => p_policy_guid, p_coll_name => p_dest_coll_name, p_key_value => p_src_key_value, p_key_operator => p_src_key_oper, 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; -- param_rec LOOP END LOOP; -- cfg_rec LOOP END copy_object_policy_assoc_cfg; PROCEDURE remove_object_policy_assoc( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_remove_ca_assoc IN NUMBER DEFAULT 1) IS BEGIN -- Remove CA associations, if specified to do so. IF (p_remove_ca_assoc = MGMT_GLOBAL.G_TRUE) THEN remove_policy_assoc_cas(p_object_guid=>p_object_guid, p_policy_guid=>p_policy_guid, p_coll_name => p_coll_name) ; END IF; EM_POLICY.remove_policy_assocs(p_object_guid, p_policy_guid, p_coll_name); EM_POLICY.remove_policy_assoc_cfgs(p_object_guid, p_policy_guid, p_coll_name); EM_POLICY.remove_policy_assoc_cfg_params(p_object_guid, p_policy_guid, p_coll_name); END remove_object_policy_assoc; -- Bulk version of remove_object_policy_assoc for a object PROCEDURE remove_object_policy_assocs (p_object_guid IN RAW, p_remove_ca_assoc IN NUMBER DEFAULT 1) IS BEGIN IF p_remove_ca_assoc = MGMT_GLOBAL.G_TRUE THEN remove_policy_assoc_cas(p_object_guid=>p_object_guid, p_policy_guid=>NULL) ; END IF ; DELETE mgmt_policy_assoc WHERE object_guid = p_object_guid ; DELETE mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid ; DELETE mgmt_policy_assoc_cfg_params WHERE object_guid = p_object_guid ; END remove_object_policy_assocs ; -- Bulk version of remove_object_policy_assoc for a policy PROCEDURE remove_object_policy_assocs (p_policy_guid IN RAW, p_remove_ca_assoc IN NUMBER DEFAULT 1) IS BEGIN IF p_remove_ca_assoc = MGMT_GLOBAL.G_TRUE THEN remove_policy_assoc_cas(p_object_guid=>NULL, p_policy_guid=>p_policy_guid) ; END IF ; DELETE mgmt_policy_assoc WHERE policy_guid = p_policy_guid ; DELETE mgmt_policy_assoc_cfg WHERE policy_guid = p_policy_guid ; DELETE mgmt_policy_assoc_cfg_params WHERE policy_guid = p_policy_guid ; END remove_object_policy_assocs; -- Bulk version of remove object policy assoc -- p_remove_all means remove all associations for the target irrespective of policy PROCEDURE remove_object_policy_assocs( p_object_guid IN RAW, p_policy_guids IN mgmt_target_guid_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_remove_ca_assoc IN NUMBER DEFAULT 1) IS BEGIN IF p_policy_guids IS NULL OR p_policy_guids.COUNT = 0 THEN RETURN ; END IF ; IF p_remove_ca_assoc = MGMT_GLOBAL.G_TRUE THEN FOR i IN p_policy_guids.FIRST..p_policy_guids.LAST LOOP EM_POLICY.remove_policy_assoc_cas(p_object_guid, p_policy_guids(i), p_coll_name); END LOOP ; END IF ; DELETE mgmt_policy_assoc WHERE object_guid = p_object_guid AND policy_guid IN (SELECT /*+ cardinality( policies 50 ) */ * FROM TABLE(CAST(p_policy_guids as mgmt_target_guid_array)) policies WHERE ROWNUM >= 0) AND coll_name = NVL(p_coll_name, coll_name) ; DELETE mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND policy_guid IN (SELECT /*+ cardinality( policies 50 ) */ * FROM TABLE(CAST(p_policy_guids as mgmt_target_guid_array)) policies WHERE ROWNUM >= 0) AND coll_name = NVL(p_coll_name, coll_name) ; DELETE mgmt_policy_assoc_cfg_params WHERE object_guid = p_object_guid AND policy_guid IN (SELECT /*+ cardinality( policies 50 ) */ * FROM TABLE(CAST(p_policy_guids as mgmt_target_guid_array)) policies WHERE ROWNUM >= 0) AND coll_name = NVL(p_coll_name, coll_name) ; END remove_object_policy_assocs ; -- Returns the key details -- NOTE : Returns ' ' if all key_values passed in are ' ' PROCEDURE get_key_details( p_key_col_cond_list IN MGMT_POLICY_KEY_COL_COND_ARRAY, p_metric_info IN EM_METRIC.METRIC_INFO_REC, p_key_value OUT VARCHAR2, p_key_part_array OUT NOCOPY mgmt_medium_string_array, p_key_operator OUT NUMBER) IS l_keycol_idx NUMBER := 0; l_key_parts_emd_str_array smp_emd_string_array; l_proc_name CONSTANT VARCHAR2(30) := 'get_key_details' ; l_dflt_key NUMBER := 0; BEGIN p_key_operator := 0; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'metric num_keys = ' || p_metric_info.num_keys, G_MODULE_NAME) ; END IF ; IF (p_metric_info.num_keys <= 1) THEN -- For 0 and 1 key column in metric definition -- Return the first key part as key_value IF ( (p_key_col_cond_list IS NOT NULL) AND (p_key_col_cond_list.COUNT > 0) ) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Keypart list is NOT NULL. Key col = [' || p_key_col_cond_list(1).key_value || ']', G_MODULE_NAME) ; END IF ; -- Use ' ' as default p_key_value := NVL(p_key_col_cond_list(1).key_value, ' '); IF (p_key_col_cond_list(1).key_value IS NULL) THEN p_key_operator := 0; ELSE p_key_operator := p_key_col_cond_list(1).has_wildcard; END IF; ELSE IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Key col is NULL. Defaulting key_value to blank', G_MODULE_NAME) ; END IF ; p_key_value := ' '; p_key_operator := 0; END IF; ELSE -- Multiple key columns in metric definition. IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Multi-Key metric ', G_MODULE_NAME) ; END IF ; p_key_part_array := mgmt_medium_string_array(); p_key_part_array.extend(p_metric_info.num_keys) ; IF ( (p_key_col_cond_list IS NOT NULL) AND (p_key_col_cond_list.COUNT > 0) ) THEN l_dflt_key := 1; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Key part list is NOT NULL ', G_MODULE_NAME) ; END IF ; FOR i IN 1..p_metric_info.num_keys LOOP l_keycol_idx := 0; FOR j IN 1..p_key_col_cond_list.COUNT LOOP IF (p_metric_info.key_cols(i) = p_key_col_cond_list(j).key_column_name) THEN l_keycol_idx := j; END IF; END LOOP; IF (l_keycol_idx > 0) THEN IF (p_key_col_cond_list(l_keycol_idx).key_value IS NULL) THEN p_key_part_array(i) := NVL(p_key_col_cond_list(l_keycol_idx).key_value, ' ') ; ELSE p_key_part_array(i) := p_key_col_cond_list(l_keycol_idx).key_value; IF (NVL(p_key_col_cond_list(l_keycol_idx).has_wildcard, 1) = 1) THEN p_key_operator := p_key_operator + POWER(2, (i-1)); END IF; END IF; ELSE p_key_part_array(i) := ' '; -- p_key_operator := p_key_operator + POWER(2, (i-1)); END IF; IF (p_key_part_array(i) <> ' ') THEN l_dflt_key := 0; END IF; END LOOP ; ELSE p_key_value := ' '; p_key_operator := 0; l_dflt_key := 1; -- FOR i IN 1..p_metric_info.num_keys -- LOOP -- p_key_part_array(i) := '%'; -- p_key_operator := p_key_operator + POWER(2, (i-1)); -- l_dflt_key := 0; -- END LOOP; END IF; IF (l_dflt_key = 1) THEN -- If default key is passed in return ' '. p_key_value := ' '; p_key_operator := 0; ELSE -- Get composite guid -- The mgmt_global.get_composite_key_guid accepts only -- smp_emd_string_array. So copy parts from mgmt_medium_string_array -- to smp_emd_string_array and pass to get_composite_key_guid function l_key_parts_emd_str_array := smp_emd_string_array(); l_key_parts_emd_str_array.extend(p_metric_info.num_keys); FOR i IN 1..p_metric_info.num_keys LOOP l_key_parts_emd_str_array(i) := p_key_part_array(i); END LOOP; p_key_value := MGMT_GLOBAL.GET_COMPOSITE_KEY_GUID(l_key_parts_emd_str_array); END IF; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Returning key value = ' || p_key_value || ' operator '|| p_key_operator, G_MODULE_NAME) ; END IF ; END get_key_details; -- -- Returns TRUE if the configuration key matches the result key -- else FALSE -- FUNCTION config_key_match(p_cfg_key IN mgmt_medium_string_array, p_result_key IN mgmt_medium_string_array, p_key_operator IN VARCHAR2, p_num_keys IN NUMBER ) RETURN BOOLEAN IS l_key_match BOOLEAN := TRUE ; l_key_index NUMBER(1) := 1 ; keyvalue_not_found1 exception ; keyvalue_not_found2 exception ; PRAGMA EXCEPTION_INIT(keyvalue_not_found1,-6532) ; PRAGMA EXCEPTION_INIT(keyvalue_not_found2,-6531) ; BEGIN IF p_cfg_key(1) = ' ' AND p_key_operator =0 THEN RETURN(TRUE) ; END IF ; WHILE l_key_match AND l_key_index <= p_num_keys LOOP IF bitand(p_key_operator,power(2,l_key_index-1)) = power(2,l_key_index-1) THEN l_key_match := l_key_match AND p_result_key(l_key_index) LIKE p_cfg_key(l_key_index) ESCAPE '\'; ELSE l_key_match := l_key_match AND p_result_key(l_key_index) = p_cfg_key(l_key_index) ; END IF ; l_key_index := l_key_index + 1 ; END LOOP ; RETURN l_key_match ; EXCEPTION WHEN keyvalue_not_found1 OR keyvalue_not_found2 THEN -- key not in result record or composite keys table raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Missing/Invalid Key Value (key_match)') ; WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' when matching keys (key_match)') ; END config_key_match; PROCEDURE get_config_key_details( p_object_guid IN RAW, p_policy_guid IN RAW, p_key_value IN VARCHAR2, p_cfg_coll_name OUT VARCHAR2, p_cfg_key_value OUT VARCHAR2, p_cfg_key_operator OUT NUMBER) IS l_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_info EM_METRIC.METRIC_INFO_REC; l_coll_name mgmt_policy_assoc.coll_name%TYPE; l_key_idx NUMBER; l_key_parts_matched BOOLEAN; l_cfg_match_found BOOLEAN; l_match_coll_name mgmt_policy_assoc_cfg.coll_name%TYPE; l_match_key mgmt_policy_assoc_cfg.key_value%TYPE; l_match_key_oper mgmt_policy_assoc_cfg.key_operator%TYPE; l_match_key_excep mgmt_policy_assoc_cfg.is_exception%TYPE; l_key_part_list mgmt_medium_string_array; l_cfgkey_part_list mgmt_medium_string_array; l_proc_name CONSTANT VARCHAR2(30) := 'get_config_key_details' ; l_count NUMBER; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Entry object guid = ' || p_object_guid || ' Policy = ' || p_policy_guid || ' Key val = ' || p_key_value, G_MODULE_NAME) ; END IF ; -- Check if a metric threshold SELECT COUNT(*) INTO l_count FROM MGMT_POLICY_ASSOC WHERE object_guid=p_object_guid AND policy_guid=p_policy_guid AND policy_type=MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC; IF l_count>0 THEN -- For metric thresholds, the metric guid is -- itself the policy guid l_metric_guid := p_policy_guid; ELSE BEGIN SELECT metric_guid INTO l_metric_guid FROM mgmt_policies WHERE policy_guid = p_policy_guid; EXCEPTION WHEN NO_DATA_FOUND THEN EM_SEVERITY.log_error(p_object_guid, MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Violation received for unknown policy GUID ' || p_policy_guid || ' Target ' || p_object_guid || ' Key Value ' || p_key_value); return; END; END IF; EM_METRIC.get_metric_info_for_target(l_metric_guid, p_object_guid, l_metric_info); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Got metric_guid = ' || l_metric_guid || ' Transposed = ' || l_metric_info.is_transposed || ' Num keys = ' || l_metric_info.num_keys, G_MODULE_NAME) ; END IF ; -- Get collection name for transposed metrics l_coll_name := NULL; IF (l_metric_info.is_transposed = MGMT_GLOBAL.G_TRUE) THEN IF (l_metric_info.num_keys <= 1) THEN l_coll_name := p_key_value; ELSE -- For multi-key UDM/transposed metrics, key part 1 is the collection name. BEGIN SELECT key_part1_value INTO l_coll_name FROM mgmt_metrics_composite_keys WHERE target_guid = p_object_guid AND composite_key = HEXTORAW(p_key_value); EXCEPTION WHEN OTHERS THEN l_coll_name := NULL; END; END IF; ELSE -- Non - transposed metrics l_coll_name := NULL; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Using coll_name = [' || l_coll_name || ']', G_MODULE_NAME) ; END IF ; l_cfg_match_found := FALSE; FOR cfg_rec IN (SELECT coll_name, key_value, key_operator, is_exception FROM mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(l_coll_name, coll_name) ORDER BY coll_name, eval_order) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Processing for config key_value = ' || cfg_rec.key_value || ', Operator = ' || cfg_rec.key_operator, G_MODULE_NAME) ; END IF ; IF (l_metric_info.num_keys <= 0) THEN l_cfg_match_found := TRUE; l_match_coll_name := cfg_rec.coll_name; l_match_key := cfg_rec.key_value; l_match_key_oper := cfg_rec.key_operator; l_match_key_excep := cfg_rec.is_exception; EXIT; -- For num_keys =0, there will be atmost 1 row. ELSIF (l_metric_info.num_keys = 1) THEN IF ( (cfg_rec.key_value = ' ') OR ((cfg_rec.key_operator = 0) AND (p_key_value = cfg_rec.key_value)) OR ((cfg_rec.key_operator = 1) AND (p_key_value like cfg_rec.key_value escape '\')) ) THEN l_cfg_match_found := TRUE; l_match_coll_name := cfg_rec.coll_name; l_match_key := cfg_rec.key_value; l_match_key_oper := cfg_rec.key_operator; l_match_key_excep := cfg_rec.is_exception; EXIT; -- exit the loop on match found END IF; ELSE -- Multi key column case -- Special case the ' ' key value. Blank key_value matches all keys. IF (cfg_rec.key_value = ' ') THEN l_cfg_match_found := TRUE; l_match_coll_name := cfg_rec.coll_name; l_match_key := cfg_rec.key_value; l_match_key_oper := cfg_rec.key_operator; l_match_key_excep := cfg_rec.is_exception; EXIT; -- exit on finding a successful match. END IF; -- Get key parts for the given key get_composite_key_parts(p_object_guid, p_key_value, l_key_part_list); -- Get key parts for the config key get_composite_key_parts(p_object_guid, cfg_rec.key_value, l_cfgkey_part_list); l_key_idx := 1; l_key_parts_matched := TRUE; WHILE l_key_parts_matched AND l_key_idx <= l_metric_info.num_keys LOOP -- If key part is ' ', then match it automatically IF (l_cfgkey_part_list(l_key_idx) = ' ') THEN -- If key part is ' ', mark as matched l_key_parts_matched := l_key_parts_matched; ELSIF (BITAND(cfg_rec.key_operator, POWER(2, (l_key_idx-1))) = POWER(2, (l_key_idx-1))) THEN l_key_parts_matched := l_key_parts_matched AND l_key_part_list(l_key_idx) LIKE l_cfgkey_part_list(l_key_idx) ESCAPE '\'; ELSE l_key_parts_matched := l_key_parts_matched AND (l_key_part_list(l_key_idx) = l_cfgkey_part_list(l_key_idx)) ; END IF ; l_key_idx := l_key_idx + 1 ; END LOOP ; IF (l_key_parts_matched) THEN l_cfg_match_found := TRUE; l_match_coll_name := cfg_rec.coll_name; l_match_key := cfg_rec.key_value; l_match_key_oper := cfg_rec.key_operator; l_match_key_excep := cfg_rec.is_exception; EXIT; -- exit on finding a successful match. END IF; END IF; END LOOP; IF ( (l_cfg_match_found) AND (l_match_key_excep = 0) ) THEN -- If the matching config is found and is not an exception, p_cfg_coll_name := l_match_coll_name; p_cfg_key_value := l_match_key; p_cfg_key_operator := l_match_key_oper; ELSE -- No key configuration match found, or the -- key configuration found is an exception p_cfg_coll_name := NULL; p_cfg_key_value := NULL; p_cfg_key_operator := 0; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Exit. Retrurning ' || ' Coll name = [' || p_cfg_coll_name || ']' || ' Key val = [' || p_cfg_key_value || ']' || ' Key oper = [' || p_cfg_key_operator || ']', G_MODULE_NAME) ; END IF ; END get_config_key_details; -- Function to get Corrective Action ID. FUNCTION get_ca_id(p_object_guid IN RAW, p_object_type IN NUMBER DEFAULT 1, p_ca_name IN VARCHAR2 DEFAULT NULL, p_incr_ref IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) RETURN RAW IS l_ca_scope NUMBER; l_ca_id MGMT_JOB.job_id%TYPE; l_proc_name CONSTANT VARCHAR2(30) := 'get_ca_id: ' ; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Enter ' || ' obj guid =[' || p_object_guid || ']' || ' obj type =[' || p_object_type || ']' || ' ca name =[' || p_ca_name || ']' || ' incr ref =[' || p_incr_ref || ']', G_MODULE_NAME) ; END IF ; l_ca_id := NULL; IF (p_ca_name IS NOT NULL) THEN IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN l_ca_scope := MGMT_CA.CA_SCOPE_TARGET; ELSIF (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_DEFAULT) THEN l_ca_scope := MGMT_CA.CA_SCOPE_TARGET_TYPE; ELSIF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY) THEN l_ca_scope := MGMT_CA.CA_SCOPE_TEMPLATE_COPY; END IF; BEGIN l_ca_id := MGMT_JOB_ENGINE.get_ca_id( p_object_guid => p_object_guid, p_ca_name => p_ca_name, p_ca_scope => l_ca_scope); EXCEPTION WHEN OTHERS THEN l_ca_id := NULL; END; -- Increment ref counter if required. IF (l_ca_id IS NOT NULL) AND (p_incr_ref = MGMT_GLOBAL.G_TRUE) THEN MGMT_JOB_ENGINE.increment_ca_ref_count(l_ca_id); END IF; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Exit ' || ' Returing CA id =[' || l_ca_id || ']', G_MODULE_NAME) ; END IF ; RETURN l_ca_id; END get_ca_id; FUNCTION get_ca_id_list( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_key_value IN VARCHAR2 DEFAULT NULL, p_key_operator IN NUMBER DEFAULT NULL) RETURN MGMT_TARGET_GUID_ARRAY IS l_ca_id_list MGMT_TARGET_GUID_ARRAY; BEGIN l_ca_id_list := MGMT_TARGET_GUID_ARRAY(); FOR ca_rec IN (SELECT crit_action_job_id, warn_action_job_id, info_action_job_id FROM mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = NVL(p_coll_name, coll_name) AND key_value = NVL(p_key_value, key_value) AND key_operator = NVL(p_key_operator, key_operator) ORDER BY coll_name, eval_order) LOOP IF (ca_rec.crit_action_job_id IS NOT NULL) THEN l_ca_id_list.extend(1); l_ca_id_list(l_ca_id_list.COUNT) := ca_rec.crit_action_job_id; END IF; IF (ca_rec.warn_action_job_id IS NOT NULL) THEN l_ca_id_list.extend(1); l_ca_id_list(l_ca_id_list.COUNT) := ca_rec.warn_action_job_id; END IF; IF (ca_rec.info_action_job_id IS NOT NULL) THEN l_ca_id_list.extend(1); l_ca_id_list(l_ca_id_list.COUNT) := ca_rec.info_action_job_id; END IF; END LOOP; RETURN l_ca_id_list; END get_ca_id_list; PROCEDURE decrement_ca_ids(p_ca_id_list IN MGMT_TARGET_GUID_ARRAY DEFAULT NULL) IS BEGIN IF ( (p_ca_id_list IS NOT NULL) AND (p_ca_id_list.COUNT > 0) ) THEN FOR ca_rec IN p_ca_id_list.FIRST..p_ca_id_list.LAST LOOP MGMT_JOB_ENGINE.decrement_ca_ref_count(p_ca_id_list(ca_rec)); END LOOP; END IF; END decrement_ca_ids; -- This function gets the new CA name for cases where the template -- and target two non-equiv CAs with the same name -- Algo: -- . Calculate new CA name by appending sfx to the orig CA -- sfx start from 0 and incremented up. -- If the length of the orig job is alreay the max leng -- the last part of the CA name is overwritten with the sfx -- . Check to see if the new CA exist in either tgt or tmpl -- FUNCTION get_new_ca_name( p_ca_name IN VARCHAR2, p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dst_object_guid IN RAW, p_dst_object_type IN NUMBER) RETURN VARCHAR2 IS l_ca_idx INTEGER := 0; l_new_ca_name_found INTEGER := 0; l_new_ca_name mgmt_job.job_name%TYPE; l_dst_ca_id MGMT_JOB.job_id%TYPE; l_src_ca_id MGMT_JOB.job_id%TYPE; l_ca_len INTEGER; l_sfx_len INTEGER; l_proc_name CONSTANT VARCHAR2(30) := 'gen_new_ca_name: ' ; BEGIN -- Pick a name that does not exist in the source or destination l_ca_idx := 0; l_new_ca_name_found := MGMT_GLOBAL.G_FALSE; l_ca_len := LENGTH(p_ca_name); WHILE (l_new_ca_name_found = MGMT_GLOBAL.G_FALSE) LOOP l_sfx_len := LENGTH(l_ca_idx); IF (l_ca_len + l_sfx_len <= 64) THEN l_new_ca_name := p_ca_name || l_ca_idx; ELSE l_new_ca_name := SUBSTR(p_ca_name, 1, 64-l_sfx_len) || l_ca_idx; END IF; l_src_ca_id := get_ca_id( p_object_guid => p_src_object_guid, p_object_type => p_src_object_type, p_ca_name => l_new_ca_name, p_incr_ref => MGMT_GLOBAL.G_FALSE); l_dst_ca_id := get_ca_id( p_object_guid => p_dst_object_guid, p_object_type => p_dst_object_type, p_ca_name => l_new_ca_name, p_incr_ref => MGMT_GLOBAL.G_FALSE); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Renaming CA to ' || '(Name = ' || l_new_ca_name || ')' || '(ID on src = ' || l_src_ca_id || ')' || '(ID on tgt = ' || l_dst_ca_id || ')', G_MODULE_NAME) ; END IF; IF ( (l_src_ca_id IS NULL) AND (l_dst_ca_id IS NULL) ) THEN l_new_ca_name_found := MGMT_GLOBAL.G_TRUE; END IF; l_ca_idx := l_ca_idx + 1; END LOOP; RETURN l_new_ca_name; END get_new_ca_name; -- Copies corrective action from one scope to another FUNCTION copy_ca_id( p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dest_object_guid IN RAW, p_dest_object_type IN NUMBER, p_ca_id IN RAW, p_ca_creds IN MGMT_MNTR_CA_ARRAY DEFAULT NULL) RETURN RAW IS l_ca_scope NUMBER; l_ca_id MGMT_JOB.job_id%TYPE; l_job_name MGMT_JOB.job_name%TYPE; l_ca_job_creds MGMT_JOB_CRED_ARRAY; l_template_ca MGMT_MNTR_CA; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_tgt_ca_id MGMT_JOB.job_id%TYPE; l_src_ca_name mgmt_job.job_name%TYPE; l_src_ca_id MGMT_JOB.job_id%TYPE; l_equiv boolean := false; l_proc_name CONSTANT VARCHAR2(30) := 'copy_ca_id: ' ; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Enter ' || ' src obj guid =[' || p_src_object_guid || ']' || ' src obj type =[' || p_src_object_type || ']' || ' dst obj guid =[' || p_dest_object_guid || ']' || ' dst obj guid =[' || p_dest_object_guid || ']' || ' ca id =[' || p_ca_id || ']', G_MODULE_NAME) ; END IF ; l_ca_id := NULL; IF (p_ca_id IS NOT NULL) THEN BEGIN SELECT job_name INTO l_job_name FROM mgmt_job WHERE job_id = p_ca_id AND is_corrective_action = 1; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid corrective action job_id ' || p_ca_id || ' provided to copy_ca_id'); END; l_src_ca_name := l_job_name; IF ( (p_src_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY) AND (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY)) THEN -- Fix bug 4591107 : Handle the case where both template and target -- have the CA with the same name but are NOT equivalent -- Get the target_guid of BEGIN SELECT target_guid INTO l_target_guid FROM mgmt_template_copies WHERE template_copy_guid = p_dest_object_guid; EXCEPTION WHEN OTHERS THEN NULL; END; -- Check to see if the CA exists on the target l_tgt_ca_id := get_ca_id( p_object_guid => l_target_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_ca_name => l_job_name, p_incr_ref => MGMT_GLOBAL.G_FALSE); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' CA id on target = ' || l_tgt_ca_id, G_MODULE_NAME) ; END IF; -- IF tgt CA exist, check to see if they are equivalent IF (l_tgt_ca_id IS NOT NULL) THEN l_equiv := MGMT_JOB_ENGINE.are_cas_equivalent( p_job_id_1 => p_ca_id, p_job_id_2 => l_tgt_ca_id); IF (NOT l_equiv) THEN l_job_name := get_new_ca_name( p_ca_name => l_job_name, p_src_object_guid => p_src_object_guid, p_src_object_type => p_src_object_type, p_dst_object_guid => l_target_guid, p_dst_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET); END IF; -- If (l_equiv) END IF; -- If tgt does not have the CA END IF; -- Check to see if the CA job is alredy created on destination l_ca_id := get_ca_id( p_object_guid => p_dest_object_guid, p_object_type => p_dest_object_type, p_ca_name => l_job_name, p_incr_ref => MGMT_GLOBAL.G_TRUE); IF (l_ca_id IS NULL) THEN -- If CA job does not exist, create one IF ( (p_src_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE) AND (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY)) THEN -- Iterate through p_ca_creds to get the correct creds.. l_ca_job_creds := NULL; IF ( (p_ca_creds IS NOT NULL) AND (p_ca_creds.COUNT > 0) )THEN FOR ca_ctr IN p_ca_creds.FIRST..p_ca_creds.LAST LOOP l_template_ca := p_ca_creds(ca_ctr); IF (l_job_name = l_template_ca.ca_name) THEN l_ca_job_creds := l_template_ca.ca_creds; EXIT; -- exit out of the loop END IF; END LOOP; END IF; MGMT_JOB_ENGINE.create_template_copy_ca( p_template_copy_guid => p_dest_object_guid, p_template_guid => p_src_object_guid, p_ca_name => l_job_name, p_ca_creds => l_ca_job_creds, p_job_id => l_ca_id); MGMT_JOB_ENGINE.increment_ca_ref_count(l_ca_id); ELSIF ( (p_src_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY) AND (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY)) THEN -- Iterate through p_ca_creds to get the correct creds.. -- TODO: use a table cast here for faster lookup l_ca_job_creds := NULL; IF ( (p_ca_creds IS NOT NULL) AND (p_ca_creds.COUNT > 0) )THEN FOR ca_ctr IN p_ca_creds.FIRST..p_ca_creds.LAST LOOP l_template_ca := p_ca_creds(ca_ctr); IF (l_job_name = l_template_ca.ca_name) THEN l_ca_job_creds := l_template_ca.ca_creds; EXIT; -- exit out of the loop END IF; END LOOP; END IF; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||' Creating CA on temp cpy ' || '(SRC CA = ' || l_src_ca_name || ')' || '(DEST CA = ' || l_job_name || ')', G_MODULE_NAME); END IF; MGMT_JOB_ENGINE.create_temp_cp_ca_from_temp_cp( p_dest_template_copy_guid => p_dest_object_guid, p_src_template_copy_guid => p_src_object_guid, p_src_ca_name => l_src_ca_name, p_dest_ca_name => l_job_name, p_ca_creds => l_ca_job_creds, p_job_id => l_ca_id); MGMT_JOB_ENGINE.increment_ca_ref_count(l_ca_id); ELSIF ( (p_src_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) AND (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY)) THEN -- Iterate through p_ca_creds to get the correct creds.. -- TODO: use a table cast here for faster lookup l_ca_job_creds := NULL; IF ( (p_ca_creds IS NOT NULL) AND (p_ca_creds.COUNT > 0) )THEN FOR ca_ctr IN p_ca_creds.FIRST..p_ca_creds.LAST LOOP l_template_ca := p_ca_creds(ca_ctr); IF (l_job_name = l_template_ca.ca_name) THEN l_ca_job_creds := l_template_ca.ca_creds; EXIT; -- exit out of the loop END IF; END LOOP; END IF; MGMT_JOB_ENGINE.create_temp_cp_ca_from_target( p_template_copy_guid => p_dest_object_guid, p_target_guid => p_src_object_guid, p_ca_name => l_job_name, p_ca_creds => l_ca_job_creds, p_job_id => l_ca_id); MGMT_JOB_ENGINE.increment_ca_ref_count(l_ca_id); ELSIF ( (p_src_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TEMPLATE_COPY) AND (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET)) THEN MGMT_JOB_ENGINE.create_target_ca_from_template( p_target_guid => p_dest_object_guid, p_ca_id => p_ca_id, p_job_id_out => l_ca_id); MGMT_JOB_ENGINE.increment_ca_ref_count(l_ca_id); ELSIF ( (p_src_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT) AND (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET)) THEN NULL; MGMT_JOB_ENGINE.create_target_ca_from_default( p_target_type_guid => p_src_object_guid, p_target_guid => p_dest_object_guid, p_source_ca_id => p_ca_id, p_ca_job_id_out => l_ca_id); MGMT_JOB_ENGINE.increment_ca_ref_count(l_ca_id); ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid copy request ' || p_ca_id || ' provided to copy_ca_id'); END IF; END IF; -- If (l_ca_id IS NOT NULL) END IF; -- If (p_ca_id IS NOT NULL) IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name||'Exit ' || ' Returing CA id =[' || l_ca_id || ']', G_MODULE_NAME) ; END IF ; RETURN l_ca_id; END copy_ca_id; -- Callback when corrective action is deleted. PROCEDURE handle_delete_ca(p_ca_job_id IN RAW) IS BEGIN -- Disassociate Critical Corrective Action UPDATE mgmt_policy_assoc_cfg SET crit_action_job_id = NULL WHERE crit_action_job_id = p_ca_job_id; -- Disassociate Warning Corrective Action UPDATE mgmt_policy_assoc_cfg SET warn_action_job_id = NULL WHERE warn_action_job_id = p_ca_job_id; -- Disassociate Information Corrective Action UPDATE mgmt_policy_assoc_cfg SET info_action_job_id = NULL WHERE info_action_job_id = p_ca_job_id; END handle_delete_ca; -- Callback to auto-associate policies PROCEDURE auto_associate_policies(p_target_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW) IS l_default_guid mgmt_policy_assoc.object_guid%TYPE; BEGIN l_default_guid := EM_TARGET.generate_target_type_guid(p_target_type); -- Pick policies that are applicable -- policy should be applicable to target type meta ver and -- metric on which the policy is defined is applicable to target. FOR pol_rec IN (SELECT p.policy_guid, m.metric_name FROM mgmt_policies p, mgmt_policy_type_versions ptv, mgmt_metrics m, mgmt_targets t WHERE p.target_type = m.target_type AND p.metric_guid = m.metric_guid AND p.policy_guid = ptv.policy_guid AND ptv.type_meta_ver = t.type_meta_ver AND m.target_type = t.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 = ' ')) AND p.auto_enable = MGMT_GLOBAL.G_TRUE AND t.target_type = p_target_type AND t.target_name = p_target_name ORDER BY p.policy_guid) LOOP BEGIN -- Use metric name as the collection name EM_POLICY.copy_object_policy_assoc( p_src_object_guid => l_default_guid, p_src_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_dest_object_guid => p_target_guid, p_dest_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_policy_guid => pol_rec.policy_guid, p_policy_type => MGMT_GLOBAL.G_TYPE_POLICY, p_src_coll_name => pol_rec.metric_name, p_dest_coll_name => pol_rec.metric_name); EXCEPTION WHEN OTHERS THEN -- Log the specified error to system error log for easy diagnosis MGMT_LOG.LOG_ERROR(v_module_name_in => EM_POLICY.G_MODULE_NAME, v_error_code_in => NULL, v_error_msg_in => 'Policy Associations Error: '|| '(target='||p_target_name|| ')(policy='||pol_rec.policy_guid|| ')(metric='||pol_rec.metric_name|| ')(error='||sqlerrm ) ; RAISE ; END ; END LOOP; END auto_associate_policies; -- Callback to delete policy data PROCEDURE handle_target_delete(p_target_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW) IS BEGIN -- Remove target-metric associations, and decrement CA counters EM_POLICY.remove_object_policy_assocs(p_object_guid => p_target_guid) ; END handle_target_delete; -- Callback to handle type metaver changes PROCEDURE handle_type_meta_ver_change(p_meta_ver_cbk_obj IN MGMT_TARGET_META_VER_CBK_OBJ) IS l_from_policy_guids MGMT_TARGET_GUID_ARRAY; l_from_metric_names MGMT_SHORT_STRING_ARRAY; l_from_auto_enable MGMT_INTEGER_ARRAY; l_to_policy_guids MGMT_TARGET_GUID_ARRAY; l_to_metric_names MGMT_SHORT_STRING_ARRAY; l_to_auto_enable MGMT_INTEGER_ARRAY; l_to_ctr INTEGER; l_from_ctr INTEGER; l_default_guid mgmt_targets.target_guid%TYPE; l_target_guid mgmt_targets.target_guid%TYPE; l_action INTEGER := 0; l_policy_guid RAW(16); l_metric_name mgmt_metrics.metric_name%TYPE; l_temp_ca_id_list MGMT_TARGET_GUID_ARRAY; l_ca_id_list MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY(); CURSOR get_policy_guids (v_target_type VARCHAR2, v_type_meta_ver VARCHAR2, v_category_props mgmt_short_string_array) IS SELECT p.policy_guid, m.metric_name, p.auto_enable FROM mgmt_policies p, mgmt_policy_type_versions ptv, mgmt_metrics m WHERE p.target_type = m.target_type AND p.metric_guid = m.metric_guid AND p.policy_guid = ptv.policy_guid AND ptv.type_meta_ver = v_type_meta_ver AND m.target_type = v_target_type AND m.type_meta_ver = v_type_meta_ver AND ((m.category_prop_1 = v_category_props(1)) OR (m.category_prop_1 = ' ')) AND ((m.category_prop_2 = v_category_props(2)) OR (m.category_prop_2 = ' ')) AND ((m.category_prop_3 = v_category_props(3)) OR (m.category_prop_3 = ' ')) AND ((m.category_prop_4 = v_category_props(4)) OR (m.category_prop_4 = ' ')) AND ((m.category_prop_5 = v_category_props(5)) OR (m.category_prop_5 = ' ')) ORDER BY p.policy_guid; l_proc_name VARCHAR2(32) := 'handle_type_meta_ver_change: '; BEGIN l_default_guid := EM_TARGET.generate_target_type_guid(p_meta_ver_cbk_obj.target_type); l_target_guid := p_meta_ver_cbk_obj.target_guid; -- l_target_guid := MGMT_TARGET.get_target_guid(p_meta_ver_cbk_obj.target_name, -- p_meta_ver_cbk_obj.target_type); -- Reassociate policies -- Bulk collect old policies OPEN get_policy_guids(p_meta_ver_cbk_obj.target_type, p_meta_ver_cbk_obj.from_meta_ver, p_meta_ver_cbk_obj.from_catprop_array); FETCH get_policy_guids BULK COLLECT INTO l_from_policy_guids, l_from_metric_names, l_from_auto_enable; IF (get_policy_guids%ISOPEN) THEN CLOSE get_policy_guids; END IF; -- Bulk collect new policies OPEN get_policy_guids(p_meta_ver_cbk_obj.target_type, p_meta_ver_cbk_obj.to_meta_ver, p_meta_ver_cbk_obj.to_catprop_array); FETCH get_policy_guids BULK COLLECT INTO l_to_policy_guids, l_to_metric_names, l_to_auto_enable; IF (get_policy_guids%ISOPEN) THEN CLOSE get_policy_guids; END IF; -- Lock all CAs associated with this target MGMT_JOB_ENGINE.lock_cas_for_object(l_target_guid, MGMT_CA.CA_SCOPE_TARGET); l_from_ctr := 1; l_to_ctr := 1; WHILE ( (l_from_ctr <= l_from_policy_guids.COUNT ) OR (l_to_ctr <= l_to_policy_guids.COUNT ) ) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Looping from_ctr=[' || l_from_ctr || ']' || ' to_ctr=[' || l_to_ctr || ']', G_MODULE_NAME) ; END IF ; l_action := 0; -- Initialize to do nothing IF (l_from_ctr > l_from_policy_guids.COUNT) THEN -- We have reached the end of from list, process from to list IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Case 1 Add from to_list ' || ' policy guid =' || l_to_policy_guids(l_to_ctr) || ' metric name =[' || l_to_metric_names(l_to_ctr) || ']', G_MODULE_NAME) ; END IF ; -- Add the policy association only if auto_enable is ON IF (l_to_auto_enable(l_to_ctr) = MGMT_GLOBAL.G_TRUE) THEN l_action := 1; -- Set action to ADD l_policy_guid := l_to_policy_guids(l_to_ctr); l_metric_name := l_to_metric_names(l_to_ctr); END IF; l_to_ctr := l_to_ctr + 1; ELSIF (l_to_ctr > l_to_policy_guids.COUNT) THEN -- We have reached the end of to list, process from from list IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Case 2: Add default cfg ' || ' policy guid =' || l_from_policy_guids(l_from_ctr) || ' metric name =[' || l_from_metric_names(l_from_ctr) || ']', G_MODULE_NAME) ; END IF ; -- Policy exists only in the from list.. l_action := 2; -- Set action to REMOVE l_policy_guid := l_from_policy_guids(l_from_ctr) ; l_metric_name := l_from_metric_names(l_from_ctr) ; l_from_ctr := l_from_ctr + 1; ELSIF (l_to_policy_guids(l_to_ctr) < l_from_policy_guids(l_from_ctr)) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Case 3 Add from to_list ' || ' policy guid =' || l_to_policy_guids(l_to_ctr) || ' metric name =[' || l_to_metric_names(l_to_ctr) || ']', G_MODULE_NAME) ; END IF ; IF (l_to_auto_enable(l_to_ctr) = MGMT_GLOBAL.G_TRUE) THEN l_action := 1; -- Set action to ADD l_policy_guid := l_to_policy_guids(l_to_ctr); l_metric_name := l_to_metric_names(l_to_ctr); END IF; l_to_ctr := l_to_ctr + 1; ELSIF (l_to_policy_guids(l_to_ctr) > l_from_policy_guids(l_from_ctr)) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Case 4: Remove policy cfg ' || ' policy guid =' || l_from_policy_guids(l_from_ctr) || ' metric name =[' || l_from_metric_names(l_from_ctr) || ']', G_MODULE_NAME) ; END IF ; -- Process from from_list l_action := 2; -- Set action to REMOVE l_policy_guid := l_from_policy_guids(l_from_ctr) ; l_metric_name := l_from_metric_names(l_from_ctr) ; l_from_ctr := l_from_ctr + 1; ELSE -- Policy exists in both to and from lists, Do nothing. l_to_ctr := l_to_ctr + 1; l_from_ctr := l_from_ctr + 1; END IF; IF (l_action = 1) THEN -- Add policy association EM_POLICY.copy_object_policy_assoc( p_src_object_guid => l_default_guid, p_src_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_dest_object_guid => l_target_guid, p_dest_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_policy_guid => l_policy_guid, p_policy_type => MGMT_GLOBAL.G_TYPE_POLICY, p_src_coll_name => l_metric_name, p_dest_coll_name => l_metric_name); ELSIF (l_action = 2) THEN -- Remove policy association, violations and summary status -- Get the list of CA referecences associated with the given target and policy -- that is being removed. All these references are handled at the end. l_temp_ca_id_list := EM_POLICY.get_ca_id_list( p_object_guid => l_default_guid, p_policy_guid => l_policy_guid ); IF ( (l_temp_ca_id_list IS NOT NULL) AND (l_temp_ca_id_list.COUNT > 0) ) THEN FOR id_ctr IN l_temp_ca_id_list.FIRST..l_temp_ca_id_list.LAST LOOP l_ca_id_list.extend(1); l_ca_id_list(l_ca_id_list.COUNT) := l_temp_ca_id_list(id_ctr); END LOOP; END IF; -- Remove target-metric associations, and do not decrement CA counters -- All CA references are decremented at the end EM_POLICY.remove_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_policy_guid, p_coll_name => NULL, p_remove_ca_assoc => MGMT_GLOBAL.G_FALSE); EM_SEVERITY.clear_open_alerts( p_target_guid => l_target_guid, p_policy_guid => l_policy_guid, p_key_values => NULL, p_is_metric => FALSE, p_clear_message => 'CLEARED: Policy association removed because of update to target type meta ver'); -- Remove policy eval details and summary EM_POLICY.remove_policy_eval_details( p_target_guid => l_target_guid, p_policy_guid => l_policy_guid); EM_POLICY.remove_policy_eval_summ( p_target_guid => l_target_guid, p_policy_guid => l_policy_guid); ELSE -- Do nothing NULL; END IF; END LOOP; -- Decrement the CA ids ref counter for CAs refereces that are deleted. -- Zero countered CAs are automatically deleted EM_POLICY.decrement_ca_ids(l_ca_id_list); END handle_type_meta_ver_change; -- Callback to handle the addition of new target type version PROCEDURE handle_tgttype_addition(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2) IS l_policy_guids MGMT_TARGET_GUID_ARRAY; BEGIN -- Get the list of policies that have to be added for this version SELECT policy_guid BULK COLLECT INTO l_policy_guids FROM mgmt_policies pol WHERE target_type = p_target_type AND policy_type = MGMT_GLOBAL.G_TYPE_POLICY AND (em_target.compare_type_meta_vers(start_type_meta_ver, p_type_meta_ver) <= 0) AND ( (end_type_meta_ver IS NULL) OR (em_target.compare_type_meta_vers(p_type_meta_ver, end_type_meta_ver) <= 0) ) AND NOT EXISTS ( SELECT NULL FROM mgmt_policy_type_versions ver WHERE ver.policy_guid = pol.policy_guid AND ver.type_meta_ver = p_type_meta_ver) ; IF ( (l_policy_guids IS NOT NULL) AND (l_policy_guids.COUNT > 0) ) THEN FORALL l_ctr IN l_policy_guids.FIRST..l_policy_guids.LAST INSERT INTO mgmt_policy_type_versions (policy_guid, type_meta_ver) VALUES (l_policy_guids(l_ctr), p_type_meta_ver); END IF; END handle_tgttype_addition; PROCEDURE set_repo_timing (p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS BEGIN em_check.check_range(p_value=>p_enabled, p_min_value=>0, p_max_value=>1, p_param_name=>' p_enabled ') ; UPDATE mgmt_policies SET repo_timing_enabled = p_enabled WHERE target_type = p_target_type AND policy_name = p_policy_name ; IF SQL%NOTFOUND THEN raise_application_error(MGMT_GLOBAL.POLICY_DOES_NOT_EXIST_ERR, 'No matching policy exists'); END IF ; END set_repo_timing; PROCEDURE get_policy_info( p_policy_guid IN RAW, p_policy_info OUT NOCOPY POLICY_INFO_REC) IS l_proc_name CONSTANT VARCHAR2(30) := 'get_policy_info' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'Enter ' || ' policy_guid =[' || p_policy_guid || ']', G_MODULE_NAME) ; END IF ; SELECT p.policy_guid, m.metric_guid, m.metric_name INTO p_policy_info.policy_guid, p_policy_info.metric_guid, p_policy_info.metric_name FROM mgmt_policies p, (SELECT target_type, metric_name, metric_guid, MAX(type_meta_ver) FROM mgmt_metrics GROUP BY target_type, metric_name, metric_guid ) m WHERE p.target_type = m.target_type AND p.metric_guid = m.metric_guid AND p.policy_guid = p_policy_guid; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' Exit ' || ' policy_guid =[' || p_policy_guid || ']' || ' metric name =[' || p_policy_info.metric_name || ']', G_MODULE_NAME) ; END IF ; END get_policy_info; PROCEDURE get_policy_info_for_target( p_policy_guid IN RAW, p_target_guid IN RAW, p_policy_info OUT NOCOPY POLICY_INFO_REC) IS l_proc_name CONSTANT VARCHAR2(30) := 'get_policy_info_for_target' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||'Enter ' || ' target_guid =[' || p_target_guid || ']' || ' policy_guid =[' || p_policy_guid || ']', G_MODULE_NAME) ; END IF ; SELECT p.policy_guid, m.metric_guid, m.metric_name INTO p_policy_info.policy_guid, p_policy_info.metric_guid, p_policy_info.metric_name FROM mgmt_targets t, mgmt_metrics m, mgmt_policies p WHERE p.target_type = m.target_type AND p.metric_guid = m.metric_guid AND m.target_type = t.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 = ' ')) AND p.policy_guid = p_policy_guid AND t.target_guid = p_target_guid; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name||' Exit ' || ' target_guid =[' || p_target_guid || ']' || ' policy_guid =[' || p_policy_guid || ']' || ' metric name =[' || p_policy_info.metric_name || ']', G_MODULE_NAME) ; END IF ; END get_policy_info_for_target; PROCEDURE delete_policy_data( p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2) IS l_policy_guid mgmt_policies.policy_guid%TYPE; l_delete_stmt VARCHAR2(4000); l_counter INTEGER :=0; l_proc_name VARCHAR2(32) := 'delete_policy_data'; l_annot_purged NUMBER := 0; l_notify_log_purged NUMBER := 0; l_notify_rq NUMBER := 0; l_policy_purge_batchsize NUMBER := 0; l_batchsize NUMBER := 0; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Target type = [' || p_target_type || ']' || ' Policy Name = [' || p_policy_name || ']', G_MODULE_NAME); END IF; BEGIN SELECT parameter_value INTO l_policy_purge_batchsize FROM MGMT_PARAMETERS WHERE parameter_name = 'policy_purge_batchsize_parameter' ; EXCEPTION WHEN NO_DATA_FOUND THEN l_policy_purge_batchsize := mgmt_global.MAX_COMMIT; END; l_policy_guid := MGMT_POLICY.get_policy_guid(p_target_type, p_policy_name); -- Remove object-policy associations, and decrement ctr for any CA assoc. remove_object_policy_assocs( p_policy_guid => l_policy_guid, p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); --disable the trigger on MGMT_VIOLATION for delete EM_SEVERITY.g_from_purge_api := TRUE ; -- Remove from all tables that have policy guid -- MGMT_VIOLATION_*, MGMT_POLICY_EVAL_* tables are included here FOR crec IN ( SELECT o.name table_name, tgc.name target_column_name, mgc.name policy_column_name FROM sys.obj$ o, sys.tab$ t, sys.col$ tgc, sys.col$ mgc WHERE o.owner# = userenv('SCHEMAID') AND tgc.name like ('%TARGET_GUID%') AND tgc.obj# = o.obj# AND mgc.name like ('%POLICY_GUID%') AND mgc.obj# = o.obj# AND o.name like ('MGMT_%') AND o.obj# = t.obj# AND bitand(t.property, 1) = 0 AND bitand(tgc.property,32) = 0 AND bitand(tgc.property,512) = 0 AND bitand(mgc.property,32) = 0 AND bitand(mgc.property,512) = 0 ORDER BY o.name ) LOOP l_delete_stmt := 'DELETE FROM ' || DBMS_ASSERT.SQL_OBJECT_NAME(crec.table_name) || ' WHERE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(crec.policy_column_name) || ' = :policy_guid AND ROWNUM <= :batchsize' ; IF crec.table_name = 'MGMT_VIOLATIONS' THEN l_batchsize := l_policy_purge_batchsize ; ELSE l_batchsize := mgmt_global.MAX_COMMIT ; END IF; LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Executing SQL ' || l_delete_stmt, G_MODULE_NAME); END IF; EXECUTE IMMEDIATE l_delete_stmt USING l_policy_guid,l_batchsize; l_counter := SQL%ROWCOUNT; -- Delete from other table through trigger has been disabled -- for MGMT_VIOLATIONS using EM_SEVERITY.G_FROM_PURGE_API flag -- hence deleting it using procedure. IF crec.table_name = 'MGMT_VIOLATIONS' AND EM_SEVERITY.p_violation_guids IS NOT NULL AND EM_SEVERITY.p_violation_guids.count > 0 THEN EM_SEVERITY.purge_for_violation_guids ( p_violation_guids => EM_SEVERITY.p_violation_guids, p_annot_purged => l_annot_purged, p_notify_log_purged => l_notify_log_purged, p_notify_rq => l_notify_rq ); EM_SEVERITY.p_violation_guids.delete; EM_SEVERITY.p_violation_guids := MGMT_TARGET_GUID_ARRAY(); END IF; COMMIT; IF l_counter < mgmt_global.MAX_COMMIT THEN EXIT; END IF; END LOOP; END LOOP; --enable the trigger on MGMT_VIOLATION for delete EM_SEVERITY.g_from_purge_api := FALSE ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Exit.', G_MODULE_NAME); END IF; END delete_policy_data; -- Dump routines PROCEDURE dump_access_list( p_access_list IN MGMT_TEMPLATE_ACCESS_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_template_access MGMT_TEMPLATE_ACCESS; l_module VARCHAR2(32); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_access_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || '> Access List is NULL', l_module); ELSIF (p_access_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || '> Access List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || '> Access List count is ' || p_access_list.COUNT, l_module); FOR acc_ctr IN p_access_list.FIRST..p_access_list.LAST LOOP l_template_access := p_access_list(acc_ctr); EMDW_LOG.DEBUG(p_prefix || '> ' || acc_ctr || ' User Name = [' || l_template_access.user_name || '] Privilege = [' || l_template_access.privilege || ']', l_module); END LOOP; END IF; END dump_access_list; PROCEDURE dump_key_col_cond_list( p_key_cond_list IN MGMT_POLICY_KEY_COL_COND_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_key_cond MGMT_POLICY_KEY_COL_COND; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_key_cond_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' KeyCond List is NULL', l_module); ELSIF (p_key_cond_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' KeyCond List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' KeyCond List count is ' || p_key_cond_list.COUNT, l_module); FOR key_ctr IN p_key_cond_list.FIRST..p_key_cond_list.LAST LOOP l_key_cond := p_key_cond_list(key_ctr); l_prefix := p_prefix || ' KeyCond ' || key_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Key Val = [' || l_key_cond.key_value || '] Wildcard = [' || l_key_cond.has_wildcard || '] Key Col Name = [' || l_key_cond.key_column_name || ']', l_module); END LOOP; END IF; END dump_key_col_cond_list; PROCEDURE dump_param_val_list ( p_param_values IN MGMT_POLICY_PARAM_VAL_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_param_value MGMT_POLICY_PARAM_VAL; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_param_values IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Param Values is NULL', l_module); ELSIF (p_param_values.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Param Values count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Param Values count is ' || p_param_values.COUNT, l_module); FOR param_ctr IN p_param_values.FIRST..p_param_values.LAST LOOP l_param_value := p_param_values(param_ctr); l_prefix := p_prefix || ' Param ' || param_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Param Name = [' || l_param_value.param_name || '] Crit = [' || l_param_value.crit_threshold || '] Warn = [' || l_param_value.warn_threshold || '] Info = [' || l_param_value.info_threshold || ']', l_module); END LOOP; END IF; END dump_param_val_list; PROCEDURE dump_key_val_list ( p_key_val_list IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_key_val MGMT_POLICY_KEY_VAL; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_key_val_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Key List is NULL', l_module); ELSIF (p_key_val_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Key List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Key List count is ' || p_key_val_list.COUNT, l_module); FOR key_ctr IN p_key_val_list.FIRST..p_key_val_list.LAST LOOP l_key_val := p_key_val_list(key_ctr); l_prefix := p_prefix || ' KeyVal ' || key_ctr || ' > '; dump_key_col_cond_list(l_key_val.key_value, l_prefix, l_module); EMDW_LOG.DEBUG(l_prefix || ' Exception = [' || l_key_val.is_exception || '] P Override = [' || l_key_val.prevent_override || '] Crit CA = [' || l_key_val.crit_action_name || '] Warn CA = [' || l_key_val.warn_action_name || '] Info CA = [' || l_key_val.info_action_name || ']', l_module); EMDW_LOG.DEBUG(l_prefix || ' Simul Act = [' || l_key_val.simultaneous_actions || '] Importance = [' || l_key_val.importance || '] Num Occ = [' || l_key_val.num_occurrences || '] Push = [' || l_key_val.is_push || '] Oper = [' || l_key_val.condition_operator || '] Msg = [' || l_key_val.message || '] Msg ID = [' || l_key_val.message_nlsid || '] ClrMsg = [' || l_key_val.clear_message || '] ClrMsgID = [' || l_key_val.clear_message_nlsid || '] Agent Fixit = [' || l_key_val.agent_fixit || ']', l_module); dump_param_val_list(l_key_val.param_values, l_prefix, l_module); END LOOP; END IF; END dump_key_val_list; PROCEDURE dump_metric_list( p_metric_list IN MGMT_MNTR_METRIC_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_template_metric MGMT_MNTR_METRIC; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_metric_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Metric List is NULL', l_module); ELSIF (p_metric_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Metric List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Metric List count is ' || p_metric_list.COUNT, l_module); FOR met_ctr IN p_metric_list.FIRST..p_metric_list.LAST LOOP l_template_metric := p_metric_list(met_ctr); l_prefix := p_prefix || ' Metric ' || met_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Metric Name = [' || l_template_metric.metric_name || '] Metric Col = [' || l_template_metric.metric_column || '] coll Name = [' || l_template_metric.coll_name || '] enabled = [' || l_template_metric.is_enabled || ']', l_module); dump_key_val_list(l_template_metric.key_val_list, l_prefix, l_module); END LOOP; END IF; END dump_metric_list; PROCEDURE dump_policy_list( p_policy_list IN MGMT_MNTR_POLICY_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_template_policy MGMT_MNTR_POLICY; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_policy_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Policy List is NULL', l_module); ELSIF (p_policy_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Policy List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Policy List count is ' || p_policy_list.COUNT, l_module); FOR pol_ctr IN p_policy_list.FIRST..p_policy_list.LAST LOOP l_template_policy := p_policy_list(pol_ctr); l_prefix := p_prefix || ' Policy ' || pol_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Policy Name = [' || l_template_policy.policy_name || '] coll Name = [' || l_template_policy.coll_name || '] enabled = [' || l_template_policy.is_enabled || ']', l_module); dump_key_val_list(l_template_policy.key_val_list, l_prefix, l_module); END LOOP; END IF; END dump_policy_list; PROCEDURE dump_cred_row_list( p_cred_row_list IN MGMT_CRED_ROW_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_cred_row MGMT_CRED_ROW_RECORD; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_cred_row_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' CollMet Cred List is NULL', l_module); ELSIF (p_cred_row_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' CollMet Cred List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' CollMet Cred List count is ' || p_cred_row_list.COUNT, l_module); FOR credr_ctr IN p_cred_row_list.FIRST..p_cred_row_list.LAST LOOP l_cred_row := p_cred_row_list(credr_ctr); l_prefix := p_prefix || ' CollCredRow ' || credr_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Set Column Name = [' || l_cred_row.credential_set_column || ']' || ' Cred Value = [' || l_cred_row.credential_value || ']', l_module); END LOOP; END IF; END dump_cred_row_list; PROCEDURE dump_cred_list( p_cred_list IN MGMT_CRED_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_cred_rec MGMT_CRED_RECORD; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_cred_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' CollMet Cred List is NULL', l_module); ELSIF (p_cred_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' CollMet Cred List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' CollMet Cred List count is ' || p_cred_list.COUNT, l_module); FOR cred_ctr IN p_cred_list.FIRST..p_cred_list.LAST LOOP l_cred_rec := p_cred_list(cred_ctr); l_prefix := p_prefix || ' CollCred ' || cred_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' User Name = [' || l_cred_rec.user_name || ']' || ' Cred Set Name = [' || l_cred_rec.credential_set_name || ']', l_module); dump_cred_row_list(l_cred_rec.creds, l_prefix, l_module); END LOOP; END IF; END dump_cred_list; PROCEDURE dump_coll_prop_list( p_prop_list IN MGMT_COLL_PROP_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_coll_prop MGMT_COLL_PROP; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_prop_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Coll Prop List is NULL', l_module); ELSIF (p_prop_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Coll Prop List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Coll Prop List count is ' || p_prop_list.COUNT, l_module); FOR prop_ctr IN p_prop_list.FIRST..p_prop_list.LAST LOOP l_coll_prop := p_prop_list(prop_ctr); l_prefix := p_prefix || ' CollProp ' || prop_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Prop Name = [' || l_coll_prop.name || ']' || ' Prop Value = [' || l_coll_prop.value || ']', l_module); END LOOP; END IF; END dump_coll_prop_list; PROCEDURE dump_coll_metric_list( p_cmetric_list IN MGMT_COLL_METRIC_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_coll_metric MGMT_COLL_METRIC; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_cmetric_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Coll Metric List is NULL', l_module); ELSIF (p_cmetric_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Coll Metric List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Coll Metric List count is ' || p_cmetric_list.COUNT, l_module); FOR met_ctr IN p_cmetric_list.FIRST..p_cmetric_list.LAST LOOP l_coll_metric := p_cmetric_list(met_ctr); l_prefix := p_prefix || ' CollMet ' || met_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' Metric Name = [' || l_coll_metric.metric_name || ']', l_module); dump_coll_prop_list(l_coll_metric.property_list, l_prefix, l_module); dump_cred_list(l_coll_metric.credentials, l_prefix, l_module); END LOOP; END IF; END dump_coll_metric_list; PROCEDURE dump_coll_schedule( p_coll_sched IN MGMT_COLL_SCHEDULE_OBJ DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_module VARCHAR2(32); l_months VARCHAR2(2000); l_days VARCHAR2(2000); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_coll_sched IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Collection Schedule is NULL', l_module); ELSE l_months := NULL; IF ((p_coll_sched.months IS NOT NULL) AND (p_coll_sched.months.COUNT > 0) ) THEN l_months := ' '; FOR mon_ctr IN p_coll_sched.months.FIRST..p_coll_sched.months.LAST LOOP l_months := l_months || ' ' || p_coll_sched.months(mon_ctr) || ' '; END LOOP; END IF; l_days := NULL; IF ((p_coll_sched.days IS NOT NULL) AND (p_coll_sched.days.COUNT > 0) ) THEN l_days := ' '; FOR day_ctr IN p_coll_sched.days.FIRST..p_coll_sched.days.LAST LOOP l_days := l_days || ' ' || p_coll_sched.days(day_ctr) || ' '; END LOOP; END IF; EMDW_LOG.DEBUG(p_prefix || ' Schedule ' || ' freq code = [' || p_coll_sched.frequency_code || ']' || ' start time = [' || to_char(p_coll_sched.start_time, 'YYYY-MM-DD HH24:MI:SS') || ']' || ' end time = [' || to_char(p_coll_sched.end_time, 'YYYY-MM-DD HH24:MI:SS') || ']' || ' exec hrs = [' || p_coll_sched.execution_hours || ']' || ' exec min = [' || p_coll_sched.execution_minutes || ']' || ' interval = [' || p_coll_sched.interval || ']' || ' months = [' || l_months || ']' || ' days = [' || l_days || ']', l_module); END IF; END dump_coll_schedule; PROCEDURE dump_collection_list( p_coll_list IN MGMT_MNTR_COLLECTION_ARRAY DEFAULT NULL, p_prefix IN VARCHAR2 DEFAULT ' ', p_module IN VARCHAR2 DEFAULT NULL) IS l_template_coll MGMT_MNTR_COLLECTION; l_module VARCHAR2(32); l_prefix VARCHAR2(128); BEGIN l_module := NVL(p_module, G_MODULE_NAME); IF (p_coll_list IS NULL) THEN EMDW_LOG.DEBUG(p_prefix || ' Collection List is NULL', l_module); ELSIF (p_coll_list.COUNT <= 0) THEN EMDW_LOG.DEBUG(p_prefix || ' Collection List count is 0', l_module); ELSE EMDW_LOG.DEBUG(p_prefix || ' Collection List count is ' || p_coll_list.COUNT, l_module); FOR coll_ctr IN p_coll_list.FIRST..p_coll_list.LAST LOOP l_template_coll := p_coll_list(coll_ctr); l_prefix := p_prefix || ' Collection ' || coll_ctr || ' > '; EMDW_LOG.DEBUG(l_prefix || ' coll Name = [' || l_template_coll.coll_name || ']' || ' enabled = [' || l_template_coll.is_enabled || ']' || ' transposed = [' || l_template_coll.is_transposed || ']' || ' store = [' || l_template_coll.store_metric || ']' || ' upload frq = [' || l_template_coll.upload_frequency || ']' || ' sched ex = [' || SUBSTR(l_template_coll.schedule_ex, 1, 200) || ']', l_module); dump_coll_schedule(l_template_coll.schedule, l_prefix, l_module); dump_coll_metric_list(l_template_coll.metric_list, l_prefix, l_module); END LOOP; END IF; END dump_collection_list; PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_POLICY_NAME, p_value); END DBMSJOB_EXTENDED_SQL_TRACE_ON; -- Clear suppressions that are expired. These are suppress_until_date type exemptions. PROCEDURE clear_expired_suppressions AS TYPE t_pguidList IS TABLE OF mgmt_current_violation.policy_guid%TYPE; v_policy_guids t_pguidList; TYPE t_tguidList IS TABLE OF mgmt_current_violation.target_guid%TYPE; v_target_guids t_tguidList; BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_POLICY_NAME); -- We could do an "update returning", but we really want to return a unique -- list of policy guids so we don't call the update compliance procedure more -- than necessary. SELECT UNIQUE target_guid, policy_guid BULK COLLECT INTO v_target_guids, v_policy_guids FROM mgmt_current_violation WHERE exempt_code = MGMT_GLOBAL.G_SUPPRESS_DATE and exempt_until < SYSDATE; -- If any of the rows need to be updated, update them, then we need to recalculate the -- compliance score for each target-policy. IF v_target_guids IS NOT NULL AND v_target_guids.COUNT > 0 THEN FOR i IN v_target_guids.FIRST..v_target_guids.LAST LOOP UPDATE mgmt_current_violation SET exempt_code = MGMT_GLOBAL.G_SUPPRESS_NONE, exempt_by = NULL, exempt_until = NULL WHERE target_guid=v_target_guids(i) and policy_guid=v_policy_guids(i) and exempt_code = MGMT_GLOBAL.G_SUPPRESS_DATE and exempt_until < SYSDATE; EM_POLICY.update_compliance_score( v_target_guids(i), v_policy_guids(i) ); COMMIT; END LOOP; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; MGMT_LOG.LOG_ERROR(EM_POLICY.G_MODULE_NAME, null, 'Error while clearing expired suppressions: ' || ' Error: ' || SUBSTR(SQLERRM, 1, 1000)); END clear_expired_suppressions; -- Procedure for deletion of all policies based on target_guid, metric_guid and key-value -- Would be call by callback EM_METRIC.handle_metric_keyval_deletion PROCEDURE del_keyval_policy ( p_target_guid IN mgmt_targets.target_guid%TYPE, p_metric_guid IN mgmt_metrics.metric_guid%TYPE, p_key_value IN mgmt_metrics_raw.key_value%TYPE ) IS l_cfg_count NUMBER ; BEGIN EM_POLICY.remove_policy_assoc_cfgs ( p_object_guid => p_target_guid, p_policy_guid => p_metric_guid, p_coll_name => NULL, p_key_value => p_key_value, p_key_operator => NULL ); EM_POLICY.remove_policy_assoc_cfg_params ( p_object_guid => p_target_guid, p_policy_guid => p_metric_guid, p_coll_name => NULL, p_key_value => p_key_value, p_key_operator => NULL, p_param_name => NULL ); -- If all key cfgs have been deleted, delete the association SELECT COUNT(1) INTO l_cfg_count FROM mgmt_policy_assoc_cfg WHERE object_guid = p_target_guid AND policy_guid = p_metric_guid ; IF (l_cfg_count = 0) THEN EM_POLICY.remove_policy_assocs( p_object_guid => p_target_guid, p_policy_guid => p_metric_guid, p_coll_name => NULL ); END IF; END del_keyval_policy ; --Procedure for creating a configuration standard policy. --For the given existing policy and target type it creates a new policy with --all of its existing policy properties. PROCEDURE create_cs_policy ( p_policy_name VARCHAR2, p_target_type VARCHAR2, p_config_std_name VARCHAR2, p_rule_name VARCHAR2, p_author VARCHAR2, p_version NUMBER, p_cs_policy_guid OUT RAW ) IS l_cs_policy_name mgmt_policies.policy_name%TYPE; l_orig_policy_guid mgmt_policies.policy_guid%TYPE; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_config_std_name, 'p_config_std_name'); EM_CHECK.check_not_null(p_rule_name, 'p_rule_name'); EM_CHECK.check_not_null(p_author, 'p_author'); l_cs_policy_name := MGMT_POLICY.generate_cs_policy_name(p_policy_name, p_config_std_name,p_rule_name,p_author,p_version); --create new policy guid for cs policy p_cs_policy_guid := MGMT_POLICY.generate_policy_guid(p_target_type, l_cs_policy_name); --Get the original policy guid BEGIN SELECT policy_guid INTO l_orig_policy_guid FROM mgmt_policies WHERE policy_name = p_policy_name AND target_type = p_target_type AND policy_type = MGMT_GLOBAL.G_TYPE_POLICY; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.POLICY_DOES_NOT_EXIST_ERR, 'No matching policy exists : Check if used policy is a metric policy'); END; --NOTE: The insertion code given below is a temporary measure for 10.2.0.3. --It is not supposed to be used in 11G.Ideally sdk create policy methods --should be used instead of the below direct insertions.Since this code is --a throw away for 11G , for 10.2.0.3, direct insertions are used. --TBD: For post 10.2.0.3 , use SDK create_policy methods instead of the direct --insertions. --insert into mgmt_policies table INSERT INTO mgmt_policies ( policy_guid, policy_name, metric_guid, target_type, start_type_meta_ver, end_type_meta_ver, policy_type, policy_label_nlsid, description, description_nlsid, author, auto_enable, cs_consider_percentage, impact, impact_nlsid, recommendation, recommendation_nlsid, violation_level, condition_type, condition, condition_operator, detailed_url_link, message, message_nlsid, clear_message, clear_message_nlsid, owner, created_date, last_updated_date, last_updated_by ) ( SELECT p_cs_policy_guid,l_cs_policy_name, metric_guid, target_type, start_type_meta_ver, end_type_meta_ver, MGMT_GLOBAL.G_TYPE_CS_POLICY, policy_label_nlsid, description, description_nlsid, author, 0, cs_consider_percentage, impact, impact_nlsid, recommendation, recommendation_nlsid, violation_level, condition_type, condition, condition_operator, detailed_url_link, message, message_nlsid, clear_message, clear_message_nlsid, owner, SYSDATE,SYSDATE,last_updated_by FROM mgmt_policies p WHERE p.policy_guid = l_orig_policy_guid ); --insert bind vars for new cs policy INSERT INTO mgmt_policy_bind_vars ( policy_guid, bind_column_name, bind_column_type ) ( SELECT p_cs_policy_guid,bind_column_name,bind_column_type FROM mgmt_policy_bind_vars v WHERE v.policy_guid = l_orig_policy_guid ); --insert into category map INSERT INTO mgmt_category_map ( target_type, type_meta_ver, object_type, object_guid, class_name, category_name ) ( SELECT target_type, type_meta_ver, MGMT_GLOBAL.G_TYPE_CS_POLICY, p_cs_policy_guid, class_name, category_name FROM mgmt_category_map m WHERE m.object_guid = l_orig_policy_guid ); -- Insert into policy parameters INSERT INTO mgmt_policy_parameters ( policy_guid, param_name, param_name_nlsid, param_type ) ( SELECT p_cs_policy_guid, param_name, param_name_nlsid, param_type FROM mgmt_policy_parameters p WHERE p.policy_guid = l_orig_policy_guid ); --Insert violation context info INSERT INTO mgmt_policy_viol_ctxt_def ( policy_guid, column_name, metric_guid, column_position, is_hidden, url_link_type, url_link_template ) ( SELECT p_cs_policy_guid,column_name, metric_guid, column_position, is_hidden, url_link_type, url_link_template FROM mgmt_policy_viol_ctxt_def ctxt WHERE ctxt.policy_guid = l_orig_policy_guid ); --Insert into mgmt_policy_type_versions INSERT INTO mgmt_policy_type_versions ( policy_guid, type_meta_ver ) ( SELECT p_cs_policy_guid , type_meta_ver FROM mgmt_policy_type_versions tv WHERE tv.policy_guid = l_orig_policy_guid ); --insert into mgmt_policy_assoc INSERT INTO mgmt_policy_assoc ( object_guid, policy_guid, coll_name, object_type, policy_type, is_enabled ) ( SELECT object_guid, p_cs_policy_guid, coll_name, object_type, MGMT_GLOBAL.G_TYPE_CS_POLICY,is_enabled FROM mgmt_policy_assoc a WHERE a.policy_guid = l_orig_policy_guid AND a.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT ); --insert into mgmt_policy_assoc_cfg --TBD:can the collname be same of original or different? INSERT INTO mgmt_policy_assoc_cfg ( object_guid, policy_guid, coll_name, 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 ) ( SELECT cfg.object_guid, p_cs_policy_guid, cfg.coll_name, cfg.key_value, cfg.key_operator, cfg.eval_order, cfg.is_exception, cfg.has_active_baseline, cfg.prevent_override, cfg.crit_action_job_id, cfg.warn_action_job_id, cfg.info_action_job_id, cfg.fixit_job, cfg.simultaneous_actions, cfg.importance, cfg.num_occurrences, cfg.is_push, cfg.condition_operator, cfg.message, cfg.message_nlsid, cfg.clear_message, cfg.clear_message_nlsid FROM mgmt_policy_assoc_cfg cfg, mgmt_policy_assoc a WHERE cfg.policy_guid = l_orig_policy_guid AND a.object_guid = cfg.object_guid AND a.policy_guid = cfg.policy_guid AND a.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT ); --TBD: check whether clear_excepted_key to be called or not. --insert into mgmt_policy_assoc_cfg_params INSERT INTO mgmt_policy_assoc_cfg_params ( object_guid, policy_guid, coll_name, key_value, key_operator, param_name, crit_threshold, warn_threshold, info_threshold ) ( SELECT cp.object_guid, p_cs_policy_guid, cp.coll_name, cp.key_value, cp.key_operator, cp.param_name, cp.crit_threshold, cp.warn_threshold, cp.info_threshold FROM mgmt_policy_assoc_cfg_params cp, mgmt_policy_assoc a WHERE cp.policy_guid = l_orig_policy_guid AND a.object_guid = cp.object_guid AND a.policy_guid = cp.policy_guid AND a.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT ); END create_cs_policy; -- Only those parameter that defines the PK are used. -- Still all the possible input parameters are specified -- because agent can send all these parameters with delete request PROCEDURE assoc_cfg_del_handler ( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT 0, p_eval_order IN NUMBER DEFAULT 1, p_is_exception IN NUMBER DEFAULT 0, p_has_active_baseline IN NUMBER DEFAULT 0, p_prevent_override IN NUMBER DEFAULT 0, p_crit_action_job_id IN RAW DEFAULT NULL, p_warn_action_job_id IN RAW DEFAULT NULL, p_info_action_job_id IN RAW DEFAULT NULL, p_fixit_job IN VARCHAR2 DEFAULT NULL, p_simultaneous_actions IN NUMBER DEFAULT 1, p_importance IN NUMBER DEFAULT NULL, p_num_occurrences IN NUMBER DEFAULT 1, p_is_push IN NUMBER DEFAULT 0, p_condition_operator IN NUMBER DEFAULT 0, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_clear_message_nlsid IN VARCHAR2 DEFAULT NULL ) IS l_target_guid RAW(16) := null; BEGIN EMD_LOADER.check_deletion_allowed(p_object_guid,p_policy_guid, EMD_LOADER.GET_CURRENT_EMD_URL(), 'mgmt_policy_assoc_cfg'); DELETE mgmt_policy_assoc_cfg WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name AND key_value = p_key_value AND key_operator = p_key_operator RETURNING object_guid INTO l_target_guid; IF l_target_guid IS NULL THEN EMDW_LOG.WARN( 'Unable to delete from mgmt_policy_assoc_cfg as no records exists for ' || ' object_guid : ' || p_object_guid || ' policy_guid : ' || p_policy_guid || ' coll_name : ' || p_coll_name || ' key_value : ' || p_key_value || ' key_operator : '|| p_key_operator, G_MODULE_NAME); END IF; END assoc_cfg_del_handler; -- Only those parameter that defines the PK are used. -- Still all the possible input parameters are specified -- because agent can send all these parameters with delete request PROCEDURE assoc_cfg_params_del_handler ( p_object_guid IN RAW, p_policy_guid IN RAW, p_coll_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT NULL, p_param_name IN VARCHAR2 DEFAULT NULL, p_crit_threshold IN VARCHAR2 DEFAULT NULL, p_warn_threshold IN VARCHAR2 DEFAULT NULL, p_info_threshold IN VARCHAR2 DEFAULT NULL ) IS l_target_guid RAW(16) := null; BEGIN EMD_LOADER.check_deletion_allowed(p_object_guid,p_policy_guid, EMD_LOADER.GET_CURRENT_EMD_URL(), 'mgmt_policy_assoc_cfg_params'); DELETE FROM mgmt_policy_assoc_cfg_params WHERE object_guid = p_object_guid AND policy_guid = p_policy_guid AND coll_name = p_coll_name AND key_value = p_key_value AND key_operator = p_key_operator AND param_name = p_param_name RETURNING object_guid INTO l_target_guid; IF l_target_guid IS NULL THEN EMDW_LOG.WARN( 'Unable to delete from mgmt_policy_assoc_cfg_params as no records exists for ' || ' object_guid : '|| p_object_guid || ' policy_guid : ' || p_policy_guid || ' coll_name : ' || p_coll_name || ' key_value : ' || p_key_value || ' key_operator : ' || p_key_operator || ' param_name : ' || p_param_name, G_MODULE_NAME); END IF; END assoc_cfg_params_del_handler; END em_policy; / show errors