Rem $Header: sdk_udp_pkgbody.sql 19-jul-2007.08:40:17 groyal Exp $ Rem Rem sdk_udp_pkgbody.sql Rem Rem Copyright (c) 2006, Oracle. All rights reserved. Rem Rem NAME Rem sdk_udp_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem groyal 07/18/07 - Make default collections enabled so that when the Rem UDP is associated with a target the collection is set Rem niramach 10/16/06 - Fix for bug 5557714.(Security fix) Rem groyal 09/05/06 - User-defined policy support Rem groyal 09/05/06 - Created Rem CREATE OR REPLACE PACKAGE BODY mgmt_user_defined_policy AS UDP_AUTHOR CONSTANT VARCHAR2(10) := 'UDP'; METRIC_PREFIX CONSTANT VARCHAR2(10) := 'EMUDP_' ; NUMBER_TYPE CONSTANT NUMBER := 2; -- Maximum string lengths MAX_NAME_LENGTH CONSTANT NUMBER := 58; MAX_DESCRIPTION_LENGTH CONSTANT NUMBER := 256; MAX_RECOMMENDATION_LENGTH CONSTANT NUMBER := 500; MAX_IMPACT_LENGTH CONSTANT NUMBER := 500; -- ----------------------------------------------------------------------------- -- -- Private Procedures -- -- ----------------------------------------------------------------------------- PROCEDURE check_policy_is_udp(p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2); PROCEDURE check_policy_not_exist(p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2); PROCEDURE check_metric_not_exist(p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2); PROCEDURE check_coll_not_exist(p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2); PROCEDURE check_category_exists(p_category IN VARCHAR2); PROCEDURE check_target_type_exists(p_target_type IN VARCHAR2); PROCEDURE check_target_exists ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_target_guid OUT RAW ); PROCEDURE check_length ( p_string IN VARCHAR2, p_string_name IN VARCHAR2, p_max_length IN NUMBER ); PROCEDURE check_test ( p_test_type IN NUMBER, p_test IN VARCHAR2, p_metric_columns IN MGMT_SHORT_STRING_ARRAY, p_parameter_names IN MGMT_SHORT_STRING_ARRAY ); PROCEDURE check_range ( p_value IN NUMBER, p_min_value IN NUMBER DEFAULT 0, p_max_value IN NUMBER DEFAULT 0, p_param_name IN VARCHAR2 DEFAULT NULL ); PROCEDURE get_select_column_list ( p_sql_text IN VARCHAR2, p_num_keys IN NUMBER, p_columns OUT MGMT_SHORT_STRING_ARRAY, p_metric_types OUT MGMT_INTEGER_ARRAY ); PROCEDURE create_custom_metric ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_sql_text IN VARCHAR2, p_num_keys IN NUMBER, p_metric_columns IN MGMT_SHORT_STRING_ARRAY, p_metric_types IN MGMT_INTEGER_ARRAY ); PROCEDURE create_custom_policy ( p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_test IN VARCHAR2, p_test_type IN NUMBER, p_test_op IN NUMBER, p_parameters IN UDP_PARAMETERS, p_description IN VARCHAR2 , p_impact IN VARCHAR2 , p_recommendation IN VARCHAR2 , p_severity_level IN NUMBER , p_category IN VARCHAR2, p_url_link IN VARCHAR2, p_violation_message IN VARCHAR2, p_clear_message IN VARCHAR2, p_metric_columns IN mgmt_short_string_array ); PROCEDURE create_custom_default_coll ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_eval_interval IN NUMBER ); PROCEDURE create_policy_objects ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_sql_text IN VARCHAR2, p_test_type IN NUMBER, p_test IN VARCHAR2, p_test_op IN NUMBER DEFAULT NULL, p_parameters IN UDP_PARAMETERS, p_num_keys IN NUMBER, p_description IN VARCHAR2, p_impact IN VARCHAR2, p_recommendation IN VARCHAR2, p_severity_level IN NUMBER, p_category IN VARCHAR2, p_url_link IN VARCHAR2, p_violation_message IN VARCHAR2, p_clear_message IN VARCHAR2, p_eval_interval IN NUMBER ); -- ----------------------------------------------------------------------------- -- -- End of Private Procedures -- -- ----------------------------------------------------------------------------- -- ----------------------------------------------------------------------------- -- -- Procedure CREATE_POLICY -- -- Description: Creates a user-defined policy where the policy's implementation -- is sql based. -- PROCEDURE create_policy ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_sql_text IN VARCHAR2, p_test IN VARCHAR2, p_parameters IN UDP_PARAMETERS DEFAULT NULL, p_num_keys IN NUMBER DEFAULT 1, p_description IN VARCHAR2 DEFAULT ' ', p_impact IN VARCHAR2 DEFAULT ' ', p_recommendation IN VARCHAR2 DEFAULT ' ', p_severity_level IN NUMBER DEFAULT G_SEVERITY_INFORMATIONAL, p_category IN VARCHAR2 DEFAULT G_CATEGORY_UNCLASSIFIED, p_url_link IN VARCHAR2 DEFAULT NULL, p_violation_message IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_eval_interval IN NUMBER DEFAULT 24 ) IS BEGIN create_policy_objects( p_policy_name => p_policy_name, p_target_type => p_target_type, p_sql_text => p_sql_text, p_test_type => MGMT_GLOBAL.G_CONDITION_SQL, p_test => p_test, p_parameters => p_parameters, p_num_keys => p_num_keys, p_description => p_description, p_impact => p_impact, p_recommendation => p_recommendation, p_severity_level => p_severity_level, p_category => p_category, p_url_link => p_url_link, p_violation_message => p_violation_message, p_clear_message => p_clear_message, p_eval_interval => p_eval_interval); END create_policy; -- ----------------------------------------------------------------------------- -- -- Procedure CREATE_POLICY -- -- Description: Creates a user-defined policy where the policy's implementation -- is a simple threshold test. -- PROCEDURE create_policy ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_sql_text IN VARCHAR2, p_column_name IN VARCHAR2, p_test_operator IN VARCHAR2, p_threshold_value IN VARCHAR2, p_threshold_data_type IN NUMBER DEFAULT G_PARAM_TYPE_NUMBER, p_num_keys IN NUMBER DEFAULT 1, p_description IN VARCHAR2 DEFAULT ' ', p_impact IN VARCHAR2 DEFAULT ' ', p_recommendation IN VARCHAR2 DEFAULT ' ', p_severity_level IN NUMBER DEFAULT G_SEVERITY_INFORMATIONAL, p_category IN VARCHAR2 DEFAULT G_CATEGORY_UNCLASSIFIED, p_url_link IN VARCHAR2 DEFAULT NULL, p_violation_message IN VARCHAR2 DEFAULT NULL, p_clear_message IN VARCHAR2 DEFAULT NULL, p_eval_interval IN NUMBER DEFAULT 24 ) IS l_parameters UDP_PARAMETERS := UDP_PARAMETERS(); BEGIN EM_CHECK.check_not_null(p_test_operator, 'p_test_operator'); check_range( p_value => p_test_operator, p_min_value => MGMT_GLOBAL.G_THRESHOLD_MIN, p_max_value => MGMT_GLOBAL.G_THRESHOLD_MAX, p_param_name => 'p_test_operator'); check_range( p_value => p_threshold_data_type, p_min_value => 1, p_max_value => 2, p_param_name => 'p_threshold_data_type'); l_parameters.extend(1) ; l_parameters(1).param_name := p_column_name; l_parameters(1).param_type := p_threshold_data_type ; l_parameters(1).threshold_value := p_threshold_value ; create_policy_objects( p_policy_name => p_policy_name, p_target_type => p_target_type, p_sql_text => p_sql_text, p_test_type => MGMT_GLOBAL.G_CONDITION_THRESHOLD, p_test => p_column_name, p_test_op => p_test_operator, p_parameters => l_parameters, p_num_keys => p_num_keys, p_description => p_description, p_impact => p_impact, p_recommendation => p_recommendation, p_severity_level => p_severity_level, p_category => p_category, p_url_link => p_url_link, p_violation_message => p_violation_message, p_clear_message => p_clear_message, p_eval_interval => p_eval_interval); END create_policy; -- ----------------------------------------------------------------------------- -- -- Procedure DELETE_POLICY -- -- Description: Deletes a user-defined policy. -- PROCEDURE delete_policy ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2 ) IS l_author mgmt_policies.author%type; l_count NUMBER; BEGIN EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); EM_CHECK.check_not_null(p_target_type, 'p_target_type'); -- verify specified policy is a user-defined policy check_policy_is_udp( p_policy_name => p_policy_name, p_target_type => p_target_type); -- delete the collection mgmt_collection.delete_default_collection( p_target_type => p_target_type, p_type_meta_ver => '1.0', p_coll_name => METRIC_PREFIX || p_policy_name, p_valid_if_list => NULL); -- delete the policy mgmt_policy.delete_policy( p_target_type => p_target_type, p_policy_name => p_policy_name); -- delete the metric associated with the policy mgmt_metric.delete_metric( p_target_type => p_target_type, p_metric_name => METRIC_PREFIX || p_policy_name); END delete_policy; -- ----------------------------------------------------------------------------- -- -- Procedure ADD_POLICY_TO_TARGET -- -- Description: Associates a user-defined policy with a target. -- PROCEDURE add_policy_to_target ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_name IN VARCHAR2 ) IS l_target_guid mgmt_targets.target_guid%type; l_interval mgmt_collections.interval%type; l_default_guid mgmt_collections.object_guid%type; l_metric_name mgmt_metrics.metric_name%type := METRIC_PREFIX || p_policy_name; l_metric_guid mgmt_metrics.metric_guid%type; l_count NUMBER ; BEGIN EM_CHECK.CHECK_NOT_NULL(p_policy_name,'p_policy_name'); EM_CHECK.CHECK_NOT_NULL(p_target_type,'p_target_type'); -- verify specified policy is a user-defined policy check_policy_is_udp( p_policy_name => p_policy_name, p_target_type => p_target_type); -- verify specified target exists check_target_exists( p_target_type => p_target_type, p_target_name => p_target_name, p_target_guid => l_target_guid); l_metric_guid := mgmt_metric.get_metric_guid(p_target_type, l_metric_name, ' '); SELECT NVL(COUNT(*),0) INTO l_count FROM mgmt_collection_metric_tasks WHERE target_guid = l_target_guid AND metric_guid = l_metric_guid; -- Start a collection only if no collections already exist IF l_count = 0 THEN -- get the default schedule associated with 1.0 l_default_guid := mgmt_collection.default_object_guid(p_target_type, '1.0'); -- Look for a disabled default collection for 1.0 BEGIN SELECT interval INTO l_interval FROM mgmt_collections WHERE object_guid = l_default_guid AND coll_name = l_metric_name AND is_enabled = MGMT_GLOBAL.G_TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Default schedule not available') ; END ; mgmt_collection.start_collection( p_target_type => p_target_type, p_target_name => p_target_name, p_metric_name => METRIC_PREFIX || p_policy_name, p_coll_schedule => mgmt_coll_schedule_obj.interval_schedule (l_interval,null,null), p_store_metric => MGMT_GLOBAL.G_FALSE); END IF; BEGIN -- Add the target policy association mgmt_policy.add_target_policy_assoc( p_target_type => p_target_type, p_target_name => p_target_name, p_policy_name => p_policy_name, p_coll_name => METRIC_PREFIX || p_policy_name); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN mgmt_policy.enable_target_policy_assoc( p_target_type => p_target_type, p_target_name => p_target_name, p_policy_name => p_policy_name, p_coll_name => METRIC_PREFIX || p_policy_name); END; END add_policy_to_target; -- ----------------------------------------------------------------------------- -- -- Procedure REMOVE_POLICY_FROM_TARGET -- -- Description: Removes the association of the user-defined policy with a target. -- PROCEDURE remove_policy_from_target ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_name IN VARCHAR2 ) IS l_target_guid mgmt_targets.target_guid%type; BEGIN EM_CHECK.CHECK_NOT_NULL(p_policy_name,'p_policy_name'); EM_CHECK.CHECK_NOT_NULL(p_target_type,'p_target_type'); -- verify specified policy is a user-defined policy check_policy_is_udp( p_policy_name => p_policy_name, p_target_type => p_target_type); -- verify specified target exists check_target_exists( p_target_type => p_target_type, p_target_name => p_target_name, p_target_guid => l_target_guid); -- stop collections for the policy mgmt_collection.stop_collection( p_target_name => p_target_name, p_target_type => p_target_type, p_coll_name => METRIC_PREFIX || p_policy_name); -- remove the association mgmt_policy.remove_target_policy_assoc( p_target_type => p_target_type, p_target_name => p_target_name, p_policy_name => p_policy_name, p_coll_name => METRIC_PREFIX || p_policy_name); END remove_policy_from_target; -- ----------------------------------------------------------------------------- -- -- Start of Private Procedures -- -- ----------------------------------------------------------------------------- -- ----------------------------------------------------------------------------- -- -- Procedure check_policy_is_udp -- -- Description: Checks to ensure specified policy is a user-defined policy -- PROCEDURE check_policy_is_udp ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2 ) IS l_author mgmt_policies.author%type; BEGIN BEGIN SELECT author INTO l_author FROM mgmt_policies WHERE policy_name = p_policy_name AND target_type = p_target_type; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Policy does not exist.'); END; IF l_author != UDP_AUTHOR THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Specified policy is not a user-defined policy'); END IF; END check_policy_is_udp; -- ----------------------------------------------------------------------------- -- -- Procedure check_policy_not_exist -- -- Description: Checks to ensure specified policy does not exist -- PROCEDURE check_policy_not_exist ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2 ) IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM mgmt_policies WHERE policy_name = p_policy_name AND target_type = p_target_type; IF l_count > 0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Policy already exists.') ; END IF; END check_policy_not_exist; -- ----------------------------------------------------------------------------- -- -- Procedure check_metric_not_exist -- -- Description: Checks to ensure specified policy does not exist -- PROCEDURE check_metric_not_exist ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2 ) IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM mgmt_metrics WHERE metric_name = METRIC_PREFIX || p_policy_name AND target_type = p_target_type; IF l_count > 0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Internal name clash. Please choose another policy name.'); END IF; END check_metric_not_exist; -- ----------------------------------------------------------------------------- -- -- Procedure check_coll_not_exist -- -- Description: Checks to ensure specified policy does not exist -- PROCEDURE check_coll_not_exist ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2 ) IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM mgmt_coll_items WHERE coll_name = METRIC_PREFIX || p_policy_name AND target_type = p_target_type ; IF l_count > 0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Internal name clash. Please choose another policy name.'); END IF; END check_coll_not_exist; -- ----------------------------------------------------------------------------- -- -- Procedure check_category_exists -- -- Description: Checks to ensure specified category exists -- PROCEDURE check_category_exists ( p_category IN VARCHAR2 ) IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM mgmt_categories WHERE class_name = MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL AND category_name = p_category; IF l_count = 0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid category.'); END IF; END check_category_exists; -- ----------------------------------------------------------------------------- -- -- Procedure check_target_type_exists -- -- Description: Checks to ensure specified target type exists -- PROCEDURE check_target_type_exists ( p_target_type IN VARCHAR2 ) IS l_count NUMBER; BEGIN SELECT COUNT(*) INTO l_count FROM mgmt_target_types WHERE target_type = p_target_type; IF l_count = 0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid target type.'); END IF; END check_target_type_exists; -- ----------------------------------------------------------------------------- -- -- Procedure check_target_exists -- -- Description: Checks to ensure specified target exists -- PROCEDURE check_target_exists ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_target_guid OUT RAW ) IS BEGIN EM_CHECK.check_not_null(p_target_type,'p_target_type'); EM_CHECK.check_not_null(p_target_name,'p_target_name'); BEGIN SELECT target_guid INTO p_target_guid FROM mgmt_targets WHERE target_type = p_target_type AND target_name = p_target_name; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Specified target does not exist') ; END; END check_target_exists; -- ----------------------------------------------------------------------------- -- -- Procedure check_length -- -- Description: Checks to ensure specified string is not too long -- PROCEDURE check_length ( p_string IN VARCHAR2, p_string_name IN VARCHAR2, p_max_length IN NUMBER ) IS l_param_name VARCHAR2(64); l_error_msg VARCHAR2(4000); BEGIN IF LENGTH(p_string) > p_max_length THEN l_param_name := NVL(p_string_name, 'parameter'); l_error_msg := 'Value passed for ' || l_param_name || ' is too large. Maximum string length is ' || p_max_length || ' characters.'; RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg); END IF; END check_length; -- ----------------------------------------------------------------------------- -- -- Procedure check_test -- -- Description: Verify the correctness of the policy's test -- PROCEDURE check_test ( p_test_type IN NUMBER, p_test IN VARCHAR2, p_metric_columns IN MGMT_SHORT_STRING_ARRAY, p_parameter_names IN MGMT_SHORT_STRING_ARRAY ) IS l_binds mgmt_namevalue_array; l_err VARCHAR2(2000); l_found BOOLEAN := FALSE; BEGIN IF p_test_type = MGMT_GLOBAL.G_CONDITION_SQL THEN IF NOT EM_POLICY_EVAL.is_valid_condition( p_condition_text => p_test, p_param_columns => p_parameter_names, p_metric_columns => p_metric_columns, p_bind_columns => l_binds, p_error_message => l_err) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid test condition.'); END IF; ELSE FOR i IN p_metric_columns.FIRST..p_metric_columns.LAST LOOP IF UPPER(p_test) = UPPER(p_metric_columns(i)) THEN l_found := TRUE; EXIT; END IF; END LOOP; IF NOT l_found THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Column name must be one of the columns in the SQL select list.'); END IF; END IF; END check_test; -- ----------------------------------------------------------------------------- -- -- Procedure check_range -- -- Description: Verify p_value is in the expected range -- PROCEDURE check_range ( p_value IN NUMBER, p_min_value IN NUMBER DEFAULT 0, p_max_value IN NUMBER DEFAULT 0, p_param_name IN VARCHAR2 DEFAULT NULL ) IS l_param_name VARCHAR2(64); l_error_msg VARCHAR2(4000); BEGIN IF ((p_value < p_min_value) OR (p_value > p_max_value)) THEN l_param_name := NVL(p_param_name, 'parameter'); IF p_min_value IS NULL THEN l_error_msg := 'Invalid parameter: The value provided for ' || l_param_name || ' must be less than ' || p_max_value || '.'; ELSIF p_max_value IS NULL THEN l_error_msg := 'Invalid parameter: The value provided for ' || l_param_name || ' must be greater than ' || p_min_value || '.'; ELSE l_error_msg := 'Invalid parameter: The value provided for ' || l_param_name || ' is out of range. ' || 'The valid range for ' || l_param_name || ' is ' || p_min_value || '..' || p_max_value || '.'; END IF; RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg); END IF; END check_range; -- ----------------------------------------------------------------------------- -- -- Procedure get_select_column_list -- -- Description: Retrieves the column names specified in the select list of the -- sql query. -- PROCEDURE get_select_column_list ( p_sql_text IN VARCHAR2, p_num_keys IN NUMBER, p_columns OUT MGMT_SHORT_STRING_ARRAY, p_metric_types OUT MGMT_INTEGER_ARRAY ) IS l_column_recs DBMS_SQL.DESC_TAB; l_cur NUMBER; l_column_count NUMBER; l_target_guid_found BOOLEAN := FALSE; BEGIN l_cur := dbms_sql.open_cursor; -- NOOP check is required for security SQL injection test fix dbms_sql.parse(l_cur, EM_CHECK.NOOP(p_sql_text), dbms_sql.NATIVE); dbms_sql.describe_columns(l_cur, l_column_count, l_column_recs); IF p_num_keys < 1 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'SQL should contain at least one key column in select list.'); END IF; IF l_column_recs IS NULL OR l_column_recs.COUNT < (p_num_keys + 1) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'SQL should contain at least one non-key column in select list.') ; END IF; p_columns := mgmt_short_string_array(); p_metric_types := mgmt_integer_array(); FOR i IN l_column_recs.FIRST..l_column_recs.LAST LOOP IF UPPER(l_column_recs(i).col_name) = 'TARGET_GUID' THEN l_target_guid_found := TRUE; ELSE p_columns.extend(1); p_metric_types.extend(1); IF l_column_recs(i).col_type = NUMBER_TYPE THEN p_metric_types(p_metric_types.COUNT) := MGMT_GLOBAL.G_METRIC_TYPE_NUMBER; ELSE p_metric_types(p_metric_types.COUNT) := MGMT_GLOBAL.G_METRIC_TYPE_STRING; END IF ; p_columns(p_columns.COUNT) := l_column_recs(i).col_name; END IF; END LOOP; IF NOT l_target_guid_found THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'SQL should contain target_guid in select list'); END IF ; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid SQL: error=' || sqlerrm) ; END get_select_column_list; -- ----------------------------------------------------------------------------- -- -- Procedure create_custom_metric -- -- Description: Create the actual repository metric -- PROCEDURE create_custom_metric ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_sql_text IN VARCHAR2, p_num_keys IN NUMBER, p_metric_columns IN MGMT_SHORT_STRING_ARRAY, p_metric_types IN MGMT_INTEGER_ARRAY ) IS l_count NUMBER ; l_metric_column MGMT_METRIC_COLUMN_OBJ; l_metric_column_list MGMT_METRIC_COLUMN_ARRAY; l_is_key NUMBER ; BEGIN l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY(); l_metric_column_list.extend(p_metric_columns.COUNT) ; FOR i IN p_metric_columns.FIRST..p_metric_columns.LAST LOOP -- Don't include target_guid column as a key column when it comes -- to creating the metric. Metric/policy evaluation engine special -- case the target_guid column. IF i <= (p_num_keys - 1) THEN l_is_key := 1; ELSE l_is_key := 0; END IF ; l_metric_column_list(i) := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => p_metric_columns(i), p_column_type => p_metric_types(i), p_is_key => l_is_key); END LOOP; MGMT_METRIC.CREATE_METRIC( p_target_type => p_target_type, p_metric_name => METRIC_PREFIX||p_policy_name, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE, p_source => p_sql_text, p_usage_type => MGMT_GLOBAL.G_USAGE_HIDDEN_COLLECT, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, p_metric_column_list => l_metric_column_list, p_is_long_running => mgmt_global.G_TRUE); END create_custom_metric; -- ----------------------------------------------------------------------------- -- -- Procedure create_custom_policy -- -- Description: Create the actual policy -- PROCEDURE create_custom_policy ( p_target_type IN VARCHAR2, p_policy_name IN VARCHAR2, p_test IN VARCHAR2, p_test_type IN NUMBER, p_test_op IN NUMBER, p_parameters IN UDP_PARAMETERS, p_description IN VARCHAR2 , p_impact IN VARCHAR2 , p_recommendation IN VARCHAR2 , p_severity_level IN NUMBER , p_category IN VARCHAR2, p_url_link IN VARCHAR2, p_violation_message IN VARCHAR2, p_clear_message IN VARCHAR2, p_metric_columns IN mgmt_short_string_array ) IS l_category_list MGMT_CATEGORY_ARRAY; l_default_settings_list MGMT_POLICY_KEY_VAL_ARRAY; l_viol_ctxt_list MGMT_VIOL_CTXT_DEF_ARRAY := MGMT_VIOL_CTXT_DEF_ARRAY(); l_param_list MGMT_POLICY_PARAM_DEF_ARRAY := MGMT_POLICY_PARAM_DEF_ARRAY(); l_def_param_val_list MGMT_POLICY_PARAM_VAL_ARRAY := MGMT_POLICY_PARAM_VAL_ARRAY(); l_crit_threshold mgmt_policy_assoc_cfg_params.crit_threshold%type ; l_warn_threshold mgmt_policy_assoc_cfg_params.crit_threshold%type ; l_info_threshold mgmt_policy_assoc_cfg_params.crit_threshold%type ; BEGIN -- Define category to which the policy belongs to l_category_list := MGMT_CATEGORY_ARRAY( MGMT_CATEGORY_OBJ.NEW( p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL, p_category_name => p_category)); -- Define violation context using metric columns l_viol_ctxt_list.EXTEND(p_metric_columns.COUNT); FOR i IN p_metric_columns.FIRST..p_metric_columns.LAST LOOP l_viol_ctxt_list(i) := MGMT_VIOL_CTXT_DEF.NEW(p_metric_column => p_metric_columns(i)); END LOOP; -- Define parameters IF p_parameters IS NOT NULL AND p_parameters.COUNT > 0 THEN l_param_list.EXTEND(p_parameters.COUNT); l_def_param_val_list.extend(p_parameters.COUNT); FOR i in p_parameters.FIRST..p_parameters.LAST LOOP l_param_list(i) := MGMT_POLICY_PARAM_DEF.NEW( p_param_name => p_parameters(i).param_name, p_param_type => p_parameters(i).param_type); l_crit_threshold := NULL; l_info_threshold := NULL; l_warn_threshold := NULL; IF p_severity_level = G_SEVERITY_INFORMATIONAL THEN l_info_threshold := p_parameters(i).threshold_value; ELSIF p_severity_level = G_SEVERITY_WARNING THEN l_warn_threshold := p_parameters(i).threshold_value; ELSE l_crit_threshold := p_parameters(i).threshold_value; END IF; -- Default values for the policy parameter l_def_param_val_list(i) := MGMT_POLICY_PARAM_VAL.NEW( p_param_name => p_parameters(i).param_name, p_crit_threshold => l_crit_threshold, p_warn_threshold => l_warn_threshold, p_info_threshold => l_info_threshold); END LOOP; END IF; l_default_settings_list := MGMT_POLICY_KEY_VAL_ARRAY( MGMT_POLICY_KEY_VAL.NEW( p_importance => MGMT_GlOBAL.G_IMPORTANCE_NORMAL, p_param_values => l_def_param_val_list)); -- Create policy MGMT_POLICY.CREATE_POLICY( p_target_type => p_target_type, p_policy_name => p_policy_name, p_metric_name => METRIC_PREFIX || p_policy_name, p_author => UDP_AUTHOR, p_condition_type => p_test_type, p_condition => p_test, p_condition_operator => p_test_op, p_category_list => l_category_list, p_description => p_description, p_impact => p_impact, p_recommendation => p_recommendation, p_violation_level => p_severity_level, p_detailed_url_link => p_url_link, p_message => p_violation_message, p_clear_message => p_clear_message, p_policy_param_list => l_param_list, p_dflt_param_val_list => l_default_settings_list, p_viol_ctxt_list => l_viol_ctxt_list); END create_custom_policy; -- ----------------------------------------------------------------------------- -- -- Procedure create_custom_default_coll -- -- Description: Create the actual default collection -- PROCEDURE create_custom_default_coll ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_eval_interval IN NUMBER ) IS l_metric_name mgmt_metrics.metric_name%type := METRIC_PREFIX || p_policy_name; l_count NUMBER; BEGIN MGMT_COLLECTION.ADD_DEFAULT_COLLECTION( p_target_type => p_target_type, p_type_meta_ver => '1.0', p_coll_name => l_metric_name, p_metrics_list => MGMT_METRIC_NAME_ARRAY(MGMT_METRIC_NAME_OBJ.NEW(l_metric_name,' ')), p_coll_schedule => MGMT_COLL_SCHEDULE_OBJ.INTERVAL_SCHEDULE(p_eval_interval*60,null,null), p_is_enabled => MGMT_GLOBAL.G_TRUE); END create_custom_default_coll; -- ----------------------------------------------------------------------------- -- -- Procedure create_policy_objects -- -- Description: Creates all policy-related objects including the repository -- metric, the policy and the default collection. -- PROCEDURE create_policy_objects ( p_policy_name IN VARCHAR2, p_target_type IN VARCHAR2, p_sql_text IN VARCHAR2, p_test_type IN NUMBER, p_test IN VARCHAR2, p_test_op IN NUMBER DEFAULT NULL, p_parameters IN UDP_PARAMETERS, p_num_keys IN NUMBER, p_description IN VARCHAR2, p_impact IN VARCHAR2, p_recommendation IN VARCHAR2, p_severity_level IN NUMBER, p_category IN VARCHAR2, p_url_link IN VARCHAR2, p_violation_message IN VARCHAR2, p_clear_message IN VARCHAR2, p_eval_interval IN NUMBER ) IS l_metric_columns MGMT_SHORT_STRING_ARRAY; l_param_names MGMT_SHORT_STRING_ARRAY := MGMT_SHORT_STRING_ARRAY(); l_metric_types MGMT_INTEGER_ARRAY; BEGIN 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_sql_text, 'p_sql_text'); EM_CHECK.check_not_null(p_test, 'p_test'); EM_CHECK.check_not_null(p_eval_interval, 'p_eval_interval'); check_range( p_value => p_eval_interval, p_min_value => 1, p_max_value => null, p_param_name => 'p_eval_interval'); check_length(p_policy_name, 'p_policy_name', MAX_NAME_LENGTH); check_length(p_description, 'p_description', MAX_DESCRIPTION_LENGTH); check_length(p_impact, 'p_impact', MAX_IMPACT_LENGTH); check_length(p_recommendation, 'p_recommendation', MAX_RECOMMENDATION_LENGTH); -- verify specified category exists check_category_exists( p_category => p_category); -- verify specified severity level is valid IF p_severity_level IS NULL OR p_severity_level NOT IN (G_SEVERITY_INFORMATIONAL, G_SEVERITY_CRITICAL, G_SEVERITY_WARNING) THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid severity level'); END IF; -- verify specified target type exists check_target_type_exists( p_target_type => p_target_type); -- verify specified policy does not exist check_policy_not_exist( p_policy_name => p_policy_name, p_target_type => p_target_type); -- verify metric does not exist check_metric_not_exist( p_policy_name => p_policy_name, p_target_type => p_target_type); -- verify metric collectin does not exist check_coll_not_exist( p_policy_name => p_policy_name, p_target_type => p_target_type); -- retrieve the columns in the select list of the sql get_select_column_list( p_sql_text => p_sql_text, p_num_keys => p_num_keys, p_columns => l_metric_columns, p_metric_types => l_metric_types); -- retrieve the parameters IF p_parameters IS NOT NULL AND p_parameters.COUNT > 0 THEN l_param_names.extend(p_parameters.COUNT) ; FOR i IN p_parameters.FIRST..p_parameters.LAST LOOP l_param_names(i) := p_parameters(i).param_name ; END LOOP ; END IF ; -- verify policy's test check_test( p_test_type => p_test_type, p_test => p_test, p_metric_columns => l_metric_columns, p_parameter_names => l_param_names); -- create policy-related objects create_custom_metric( p_policy_name => p_policy_name, p_target_type => p_target_type, p_sql_text => p_sql_text, p_num_keys => p_num_keys, p_metric_columns => l_metric_columns, p_metric_types => l_metric_types); create_custom_policy( p_target_type => p_target_type, p_policy_name => p_policy_name, p_test => p_test, p_test_type => p_test_type, p_test_op => p_test_op, p_parameters => p_parameters, p_description => p_description, p_impact => p_impact, p_recommendation => p_recommendation, p_severity_level => p_severity_level, p_category => p_category, p_url_link => p_url_link, p_violation_message => p_violation_message, p_clear_message => p_clear_message, p_metric_columns => l_metric_columns); create_custom_default_coll( p_target_type => p_target_type, p_policy_name => p_policy_name, p_eval_interval => p_eval_interval); END create_policy_objects; END mgmt_user_defined_policy; / SHOW ERRORS;