Rem Rem $Header: sdk_collections_pkgbody.sql 22-aug-2007.01:28:50 jsadras Exp $ Rem Rem sdk_collections_pkgbody.sql Rem Rem Copyright (c) 2004, 2007, Oracle. All rights reserved. Rem Rem NAME Rem sdk_collections_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem jsadras 08/17/07 - Bug:6347110, task workers do not start if Rem job_q_processes=0 Rem jsadras 08/22/07 - Backport jsadras_bug-6347110 from main Rem sthiruna 07/31/06 - Copying 10.2.0.3 Config Standard changes from EMCORE_MAIN_LINUX Rem jsadras 07/17/06 - Backport jsadras_bug-5002887 from main Rem jsadras 02/27/06 - add support for external metric evaluation Rem jsadras 02/06/06 - Bulk insert raw API Rem kchiasso 02/23/06 - change owner to author Rem aragarwa 02/02/06 - Change in configuration standard tables. Rem niramach 01/25/06 - Update run_collection procedure. Rem niramach 01/09/06 - 10.3 work start:Add optional parameters to Rem run_collection to identify configuration standard Rem being evaluated. Rem jsadras 12/09/05 - Backport jsadras_bug-4702199 from main Rem jsadras 10/28/05 - Bug:4702199, handling default collections Rem rpinnama 10/24/05 - Fix 4692595 : Donot clear alerts for common Rem metrics in handle_metaver_change callback Rem jsadras 08/30/05 - Batching support for bulk collections Rem jsadras 08/23/05 - Bug:4550781,auto enable for default collections Rem jsadras 07/27/05 - reorder deletes in delete_collection Rem rpinnama 07/28/05 - Remove CA assocs while removing thresholds Rem jsadras 07/15/05 - Rem rpinnama 05/09/05 - Add delete_collection API. Rem jsadras 04/27/05 - task class support for workers Rem jsadras 03/24/05 - add transient support Rem jsadras 03/21/05 - Check Nulls Rem jsadras 01/31/05 - metric version support -default_object_guid Rem jsadras 12/17/04 - bulk collection Rem jsadras 12/16/04 - run_collection Rem jsadras 11/26/04 - add task_type to worker apis Rem rpinnama 12/02/04 - Use object type constants Rem jsadras 11/15/04 - coll_name Rem jsadras 11/01/04 - mgmt_metric_names Rem jsadras 10/27/04 - add_default_collection Rem jsadras 10/20/04 - signature_change Rem jsadras 10/25/04 - remove stop_coll Rem jsadras 10/07/04 - gvparameter on rac Rem jsadras 10/04/04 - def_collections Rem jsadras 09/20/04 - cutover Rem jsadras 09/16/04 - default_guid Rem jsadras 09/09/04 - raise error Rem jsadras 09/07/04 - metric_column Rem jsadras 09/01/04 - workers Rem jsadras 08/18/04 - more features Rem jsadras 07/26/04 - Rem jsadras 07/24/04 - jsadras_repocollect1 Rem jsadras 07/06/04 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_COLLECTION IS G_BULK_BATCH_SIZE_PARAM CONSTANT VARCHAR2(30) := 'Repo Collection Batch Size' ; G_DEFAULT_BATCH_SIZE CONSTANT NUMBER(4) := '5000' ; G_CUSTOM_ERR_START CONSTANT NUMBER := -20999 ; G_CUSTOM_ERR_END CONSTANT NUMBER := -20001 ; DATE_1900_01_01 CONSTANT DATE := TO_DATE('1900-01-01', 'YYYY-MM-DD'); -- -- Generate the object guid for the specified target type -- FUNCTION default_object_guid(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2) RETURN RAW IS l_default_guid mgmt_collections.object_guid%type ; BEGIN SELECT target_type_ver_guid INTO l_default_guid FROM mgmt_target_type_versions WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver ; RETURN(l_default_guid) ; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN(EM_TARGET.generate_type_ver_guid(p_target_type, p_type_meta_ver)); END default_object_guid ; -- -- PURPOSE -- Function to find out if the target/metric has a collection -- FUNCTION has_collection(p_target_guid IN RAW, p_metric_guid IN RAW ) RETURN BOOLEAN IS l_count number ; BEGIN SELECT count(*) INTO l_count FROM mgmt_collections coll, mgmt_collection_metric_tasks coll_metrics WHERE coll.object_guid = p_target_guid AND coll.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND coll_metrics.target_guid = p_target_guid AND coll_metrics.metric_guid = p_metric_guid AND coll_metrics.coll_name = coll.coll_name ; IF l_count > 0 THEN RETURN(TRUE) ; ELSE RETURN(FALSE) ; END IF ; END has_collection; -- -- PURPOSE -- Function to find out if the target/metric has a collection -- FUNCTION has_collection(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ') RETURN BOOLEAN IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_source_type mgmt_metrics.source_type%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; BEGIN EM_COLL_UTIL.validate_target_metric( p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; RETURN(has_collection(l_target_guid,l_metric_guid)) ; END has_collection; -- -- PURPOSE -- Function to find out the collection name for a target/metric -- Assumption: Only one collection per target metric -- otherwise this will raise exception too_many_rows -- FUNCTION get_collection_name(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ') RETURN VARCHAR2 IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_source_type mgmt_metrics.source_type%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; l_coll_name mgmt_collection_metric_tasks.coll_name%type ; BEGIN EM_COLL_UTIL.validate_target_metric( p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; SELECT coll_metrics.coll_name INTO l_coll_name FROM mgmt_collection_metric_tasks coll_metrics WHERE coll_metrics.target_guid = l_target_guid AND coll_metrics.metric_guid = l_metric_guid ; RETURN(l_coll_name) ; END get_collection_name; -- -- PURPOSE -- To start a repository collection for a bunch of metrics -- -- Parameters : See definition -- PROCEDURE start_collection (p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metrics_list IN mgmt_metric_name_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_transient IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_source_type mgmt_metrics.source_type%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; l_metric_column_list mgmt_short_string_array := mgmt_short_string_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection(s):Enter',G_MODULE_NAME) ; END IF ; IF p_metrics_list IS NULL OR p_metrics_list.COUNT=0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Metric definition list cannot be null') ; END IF ; IF p_store_metric NOT IN (MGMT_GLOBAL.G_TRUE,MGMT_GLOBAL.G_FALSE) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid value for store metric - '||p_store_metric) ; END IF ; EM_CHECK.check_not_null(p_is_transient,'p_is_transient') ; EM_CHECK.check_not_null(p_store_metric,'p_store_metric') ; EM_CHECK.check_range(p_is_transient,0,1,'p_is_transient') ; EM_CHECK.check_range(p_store_metric,0,1,'p_store_metric') ; FOR i in p_metrics_list.FIRST..p_metrics_list.LAST LOOP EM_COLL_UTIL.validate_target_metric (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metrics_list(i).metric_name, p_metric_column=>p_metrics_list(i).metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; -- Collection name is set to ' ' if null EM_COLL_UTIL.add_collection_entry (p_object_guid=>l_target_guid , p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name=> nvl(p_coll_name,p_metrics_list(1).metric_name), p_store_metric=>p_store_metric, p_is_transient=>p_is_transient, p_is_enabled => MGMT_GLOBAL.G_TRUE, p_is_repository=>l_is_rep_metric, p_schedule_ex=>p_schedule_ex, p_coll_schedule => p_coll_schedule ) ; -- schedule collections if repository metric IF l_is_rep_metric = MGMT_GLOBAL.G_TRUE THEN EM_COLL_UTIL.schedule_repo_collection (p_target_guid=>l_target_guid, p_coll_name=>nvl(p_coll_name,p_metrics_list(1).metric_name), p_metric_guid=>l_metric_guid, p_cat_prop_array=>l_cat_prop_array, p_source_type=>l_source_type, p_op_code=>EM_COLL_UTIL.G_SCHEDULE_COLLECTION_OP, p_coll_schedule=>p_coll_schedule, p_timezone_region=>l_timezone_region) ; -- just add a collection metric ELSE EM_COLL_UTIL.add_modify_metric_task (p_target_guid=>l_target_guid, p_metric_guid=>l_metric_guid, p_coll_name=>nvl(p_coll_name,p_metrics_list(1).metric_name), p_task_id=>NULL, p_dml_operation=>'I' ) ; END IF ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection(s):Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection(s):Exit exception'||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' when starting collections') ; END IF ; END start_collection; -- -- Same as above except that in this only metric names are accepted -- metric column defaults to ' ' -- PROCEDURE start_collection (p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metrics_list IN mgmt_short_string_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_transient IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS l_metrics_list mgmt_metric_name_array := mgmt_metric_name_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection(ss):Enter normal',G_MODULE_NAME) ; END IF ; IF p_metrics_list IS NULL OR p_metrics_list.COUNT=0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Metric definition list cannot be null') ; END IF ; l_metrics_list.extend(p_metrics_list.COUNT) ; FOR i IN p_metrics_list.FIRST..p_metrics_list.LAST LOOP l_metrics_list(i) := mgmt_metric_name_obj.new( p_metrics_list(i), ' ') ; END LOOP ; start_collection (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metrics_list=>l_metrics_list, p_coll_name=>nvl(p_coll_name,p_metrics_list(1)), p_schedule_ex=>p_schedule_ex, p_coll_schedule=>p_coll_schedule, p_store_metric=>p_store_metric, p_is_transient=>p_is_transient) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection(ss):Exit normal',G_MODULE_NAME) ; END IF ; END start_collection ; -- -- start collection for a single target/metric -- PROCEDURE start_collection (p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL, p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_transient IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS l_metrics_list mgmt_metric_name_array := mgmt_metric_name_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection:Enter',G_MODULE_NAME) ; END IF ; l_metrics_list.extend(1) ; l_metrics_list(1) := mgmt_metric_name_obj.new (p_metric_name=>p_metric_name, p_metric_column=>p_metric_column); start_collection(p_target_type=>p_target_type, p_target_name=>p_target_name, p_metrics_list=>l_metrics_list, p_coll_name=>nvl(p_coll_name,p_metric_name), p_store_metric=>p_store_metric, p_schedule_ex=>p_schedule_ex, p_coll_schedule=>p_coll_schedule, p_is_transient=>p_is_transient) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_collection:Exit normal',G_MODULE_NAME) ; END IF ; END start_collection; -- -- PURPOSE -- Procedure to modify a agent or repository collection -- only schedule or store_metric can be modified -- This will only store the modified information in the repository -- Agent will not be using this information -- PROCEDURE modify_collection (p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL, p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_store_metric IN NUMBER DEFAULT NULL, p_is_transient IN NUMBER DEFAULT NULL) IS l_store_metric mgmt_collections.store_metric%type ; l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_source_type mgmt_metrics.source_type%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_collection:Enter',G_MODULE_NAME) ; END IF ; EM_COLL_UTIL.validate_target_metric (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; IF p_store_metric IS NOT NULL THEN EM_CHECK.check_range(p_store_metric,0,1,'p_store_metric') ; END IF ; IF p_is_transient IS NOT NULL THEN EM_CHECK.check_range(p_is_transient,0,1,'p_is_transient') ; END IF ; IF p_store_metric IN (MGMT_GLOBAL.G_TRUE, MGMT_GLOBAL.G_FALSE) OR p_coll_schedule IS NOT NULL OR p_is_transient IN (MGMT_GLOBAL.G_TRUE, MGMT_GLOBAL.G_FALSE) THEN EM_COLL_UTIL.modify_collection_entry (p_object_guid=>l_target_guid, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name=>nvl(p_coll_name,p_metric_name), p_is_enabled=>NULL, p_store_metric=>p_store_metric, p_schedule_ex=>p_schedule_ex, p_coll_schedule=>p_coll_schedule, p_is_transient=>p_is_transient) ; IF l_is_rep_metric = MGMT_GLOBAL.G_TRUE AND p_coll_schedule IS NOT NULL THEN EM_COLL_UTIL.schedule_repo_collection (p_target_guid=>l_target_guid, p_coll_name=>nvl(p_coll_name,p_metric_name), p_metric_guid=>l_metric_guid, p_cat_prop_array=>l_cat_prop_array, p_source_type=>l_source_type, p_op_code=>EM_COLL_UTIL.G_SCHEDULE_COLLECTION_OP, p_coll_schedule=>p_coll_schedule, p_timezone_region=>l_timezone_region) ; END IF ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_collection:Exit normal',G_MODULE_NAME) ; END IF ; END modify_collection; -- -- PURPOSE -- Stop the collection for the target/metric -- Removes the collection record -- PROCEDURE stop_collection(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2 DEFAULT NULL, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL) IS BEGIN EM_CHECK.check_not_null(p_target_name,'p_target_name') ; EM_CHECK.check_not_null(p_target_type,'p_target_type') ; EM_COLL_UTIL.suspend_resume_stop_collection (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_STOP_COLLECTION_OP) ; END stop_collection ; -- PURPOSE -- Deletes the collection for the target/metric -- Also deletes any thresholds, policy setup for the target/metric -- if metric name/collection name is not given -- then all collections for target would be stopped -- PROCEDURE delete_collection(p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guids MGMT_TARGET_GUID_ARRAY; l_policy_guids MGMT_TARGET_GUID_ARRAY; l_proc_name CONSTANT VARCHAR2(30) := 'delete_collection : ' ; BEGIN EM_CHECK.check_not_null(p_target_name, 'p_target_name') ; EM_CHECK.check_not_null(p_target_type, 'p_target_type') ; EM_CHECK.check_not_null(p_coll_name, 'p_coll_name') ; -- TODO: Check for user privileges IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Enter' || ' p_target_type = [' || p_target_type || ']' || ' p_target_name = [' || p_target_name || ']' || ' p_coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); -- Delete the alerts also associated with the collections EM_COLL_UTIL.delete_collection_int( p_target_guid => l_target_guid, p_coll_name => p_coll_name, p_clear_alerts => 1); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Exit ' || ' p_target_type = [' || p_target_type || ']' || ' p_target_name = [' || p_target_name || ']' || ' p_coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; END delete_collection; -- -- PURPOSE -- Suspend the specified repository collection -- if metric name is not given all repository collections for the -- target would be suspended -- PROCEDURE suspend_collection(p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2 DEFAULT NULL, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL) IS BEGIN EM_CHECK.check_not_null(p_target_name,'p_target_name') ; EM_CHECK.check_not_null(p_target_type,'p_target_type') ; EM_COLL_UTIL.suspend_resume_stop_collection (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_SUSPEND_COLLECTION_OP) ; END suspend_collection; -- -- PURPOSE -- Resume the repository collections for the specified target -- if metric_name or collection name is not specified then -- all collections for the target would be resumed -- PROCEDURE resume_collection(p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2 DEFAULT NULL, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL) IS BEGIN EM_CHECK.check_not_null(p_target_name,'p_target_name') ; EM_CHECK.check_not_null(p_target_type,'p_target_type') ; EM_COLL_UTIL.suspend_resume_stop_collection(p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_SCHEDULE_COLLECTION_OP) ; END resume_collection; -- --On demand collection of metrics for UI -- Runs the collection and also returns the metric values -- PROCEDURE run_collection (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL, p_key_value IN VARCHAR2 DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_evaluate_policy IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_metric_values OUT mgmt_metric_value_array ) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_source_type mgmt_metrics.source_type%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_error_message varchar2(1000) ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; l_evaluate_policy NUMBER(1) ; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.INFO('run_collection:Enter',G_MODULE_NAME) ; END IF ; p_metric_values := mgmt_metric_value_array() ; EM_COLL_UTIL.validate_target_metric (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; EM_COLL_UTIL.lock_metric(p_metric_guid=>l_metric_guid, p_willing_to_wait=>TRUE) ; -- If metric is to be stored then policy has to be evaluated -- to keep in sync metric and violations data IF p_store_metric = MGMT_GLOBAL.G_TRUE OR p_evaluate_policy = MGMT_GLOBAL.G_TRUE THEN l_evaluate_policy := MGMT_GLOBAL.G_TRUE ; ELSE l_evaluate_policy := MGMT_GLOBAL.G_FALSE ; END IF ; EM_COLL_UTIL.run_collection( p_target_guids=>mgmt_target_guid_array(l_target_guid), p_metric_guid=>l_metric_guid, p_coll_name=>p_coll_name, p_key_value=>p_key_value, p_eval_mode=>EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_store_metric=>p_store_metric, p_store_metric_errors=>p_store_metric, p_metric_values=>p_metric_values, p_error_message=>l_error_message, p_evaluate_policy=>l_evaluate_policy, p_timezone_region=>l_timezone_region) ; IF l_error_message is NOT NULL THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, l_error_message) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('run_collection:Exit Exception '||sqlerrm, G_MODULE_NAME) ; END IF ; RAISE ; END run_collection; -- --On demand collection of metrics -- Runs the metric collection and evaluates policy -- metrics are stored optionally -- --NOTE: The configuration standard policy evaluation is done as a bulk collection --for multiple targets and not for individual target.So run_collection with bulk --target list as parameter should be used for cs type policy evaluation. PROCEDURE run_collection (p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_store_metric_errors IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE ) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_task_id mgmt_collection_tasks.task_id%type ; l_metric_values mgmt_metric_value_array := mgmt_metric_value_array() ; l_error_message mgmt_metric_errors.metric_error_message%type ; l_source_type mgmt_metrics.source_type%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection:Enter',G_MODULE_NAME) ; END IF ; EM_COLL_UTIL.validate_target_metric (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>p_metric_name, p_metric_column=>p_metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; EM_COLL_UTIL.run_collection (p_target_guids=>mgmt_target_guid_array(l_target_guid), p_metric_guid=>l_metric_guid, p_eval_mode=>EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_metric_values=>l_metric_values, p_store_metric=>p_store_metric, p_store_metric_errors=>p_store_metric_errors, p_error_message=>l_error_message, p_evaluate_policy=>MGMT_GLOBAL.G_TRUE, p_coll_name=>p_coll_name, p_timezone_region=>l_timezone_region) ; IF l_error_message IS NOT NULL THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, l_error_message) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('run_collection:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; RAISE ; END run_collection; -- -- Run all the collections with collection name = p_coll_name -- PROCEDURE run_collection(p_coll_name IN VARCHAR2) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection(coll):Enter',G_MODULE_NAME) ; END IF ; EM_COLL_UTIL.run_collection(p_coll_name=>p_coll_name) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection(coll):Exit Normal ',G_MODULE_NAME) ; END IF ; END run_collection ; -- -- Run the collection for all the targets in the array -- PROCEDURE run_collection (p_targets_list IN MGMT_TARGET_ARRAY, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_store_metric_errors IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_config_std_name IN VARCHAR2 DEFAULT NULL, p_config_std_author IN VARCHAR2 DEFAULT NULL, p_config_std_version IN NUMBER DEFAULT 1 ) IS l_target_list mgmt_target_guid_array := mgmt_target_guid_array() ; CURSOR targets_cur IS SELECT 1 typ, metrics.metric_guid, metrics.type_meta_ver, metrics.category_prop_1, metrics.category_prop_2, metrics.category_prop_3, metrics.category_prop_4, metrics.category_prop_5, targets.target_guid, targets.timezone_region FROM mgmt_metrics metrics, mgmt_targets targets WHERE (targets.target_name,targets.target_type) IN (SELECT * FROM TABLE(CAST(p_targets_list AS MGMT_TARGET_ARRAY))) AND metrics.target_type = targets.target_type AND metrics.metric_name = p_metric_name AND metrics.metric_column = p_metric_column AND metrics.type_meta_ver = targets.type_meta_ver AND (metrics.category_prop_1 = ' ' OR metrics.category_prop_1 = targets.category_prop_1) AND (metrics.category_prop_2 = ' ' OR metrics.category_prop_2 = targets.category_prop_2) AND (metrics.category_prop_3 = ' ' OR metrics.category_prop_3 = targets.category_prop_3) AND (metrics.category_prop_4 = ' ' OR metrics.category_prop_4 = targets.category_prop_4) AND (metrics.category_prop_5 = ' ' OR metrics.category_prop_5 = targets.category_prop_5) UNION ALL -- dummy last record so we do not have to repeat code out of loop SELECT 2,null,null,null,null,null,null,null,null,null FROM DUAL ORDER BY 1,2,3,4,5,6,7 ; --configuration standard Id l_cs_guid mgmt_cs_config_standard.cs_guid%TYPE; l_prev_rec targets_cur%rowtype ; l_curr_rec targets_cur%rowtype ; l_metric_values mgmt_metric_value_array := mgmt_metric_value_array() ; l_error_message mgmt_metric_errors.metric_error_message%type ; l_batch_size NUMBER ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection(list):Enter',G_MODULE_NAME) ; END IF ; l_batch_size := get_bulk_batch_size ; IF p_targets_list IS NOT NULL AND p_targets_list.COUNT >0 THEN --If config standard details are given, get the cs_guid. IF (p_config_std_author IS NOT NULL AND p_config_std_name IS NOT NULL) THEN l_cs_guid := MGMT_CONFIG_STD.get_config_std_guid( p_config_std_name =>p_config_std_name, p_config_std_author => p_config_std_author, p_version => p_config_std_version); END IF; FOR targets IN targets_cur LOOP -- if this is the last(dummy) record or -- if the category prop or timezone changes then call collection IF targets.typ=2 OR ( l_prev_rec.type_meta_ver IS NOT NULL AND ( targets.timezone_region != l_prev_rec.timezone_region OR targets.category_prop_1 != l_prev_rec.category_prop_1 OR targets.category_prop_2 != l_prev_rec.category_prop_2 OR targets.category_prop_3 != l_prev_rec.category_prop_3 OR targets.category_prop_4 != l_prev_rec.category_prop_4 OR targets.category_prop_5 != l_prev_rec.category_prop_5 OR targets.type_meta_ver != l_prev_rec.type_meta_ver OR l_target_list.COUNT = l_batch_size ) ) THEN IF l_target_list IS NOT NULL AND l_target_list.COUNT > 0 THEN EM_COLL_UTIL.run_collection (p_target_guids=>l_target_list, p_metric_guid=>l_prev_rec.metric_guid, p_eval_mode=>EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_metric_values=>l_metric_values, p_store_metric=>p_store_metric, p_store_metric_errors=>p_store_metric_errors, p_error_message=>l_error_message, p_evaluate_policy=>MGMT_GLOBAL.G_TRUE, p_coll_name=>p_coll_name, p_timezone_region=>l_prev_rec.timezone_region, p_config_std_guid =>l_cs_guid ) ; l_target_list.DELETE ; END IF ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('run_collection(list):Adding Target:'|| targets.target_guid||' to list',G_MODULE_NAME); END IF ; l_target_list.extend ; l_target_list(l_target_list.COUNT) := targets.target_guid ; l_prev_rec := targets ; END LOOP ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection(list):Exit',G_MODULE_NAME) ; END IF ; END run_collection ; -- -- Set the bulk batch size for run_collection. -- This will be used internally split the targets passed to -- run_collections ( which accepts p_targets_list above) into batches -- for performance, since huge pl/sql tables can cause performance issues -- PROCEDURE set_bulk_batch_size(p_limit IN NUMBER) IS BEGIN -- Check for reasonable ranges EM_CHECK.CHECK_RANGE(p_limit,1,100000,'p_limit') ; INSERT INTO mgmt_parameters (parameter_name,parameter_value,parameter_comment,internal_flag) VALUES (G_BULK_BATCH_SIZE_PARAM,p_limit,G_BULK_BATCH_SIZE_PARAM,1) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE mgmt_parameters SET parameter_value = p_limit WHERE parameter_name = G_BULK_BATCH_SIZE_PARAM ; END set_bulk_batch_size ; -- -- get the current bulk batch size limit -- FUNCTION get_bulk_batch_size RETURN NUMBER IS l_parameter_value mgmt_parameters.parameter_value%type ; BEGIN SELECT parameter_value INTO l_parameter_value FROM mgmt_parameters WHERE parameter_name = G_BULK_BATCH_SIZE_PARAM ; RETURN(l_parameter_value) ; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN(G_DEFAULT_BATCH_SIZE) ; END get_bulk_batch_size ; -- -- Function to find if a default collection exists for the target type -- FUNCTION default_collection_exists(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_coll_name IN VARCHAR2 ) RETURN BOOLEAN IS l_count number ; BEGIN SELECT count(*) INTO l_count FROM mgmt_coll_items WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = p_coll_name ; RETURN(l_count > 0 ) ; END default_collection_exists; -- -- -- PURPOSE -- Add a default collection item for the target_type -- with the specified metrics and schedule -- PROCEDURE add_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_metrics_list IN mgmt_metric_name_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array DEFAULT NULL, p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_coll_properties IN mgmt_namevalue_array DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_auto_enable IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE ) IS l_metric_guid_list mgmt_short_string_array := mgmt_short_string_array() ; l_error_loc varchar2(20) ; l_default_guid mgmt_collections.object_guid%type ; l_metric_column_list mgmt_short_string_array := mgmt_short_string_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_default_collection(nm):Enter',G_MODULE_NAME) ; END IF ; IF p_metrics_list IS NULL OR p_metrics_list.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'list of metrics needs to be provided') ; END IF ; l_default_guid := default_object_guid(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver) ; IF default_collection_exists(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Default collection exists') ; END IF ; EM_COLL_UTIL.validate_schedule(p_coll_schedule) ; -- Populate mgmt_coll_items and mgmt_coll_item_metrics EM_COLL_UTIL.add_mod_del_coll_items (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_metrics_list=>p_metrics_list, p_coll_name=>nvl(p_coll_name,p_metrics_list(1).metric_name), p_op_code=>EM_COLL_UTIL.G_CREATE_COLL_ITEMS, p_valid_if_list=>p_valid_if_list, p_is_enabled=>p_is_enabled) ; -- populate mgmt_collections EM_COLL_UTIL.add_collection_entry (p_object_guid=>l_default_guid , p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=> nvl(p_coll_name,p_metrics_list(1).metric_name), p_is_repository=>MGMT_GLOBAL.G_TRUE, p_schedule_ex=>NULL, p_coll_schedule => p_coll_schedule , p_store_metric => p_store_metric , p_is_enabled => p_is_enabled ) ; -- populate mgmt_coll_item_properties EM_COLL_UTIL.create_coll_properties (p_object_guid=>l_default_guid, p_metric_guid=>MGMT_GLOBAL.G_ALL_ZERO_GUID, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=>p_coll_name, p_coll_properties=>p_coll_properties) ; IF p_auto_enable = MGMT_GLOBAL.G_TRUE THEN start_default_collection(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_default_collection(nm):Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('add_default_collection(nm):Exit exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' when adding default collections') ; END IF ; END add_default_collection; PROCEDURE add_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_metrics_list IN mgmt_short_string_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array DEFAULT NULL, p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_coll_properties IN mgmt_namevalue_array DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_auto_enable IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE ) IS l_metrics_list mgmt_metric_name_array := mgmt_metric_name_array(); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_default_collection(ss):Enter',G_MODULE_NAME) ; END IF ; IF p_metrics_list IS NULL OR p_metrics_list.COUNT=0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'list of metrics needs to be provided') ; END IF ; l_metrics_list.extend(p_metrics_list.COUNT) ; FOR i in p_metrics_list.FIRST..p_metrics_list.LAST LOOP l_metrics_list(i) := mgmt_metric_name_obj.new(p_metrics_list(i),' ') ; END LOOP ; add_default_collection (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_metrics_list=>l_metrics_list, p_coll_name=>p_coll_name, p_valid_if_list=>p_valid_if_list, p_coll_schedule=>p_coll_schedule, p_coll_properties=>p_coll_properties, p_store_metric =>p_store_metric, p_is_enabled=>p_store_metric , p_auto_enable=>p_auto_enable ); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_default_collection(ss):Exit',G_MODULE_NAME) ; END IF ; END add_default_collection; -- -- PURPOSE -- modify the default collection -- if metric names are given, then it replaces current metrics -- valid-ifs have to be given if the metrics are given -- If schedule is specified then it replaces current schedule -- If collection properties is specified then it replaces current properties -- if store_metric is specified it changes the collection -- if p_is_enabled is set then it enables/disables default collection -- PROCEDURE modify_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_metrics_list IN mgmt_metric_name_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array DEFAULT NULL, p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_coll_properties IN mgmt_namevalue_array DEFAULT NULL, p_store_metric IN NUMBER DEFAULT NULL, p_is_enabled IN NUMBER DEFAULT NULL ) IS l_default_guid mgmt_collections.object_guid%type ; l_metric_column_list mgmt_short_string_array := mgmt_short_string_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_default_collection:Enter',G_MODULE_NAME) ; END IF ; l_default_guid := default_object_guid(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver) ; IF NOT default_collection_exists(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Collection does not exist') ; END IF ; IF p_coll_schedule IS NOT NULL OR p_is_enabled IS NOT NULL OR p_store_metric IS NOT NULL THEN IF p_coll_schedule IS NOT NULL THEN EM_COLL_UTIL.validate_schedule(p_coll_schedule) ; END IF ; EM_COLL_UTIL.modify_collection_entry (p_object_guid=>l_default_guid, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=>p_coll_name, p_schedule_ex=>NULL, p_coll_schedule=>p_coll_schedule, p_store_metric=>p_store_metric, p_is_enabled=>p_is_enabled) ; END IF ; IF p_metrics_list IS NOT NULL AND p_metrics_list.COUNT > 0 THEN -- delete and create mgmt_coll_items and coll_item_metrics EM_COLL_UTIL.add_mod_del_coll_items( p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_DELETE_COLL_ITEMS, p_metrics_list=>p_metrics_list, p_valid_if_list=>p_valid_if_list, p_is_enabled=>p_is_enabled) ; EM_COLL_UTIL.add_mod_del_coll_items (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_CREATE_COLL_ITEMS, p_metrics_list=>p_metrics_list, p_valid_if_list=>p_valid_if_list, p_is_enabled=>p_is_enabled) ; END IF ; IF p_coll_properties IS NOT NULL THEN -- delete and create mgmt_coll_item_properties EM_COLL_UTIL.delete_coll_properties (p_object_guid=>l_default_guid, p_metric_guid=>MGMT_GLOBAL.G_ALL_ZERO_GUID, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=>p_coll_name) ; EM_COLL_UTIL.create_coll_properties (p_object_guid=>l_default_guid, p_metric_guid=>MGMT_GLOBAL.G_ALL_ZERO_GUID, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=>p_coll_name, p_coll_properties=>p_coll_properties) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_default_collection:Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('modify_default_collection:Exit Exception - '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' when modifying default collections') ; END IF ; END modify_default_collection; -- -- Modify default collection -- The following can be modified for a default collection -- Modify the list of metrics in a collection -- Modify the valid-ifs for the collection -- Modify collection schedule -- Modify collection properties -- Modify store_metric -- Enable/Disable default collection --- PROCEDURE modify_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_metrics_list IN mgmt_short_string_array, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array DEFAULT NULL, p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_coll_properties IN mgmt_namevalue_array DEFAULT NULL, p_store_metric IN NUMBER DEFAULT NULL, p_is_enabled IN NUMBER DEFAULT NULL ) IS l_metrics_list mgmt_metric_name_array := mgmt_metric_name_array(); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_default_collection(ss):Enter',G_MODULE_NAME) ; END IF ; IF p_metrics_list IS NULL OR p_metrics_list.COUNT=0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'list of metrics needs to be provided') ; END IF ; l_metrics_list.extend(p_metrics_list.COUNT) ; FOR i in p_metrics_list.FIRST..p_metrics_list.LAST LOOP l_metrics_list(i) := mgmt_metric_name_obj.new(p_metrics_list(i),' ') ; END LOOP ; modify_default_collection (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_metrics_list=>l_metrics_list, p_coll_name=>p_coll_name, p_valid_if_list=>p_valid_if_list, p_coll_schedule=>p_coll_schedule, p_coll_properties=>p_coll_properties, p_store_metric =>p_store_metric, p_is_enabled=>p_store_metric ); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_default_collection(ss):Exit',G_MODULE_NAME) ; END IF ; END modify_default_collection; -- -- -- Enable default collections -- The default collection be applied to future targets -- added in the repository -- PROCEDURE enable_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array DEFAULT NULL) IS BEGIN EM_COLL_UTIL.add_mod_del_coll_items (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_ENABLE_COLL_ITEMS, p_valid_if_list=>p_valid_if_list ) ; END enable_default_collection; -- -- Disable default collections -- The default collections will not be applied to future targets -- added in the repository -- PROCEDURE disable_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array DEFAULT NULL) IS BEGIN EM_COLL_UTIL.add_mod_del_coll_items (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_DISABLE_COLL_ITEMS, p_valid_if_list=>p_valid_if_list ) ; END disable_default_collection; -- -- PURPOSE -- Delete the default collection for the target type -- If valid-if list is given then only the default collections -- for the given valid-if will be deleted -- PROCEDURE delete_default_collection (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT NULL, p_valid_if_list IN mgmt_validif_array) IS l_default_guid mgmt_collections.object_guid%type ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_default_collection:Enter',G_MODULE_NAME) ; END IF ; IF NOT default_collection_exists(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Default Collection does not exist') ; END IF ; -- Delete collection items for the given valid-if EM_COLL_UTIL.add_mod_del_coll_items (p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name, p_op_code=>EM_COLL_UTIL.G_DELETE_COLL_ITEMS, p_valid_if_list=>p_valid_if_list) ; -- Same collection item can exist for multiple valid-ifs -- if there is no entry for the coll item then -- delete collection properties and collection entry IF NOT default_collection_exists(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name) THEN -- create a default guid using target type l_default_guid := default_object_guid(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver) ; EM_COLL_UTIL.delete_coll_properties (p_object_guid=>l_default_guid, p_metric_guid => MGMT_GLOBAL.G_ALL_ZERO_GUID, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=>p_coll_name) ; EM_COLL_UTIL.delete_collection_entry (p_object_guid=>l_default_guid, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT, p_coll_name=>p_coll_name) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_default_collection:Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_default_collection:Exit exception '||sqlerrm, G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' when deleting default collections') ; END IF ; END delete_default_collection; -- -- PURPOSE -- procedure to start the default collection for the target -- Existing collections if any are deleted. This is to be called -- from target add callback. -- Assumption: The target already exists in mgmt_targets table -- PROCEDURE start_default_collection(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_target_guid IN RAW DEFAULT NULL) IS CURSOR valid_metrics(p_target_type IN VARCHAR2, p_target_guid IN RAW, p_object_guid IN RAW) IS SELECT coll_item.coll_name, LEAD(coll_item.coll_name) OVER (ORDER BY coll_item.coll_name) next_coll_name, metrics.metric_name, metrics.metric_column, coll.store_metric, targets.target_guid, targets.type_meta_ver, coll_item.category_prop_1, coll_item.category_prop_2, coll_item.category_prop_3, coll_item.category_prop_4, coll_item.category_prop_5, coll_metrics.metric_guid, mgmt_coll_schedule_obj.new( coll.frequency_code, coll.execution_hours, coll.execution_minutes, coll.interval, coll.months, coll.days, nvl(mgmt_global.sysdate_tzrgn(targets.timezone_region), mgmt_global.sysdate_utc), NULL) schedule_obj FROM mgmt_coll_items coll_item, mgmt_targets targets, mgmt_coll_item_metrics coll_metrics, mgmt_collections coll, mgmt_metrics metrics WHERE targets.target_guid = p_target_guid AND coll_item.target_type = p_target_type AND coll_item.type_meta_ver = targets.type_meta_ver AND (coll_item.category_prop_1 = ' ' OR coll_item.category_prop_1 = targets.category_prop_1) AND (coll_item.category_prop_2 = ' ' OR coll_item.category_prop_2 = targets.category_prop_2) AND (coll_item.category_prop_3 = ' ' OR coll_item.category_prop_3 = targets.category_prop_3) AND (coll_item.category_prop_4 = ' ' OR coll_item.category_prop_4 = targets.category_prop_4) AND (coll_item.category_prop_5 = ' ' OR coll_item.category_prop_5 = targets.category_prop_5) AND coll_item.is_enabled = MGMT_GLOBAL.G_TRUE AND coll_metrics.target_type = p_target_type AND coll_metrics.coll_name = coll_item.coll_name AND coll_metrics.type_meta_ver = coll_item.type_meta_ver AND coll.object_guid = p_object_guid AND coll.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT AND coll.coll_name = coll_item.coll_name AND metrics.metric_guid = coll_metrics.metric_guid AND metrics.target_type = targets.target_type AND metrics.type_meta_ver = targets.type_meta_ver AND (metrics.category_prop_1 = ' ' OR metrics.category_prop_1 = targets.category_prop_1 )AND (metrics.category_prop_2 = ' ' OR metrics.category_prop_2 = targets.category_prop_2 ) AND (metrics.category_prop_3 = ' ' OR metrics.category_prop_3 = targets.category_prop_3 ) AND (metrics.category_prop_4 = ' ' OR metrics.category_prop_4 =targets.category_prop_4 ) AND (metrics.category_prop_5 = ' ' OR metrics.category_prop_5 = targets.category_prop_5 ) AND metrics.is_repository = MGMT_GLOBAL.G_TRUE ORDER by coll_name ; l_default_guid mgmt_collections.object_guid%type ; l_target_guid mgmt_targets.target_guid%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_metrics_list mgmt_metric_name_array := mgmt_metric_name_array() ; invalid_target_exception EXCEPTION; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_default_collection:Enter',G_MODULE_NAME) ; END IF ; BEGIN SELECT target_guid, type_meta_ver INTO l_target_guid, l_type_meta_ver FROM mgmt_targets WHERE target_name = p_target_name AND target_type = p_target_type ; EXCEPTION WHEN NO_DATA_FOUND THEN raise invalid_target_exception ; END ; l_default_guid := default_object_guid(p_target_type=>p_target_type, p_type_meta_ver=>l_type_meta_ver) ; FOR rec in valid_metrics(p_target_type,l_target_guid,l_default_guid) LOOP BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('start_default_collection: Mgid='||rec.metric_guid|| ' catprop='||rec.category_prop_1||','|| rec.category_prop_2||','|| rec.category_prop_3||','|| rec.category_prop_4||','|| rec.category_prop_5,G_MODULE_NAME) ; END IF ; l_metrics_list.extend(1) ; l_metrics_list(l_metrics_list.COUNT) := mgmt_metric_name_obj.new ( p_metric_name=>rec.metric_name, p_metric_column=>rec.metric_column); IF rec.coll_name != nvl(rec.next_coll_name,rec.coll_name||'1' ) THEN BEGIN start_collection(p_target_type=>p_target_type, p_target_name=>p_target_name, p_metrics_list=>l_metrics_list, p_coll_name=>rec.coll_name, p_store_metric=>rec.store_metric, p_schedule_ex=>NULL, p_coll_schedule=>rec.schedule_obj) ; EXCEPTION WHEN OTHERS THEN MGMT_LOG.log_error( v_module_name_in=>MGMT_COLLECTION.G_MODULE_NAME, v_error_code_in=>MGMT_GLOBAL.COLLECTION_ERR, v_error_msg_in=>'Collection error: Starting default collections for '|| ' Target='||p_target_name|| ' Collection='||rec.coll_name || ' Error='|| substr(sqlerrm,12), v_facility_in=>NULL, v_log_level_in=>MGMT_GLOBAL.G_ERROR) ; END ; l_metrics_list := mgmt_metric_name_array() ; END IF ; END ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_default_collection:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN invalid_target_exception THEN NULL ; END start_default_collection; -- -- Start default collection for all existing targets for -- The target type version -- PROCEDURE start_default_collection(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2) IS CURSOR valid_metrics(p_target_type IN VARCHAR2, p_object_guid IN RAW) IS SELECT targets.target_name, coll_item.coll_name, LEAD(targets.target_name) OVER (ORDER BY targets.target_name,coll_item.coll_name) next_target_name, LEAD(coll_item.coll_name) OVER (ORDER BY targets.target_name,coll_item.coll_name) next_coll_name, metrics.metric_name, metrics.metric_column, coll.store_metric, targets.target_guid, targets.type_meta_ver, coll_item.category_prop_1, coll_item.category_prop_2, coll_item.category_prop_3, coll_item.category_prop_4, coll_item.category_prop_5, coll_metrics.metric_guid, mgmt_coll_schedule_obj.new( coll.frequency_code, coll.execution_hours, coll.execution_minutes, coll.interval, coll.months, coll.days, nvl(mgmt_global.sysdate_tzrgn(targets.timezone_region), mgmt_global.sysdate_utc), NULL) schedule_obj FROM mgmt_coll_items coll_item, mgmt_targets targets, mgmt_coll_item_metrics coll_metrics, mgmt_collections coll, mgmt_metrics metrics WHERE targets.target_type = p_target_type AND targets.type_meta_ver = p_type_meta_ver AND coll_item.target_type = p_target_type AND coll_item.type_meta_ver = p_type_meta_ver AND (coll_item.category_prop_1 = ' ' OR coll_item.category_prop_1 = targets.category_prop_1) AND (coll_item.category_prop_2 = ' ' OR coll_item.category_prop_2 = targets.category_prop_2) AND (coll_item.category_prop_3 = ' ' OR coll_item.category_prop_3 = targets.category_prop_3) AND (coll_item.category_prop_4 = ' ' OR coll_item.category_prop_4 = targets.category_prop_4) AND (coll_item.category_prop_5 = ' ' OR coll_item.category_prop_5 = targets.category_prop_5) AND coll_item.is_enabled = MGMT_GLOBAL.G_TRUE AND coll_metrics.target_type = p_target_type AND coll_metrics.coll_name = coll_item.coll_name AND coll_metrics.type_meta_ver = p_type_meta_ver AND coll.object_guid = p_object_guid AND coll.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT AND coll.coll_name = coll_item.coll_name AND metrics.metric_guid = coll_metrics.metric_guid AND metrics.target_type = targets.target_type AND metrics.type_meta_ver = targets.type_meta_ver AND (metrics.category_prop_1 = ' ' OR metrics.category_prop_1 = targets.category_prop_1 )AND (metrics.category_prop_2 = ' ' OR metrics.category_prop_2 = targets.category_prop_2 ) AND (metrics.category_prop_3 = ' ' OR metrics.category_prop_3 = targets.category_prop_3 ) AND (metrics.category_prop_4 = ' ' OR metrics.category_prop_4 =targets.category_prop_4 ) AND (metrics.category_prop_5 = ' ' OR metrics.category_prop_5 = targets.category_prop_5 ) ORDER BY target_name,coll_name ; l_default_guid mgmt_collections.object_guid%type ; l_metrics_list mgmt_metric_name_array := mgmt_metric_name_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_default_collection:Enter',G_MODULE_NAME) ; END IF ; l_default_guid := default_object_guid(p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver) ; FOR rec in valid_metrics(p_target_type,l_default_guid) LOOP BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('start_default_collection:'|| ' Tgt='||rec.target_name|| ' Mgid='||rec.metric_guid|| ' catprop='||rec.category_prop_1||','|| rec.category_prop_2||','|| rec.category_prop_3||','|| rec.category_prop_4||','|| rec.category_prop_5,G_MODULE_NAME) ; END IF ; l_metrics_list.extend(1) ; l_metrics_list(l_metrics_list.COUNT) := mgmt_metric_name_obj.new ( p_metric_name=>rec.metric_name, p_metric_column=>rec.metric_column); IF rec.target_name != nvl(rec.next_target_name,rec.target_name||'1' ) OR rec.coll_name != nvl(rec.next_coll_name,rec.coll_name||'1' ) THEN BEGIN start_collection(p_target_type=>p_target_type, p_target_name=>rec.target_name, p_metrics_list=>l_metrics_list, p_coll_name=>rec.coll_name, p_store_metric=>rec.store_metric, p_schedule_ex=>NULL, p_coll_schedule=>rec.schedule_obj) ; EXCEPTION WHEN OTHERS THEN MGMT_LOG.log_error( v_module_name_in=>MGMT_COLLECTION.G_MODULE_NAME, v_error_code_in=>MGMT_GLOBAL.COLLECTION_ERR, v_error_msg_in=>'Collection error: Starting default collections for '|| ' Target='||rec.target_name|| ' Collection='||rec.coll_name|| ' Error='|| substr(sqlerrm,12), v_facility_in=>NULL, v_log_level_in=>MGMT_GLOBAL.G_ERROR) ; END ; l_metrics_list := mgmt_metric_name_array() ; END IF ; END ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_default_collection:Exit Normal',G_MODULE_NAME) ; END IF ; END start_default_collection; -- -- Procedure to store metric data returned by external -- metric evaluation procedures directly and to evaluate thresholds on it -- if specified -- if p_store_metric = MGMT_GLOBAL.G_TRUE then data will be stored in -- mgmt_metrics_raw -- if p_check_thresholds =MGMT_GLOBAL.G_TRUE is specified then metric thresholds -- defined for the target/metric will be checked and alert will be raised as -- appropriate -- p_coll_name: collection name has to be specified if thresholds have to be -- checked, since thresholds are defined on collection name -- p_collection_timestamp: if not specified will default to the system -- date in the target's timezone -- PROCEDURE store_metric_data (p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_results IN mgmt_metric_results_table, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_coll_name IN VARCHAR2 DEFAULT ' ', p_check_thresholds IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE, p_collection_timestamp IN DATE DEFAULT NULL ) IS l_target_guid mgmt_targets.target_guid%TYPE ; l_metric_guid mgmt_metrics.metric_guid%TYPE ; l_collection_timestamp DATE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data(sdk):Enter',G_MODULE_NAME) ; END IF ; l_target_guid := mgmt_target.get_target_guid(p_target_name,p_target_type) ; l_metric_guid := mgmt_metric.get_metric_guid_for_target (p_target_type,p_target_name,p_metric_name,' ') ; l_collection_timestamp := NVL(p_collection_timestamp, mgmt_target.sysdate_target(l_target_guid)); EM_COLL_UTIL.store_metric_data (p_target_guid => l_target_guid, p_metric_guid => l_metric_guid, p_metric_results => p_metric_results, p_store_metric => p_store_metric, p_coll_name => p_coll_name, p_check_thresholds => p_check_thresholds, p_collection_timestamp => p_collection_timestamp) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data(sdk):Exit',G_MODULE_NAME) ; END IF ; END store_metric_data; PROCEDURE validate_task_class(p_task_class IN VARCHAR2) IS BEGIN IF p_task_class = EM_TASK.G_ALL_TASKS THEN RETURN ; END IF ; IF substr(p_task_class,1,1)=',' OR substr(p_task_class,-1,1)=',' OR instr(p_task_class,',,')>0 THEN RAISE no_data_found ; END IF ; FOR i IN 1..length(p_task_class) LOOP IF substr(p_task_class,i,1) != ',' AND TO_NUMBER(substr(p_task_class,i,1)) NOT BETWEEN 0 AND 9 THEN raise no_data_found ; END IF ; END LOOP ; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'task class should be number or '|| 'comma seperated list of numbers without whitespace') ; END validate_task_class ; -- -- PURPOSE -- Set the default worker counts -- PROCEDURE set_worker_count(p_task_class IN VARCHAR2, p_worker_count IN NUMBER) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_worker_count:Enter task class='||p_task_class|| ' worker_count='||p_worker_count,G_MODULE_NAME) ; END IF ; EM_CHECK.check_not_null(p_task_class,'p_task_class') ; EM_CHECK.check_not_null(p_worker_count,'p_worker_count') ; EM_CHECK.check_range(p_worker_count,0,100,'p_worker_count') ; validate_task_class(p_task_class) ; IF p_worker_count > 0 THEN UPDATE mgmt_task_worker_counts SET worker_count = p_worker_count WHERE task_class_list = p_task_class ; IF SQL%NOTFOUND THEN INSERT INTO mgmt_task_worker_counts (task_class_list,worker_count) VALUES (p_task_class,p_worker_count) ; END IF ; ELSE DELETE mgmt_task_worker_counts WHERE task_class_list = p_task_class ; END IF ; COMMIT ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_worker_count:Exit',G_MODULE_NAME) ; END IF ; END set_worker_count ; -- -- PURPOSE -- To start default number of workers -- PROCEDURE start_workers IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_workers:Enter',G_MODULE_NAME) ; END IF ; remove_all_workers ; FOR rec IN (SELECT * FROM mgmt_task_worker_counts WHERE worker_count >0 ) LOOP add_workers(rec.worker_count,rec.task_class_list) ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('start_workers:Exit',G_MODULE_NAME) ; END IF ; END start_workers ; -- -- PURPOSE -- Increase the number of workers by worker_count for given task class -- PROCEDURE add_workers(p_worker_count IN NUMBER DEFAULT 1, p_task_class IN VARCHAR2 DEFAULT NULL) IS l_worker_id mgmt_collection_workers.worker_id%TYPE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_workers:Enter Worker Count='||p_worker_count|| ' task_class='||p_task_class,G_MODULE_NAME) ; END IF ; IF nvl(p_worker_count,0) <= 0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Worker count should be greater than 0') ; END IF ; LOCK TABLE mgmt_collection_workers IN EXCLUSIVE MODE ; FOR i IN 1..p_worker_count LOOP l_worker_id := EM_TASK.CREATE_WORKER(p_background=>TRUE, p_task_class_list=> NVL(p_task_class,EM_TASK.G_ALL_TASKS)) ; END LOOP ; COMMIT ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_workers:Exit',G_MODULE_NAME) ; END IF ; END add_workers; -- -- PURPOSE -- Decrease the number of workers by worker_count -- IF p_stop_mode = G_NORMAL_STOP Then the worker would -- complete all pending tasks and when it does not find any more -- tasks it will stop -- IF p_stop_mode= G_IMMEDIATE_STOP then the worker would -- complete its current task and then exit -- PROCEDURE remove_workers(p_worker_count IN NUMBER, p_stop_mode IN NUMBER := G_NORMAL_STOP, p_task_class IN VARCHAR2 DEFAULT NULL) IS BEGIN -- Ignore if p_worker_count is NULL IF p_worker_count > 0 THEN EM_TASK.stop_workers(p_worker_count=>p_worker_count, p_stop_mode=>p_stop_mode, p_task_class_list=> NVL(p_task_class,EM_TASK.G_ALL_TASKS)); END IF ; END remove_workers; -- -- PURPOSE -- Stop all workers -- PROCEDURE remove_all_workers(p_stop_mode IN NUMBER := G_NORMAL_STOP) IS BEGIN LOCK TABLE mgmt_collection_workers IN EXCLUSIVE MODE ; FOR rec IN (SELECT task_class_list,count(*) worker_count FROM mgmt_collection_workers WHERE worker_status != EM_TASK.G_WORKER_STATUS_STOP_PENDING GROUP BY task_class_list) LOOP EM_TASK.stop_workers(p_worker_count=> rec.worker_count, p_task_class_list=>rec.task_class_list, p_stop_mode=>p_stop_mode) ; END LOOP ; END remove_all_workers; -- -- PURPOSE -- Stop the worker associated with the dbms_job and drop the job -- PROCEDURE stop_dbms_job(p_job_id IN NUMBER, p_stop_mode IN NUMBER := G_NORMAL_STOP) IS l_worker_id mgmt_collection_workers.worker_id%type ; BEGIN -- Full table scan here, since small table it is ok SELECT worker_id INTO l_worker_id FROM mgmt_collection_workers WHERE job_id = p_job_id ; EM_TASK.REMOVE_WORKER(l_worker_id) ; COMMIT ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid DBMS Job Id - '||p_job_id) ; END stop_dbms_job ; -- -- PURPOSE -- Run a collection worker in current session syncronously -- A new worker would be created and run in current session --Caution:-- *** ISSUES A COMMIT****** -- PROCEDURE run_worker(p_task_class IN VARCHAR2 DEFAULT NULL) IS l_worker_id mgmt_collection_workers.worker_id%TYPE ; PRAGMA autonomous_transaction ; BEGIN l_worker_id := EM_TASK.create_worker(p_background=>FALSE, p_task_class_list=> NVL(p_task_class,EM_TASK.G_ALL_TASKS)); -- commit to unlock the worker record COMMIT ; EM_TASK.worker(l_worker_id) ; COMMIT ; EXCEPTION WHEN OTHERS THEN ROLLBACK ; RAISE ; END run_worker; END mgmt_collection; / show errors