Rem Rem $Header: test_metadata_edit_pkgbody.sql 31-oct-2006.22:19:25 mfidanbo Exp $ Rem Rem test_metadata_edit_pkgbody.sql Rem Rem Copyright (c) 2004, 2006, Oracle. All rights reserved. Rem Rem NAME Rem test_metadata_edit_pkgbod.sql - Rem Rem DESCRIPTION Rem Package for managing test metadata Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem mfidanbo 10/31/06 - Rem afontana 03/15/06 - Backport afontana_bug-5077806 from main Rem afontana 09/13/05 - update metrics during test registration if Rem needed Rem afontana 07/25/05 - change EMDW_LOG to MGMT_LOG Rem afontana 06/23/05 - bug 4451241 (generic_service 2.0) Rem afontana 07/01/05 - grabtrans 'afontana_bug-4451241' Rem afontana 06/14/05 - enable_test_target_map calls Rem mgmt_metric.add_metric_version_row Rem afontana 02/28/05 - use em_metric.copy_metric Rem afontana 11/16/04 - test metadata enhancements Rem afontana 11/15/04 - copy entire row for migrating beacon metrics Rem afontana 10/05/04 - add encrypt field to MGMT_TEST_METRIC_PROPS Rem jsadras 09/21/04 - eval_func Rem afontana 09/10/04 - afontana_test_meta_repos Rem afontana 09/09/04 - add password attribute to properties Rem afontana 08/30/04 - add levels Rem afontana 08/09/04 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_TEST_METADATA_EDIT AS ------------------------------------------------------------------------------- -- -- Private declarations -- PROCEDURE INSERT_TEST ( v_test IN mgmt_test_metadata_obj ); PROCEDURE INSERT_TEST_QUALIFIER ( v_test IN VARCHAR2, v_qual IN mgmt_test_qualifier_obj ); PROCEDURE INSERT_GROUP ( v_test IN VARCHAR2, v_group IN mgmt_test_prop_group_obj ); PROCEDURE INSERT_PROP ( v_test IN VARCHAR2, v_prop IN mgmt_test_prop_obj ); PROCEDURE INSERT_PROP_QUALIFIER ( v_test IN VARCHAR2, v_qual IN mgmt_test_prop_qualifier_obj ); PROCEDURE INSERT_PROP_LEVEL ( v_test IN VARCHAR2, v_level IN mgmt_test_prop_level_obj ); PROCEDURE INSERT_CHOICE ( v_test IN VARCHAR2, v_choice IN mgmt_test_choice_obj ); PROCEDURE INSERT_METRIC ( v_test IN VARCHAR2, v_metric IN mgmt_test_metric_obj ); PROCEDURE INSERT_MCOLUMN ( v_test IN VARCHAR2, v_mcolumn IN mgmt_test_mcolumn_in_obj ); PROCEDURE INSERT_PROMOTION ( v_test IN VARCHAR2, v_promotion IN mgmt_test_promotion_obj ); PROCEDURE INSERT_THRESHOLD ( v_test IN VARCHAR2, v_threshold IN mgmt_test_thresholds_obj ); PROCEDURE ENABLE_TEST_FOR_TARGET_HELPER ( v_test IN VARCHAR2, v_target_type IN VARCHAR2, v_target_ver IN VARCHAR2, v_cat_prop1 IN VARCHAR2, v_cat_prop2 IN VARCHAR2, v_cat_prop3 IN VARCHAR2, v_cat_prop4 IN VARCHAR2, v_cat_prop5 IN VARCHAR2, v_monitoring_type IN VARCHAR2, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 ); ------------------------------------------------------------------------------- -- -- Public Procedures -- -- -- PROCEDURE INSERT_COMPLETE_TEST -- -- Inserts a complete test specification to all tables. -- -- Error handling is done by assuming failure. -- The error code is set before the corresponding function is called -- If an exception occurs, then the error code will already be set correctly. -- PROCEDURE INSERT_COMPLETE_TEST ( v_test IN mgmt_test_metadata_obj, v_test_qualifiers IN mgmt_test_qualifier_arr, v_groups IN mgmt_test_prop_group_arr, v_properties IN mgmt_test_prop_arr, v_prop_qualifiers IN mgmt_test_prop_qualifier_arr, v_levels IN mgmt_test_prop_level_arr, v_choices IN mgmt_test_choice_arr, v_metrics IN mgmt_test_metric_arr, v_mcolumns IN mgmt_test_mcolumn_in_arr, v_def_promotion IN mgmt_test_promotion_arr, v_def_thresholds IN mgmt_test_thresholds_arr, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 ) IS v_test_type VARCHAR2(64) := NULL; v_count PLS_INTEGER; BEGIN SELECT COUNT(*) INTO v_count FROM MGMT_TEST WHERE test_type = v_test.test_type; IF v_count <> 0 THEN v_result := p_success; v_err_desc := NULL; UPGRADE_TEST(v_test, v_test_qualifiers, v_groups, v_properties, v_prop_qualifiers, v_levels, v_choices, v_metrics, v_mcolumns, v_def_promotion, v_def_thresholds, v_result, v_err_desc); RETURN; END IF; v_result := p_test_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_TEST'; INSERT_TEST(v_test); v_test_type := v_test.test_type; v_result := p_test_qual_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_TEST_QUALIFIER'; IF v_test_qualifiers IS NOT NULL AND v_test_qualifiers.count > 0 THEN FOR i in 1 .. v_test_qualifiers.count LOOP INSERT_TEST_QUALIFIER(v_test_type, v_test_qualifiers(i)); END LOOP; END IF; v_result:= p_metrics_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_METRIC'; IF v_metrics IS NOT NULL AND v_metrics.count > 0 THEN FOR i in 1 .. v_metrics.count LOOP INSERT_METRIC(v_test_type, v_metrics(i)); END LOOP; END IF; v_result:= p_mcolumns_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_MCOLUMN'; IF v_mcolumns IS NOT NULL AND v_mcolumns.count > 0 THEN FOR i in 1 .. v_mcolumns.count LOOP INSERT_MCOLUMN(v_test_type, v_mcolumns(i)); END LOOP; END IF; v_result := p_groups_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_GROUP'; IF v_groups IS NOT NULL AND v_groups.count > 0 THEN FOR i in 1 .. v_groups.count LOOP INSERT_GROUP(v_test_type, v_groups(i)); END LOOP; END IF; v_result := p_properties_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_PROP'; IF v_properties IS NOT NULL AND v_properties.count > 0 THEN FOR i in 1 .. v_properties.count LOOP INSERT_PROP(v_test_type, v_properties(i)); END LOOP; END IF; v_result := p_prop_qual_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_PROP_QUALIFIER'; IF v_prop_qualifiers IS NOT NULL AND v_prop_qualifiers.count > 0 THEN FOR i in 1 .. v_prop_qualifiers.count LOOP INSERT_PROP_QUALIFIER(v_test_type, v_prop_qualifiers(i)); END LOOP; END IF; v_result := p_properties_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_PROP_LEVEL'; IF v_levels IS NOT NULL AND v_levels.count > 0 THEN FOR i in 1 .. v_levels.count LOOP INSERT_PROP_LEVEL(v_test_type, v_levels(i)); END LOOP; END IF; v_result := p_choices_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_CHOICE'; IF v_choices IS NOT NULL AND v_choices.count > 0 THEN FOR i in 1 .. v_choices.count LOOP INSERT_CHOICE(v_test_type, v_choices(i)); END LOOP; END IF; v_result := p_promotion_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_PROMOTION'; IF v_def_promotion IS NOT NULL AND v_def_promotion.count > 0 THEN FOR i in 1 .. v_def_promotion.count LOOP INSERT_PROMOTION(v_test_type, v_def_promotion(i)); END LOOP; END IF; v_result := p_thresholds_insert_sql_error; v_err_desc := 'MGMT_TEST_METADATA_EDIT.INSERT_THRESHOLD'; IF v_def_thresholds IS NOT NULL AND v_def_thresholds.count > 0 THEN FOR i in 1 .. v_def_thresholds.count LOOP INSERT_THRESHOLD(v_test_type, v_def_thresholds(i)); END LOOP; END IF; v_result := p_success; v_err_desc := NULL; EXCEPTION WHEN OTHERS THEN IF v_err_desc IS NULL THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.INSERT_COMPLETE_TEST ' || SQLERRM, 1, p_max_err_size); ELSE --fall through from exception above v_err_desc := SUBSTR(v_err_desc || SQLERRM, 1, p_max_err_size); END IF; END INSERT_COMPLETE_TEST; -- -- PROCEDURE UPGRADE_COMPLETE_TEST -- -- Replaces a test definition with a new definition. -- PROCEDURE UPGRADE_TEST ( v_test IN mgmt_test_metadata_obj, v_test_qualifiers IN mgmt_test_qualifier_arr, v_groups IN mgmt_test_prop_group_arr, v_properties IN mgmt_test_prop_arr, v_prop_qualifiers IN mgmt_test_prop_qualifier_arr, v_levels IN mgmt_test_prop_level_arr, v_choices IN mgmt_test_choice_arr, v_metrics IN mgmt_test_metric_arr, v_mcolumns IN mgmt_test_mcolumn_in_arr, v_def_promotion IN mgmt_test_promotion_arr, v_def_thresholds IN mgmt_test_thresholds_arr, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 ) IS v_count PLS_INTEGER; va_target_type SMP_EMD_STRING_ARRAY; va_target_meta_ver SMP_EMD_STRING_ARRAY; va_cat_prop1 SMP_EMD_STRING_ARRAY; va_cat_prop2 SMP_EMD_STRING_ARRAY; va_cat_prop3 SMP_EMD_STRING_ARRAY; va_cat_prop4 SMP_EMD_STRING_ARRAY; va_cat_prop5 SMP_EMD_STRING_ARRAY; BEGIN SELECT COUNT(*) INTO v_count FROM MGMT_TEST WHERE test_type = v_test.test_type; IF v_count < 1 THEN v_result := p_test_not_found_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.UPGRADE_TEST ' || 'Test Type Not Found: ' || v_test.test_type, 1, p_max_err_size); END IF; -- Before deleting, remember all the targets that were enabled -- for this test. SELECT TARGET_TYPE, TARGET_META_VER, CAT_PROP1, CAT_PROP2, CAT_PROP3, CAT_PROP4, CAT_PROP5 BULK COLLECT INTO va_target_type, va_target_meta_ver, va_cat_prop1, va_cat_prop2, va_cat_prop3, va_cat_prop4, va_cat_prop5 FROM MGMT_TEST_TARGET_MAP WHERE TEST_TYPE = v_test.test_type; DELETE_TEST(v_test.test_type, v_result, v_err_desc); IF v_result <> p_success THEN RETURN; END IF; INSERT_COMPLETE_TEST(v_test, v_test_qualifiers, v_groups, v_properties, v_prop_qualifiers, v_levels, v_choices, v_metrics, v_mcolumns, v_def_promotion, v_def_thresholds, v_result, v_err_desc); IF v_result <> p_success THEN RETURN; END IF; --Need to re-enable test type, since it was deleted. FOR v_count IN 1..va_target_type.COUNT LOOP ENABLE_TEST_FOR_TARGET(v_test => v_test.test_type, v_target_type => va_target_type(v_count), v_target_ver => va_target_meta_ver(v_count), v_cat_prop1 => va_cat_prop1(v_count), v_cat_prop2 => va_cat_prop2(v_count), v_cat_prop3 => va_cat_prop3(v_count), v_cat_prop4 => va_cat_prop4(v_count), v_cat_prop5 => va_cat_prop5(v_count), v_result => v_result, v_err_desc => v_err_desc); IF v_result <> p_success THEN RETURN; END IF; END LOOP; EXCEPTION WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.UPGRADE_TEST ' || SQLERRM, 1, p_max_err_size); END UPGRADE_TEST; -- -- PROCEDURE ENABLE_TEST_FOR_TARGET -- -- This procedure will verify that all of the metrics -- associated with the test exist for the given target. -- If those metrics do not exist, then they will be created -- in the repository for the target_type:version. -- If the version supplied is NULL, then all known versions -- of the target_type will be affected. -- -- For a given [target type,version], this -- procedure will copy all of the test metrics -- to the specified target type. -- -- If a specified target already has such a metric, -- the metric will not be copied, and no failure will -- occur. If the specified target has some of the columns -- but not all, only the new ones will be copied. In the -- latter case, we check to make sure that we are not -- creating an inconsistency in the set of key columns -- for an existing metric. -- PROCEDURE ENABLE_TEST_FOR_TARGET ( v_test IN VARCHAR2, v_target_type IN VARCHAR2, v_target_ver IN VARCHAR2, v_cat_prop1 IN VARCHAR2, v_cat_prop2 IN VARCHAR2, v_cat_prop3 IN VARCHAR2, v_cat_prop4 IN VARCHAR2, v_cat_prop5 IN VARCHAR2, v_monitoring_type IN VARCHAR2 DEFAULT mgmt_global.G_BEACON_TARGET_TYPE, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 )IS v_count PLS_INTEGER; l_target_ver mgmt_target_types.MAX_TYPE_META_VER%type; BEGIN v_result := p_success; v_err_desc := NULL; IF v_target_ver IS NULL THEN l_target_ver := '1.0'; ELSE l_target_ver := v_target_ver; END IF; -- record the mapping if it is not already recorded SELECT COUNT(*) INTO v_count FROM mgmt_test_target_map WHERE target_type = v_target_type AND (em_target.compare_type_meta_vers(target_meta_ver, l_target_ver) <= 0) AND (cat_prop1 = v_cat_prop1 OR cat_prop1 = ' ') AND (cat_prop2 = v_cat_prop2 OR cat_prop2 = ' ') AND (cat_prop3 = v_cat_prop3 OR cat_prop3 = ' ') AND (cat_prop4 = v_cat_prop4 OR cat_prop4 = ' ') AND (cat_prop5 = v_cat_prop5 OR cat_prop5 = ' ') AND test_type = v_test; IF v_count = 0 THEN INSERT INTO mgmt_test_target_map (target_type, target_meta_ver, cat_prop1, cat_prop2, cat_prop3, cat_prop4, cat_prop5, test_type) VALUES (v_target_type, l_target_ver, v_cat_prop1, v_cat_prop2, v_cat_prop3, v_cat_prop4, v_cat_prop5, v_test); END IF; -- copy any missing metrics to the targets FOR version IN ( SELECT DISTINCT (tv.type_meta_ver) FROM MGMT_TARGET_TYPE_VERSIONS tv WHERE tv.target_type = v_target_type AND (em_target.compare_type_meta_vers(l_target_ver, tv.type_meta_ver) <= 0) ) LOOP ENABLE_TEST_FOR_TARGET_HELPER(v_test, v_target_type, version.type_meta_ver, v_cat_prop1, v_cat_prop2, v_cat_prop3, v_cat_prop4, v_cat_prop5, v_monitoring_type, v_result, v_err_desc); IF (v_result <> p_success) THEN RETURN; END IF; END LOOP; -- register metrics for the test so that they will continue -- to be copied for each subsequent target type meta version FOR metric IN ( SELECT DISTINCT m1.METRIC_NAME FROM MGMT_TEST_METRICS m1 WHERE m1.TEST_TYPE = v_test AND ((v_target_type, m1.METRIC_NAME) NOT IN (SELECT m2.TARGET_TYPE, m2.METRIC_NAME FROM MGMT_METRIC_VERSIONS m2)) ) LOOP em_metric.add_metric_version_row(p_target_type => v_target_type, p_metric_name => metric.METRIC_NAME, p_start_type_meta_ver => l_target_ver); END LOOP; EXCEPTION WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.ENABLE_TEST_FOR_TARGET ' || SQLERRM, 1, p_max_err_size); END ENABLE_TEST_FOR_TARGET; -- -- PROCEDURE DELETE_TEST -- -- Deletes a test from all tables. -- PROCEDURE DELETE_TEST ( v_test_type IN VARCHAR2, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 ) IS v_count NUMBER; BEGIN SELECT count(*) INTO v_count FROM MGMT_TEST WHERE test_type = v_test_type; IF v_count <> 1 THEN v_result := p_test_not_found_error; v_err_desc := 'Test Does Not Exist : ' || v_test_type; RETURN; END IF; DELETE FROM MGMT_TEST_DEFAULT_THRESHOLDS WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_DEFAULT_PROMOTION WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_PROP_CHOICES WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_PROP_LEVEL WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_PROP WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_PROP_UIGROUP WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_MCOLUMNS WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_METRICS WHERE test_type = v_test_type; DELETE FROM MGMT_TEST_TARGET_MAP WHERE test_type = v_test_type; DELETE FROM MGMT_TEST WHERE test_type = v_test_type; v_result := p_success; EXCEPTION WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.DELETE_TEST ' || SQLERRM, 1, p_max_err_size); END DELETE_TEST; -- -- PROCEDURE DEPRECATE_TEST -- -- Takes a test_id and deprecates that test. -- -- On success, returns 0. -- On failure, returns < 0 with err_desc filled. -- PROCEDURE DEPRECATE_TEST ( v_test_type IN VARCHAR2, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 ) IS BEGIN UPDATE MGMT_TEST mt SET mt.deprecated = 'Y' WHERE mt.test_type = v_test_type; v_result := p_success; EXCEPTION WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.DEPRECATE_TEST ' || SQLERRM, 1, p_max_err_size); END DEPRECATE_TEST; FUNCTION GEN_METRIC_PROPERTY_GUID(v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_property_name IN VARCHAR2) RETURN RAW IS v_guid RAW(16); BEGIN v_guid := dbms_obfuscation_toolkit.md5( input => utl_raw.cast_to_raw( v_target_type || ';' || v_metric_name || ';' || v_property_name)); RETURN v_guid; EXCEPTION WHEN OTHERS THEN RETURN NULL; END GEN_METRIC_PROPERTY_GUID; PROCEDURE ADD_METRIC_PROPERTY(v_target_type IN VARCHAR2, v_type_meta_ver IN VARCHAR2, v_metric_name IN VARCHAR2, v_property_name IN VARCHAR2, v_scope IN VARCHAR2, v_optional IN CHAR, v_encrypt IN CHAR, v_result OUT INTEGER, v_err_desc OUT VARCHAR2) IS v_guid RAW(16); BEGIN v_guid := gen_metric_property_guid(v_target_type, v_metric_name, v_property_name); INSERT INTO MGMT_TEST_METRIC_PROPS (property_guid, target_type, type_meta_ver, metric_name, property_name, scope, optional, encrypt) VALUES (v_guid, v_target_type, v_type_meta_ver, v_metric_name, v_property_name, v_scope, v_optional, v_encrypt); v_result := p_success; v_err_desc := NULL; EXCEPTION --If two tests use the same metric, they might attempt to enter the --same property twice. This can be safely ignored. WHEN DUP_VAL_ON_INDEX THEN MGMT_LOG.LOG_ERROR(v_module_name_in => 'TEST_METADATA', v_error_code_in => NULL, v_error_msg_in => 'Ignoring duplicate metric property: ' || v_metric_name || ' ' || v_property_name, v_log_level_in => MGMT_GLOBAL.G_INFO); WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.ADD_METRIC_PROPERTY ' || SQLERRM, 1, p_max_err_size); END ADD_METRIC_PROPERTY; PROCEDURE INSERT_PROPERTIES(v_props IN mgmt_test_mprop_ver_arr, v_result OUT INTEGER, v_err_desc OUT VARCHAR2) IS v_prop mgmt_test_mprop_ver_obj; BEGIN IF v_props IS NOT NULL AND v_props.count > 0 THEN FOR i in 1 .. v_props.count LOOP v_prop := v_props(i); add_metric_property(v_prop.target_type, v_prop.type_meta_ver, v_prop.metric_name, v_prop.property_name, v_prop.scope, v_prop.optional, v_prop.encrypt, v_result, v_err_desc); IF v_result <> p_success THEN RETURN; END IF; END LOOP; END IF; v_result := p_success; v_err_desc := NULL; EXCEPTION WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR('MGMT_TEST_METADATA_EDIT.INSERT_PROPERTIES_COMMIT ' || SQLERRM, 1, p_max_err_size); END INSERT_PROPERTIES; ------------------------------------------------------------------------------- ---- ---- Private Procedures ---- PROCEDURE INSERT_TEST ( v_test IN mgmt_test_metadata_obj ) IS BEGIN INSERT INTO MGMT_TEST (test_type, label, nlsid, resource_bundle, collection_generator, min_beacon_ver, test_version, deprecated, avail_metric, avail_metric_column, help_id, tip_text, tip_text_nlsid, validator ) VALUES ( v_test.test_type, v_test.label, v_test.nlsid, v_test.resource_bundle, v_test.collection_generator, v_test.min_beacon_ver, v_test.test_version, v_test.deprecated, v_test.avail_metric, v_test.avail_mcolumn, v_test.help_id, v_test.tip_text, v_test.tip_text_nlsid, v_test.validator ); END INSERT_TEST; PROCEDURE INSERT_TEST_QUALIFIER ( v_test IN VARCHAR2, v_qual IN mgmt_test_qualifier_obj ) IS BEGIN INSERT INTO MGMT_TEST_QUALIFIERS ( test_type, qualifier, text_value, numeric_value ) VALUES ( v_test, v_qual.qualifier, v_qual.text_value, v_qual.numeric_value ); END INSERT_TEST_QUALIFIER; PROCEDURE INSERT_GROUP ( v_test IN VARCHAR2, v_group IN mgmt_test_prop_group_obj ) IS BEGIN INSERT INTO MGMT_TEST_PROP_UIGROUP ( test_type, group_name, display_order, label, nlsid, tip_text, tip_text_nlsid ) VALUES ( v_test, v_group.group_name, v_group.display_order, v_group.label, v_group.nlsid, v_group.tip_text, v_group.tip_text_nlsid ); END INSERT_GROUP; PROCEDURE INSERT_PROP ( v_test IN VARCHAR2, v_prop IN mgmt_test_prop_obj ) IS BEGIN INSERT INTO MGMT_TEST_PROP ( test_type, prop_name, group_name, display_order, label, nlsid, property_type, default_numeric_value, default_string_value, max_value, min_value, hidden, encrypt, password, varies_per_beacon, tip_text, tip_text_nlsid, validator ) VALUES ( v_test, v_prop.property_name, v_prop.group_name, v_prop.display_order, v_prop.label, v_prop.nlsid, v_prop.property_type, v_prop.default_numeric_value, v_prop.default_string_value, v_prop.maximum_value, v_prop.minimum_value, v_prop.hidden, v_prop.encrypt, v_prop.password, v_prop.varies_per_beacon, v_prop.tip_text, v_prop.tip_text_nlsid, v_prop.validator ); END INSERT_PROP; PROCEDURE INSERT_PROP_QUALIFIER ( v_test IN VARCHAR2, v_qual IN mgmt_test_prop_qualifier_obj ) IS BEGIN INSERT INTO MGMT_TEST_PROP_QUALIFIERS ( test_type, property, qualifier, text_value, numeric_value ) VALUES ( v_test, v_qual.property, v_qual.qualifier, v_qual.text_value, v_qual.numeric_value ); END INSERT_PROP_QUALIFIER; PROCEDURE INSERT_PROP_LEVEL ( v_test IN VARCHAR2, v_level IN mgmt_test_prop_level_obj ) IS BEGIN INSERT INTO MGMT_TEST_PROP_LEVEL ( test_type, property, level_name, optional ) VALUES ( v_test, v_level.property, v_level.level_name, v_level.optional ); END INSERT_PROP_LEVEL; PROCEDURE INSERT_CHOICE ( v_test IN VARCHAR2, v_choice IN mgmt_test_choice_obj ) IS BEGIN INSERT INTO MGMT_TEST_PROP_CHOICES ( test_type, prop_name, choice_name, display_order, label, nlsid ) VALUES ( v_test, v_choice.property_name, v_choice.choice_name, v_choice.choice_order, v_choice.label, v_choice.nlsid ); END INSERT_CHOICE; PROCEDURE INSERT_METRIC ( v_test IN VARCHAR2, v_metric IN mgmt_test_metric_obj ) IS BEGIN INSERT INTO MGMT_TEST_METRICS ( test_type, metric_name, metric_number, interactive, level_name ) VALUES ( v_test, v_metric.metric_name, v_metric.metric_number, v_metric.interactive, v_metric.level_name ); END INSERT_METRIC; PROCEDURE INSERT_MCOLUMN ( v_test IN VARCHAR2, v_mcolumn IN mgmt_test_mcolumn_in_obj ) IS BEGIN INSERT INTO MGMT_TEST_MCOLUMNS ( test_type, metric_name, metric_column, display_order ) VALUES ( v_test, v_mcolumn.metric_name, v_mcolumn.metric_column, v_mcolumn.display_order ); END INSERT_MCOLUMN; PROCEDURE INSERT_PROMOTION ( v_test IN VARCHAR2, v_promotion IN mgmt_test_promotion_obj ) IS BEGIN INSERT INTO MGMT_TEST_DEFAULT_PROMOTION ( test_type, src_metric_name, src_metric_column, src_metric_key1_value, src_metric_key2_value, src_metric_key3_value, src_metric_key4_value, src_metric_key5_value, eval_func, critical, warning, operator, num_occurrences, dest_metric_name, dest_metric_column, dest_metric_key1_value, dest_metric_key2_value, dest_metric_key3_value, dest_metric_key4_value, dest_metric_key5_value, default_chart ) VALUES ( v_test, v_promotion.src_metric_name, v_promotion.src_metric_column, v_promotion.src_metric_key1_value, v_promotion.src_metric_key2_value, v_promotion.src_metric_key3_value, v_promotion.src_metric_key4_value, v_promotion.src_metric_key5_value, v_promotion.eval_func, v_promotion.critical, v_promotion.warning, v_promotion.operator, v_promotion.num_occurrences, v_promotion.dest_metric_name, v_promotion.dest_metric_column, v_promotion.dest_metric_key1_value, v_promotion.dest_metric_key2_value, v_promotion.dest_metric_key3_value, v_promotion.dest_metric_key4_value, v_promotion.dest_metric_key5_value, v_promotion.default_chart ); END INSERT_PROMOTION; PROCEDURE INSERT_THRESHOLD ( v_test IN VARCHAR2, v_threshold IN mgmt_test_thresholds_obj ) IS BEGIN INSERT INTO MGMT_TEST_DEFAULT_THRESHOLDS ( test_type, metric_name, metric_column, metric_key1_value, metric_key2_value, metric_key3_value, metric_key4_value, metric_key5_value, critical, warning, operator, num_occurrences ) VALUES ( v_test, v_threshold.metric_name, v_threshold.metric_column, v_threshold.metric_key1_value, v_threshold.metric_key2_value, v_threshold.metric_key3_value, v_threshold.metric_key4_value, v_threshold.metric_key5_value, v_threshold.critical, v_threshold.warning, v_threshold.operator, v_threshold.num_occurrences ); END INSERT_THRESHOLD; -- -- PROCEDURE ENABLE_TEST_FOR_TARGET_HELPER -- -- Helper function for ENABLE_TEST_FOR_TARGET -- The major difference is that target_ver -- may not be NULL. -- -- For a given [target type,version], this -- procedure will copy all of the test metrics -- to the specified target type. -- -- If a specified target already has such a metric, -- the metric will not be copied, and no failure will -- occur. If the specified target has some of the columns -- but not all, only the new ones will be copied. In the -- latter case, we check to make sure that we are not -- creating an inconsistency in the set of key columns -- for an existing metric. -- PROCEDURE ENABLE_TEST_FOR_TARGET_HELPER ( v_test IN VARCHAR2, v_target_type IN VARCHAR2, v_target_ver IN VARCHAR2, v_cat_prop1 IN VARCHAR2, v_cat_prop2 IN VARCHAR2, v_cat_prop3 IN VARCHAR2, v_cat_prop4 IN VARCHAR2, v_cat_prop5 IN VARCHAR2, v_monitoring_type IN VARCHAR2, v_result OUT INTEGER, v_err_desc OUT VARCHAR2 ) IS l_max_bcn_meta_ver MGMT_TARGET_TYPES.MAX_TYPE_META_VER%TYPE; BEGIN SELECT MAX_TYPE_META_VER INTO l_max_bcn_meta_ver FROM MGMT_TARGET_TYPES WHERE TARGET_TYPE = v_monitoring_type; FOR rec IN (SELECT metrics.metric_name metric_name, metrics.metric_column metric_column, metrics.category_prop_1 cat_prop1, metrics.category_prop_2 cat_prop2, metrics.category_prop_3 cat_prop3, metrics.category_prop_4 cat_prop4, metrics.category_prop_5 cat_prop5 FROM MGMT_METRICS metrics, MGMT_TEST_METRICS test_metrics WHERE metrics.TARGET_TYPE = v_monitoring_type AND metrics.TYPE_META_VER = l_max_bcn_meta_ver AND metrics.METRIC_NAME = test_metrics.METRIC_NAME AND test_metrics.TEST_TYPE = v_test) LOOP BEGIN MGMT_TEST_METADATA_METRICS.COPY_METRIC( -- From This v_monitoring_type, rec.metric_name, rec.metric_column, l_max_bcn_meta_ver, rec.cat_prop1, rec.cat_prop2, rec.cat_prop3, rec.cat_prop4, rec.cat_prop5, -- To This v_target_type, rec.metric_name, rec.metric_column, v_target_ver, v_cat_prop1, v_cat_prop2, v_cat_prop3, v_cat_prop4, v_cat_prop5); EXCEPTION --If the metric already exists, try to update the display names. WHEN DUP_VAL_ON_INDEX THEN MGMT_TEST_METADATA_METRICS.UPDATE_METRIC( -- From This v_monitoring_type, rec.metric_name, rec.metric_column, l_max_bcn_meta_ver, rec.cat_prop1, rec.cat_prop2, rec.cat_prop3, rec.cat_prop4, rec.cat_prop5, -- To This v_target_type, rec.metric_name, rec.metric_column, v_target_ver, v_cat_prop1, v_cat_prop2, v_cat_prop3, v_cat_prop4, v_cat_prop5); END; END LOOP; EXCEPTION WHEN OTHERS THEN v_result := p_unknown_sql_error; v_err_desc := SUBSTR( 'MGMT_TEST_METADATA_EDIT.ENABLE_TEST_FOR_TARGET_HELPER ' || SQLERRM, 1, p_max_err_size); END ENABLE_TEST_FOR_TARGET_HELPER; END MGMT_TEST_METADATA_EDIT; / -- ------------------------------------------------------------------------ SHOW ERRORS;