Rem Rem $Header: group_pkgbody.sql 11-apr-2007.04:38:04 acgopala Exp $ Rem Rem group_pkgbody.sql Rem Rem Copyright (c) 2004, 2007, Oracle. All rights reserved. Rem Rem NAME Rem group_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem acgopala 04/10/07 - dif fix dif_fix_core1024 Rem gan 12/02/05 - Backport gan_bug-4669119 from main Rem pmodi 08/26/05 - Bug:4571749 - Chg coll_name to avoid PK Rem gan 07/26/05 - bug 4514324, check null Rem dtsao 07/05/05 - Pass collection name when deleteing charts Rem ramalhot 04/05/05 - create_group modified Rem ramalhot 03/11/05 - add group and modify_group now refers to Rem em_target pkg Rem neearora 02/23/05 - Added new procedure upsert_group Rem pmodi 02/02/05 - group to system conversion api support Rem ramalhot 01/10/05 - ramalhot_goup_migration Rem ramalhot 12/30/04 - Created Rem CREATE OR REPLACE PACKAGE BODY em_group AS PROCEDURE check_group ( p_target_type IN mgmt_targets.target_type%TYPE ); -- Prcocedure to create chart for a group PROCEDURE create_charts ( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_charts_list IN MGMT_GRP_CHART_ARRAY ) IS l_chart_guid mgmt_group_chart.comp_chart_guid%TYPE; l_member_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_schedule_obj MGMT_COLL_SCHEDULE_OBJ := NULL; l_target_guid mgmt_targets.target_guid%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('create_charts:Entry',MODULE_NAME); END IF; -- get the target guid l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); -- get the guid for the metric level l_metric_guid := MGMT_METRIC.get_metric_guid(p_target_type,SUM_METRIC, ' '); IF (p_charts_list IS NOT NULL AND p_charts_list.COUNT > 0)THEN FOR j IN 1..p_charts_list.COUNT LOOP -- get the guid for the metric level l_member_metric_guid := MGMT_METRIC.get_metric_guid(p_charts_list(j).target_type, p_charts_list(j).metric_name, p_charts_list(j).metric_column); INSERT INTO mgmt_group_chart (comp_target_guid, chart_type, member_metric_guid, display_order, targets_count) VALUES (l_target_guid, p_charts_list(j).chart_type, l_member_metric_guid, p_charts_list(j).display_order, p_charts_list(j).targets_count) RETURNING comp_chart_guid INTO l_chart_guid; -- if this is of type selected targets -- get and save the appropriate target info IF (p_charts_list(j).chart_type = MGMT_GROUP.CHART_SELECTED_TARGETS) THEN INSERT INTO mgmt_group_chart_seltargets (comp_chart_guid, target_guid) SELECT l_chart_guid, target_guid FROM mgmt_targets t, TABLE(CAST(p_charts_list(j).sel_targets AS SMP_EMD_NVPAIR_ARRAY)) sel WHERE sel.name = t.target_name AND sel.value = t.target_type; -- if chart requires summarization -- save appropriate definiton ELSIF (p_charts_list(j).chart_type = MGMT_GROUP.CHART_SUMMARY_METRIC) THEN INSERT INTO mgmt_group_summary_chart_def (comp_chart_guid, comp_target_guid, comp_metric_guid, min_column, max_column, avg_column, sum_column, stdev_column) VALUES (l_chart_guid, l_target_guid, l_metric_guid, p_charts_list(j).min_column, p_charts_list(j).max_column, p_charts_list(j).avg_column, p_charts_list(j).sum_column, p_charts_list(j).stdev_column ); END IF; END LOOP; l_schedule_obj := MGMT_COLL_SCHEDULE_OBJ.interval_schedule(15,NULL,NULL); -- for charts that require summarization -- get the metric details FOR rec IN (SELECT sm.metric_guid metric_guid , 'Sum-'||substr(sm.metric_column,1,27)||'-'|| sm.metric_guid col_name FROM ( SELECT DISTINCT m.metric_guid, m.target_type, m.metric_column FROM TABLE(CAST(p_charts_list AS MGMT_GRP_CHART_ARRAY)) c, mgmt_metrics m WHERE c.chart_type = MGMT_GROUP.CHART_SUMMARY_METRIC AND c.target_type = m.target_type AND c.metric_name = m.metric_name AND c.metric_column = m.metric_column ) sm ) LOOP -- set up collections MGMT_COLLECTION.start_collection(p_target_name => p_target_name, p_target_type => p_target_type, p_metric_name => SUM_METRIC, p_coll_name => rec.col_name, p_coll_schedule => l_schedule_obj); -- add a collection property to uniquely -- identify the member metric guid we are summarising EM_COLL_UTIL.add_coll_item_property(p_object_guid => l_target_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_metric_guid => l_metric_guid, p_coll_name => rec.col_name, p_property_name => MEM_METRIC_GUID_PROP_NAME, p_property_value => rec.metric_guid); END LOOP; END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('create_charts:Exit',MODULE_NAME); END IF; END create_charts; -- Procedure to delete charts for a group PROCEDURE delete_charts ( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE ) IS l_metric_guid mgmt_metrics.metric_guid%TYPE; l_target_guid mgmt_targets.target_guid%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('delete_charts:Entry',MODULE_NAME); END IF; -- get the target guid l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); -- get the guid for the metric level l_metric_guid := MGMT_METRIC.get_metric_guid(p_target_type,SUM_METRIC, ' '); -- delete all previous chart customization data DELETE FROM mgmt_group_chart_seltargets WHERE comp_chart_guid IN ( SELECT comp_chart_guid FROM mgmt_group_chart WHERE comp_target_guid = l_target_guid); DELETE FROM mgmt_group_summary_chart_def WHERE comp_target_guid = l_target_guid; FOR chart in ( SELECT 'Sum-'||substr(m.metric_column,1,27)||'-'|| m.metric_guid col_name FROM mgmt_group_chart c, mgmt_metrics m WHERE c.comp_target_guid = l_target_guid AND c.chart_type = MGMT_GROUP.CHART_SUMMARY_METRIC AND c.member_metric_guid = m.metric_guid ) LOOP -- delete all the collections previously setup for this target MGMT_COLLECTION.stop_collection(p_target_name => p_target_name, p_target_type => p_target_type, p_metric_name => SUM_METRIC, p_coll_name => chart.col_name); END LOOP; EM_COLL_UTIL.delete_coll_properties(p_object_guid => l_target_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_metric_guid => l_metric_guid); DELETE FROM mgmt_group_chart WHERE comp_target_guid = l_target_guid; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('delete_charts:Exit',MODULE_NAME); END IF; END delete_charts; -- Procedure to modify charts for a group PROCEDURE modify_charts ( p_target_name IN mgmt_targets.target_name%TYPE, p_target_type IN mgmt_targets.target_type%TYPE, p_charts_list IN MGMT_GRP_CHART_ARRAY ) IS BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('modify_charts:Entry',MODULE_NAME); END IF; delete_charts(p_target_name, p_target_type); create_charts(p_target_name, p_target_type, p_charts_list); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('modify_charts:Exit',MODULE_NAME); END IF; END modify_charts; -- To create a custom columns for given target type and target name PROCEDURE create_group_custom_columns ( p_target_name IN mgmt_targets.target_name%type, p_target_type IN mgmt_targets.target_type%type, p_column_list IN MGMT_GRP_COLUMN_ARRAY ) IS l_target_guid MGMT_TARGETS.target_guid%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('CREATE_GROUP_CUSTOM_COLUMNS:Entry',MODULE_NAME); END IF; -- get the target guid l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); IF (p_column_list IS NOT NULL AND p_column_list.COUNT > 0) THEN INSERT INTO mgmt_group_custom_columns (composite_target_guid, column_type, member_metric_guid, property_name, target_type, abbreviation, display_order) SELECT l_target_guid, c.column_type, c.metric_guid, c.property_name, c.target_type, c.abbreviation, c.display_order FROM TABLE(CAST(p_column_list AS MGMT_GRP_COLUMN_ARRAY)) c ORDER BY c.display_order; END IF; IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('CREATE_GROUP_CUSTOM_COLUMNS:Exit',MODULE_NAME); END IF; END create_group_custom_columns ; -- To delete a custom columns for given target type and target name PROCEDURE delete_group_custom_columns ( p_target_name IN mgmt_targets.target_name%type, p_target_type IN mgmt_targets.target_type%type ) IS l_target_guid MGMT_TARGETS.target_guid%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('DELETE_GROUP_CUSTOM_COLUMNS:Entry',MODULE_NAME); END IF; -- get the target guid l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); DELETE FROM mgmt_group_custom_columns WHERE composite_target_guid = l_target_guid; IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('DELETE_GROUP_CUSTOM_COLUMNS:Exit',MODULE_NAME); END IF; END delete_group_custom_columns ; -- To modify a custom columns for given target type and target name PROCEDURE modify_group_custom_columns ( p_target_name IN mgmt_targets.target_name%type, p_target_type IN mgmt_targets.target_type%type, p_column_list IN MGMT_GRP_COLUMN_ARRAY ) IS BEGIN IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('MODIFY_GROUP_CUSTOM_COLUMNS:Entry',MODULE_NAME); END IF; delete_group_custom_columns(p_target_name => p_target_name, p_target_type => p_target_type ); create_group_custom_columns(p_target_name => p_target_name, p_target_type => p_target_type, p_column_list => p_column_list ); IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('MODIFY_GROUP_CUSTOM_COLUMNS:Exit',MODULE_NAME); END IF; END modify_group_custom_columns ; -- -- PROCEDURE : create_system_from_group -- -- PURPOSE : To create system from group. -- Procedure to support group to system conversion API : Internal method -- -- IN parameter : -- p_group_name : Target name of Group from which system needs to be created -- p_group_type : Traget type of Group type from which system needs to be created -- p_system_name : Target name for new system. If not specified then it would be same as Group name -- p_system_type : Target type of new system. -- p_delete_group : Whether group to be deleted or not -- 1 > If called from public method migrate_group_to_system -- 0 > If called from public method create_system_from_group -- -- OUT parameter : -- None -- -- ERROR CODES: -- INSUFFICIENT_PRIVILEGES_ERR : If user does not have required privilege -- INVALID_GROUP_ERR : If given group does not exists PROCEDURE create_system_from_group ( p_group_name IN VARCHAR2, p_group_type IN VARCHAR2, p_system_name IN VARCHAR2 DEFAULT NULL, p_system_type IN VARCHAR2 DEFAULT MGMT_GLOBAL.G_GENERIC_SYSTEM_TYPE, p_delete_group IN NUMBER DEFAULT 1 ) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_timezone_region mgmt_targets.timezone_region%type; l_system_name mgmt_targets.target_name%type; l_group_target_guid RAW(16); l_system_target_guid RAW(16); l_member_targets SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); l_targets_to_add SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); l_targets_to_remove SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); l_group_custom_columns MGMT_GRP_COLUMN_ARRAY := MGMT_GRP_COLUMN_ARRAY(); l_group_chart_cur EM_GROUPS_UI.cursorType; l_chart_guid mgmt_group_chart.comp_chart_guid%type; l_chart_type mgmt_group_chart.chart_type%type; l_display_order mgmt_group_chart.display_order%type; l_target_count mgmt_group_chart.targets_count%type; l_target_type mgmt_targets.target_type%type; l_metric_guid mgmt_targets.target_guid%type; l_metric_name mgmt_metrics.metric_name%type; l_metric_column mgmt_metrics.metric_column%type; l_column_label_nlsid mgmt_metrics.column_label_nlsid%type; l_column_label mgmt_metrics.column_label%type; l_group_chart_list MGMT_GRP_CHART_ARRAY := MGMT_GRP_CHART_ARRAY(); l_chart_seltargets SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); l_min_column mgmt_group_summary_chart_def.min_column%type := null; l_max_column mgmt_group_summary_chart_def.max_column%type := null; l_avg_column mgmt_group_summary_chart_def.avg_column%type := null; l_sum_column mgmt_group_summary_chart_def.sum_column%type := null; l_stdev_column mgmt_group_summary_chart_def.stdev_column%type := null; l_property_list MGMT_TARGET_PROPERTY_LIST := MGMT_TARGET_PROPERTY_LIST(); BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('create_system_from_group:Entry',MODULE_NAME); END IF; -- The user issuing this call must have FULL privilege on the group IF MGMT_USER.has_priv(l_current_user, MGMT_USER.FULL_TARGET, p_group_name, p_group_type) = 0 THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'The current user does not have sufficient privileges to perform this action'); END IF; BEGIN SELECT timezone_region, target_guid INTO l_timezone_region, l_group_target_guid FROM mgmt_targets WHERE target_name = p_group_name AND target_type = p_group_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_GROUP_ERR, MGMT_GLOBAL.INVALID_GROUP_ERR_M); END; IF p_system_name is NULL THEN l_system_name := p_group_name ; ELSE l_system_name := p_system_name ; END IF; -- Start : Create system -- Select all member target and add them as system member SELECT SMP_EMD_NVPAIR(member_target_name, member_target_type) BULK COLLECT INTO l_member_targets FROM mgmt_target_memberships WHERE composite_target_guid = l_group_target_guid ; EM_SYSTEM.create_system(p_system_name => l_system_name, p_system_type => p_system_type, p_members => l_member_targets, p_timezone_region => l_timezone_region ); l_system_target_guid := MGMT_TARGET.get_target_guid(l_system_name, p_system_type); -- End : Create system -- Start : Change membership with Parent targets l_targets_to_add.extend; l_targets_to_add(l_targets_to_add.LAST) := new SMP_EMD_NVPAIR(l_system_name,p_system_type); l_targets_to_remove.extend; l_targets_to_remove(l_targets_to_remove.LAST) := new SMP_EMD_NVPAIR(p_group_name, p_group_type); FOR i in (SELECT composite_target_name, composite_target_type, composite_target_guid FROM mgmt_target_memberships WHERE member_target_guid = l_group_target_guid ) LOOP EM_ASSOC.create_target_assocs( p_assoc_def_name => MGMT_ASSOC.ASSOC_DEF_CONTAINS, p_source_target_name => i.composite_target_name, p_source_target_type => i.composite_target_type, p_assoc_targets_list => l_targets_to_add, p_suppress_error => TRUE ); IF p_delete_group = 1 THEN EM_ASSOC.delete_target_assocs( p_assoc_def_name => MGMT_ASSOC.ASSOC_DEF_CONTAINS, p_source_target_name => i.composite_target_name, p_source_target_type => i.composite_target_type, p_assoc_targets_list => l_targets_to_remove, p_suppress_error => TRUE ); END IF; END LOOP ; -- End : Change membership with Parent targets -- Start : Select registered chart for given group and register for System -- Get all registered chart for given group GUID EM_GROUPS_UI.get_group_charts_list(target_name_in => p_group_name, target_type_in => p_group_type, charts_list_out => l_group_chart_cur); LOOP FETCH l_group_chart_cur into l_chart_guid, l_chart_type, l_display_order, l_target_count, l_target_type, l_metric_guid, l_metric_name, l_metric_column, l_column_label_nlsid,l_column_label ; EXIT WHEN l_group_chart_cur%NOTFOUND; l_chart_seltargets := NULL; l_min_column := NULL; l_max_column := NULL; l_avg_column := NULL; l_sum_column := NULL; l_stdev_column := NULL; IF l_chart_type = MGMT_GROUP.CHART_SELECTED_TARGETS THEN SELECT SMP_EMD_NVPAIR(comp_chart_guid, target_guid) BULK COLLECT INTO l_chart_seltargets FROM mgmt_group_chart_seltargets WHERE comp_chart_guid = l_chart_guid ; ELSIF l_chart_type = MGMT_GROUP.CHART_SUMMARY_METRIC THEN SELECT min_column, max_column, avg_column, sum_column, stdev_column INTO l_min_column, l_max_column, l_avg_column, l_sum_column, l_stdev_column FROM mgmt_group_summary_chart_def WHERE comp_chart_guid = l_chart_guid ; END IF; l_group_chart_list.extend; l_group_chart_list(l_group_chart_list.last) := new MGMT_GRP_CHART(l_target_type, l_metric_name, l_metric_column, l_chart_type, 1, 3, l_min_column, l_max_column, l_avg_column, l_sum_column, l_stdev_column, l_chart_seltargets ); END LOOP; -- Register charts for system EM_GROUP.create_charts(p_target_name => l_system_name, p_target_type => p_system_type, p_charts_list => l_group_chart_list ); -- End : Select registered chart for given group and register for System -- Start : Register custom columns SELECT MGMT_GRP_COLUMN (target_type,NULL, NULL, member_metric_guid, column_type, display_order, property_name, abbreviation) BULK COLLECT INTO l_group_custom_columns FROM mgmt_group_custom_columns WHERE composite_target_guid = l_group_target_guid ; EM_GROUP.create_group_custom_columns(p_target_name => l_system_name, p_target_type => p_system_type, p_column_list => l_group_custom_columns); -- End : Register custom columns -- Start : Register target properties -- Select all properties which are common SELECT MGMT_TARGET_PROPERTY(property_name, property_type, property_value) BULK COLLECT INTO l_property_list FROM mgmt_target_properties WHERE target_guid = l_group_target_guid AND EXISTS (SELECT 1 FROM mgmt_target_prop_defs def WHERE def.target_type = p_system_type AND def.property_name = property_name ) ; IF (l_property_list IS NOT NULL AND l_property_list.count > 0 ) THEN MGMT_TARGET.set_target_properties(p_target_name => l_system_name, p_target_type => p_system_type, p_properties => l_property_list); END IF; -- End : Register target properties -- Start : Update Job and Blackout IF p_delete_group = 1 then UPDATE mgmt_job_target SET target_guid = l_system_target_guid WHERE target_guid = l_group_target_guid ; UPDATE mgmt_job_ext_targets SET target_guid = l_system_target_guid WHERE target_guid = l_group_target_guid ; UPDATE mgmt_job_step_targets SET target_guid = l_system_target_guid WHERE target_guid = l_group_target_guid ; -- modify the blackouts for system UPDATE mgmt_blackout_target_details SET target_guid = l_system_target_guid WHERE target_guid = l_group_target_guid ; UPDATE mgmt_blackout_flat_targets SET target_guid = l_system_target_guid WHERE target_guid = l_group_target_guid ; END IF; -- End : Update Job and Blackout -- Start : Deleteing target IF p_delete_group = 1 THEN MGMT_ADMIN.delete_target_sync(p_group_name, p_group_type); END IF; -- End : Deleteing target IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('create_system_from_group:Exit',MODULE_NAME); END IF; END create_system_from_group; PROCEDURE create_system_from_group ( p_group_name IN VARCHAR2, p_group_type IN VARCHAR2, p_system_name IN VARCHAR2 DEFAULT NULL, p_system_type IN VARCHAR2 DEFAULT MGMT_GLOBAL.G_GENERIC_SYSTEM_TYPE ) IS BEGIN create_system_from_group(p_group_name => p_group_name, p_group_type => p_group_type, p_system_name => p_system_name, p_system_type => p_system_type, p_delete_group => 0 ); END create_system_from_group; PROCEDURE migrate_group_to_system ( p_group_name IN VARCHAR2, p_group_type IN VARCHAR2, p_system_name IN VARCHAR2 DEFAULT NULL, p_system_type IN VARCHAR2 DEFAULT MGMT_GLOBAL.G_GENERIC_SYSTEM_TYPE ) IS BEGIN create_system_from_group(p_group_name => p_group_name, p_group_type => p_group_type, p_system_name => p_system_name, p_system_type => p_system_type, p_delete_group => 1 ); END migrate_group_to_system; -- API to create a group PROCEDURE create_group ( p_group_name IN mgmt_targets.target_name%TYPE, p_group_type IN mgmt_targets.target_type%TYPE, p_member_targets IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL, p_group_owner IN mgmt_targets.owner%TYPE DEFAULT NULL, p_timezone_rgn IN mgmt_targets.timezone_region%TYPE DEFAULT NULL ) IS l_target_guid mgmt_targets.target_guid%TYPE; l_type_display_name mgmt_targets.type_display_name%TYPE; l_member_targets_count NUMBER; BEGIN IF (emdw_log.p_is_info_set)THEN IF p_member_targets IS NULL THEN l_member_targets_count := 0; ELSE l_member_targets_count := p_member_targets.COUNT; END IF; emdw_log.info('create_group:Entry p_group_name ='||p_group_name || ' p_group_type ='||p_group_type || ' p_member_targets ='|| l_member_targets_count || ' p_tz_rgn = ' || p_timezone_rgn || ' p_group_owner =' || p_group_owner || ' ', MODULE_NAME); END IF; -- Ensure that the specified target type is a group check_group(p_group_type); -- Get the display name for this group type.. BEGIN SELECT property_value INTO l_type_display_name FROM mgmt_type_properties WHERE target_type = p_group_type AND property_name = 'typeDisplayName'; EXCEPTION WHEN NO_DATA_FOUND THEN -- default to the internal type.. l_type_display_name := p_group_type; END; EM_TARGET.add_aggregate_target(p_target_guid => l_target_guid, p_target_name => p_group_name, p_target_type => p_group_type, p_member_targets => p_member_targets, p_target_owner => p_group_owner, p_tz_rgn => p_timezone_rgn, p_type_display_name => l_type_display_name, p_required_member_priv => MGMT_USER.VIEW_TARGET, p_is_group => 1 ); IF (emdw_log.p_is_info_set)THEN emdw_log.info('create_group:Exit',MODULE_NAME); END IF; END create_group; -- API to modify a group PROCEDURE modify_group ( p_group_name IN mgmt_targets.target_name%TYPE, p_group_type IN mgmt_targets.target_type%TYPE, p_targets_to_add IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL, p_targets_to_remove IN SMP_EMD_NVPAIR_ARRAY DEFAULT NULL, p_group_owner IN mgmt_targets.owner%TYPE DEFAULT NULL) IS l_group_guid mgmt_targets.target_guid%TYPE; l_targets_to_add_count NUMBER; l_targets_to_del_count NUMBER; BEGIN IF (emdw_log.p_is_info_set)THEN IF p_targets_to_add IS NULL THEN l_targets_to_add_count := 0; ELSE l_targets_to_add_count := p_targets_to_add.COUNT; END IF; IF p_targets_to_remove IS NULL THEN l_targets_to_del_count := 0; ELSE l_targets_to_del_count := p_targets_to_remove.COUNT; END IF; emdw_log.info('modify_group:Entry p_group_name ='||p_group_name || ' p_group_type ='||p_group_type || ' p_targets_to_add ='|| l_targets_to_add_count || ' p_targets_to_remove ='||l_targets_to_del_count || ' p_group_owner =' || p_group_owner || ' ', MODULE_NAME); END IF; -- Ensure that the specified target type is a group check_group(p_group_type); l_group_guid := MGMT_TARGET.get_target_guid(p_group_name,p_group_type); EM_TARGET.modify_aggregate_target(p_target_guid => l_group_guid, p_target_name => p_group_name, p_target_type => p_group_type, p_member_targets_to_add => p_targets_to_add, p_member_targets_to_remove => p_targets_to_remove, p_target_owner => p_group_owner, p_required_target_priv => MGMT_USER.OPERATOR_TARGET, p_required_member_priv => MGMT_USER.VIEW_TARGET ); EXCEPTION WHEN MGMT_GLOBAL.target_does_not_exist THEN raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR, 'The specified group ' || p_group_name || ' does not exist'); END modify_group; -- -- PROCEDURE: upsert_group -- PURPOSE -- this procedure will be used by emctl to create/update the group. If the group with the -- given name and type is not present then it will create a new group otherwise it will update -- the existing group. PROCEDURE upsert_group(p_upsert IN NUMBER, p_group_name IN VARCHAR2, p_group_type IN VARCHAR2, p_member_targets IN SMP_EMD_NVPAIR_ARRAY, p_timezone_region IN VARCHAR2) AS l_upsert NUMBER := p_upsert; l_members_del SMP_EMD_NVPAIR_ARRAY; l_new_members SMP_EMD_NVPAIR_ARRAY := p_member_targets; BEGIN IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('UPSERT_GROUP:Entry',MODULE_NAME); END IF; BEGIN MGMT_GROUP.create_group(p_group_name => p_group_name, p_group_type => p_group_type, p_member_targets => p_member_targets, p_timezone_rgn => p_timezone_region ); EXCEPTION WHEN MGMT_GLOBAL.TARGET_ALREADY_EXISTS THEN IF(l_upsert = 1) THEN MGMT_GROUP.modify_group(p_group_name => p_group_name, p_group_type => p_group_type, p_targets_to_add => p_member_targets, p_targets_to_remove => null ); ELSE RAISE; END IF; END; IF (EMDW_LOG.p_is_info_set) THEN EMDW_LOG.info('UPSERT_GROUP:Exit',MODULE_NAME); END IF; END upsert_group; ------------------------------------Internal Procedures/Functions ------------------------------- -- Check that the target type is a group: throw an exception -- if not. Internal method PROCEDURE check_group ( p_target_type IN mgmt_targets.target_type%TYPE ) IS l_is_group NUMBER := 0; BEGIN l_is_group := MGMT_TARGET.get_type_property(p_target_type, MGMT_GLOBAL.G_IS_GROUP_PROP, '0'); IF l_is_group=0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'The specified target is not a group, type = ' || p_target_type); END IF; END; END em_group; / show error;