Rem Rem $Header: sdk_violation_pkgbody.sql 09-jan-2006.05:12:50 hbadheka Exp $ Rem Rem sdk_violation_pkgbody.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem sdk_violation_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hbadheka 12/25/05 - add get_violation_guid Rem pmodi 08/23/05 - Bug:4558731 - pass collection name in Rem add_violation_suppression Rem niramach 05/16/05 - Fix bug 4369453. Rem rpinnama 02/17/05 - Add not null checks Rem niramach 02/03/05 - exempt->suppress conversion. Rem jsadras 01/20/05 - purge violations Rem jsadras 12/07/04 - purge_apis Rem groyal 09/23/04 - Set/clear exempt_by as appropriate Rem rpinnama 09/09/04 - Add cfg_* to log_violation APIs Rem rpinnama 09/01/04 - Add context before adding the violation Rem rpinnama 08/03/04 - Get target_guid instead of generating one. Rem rpinnama 07/26/04 - rpinnama_add_policy_api Rem rpinnama 07/14/04 - Created Rem CREATE OR REPLACE PACKAGE BODY mgmt_violation AS -- -- API to log a metric threshold violation -- PROCEDURE log_threshold_violation( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_collection_timestamp IN DATE DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL, p_value IN NUMBER DEFAULT NULL, p_string_value IN VARCHAR2 DEFAULT NULL, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_message_params IN VARCHAR2 DEFAULT NULL, p_action_message IN VARCHAR2 DEFAULT NULL, p_action_message_nlsid IN VARCHAR2 DEFAULT NULL, p_action_message_params IN VARCHAR2 DEFAULT NULL, p_advisory_id IN VARCHAR2 DEFAULT NULL, p_cfg_coll_name IN VARCHAR2 DEFAULT NULL, p_cfg_key_value IN VARCHAR2 DEFAULT NULL, p_cfg_key_operator IN NUMBER DEFAULT NULL, p_violation_ctxt_list IN MGMT_VIOL_NAME_VALUE_ARRAY DEFAULT NULL, p_notification_status IN NUMBER DEFAULT MGMT_GLOBAL.G_NOTIF_STATUS_NEW) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; BEGIN -- Check for not nulls EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_metric_name, 'p_metric_name'); EM_CHECK.check_not_null(p_metric_column, 'p_metric_column'); l_target_guid := mgmt_target.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_metric_guid := mgmt_metric.get_metric_guid( p_target_type => p_target_type, p_metric_name => p_metric_name, p_metric_column => p_metric_column); log_violation( p_target_guid => l_target_guid, p_policy_guid => l_metric_guid, p_key_value => p_key_value, p_collection_timestamp => p_collection_timestamp, p_violation_level => p_violation_level, p_violation_type => 0, -- Passing 0 as default p_value => p_value, p_string_value => p_string_value, p_message => p_message, p_message_nlsid => p_message_nlsid, p_message_params => p_message_params, p_action_message => p_action_message, p_action_message_nlsid => p_action_message_nlsid, p_action_message_params => p_action_message_params, p_advisory_id => p_advisory_id, p_cfg_coll_name => p_cfg_coll_name, p_cfg_key_value => p_cfg_key_value, p_cfg_key_operator => p_cfg_key_operator, p_violation_ctxt_list => p_violation_ctxt_list, p_notification_status => p_notification_status); END log_threshold_violation; -- -- API to log a policy violation -- PROCEDURE log_policy_violation( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_policy_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_collection_timestamp IN DATE DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL , p_action_message IN VARCHAR2 DEFAULT NULL, p_action_message_nlsid IN VARCHAR2 DEFAULT NULL, p_action_message_params IN VARCHAR2 DEFAULT NULL, p_advisory_id IN VARCHAR2 DEFAULT NULL, p_cfg_coll_name IN VARCHAR2 DEFAULT NULL, p_cfg_key_value IN VARCHAR2 DEFAULT NULL, p_cfg_key_operator IN NUMBER DEFAULT NULL, p_violation_ctxt_list IN MGMT_VIOL_NAME_VALUE_ARRAY DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; BEGIN -- Check for not nulls. EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); l_target_guid := mgmt_target.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_policy_guid := mgmt_policy.get_policy_guid( p_target_type => p_target_type, p_policy_name => p_policy_name); -- The value cannot be provided as it the policy could be a condition on -- multiple columns, Violation context contains details about what -- values violated the policy log_violation( p_target_guid => l_target_guid, p_policy_guid => l_policy_guid, p_key_value => p_key_value, p_collection_timestamp => p_collection_timestamp, p_violation_level => p_violation_level, p_violation_type => MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY, p_value => NULL, p_string_value => NULL, p_message => NULL, p_message_nlsid => NULL, p_message_params => NULL, p_action_message => p_action_message, p_action_message_nlsid => p_action_message_nlsid, p_action_message_params => p_action_message_params, p_advisory_id => p_advisory_id, p_cfg_coll_name => p_cfg_coll_name, p_cfg_key_value => p_cfg_key_value, p_cfg_key_operator => p_cfg_key_operator, p_violation_ctxt_list => p_violation_ctxt_list); END log_policy_violation; -- -- Log a violation given a target_guid, policy_guid -- PROCEDURE log_violation( p_target_guid IN RAW, p_policy_guid IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_collection_timestamp IN DATE DEFAULT NULL, p_violation_level IN NUMBER DEFAULT NULL, p_violation_type IN NUMBER DEFAULT NULL, p_value IN NUMBER DEFAULT NULL, p_string_value IN VARCHAR2 DEFAULT NULL, p_message IN VARCHAR2 DEFAULT NULL, p_message_nlsid IN VARCHAR2 DEFAULT NULL, p_message_params IN VARCHAR2 DEFAULT NULL, p_action_message IN VARCHAR2 DEFAULT NULL, p_action_message_nlsid IN VARCHAR2 DEFAULT NULL, p_action_message_params IN VARCHAR2 DEFAULT NULL, p_advisory_id IN VARCHAR2 DEFAULT NULL, p_cfg_coll_name IN VARCHAR2 DEFAULT NULL, p_cfg_key_value IN VARCHAR2 DEFAULT NULL, p_cfg_key_operator IN NUMBER DEFAULT NULL, p_violation_ctxt_list IN MGMT_VIOL_NAME_VALUE_ARRAY DEFAULT NULL, p_notification_status IN NUMBER DEFAULT MGMT_GLOBAL.G_NOTIF_STATUS_NEW) IS l_violation_guid mgmt_violations.violation_guid%TYPE; l_viol_nvpair mgmt_viol_name_value; BEGIN EM_SEVERITY.add_violation_context( p_target_guid => p_target_guid, p_policy_guid => p_policy_guid, p_key_value => p_key_value, p_collection_timestamp => p_collection_timestamp, p_violation_ctxt_list => p_violation_ctxt_list); -- Not passing p_violation_guid, violation_duration, -- annotated_flag EM_SEVERITY.insert_violation( p_target_guid => p_target_guid, p_policy_guid => p_policy_guid, p_key_value => p_key_value, p_collection_timestamp => p_collection_timestamp, p_violation_level => p_violation_level, p_violation_type => p_violation_type, p_value => p_value, p_string_value => p_string_value, p_message => p_message, p_message_nlsid => p_message_nlsid, p_message_params => p_message_params, p_action_message => p_action_message, p_action_nlsid => p_action_message_nlsid, p_action_message_params => p_action_message_params, p_advisory_id => p_advisory_id, p_cfg_coll_name => p_cfg_coll_name, p_cfg_key_value => p_cfg_key_value, p_cfg_key_operator => p_cfg_key_operator, p_notification_status => p_notification_status); END log_violation; -- -- API to suppress a current violation -- Supported only for policy violations -- PROCEDURE add_violation_suppression( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_policy_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ', p_suppress_code IN NUMBER DEFAULT 0, p_suppress_until IN DATE DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_suppress_until DATE; l_cfg_coll_name mgmt_current_violation.cfg_coll_name%TYPE; BEGIN -- Check for not nulls EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); -- Validate suppress code range EM_CHECK.check_range(p_suppress_code, MGMT_GLOBAL.G_SUPPRESS_MIN, MGMT_GLOBAL.G_SUPPRESS_MAX, 'p_suppress_code'); l_suppress_until := NULL; IF (p_suppress_code = MGMT_GLOBAL.G_SUPPRESS_DATE) THEN EM_CHECK.check_not_null(p_suppress_until, 'p_suppress_until'); l_suppress_until := p_suppress_until; END IF; l_target_guid := mgmt_target.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_policy_guid := mgmt_policy.get_policy_guid( p_target_type => p_target_type, p_policy_name => p_policy_name); UPDATE mgmt_current_violation SET exempt_code = p_suppress_code, exempt_until = l_suppress_until, exempt_by = l_current_user WHERE target_guid = l_target_guid AND policy_guid = l_policy_guid AND key_value = p_key_value RETURNING cfg_coll_name INTO l_cfg_coll_name; IF (SQL%ROWCOUNT = 0) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'There is no currrent violation for the given target_type = ' || p_target_type || ' target_name = ' || p_target_name || ' policy_name = ' || p_policy_name || ' key value = ' || p_key_value); ELSE EM_POLICY.update_compliance_score( l_target_guid, l_policy_guid, l_cfg_coll_name); END IF; END add_violation_suppression; -- -- API to suppress a current violation given a violation guid -- Supported only for policy violations -- PROCEDURE add_violation_suppression( p_violation_guid IN RAW, p_suppress_code IN NUMBER DEFAULT 0, p_suppress_until IN DATE DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_suppress_until DATE; l_cfg_coll_name mgmt_current_violation.cfg_coll_name%TYPE; BEGIN -- Validate suppress code range EM_CHECK.check_range(p_suppress_code, MGMT_GLOBAL.G_SUPPRESS_MIN, MGMT_GLOBAL.G_SUPPRESS_MAX, 'p_suppress_code'); l_suppress_until := NULL; IF (p_suppress_code = MGMT_GLOBAL.G_SUPPRESS_DATE) THEN EM_CHECK.check_not_null(p_suppress_until, 'p_suppress_until'); l_suppress_until := p_suppress_until; END IF; UPDATE mgmt_current_violation SET exempt_code = p_suppress_code, exempt_until = l_suppress_until, exempt_by = l_current_user WHERE violation_guid = p_violation_guid RETURNING TARGET_GUID, POLICY_GUID, cfg_coll_name INTO l_target_guid, l_policy_guid, l_cfg_coll_name; IF (SQL%ROWCOUNT = 0) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'There is no currrent violation with the given violation_guid ' || p_violation_guid); ELSE EM_POLICY.update_compliance_score( l_target_guid, l_policy_guid, l_cfg_coll_name); END IF; END add_violation_suppression; -- -- API to remove a violation suppression -- Supported only for policy violations -- PROCEDURE remove_violation_suppression( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_policy_name IN VARCHAR2, p_key_value IN VARCHAR2 DEFAULT ' ') IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_cfg_coll_name mgmt_current_violation.cfg_coll_name%TYPE; BEGIN -- Check for not nulls EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); l_target_guid := mgmt_target.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_policy_guid := mgmt_policy.get_policy_guid( p_target_type => p_target_type, p_policy_name => p_policy_name); UPDATE mgmt_current_violation SET exempt_code = MGMT_GLOBAL.G_SUPPRESS_NONE, exempt_until = NULL, exempt_by = NULL WHERE target_guid = l_target_guid AND policy_guid = l_policy_guid AND key_value = p_key_value RETURNING cfg_coll_name INTO l_cfg_coll_name; IF (SQL%ROWCOUNT = 0) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'There is no currrent violation for the given target_type = ' || p_target_type || ' target_name = ' || p_target_name || ' policy_name = ' || p_policy_name || ' key value = ' || p_key_value); ELSE EM_POLICY.update_compliance_score( l_target_guid, l_policy_guid,l_cfg_coll_name); END IF; END remove_violation_suppression; -- -- API to remove a violation suppression given a violation guid -- Supported only for policy violations -- PROCEDURE remove_violation_suppression( p_violation_guid IN RAW) IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_cfg_coll_name mgmt_current_violation.cfg_coll_name%TYPE; BEGIN UPDATE mgmt_current_violation SET exempt_code = MGMT_GLOBAL.G_SUPPRESS_NONE, exempt_until = NULL, exempt_by = NULL WHERE violation_guid = p_violation_guid RETURNING TARGET_GUID, POLICY_GUID, cfg_coll_name INTO l_target_guid, l_policy_guid, l_cfg_coll_name; IF (SQL%ROWCOUNT = 0) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'There is no currrent violation for the given violation_guid ' || p_violation_guid); ELSE EM_POLICY.update_compliance_score( l_target_guid, l_policy_guid,l_cfg_coll_name); END IF; END remove_violation_suppression; FUNCTION get_viol_guid_with_keyGUID ( p_target_guid IN RAW, p_policy_guid IN RAW, p_key_value_raw IN RAW, p_collection_timestamp IN DATE DEFAULT NULL ) RETURN RAW IS l_violation_guid mgmt_violations.violation_guid%TYPE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_viol_guidwithkey:'|| ' target='||p_target_guid|| ' policy='||p_policy_guid|| ' key='||p_key_value_Raw|| ' time='||to_char(p_collection_timestamp,'DD-MON-YY HH24:MI:SS'), 'sdk_violation') ; END IF ; SELECT violation_guid INTO l_violation_guid FROM MGMT_VIOLATIONS WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND key_value = p_key_value_raw AND collection_timestamp = p_collection_timestamp ; RETURN(l_violation_guid) ; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Violation does not exist') ; WHEN OTHERS THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm) ; END get_viol_guid_with_keyGUID ; FUNCTION get_viol_guid_with_keyString ( p_target_guid IN RAW, p_policy_guid IN RAW, p_key_value IN VARCHAR2 DEFAULT ' ', p_collection_timestamp IN DATE DEFAULT NULL ) RETURN RAW IS l_violation_guid mgmt_violations.violation_guid%TYPE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_viol_guidwithstring:'|| ' target='||p_target_guid|| ' policy='||p_policy_guid|| ' key='||p_key_value|| ' time='||to_char(p_collection_timestamp,'DD-MON-YY HH24:MI:SS'), 'sdk_violation') ; END IF ; SELECT violation_guid INTO l_violation_guid FROM MGMT_VIOLATIONS WHERE target_guid = p_target_guid AND policy_guid = p_policy_guid AND key_value = p_key_value AND collection_timestamp = p_collection_timestamp ; RETURN(l_violation_guid) ; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Violation does not exist') ; END get_viol_guid_with_keyString ; PROCEDURE get_target_metric_info(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_target_guid OUT RAW, p_metric_guid OUT RAW, p_num_keys OUT NUMBER) IS BEGIN SELECT t.target_guid, m.metric_guid , m.num_keys INTO p_target_guid, p_metric_guid, p_num_keys FROM mgmt_targets t, mgmt_metrics m WHERE t.target_type = p_target_type AND t.target_name = p_target_name AND m.target_type = p_target_type AND m.metric_name = p_metric_name AND m.metric_column = p_metric_column AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.NO_SUCH_METRIC_ERR, 'Metric not found. target_type ' || p_target_type || ' target_name = ' || p_target_name || ' metric_name = ' || p_metric_name || ' metric_column = ' || p_metric_column); END get_target_metric_info ; -- -- Purge closed alerts for the specified metric for user specified keys -- PROCEDURE purge_alerts(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_collection_timestamp IN DATE DEFAULT NULL, p_key_values IN mgmt_key_value_array DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_num_keys mgmt_metrics.num_keys%type ; l_key_values mgmt_medium_string_table := mgmt_medium_string_table() ; BEGIN get_target_metric_info(p_target_name=>p_target_name, p_target_type=>p_target_type, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_metric_guid=>l_metric_guid, p_num_keys=>l_num_keys) ; IF l_num_keys > 0 AND p_key_values IS NOT NULL and p_key_values.COUNT > 0 THEN -- convert the user entered keys into composite keys EM_SEVERITY.convert_keys (p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_key_values_in=>p_key_values, p_collection_timestamp=>p_collection_timestamp, p_num_keys=>l_num_keys, p_key_values_out=>l_key_values) ; --user has specified some key combination, if it does not exist --we want to return back and not purge all data IF l_key_values IS NULL OR l_key_values.COUNT = 0 THEN RETURN ; END IF ; END IF ; EM_SEVERITY.purge_violations(p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_key_values=>l_key_values, p_collection_timestamp=> p_collection_timestamp); END purge_alerts ; -- -- Purge violations for the specified policy for user specified keys -- PROCEDURE purge_violations (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_collection_timestamp IN DATE DEFAULT NULL, p_key_values IN mgmt_key_value_array DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_policy_guid mgmt_policies.policy_guid%type ; l_num_keys mgmt_metrics.num_keys%type ; l_key_values mgmt_medium_string_table := mgmt_medium_string_table() ; BEGIN BEGIN SELECT policy_guid, metric_guid INTO l_policy_guid, l_metric_guid FROM mgmt_policies WHERE target_type = p_target_type AND policy_name = p_policy_name ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.POLICY_DOES_NOT_EXIST_ERR, 'Policy not found. target_type = ' || p_target_type || ' policy_name = ' || p_policy_name); END ; l_target_guid := mgmt_target.get_target_guid(target_type_in=>p_target_type, target_name_in=>p_target_name); BEGIN SELECT num_keys INTO l_num_keys FROM mgmt_metrics WHERE metric_guid = l_metric_guid AND ROWNUM = 1 ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.NO_SUCH_METRIC_ERR, 'Metric not found for policy='||p_policy_name) ; END ; IF l_num_keys > 0 AND p_key_values IS NOT NULL and p_key_values.COUNT > 0 THEN -- -- convert the user entered keys into composite keys -- EM_SEVERITY.convert_keys (p_target_guid=>l_target_guid, p_policy_guid=>l_policy_guid, p_key_values_in=>p_key_values, p_collection_timestamp=>p_collection_timestamp, p_num_keys=>l_num_keys, p_key_values_out=>l_key_values) ; --user has specified some key combination, if it does not exist --we want to return back and not purge all data IF l_key_values IS NULL OR l_key_values.COUNT = 0 THEN RETURN ; END IF ; END IF ; EM_SEVERITY.purge_violations(p_target_guid=>l_target_guid, p_policy_guid=>l_policy_guid, p_key_values=>l_key_values, p_collection_timestamp=> p_collection_timestamp); END purge_violations ; -- -- Purge alerts for the specified metric -- composite key guids is input -- PROCEDURE purge_alerts(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_collection_timestamp IN DATE DEFAULT NULL, p_key_values IN mgmt_medium_string_table DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_num_keys mgmt_metrics.num_keys%type ; BEGIN get_target_metric_info(p_target_name=>p_target_name, p_target_type=>p_target_type, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_metric_guid=>l_metric_guid, p_num_keys=>l_num_keys) ; EM_SEVERITY.purge_violations (p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_key_values=>p_key_values, p_collection_timestamp=>p_collection_timestamp); END purge_alerts ; -- -- Purge violations for specified policy -- composite key guids is input -- PROCEDURE purge_violations(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_collection_timestamp IN DATE DEFAULT NULL, p_key_values IN mgmt_medium_string_table DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%type ; l_policy_guid mgmt_metrics.metric_guid%type ; BEGIN l_policy_guid := mgmt_policy.get_policy_guid(p_target_type=>p_target_type, p_policy_name=>p_policy_name) ; l_target_guid := mgmt_target.get_target_guid(target_type_in=>p_target_type, target_name_in=>p_target_name) ; EM_SEVERITY.purge_violations (p_target_guid=>l_target_guid, p_policy_guid=>l_policy_guid, p_key_values=>p_key_values, p_collection_timestamp=>p_collection_timestamp); END purge_violations ; -- -- Clear open alerts for the target/metric with user input keys -- PROCEDURE clear_open_alerts(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_key_values IN mgmt_key_value_array DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_num_keys mgmt_metrics.num_keys%type ; l_key_values mgmt_medium_string_table := mgmt_medium_string_table() ; BEGIN get_target_metric_info(p_target_name=>p_target_name, p_target_type=>p_target_type, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_metric_guid=>l_metric_guid, p_num_keys=>l_num_keys) ; IF l_num_keys > 0 AND p_key_values IS NOT NULL and p_key_values.COUNT > 0 THEN -- convert the user entered keys into composite keys EM_SEVERITY.convert_keys (p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_key_values_in=>p_key_values, p_collection_timestamp=>NULL, p_num_keys=>l_num_keys, p_key_values_out=>l_key_values) ; --user has specified some key combination, if it does not exist --we want to return back and not purge all data IF l_key_values IS NULL OR l_key_values.COUNT = 0 THEN RETURN ; END IF ; END IF ; EM_SEVERITY.clear_open_alerts (p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_key_values=>l_key_values, p_is_metric=>TRUE) ; END clear_open_alerts ; -- -- Clear open alerts for the target/metric with composite guids -- PROCEDURE clear_open_alerts(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_key_values IN mgmt_medium_string_table) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_num_keys mgmt_metrics.num_keys%type ; BEGIN get_target_metric_info(p_target_name=>p_target_name, p_target_type=>p_target_type, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_metric_guid=>l_metric_guid, p_num_keys=>l_num_keys) ; EM_SEVERITY.clear_open_alerts (p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_key_values=>p_key_values, p_is_metric=>TRUE) ; END clear_open_alerts ; END mgmt_violation; / show errors