Rem Rem $Header: website_mntr_pkgbody.sql 21-nov-2006.17:42:38 yxie Exp $ Rem Rem website_mntr_pkgbody.sql Rem Rem Copyright (c) 2002, 2006, Oracle. All rights reserved. Rem Rem NAME Rem website_mntr_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem yxie 11/21/06 - XbranchMerge yxie_bug5527422 from main Rem yxie 09/14/06 - XbranchMerge yxie_bug-5500487 from main Rem jureyes 08/10/06 - Backport jureyes_bug5186918 from main Rem yxie 07/12/06 - XbranchMerge yxie_forms-monitoring from main Rem yxie 08/23/06 - remove duplicate method Rem jureyes 07/31/06 - Fix bug 5186918 (enable oc4j configuration for Rem 10.1.3.1) Rem yxie 08/18/05 - fix bug 4561756 Rem jureyes 07/12/05 - Change "depends_on" to "supports_e2e_on" Rem yxie 07/07/05 - Fix version number for oracle_apache Rem yxie 06/06/05 - changes depends_on to supports_eum_on Rem yxie 04/22/05 - Fix bug 3336996 Rem yxie 04/15/05 - changing contains to depends_on Rem yxie 04/07/05 - add support for generic apache Rem rmarripa 03/11/05 - remove obsolete tables Rem yxie 01/28/05 - fix license bug 4126691 Rem ramalhot 01/17/05 - assoc def name changed from member to contains Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem yxie 12/14/04 - fix invalid number on comparing apache version Rem yxie 11/01/04 - getWebappConfigData: add apache and webcache under ias control case Rem eporter 10/19/04 - getWebappConfigData: change to oracle_webserver Rem yxie 10/08/04 - grabtrans 'yxie_difs_solve' Rem ramalhot 10/05/04 - g_contains_guid removed from dynamic sql code Rem ramalhot 08/25/04 - cutover to new assoc tables Rem snakai 02/09/04 - fix null comparisons Rem snakai 11/11/03 - add support for metric versioning Rem asawant 11/05/03 - Fixing configuration query Rem rpatti 09/25/03 - removing obsolete code Rem asawant 07/08/03 - Adding code for config webapp OC4J and Webcache Rem asawant 05/19/03 - Adding code to return OC4J aggregate detail data Rem jriel 04/24/03 - move general metrics Rem jriel 04/18/03 - split webapp homepage procedures Rem asawant 03/27/03 - cut over EMD_BCNTXN.EMD_BCN_SYNC_LIST Rem asawant 07/11/02 - Adding filter Rem rmarripa 07/08/02 - handel no avail txn error Rem rmarripa 07/03/02 - add procedure to get website homepage data Rem rpinnama 05/16/02 - Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem CREATE OR REPLACE PACKAGE body emd_mntr_website AS -- getWebsiteHomepageData -- -- Get the website homepage data. This includes the data for all the -- components. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- page_type: VARCHAR2 page type -- metric_name: VARCHAR2 Metric name for chronos data -- obj_type: VARCHAR2 Object type for chronos data -- -- OUT Parameters: -- tgt_mntr_sum_data CURSORTYPE A cursor which returns rows containing: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- avail_pct: NUMBER The availability percent over the timeperiod -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- memtgt_mntr_sum_data CURSORTYPE A cursor containing same above mentione -- data. -- avail_txn_perf_data p_cursor_type Cursor containing the current metric -- values for availability txn from availbility beacons and -- local beacon. -- beacon_name: VARCHAR2 beacon Name -- Composite_key: VARCHAR2 Composite key for the beacon and availability txn -- Collection_timestamp: VARCHAR2 Collection timestamp -- is_bcn : Char(1) 0 - Target; 1 - beacon -- met_col : VARCHAR2 Metric column name -- val : VARCHAR2 Metric Value -- str_val : VARCHAR2 Metric Value description. This contains the error -- description. -- is_old : CHAR(1) - 'Y' - if the data is old; otherwise 'N' - This column -- is not being used. This can be removed. -- avail_txn_sev_data p_cursor_type - CURSOR containing the alerts for availability txn -- beacon_name: VARCHAR2 beacon Name -- is_bcn : Char(1) 0 - Target; 1 - beacon -- sev_code: INTEGER -- -- avail_txn_chart_data: p_cursor_type - Cursor containing the chart data for -- avg_response_time metric for availability txn from all the -- beacons including LOCAL for last 24 hours. -- collection_timestamp: VARCHAR2 Collection timestamp -- bcn_name: VARCHAR2 beacon name -- val: VARCHAR2 metric value -- watchlist_data: MGMT_MNTR_USER_STATS_ARRAY Array of MGMT_MNTR_USER_STATS_OBJ -- url_perf_rows_data: cursorType -- Top 5 slowest URLs -- query_name: VARCHAR2 Url name -- display_name: VARCHAR2 display name of the Url -- visit_link: VARCHAR2 - Visit the page Url -- hits: NUMBER number of hits -- avg_resp_time: NUMBER Average response time -- min_resp_time: NUMBER Minimum response time -- max_resp_time: NUMBER Maximum response time -- filter_min_hits : the min hits filter -- bcn_list: MGMT_BCN_SYNC_ARRAY Array of MGMT_BCN_SYNC objects -- bcn_sev_data p_cursor_type - CURSOR containing the alerts for associate beacons -- beacon_name: VARCHAR2 beacon Name -- is_bcn : Char(1) 0 - Target; 1 - beacon -- sev_code: INTEGER -- avail_txn_guid VARCHAR2 Availability transaction guid -- txn_name VARCHAR2 Availability Transaction Name -- tgt_tz NUMBER Target Timezone -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebsiteHomepageData( targetName IN VARCHAR2, targetType IN VARCHAR2, page_type IN VARCHAR2, metric_name IN VARCHAR2, obj_type IN VARCHAR2, tgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, memtgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, avail_txn_perf_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_sev_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_chart_data OUT EMD_BCNTXN.P_CURSOR_TYPE, watchlist_data OUT MGMT_MNTR_USER_STATS_ARRAY, url_perf_rows_data OUT EMD_MNTR_USER.CURSORTYPE, filter_min_hits OUT NUMBER, bcn_list OUT MGMT_BCN_SYNC_ARRAY, bcn_sev_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_guid OUT VARCHAR2, avail_txn_name OUT VARCHAR2, tgt_tz OUT NUMBER, homepage_url_prop OUT VARCHAR2, result OUT NUMBER, err_desc OUT VARCHAR2 ) IS v_valid_time NUMBER := 60 * 60 * 1000; v_show_chronos NUMBER(1) ; v_tgt_id VARCHAR2(32); v_page_type VARCHAR2(32); v_metric_name VARCHAR2(32); v_obj_type VARCHAR2(32); v_err_code NUMBER; BEGIN IF ( ( targetName is null ) OR ( targetType is null ) ) THEN result := EMD_BCNTXN.p_bcn_err_badparams; END IF; v_page_type := page_type; v_metric_name := metric_name; v_obj_type := obj_type; IF ( v_page_type is null ) THEN v_page_type := 'byDay'; END IF; IF ( v_metric_name is null ) THEN v_metric_name := 'latency'; END IF; IF ( v_obj_type is null ) THEN v_obj_type := 'byUrl'; END IF; result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( targetName, targetType, EMD_BCNTXN.p_view_mntr_reports, err_desc ); IF result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; BEGIN SELECT RAWTOHEX(target_guid) INTO v_tgt_id FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType; EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_bcn_err_tgtnotfound; RETURN; END; -- Get the target monitoring summary cursor MGMT_TARGET.GET_TARGET_MONITORING_SUMMARY ( targetName, targetType, null, MGMT_GLOBAL.G_DAY, tgt_mntr_sum_data ); -- Get the target monitoring summary cursor for all -- member targets MGMT_TARGET.GET_MEMBER_MONITORING_SUMMARY ( targetName, targetType, null, MGMT_GLOBAL.G_DAY, memtgt_mntr_sum_data ); EMD_BCNTXN.EMD_BCN_GET_WEBSITE_HP_DATA ( targetName, targetType, v_valid_time, avail_txn_guid, avail_txn_name, tgt_tz, avail_txn_perf_data, avail_txn_sev_data, avail_txn_chart_data, result, err_desc ); v_err_code := result; EMD_MNTR_USER.GETWATCHlISTSTATS ( targetName, targetType, v_page_type, v_metric_name, v_show_chronos, watchlist_data ); EMD_MNTR_USER.GETROWSBYOBJANDSTAT ( targetName, targetType, v_page_type, v_metric_name, v_obj_type, 'showPerf', null, 0, filter_min_hits, url_perf_rows_data ); EMD_BCNTXN.EMD_BCN_SYNC_SEV_LIST ( v_tgt_id, bcn_list, bcn_sev_data, result, err_desc ); BEGIN SELECT property_value INTO homepage_url_prop FROM mgmt_target_properties WHERE property_name = 'homepageUrl' AND target_guid = ( SELECT target_guid FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType); EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_err_homepage_prop_notfound; RETURN; END; -- The best approach would be to return all the error -- codes and display them on the UI. IF ( v_err_code <> EMD_BCNTXN.p_bcn_success ) THEN result := v_err_code; END IF; RETURN; EXCEPTION WHEN OTHERS THEN result := EMD_BCNTXN.p_bcn_err_oraerr; err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebsiteHomepageData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType ); IF tgt_mntr_sum_data%ISOPEN THEN CLOSE tgt_mntr_sum_data; END IF; IF memtgt_mntr_sum_data%ISOPEN THEN CLOSE memtgt_mntr_sum_data; END IF; IF avail_txn_perf_data%ISOPEN THEN CLOSE avail_txn_perf_data; END IF; IF avail_txn_sev_data%ISOPEN THEN CLOSE avail_txn_sev_data; END IF; IF avail_txn_chart_data%ISOPEN THEN CLOSE avail_txn_chart_data; END IF; IF url_perf_rows_data%ISOPEN THEN CLOSE url_perf_rows_data; END IF; IF bcn_sev_data%ISOPEN THEN CLOSE bcn_sev_data; END IF; END getWebsiteHomepageData; -- getWebappGeneralMetricData -- -- Get the general metrics used on the webapp home page -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- -- OUT Parameters: -- current_status_time : DATE - the timestamp of the last target status change -- homepage_url : VARCHAR2 - URL of the homepage for this webapp -- components_down : NUMBER - count of components down -- beacons_down : NUMBER - count of beacons down -- beacons_out_of_sync : NUMBER - count of beacons out of sync -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappGeneralMetricData( targetName IN VARCHAR2, targetType IN VARCHAR2, current_status_time OUT DATE, homepage_url_prop OUT VARCHAR2, components_down OUT NUMBER, beacons_down OUT NUMBER, beacons_out_of_sync OUT NUMBER, result OUT NUMBER, err_desc OUT VARCHAR2 ) IS v_tgt_id VARCHAR2(32); v_err_code NUMBER; BEGIN IF ( ( targetName is null ) OR ( targetType is null ) ) THEN result := EMD_BCNTXN.p_bcn_err_badparams; END IF; result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( targetName, targetType, EMD_BCNTXN.p_view_mntr_reports, err_desc ); IF result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; BEGIN SELECT RAWTOHEX(target_guid) INTO v_tgt_id FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType; EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_bcn_err_tgtnotfound; RETURN; END; BEGIN SELECT property_value INTO homepage_url_prop FROM mgmt_target_properties WHERE property_name = 'homepageUrl' AND target_guid = v_tgt_id; EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_err_homepage_prop_notfound; RETURN; END; BEGIN SELECT start_collection_timestamp INTO current_status_time FROM mgmt_current_availability WHERE target_guid = v_tgt_id; EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_bcn_err_tgtnotfound; RETURN; END; BEGIN SELECT count(assoc_target_guid) INTO components_down FROM mgmt_target_assocs mem, mgmt_current_availability avl WHERE mem.source_target_guid = v_tgt_id AND avl.target_guid = mem.assoc_target_guid AND mem.assoc_guid = MGMT_ASSOC.g_depends_on_guid AND avl.current_status = 0; EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_bcn_err_tgtnotfound; RETURN; END; BEGIN SELECT count(b.beacon_target_guid) INTO beacons_down FROM mgmt_targets t, mgmt_bcn_target b, mgmt_current_availability a WHERE t.target_guid = v_tgt_id AND b.target_guid = t.target_guid AND ( (b.beacon_target_guid <> t.target_guid AND a.target_guid = b.beacon_target_guid) OR (b.beacon_target_guid = t.target_guid AND a.target_guid = (SELECT target_guid FROM mgmt_targets WHERE emd_url = t.emd_url AND target_type = 'oracle_emd') ) ) AND a.current_status = 0; EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_bcn_err_bcnnotfound; RETURN; END; --In 10gR2 there is no state maintained in the repository to compute the -- beacon out of sync. beacons_out_of_sync := 0; /* BEGIN SELECT count(distinct b.beacon_target_guid) INTO beacons_out_of_sync FROM mgmt_targets t, mgmt_bcn_target b, mgmt_bcn_txn_defn td, mgmt_bcn_target_txn bt WHERE t.target_guid = v_tgt_id AND b.target_guid = t.target_guid AND td.target_guid = t.target_guid AND bt.target_guid = t.target_guid AND bt.beacon_target_guid = b.beacon_target_guid AND bt.txn_guid = td.txn_guid AND ( (b.is_removing = 'Y' AND bt.state = 'M') OR (b.is_removing = 'N' AND ( (td.state = 'M' AND (bt.state <> 'M' OR bt.version <> td.version OR bt.req_update > 0)) OR (td.state <> 'M' AND bt.state = 'M') ) ) ); EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_bcn_err_bcnnotfound; RETURN; END; */ RETURN; EXCEPTION WHEN OTHERS THEN result := EMD_BCNTXN.p_bcn_err_oraerr; err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappGeneralMetricData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType ); END getWebappGeneralMetricData; -- getWebappComponentsData -- -- Get the webapp components data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- -- OUT Parameters: -- tgt_mntr_sum_data CURSORTYPE A cursor which returns rows containing: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- display_name: VARCHAR2 The display name of the target -- type_display_name: VARCHAR2 The display name of the target type -- avail_pct: NUMBER The availability percent over the timeperiod -- current_status: NUMBER The current availability status - check -- mgmt current availability table for definition of values. -- warning_count: NUMBER The number of currently open warning alerts -- critical_count: NUMBER The number of currently open critical alerts -- memtgt_mntr_sum_data CURSORTYPE A cursor containing same above mentione -- data. -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappComponentsData( targetName IN VARCHAR2, targetType IN VARCHAR2, tgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, memtgt_mntr_sum_data OUT MGMT_TARGET.CURSOR_TYPE, result OUT NUMBER, err_desc OUT VARCHAR2 ) IS v_err_code NUMBER; BEGIN IF ( ( targetName is null ) OR ( targetType is null ) ) THEN result := EMD_BCNTXN.p_bcn_err_badparams; END IF; result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( targetName, targetType, EMD_BCNTXN.p_view_mntr_reports, err_desc ); IF result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; -- Get the target monitoring summary cursor MGMT_TARGET.GET_TARGET_MONITORING_SUMMARY ( targetName, targetType, null, MGMT_GLOBAL.G_DAY, tgt_mntr_sum_data ); -- Get the target monitoring summary cursor for all member targets MGMT_TARGET.GET_MEMBER_MONITORING_SUMMARY ( targetName, targetType, null, MGMT_GLOBAL.G_DAY, memtgt_mntr_sum_data ); RETURN; EXCEPTION WHEN OTHERS THEN result := EMD_BCNTXN.p_bcn_err_oraerr; err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappComponentsData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType ); IF tgt_mntr_sum_data%ISOPEN THEN CLOSE tgt_mntr_sum_data; END IF; IF memtgt_mntr_sum_data%ISOPEN THEN CLOSE memtgt_mntr_sum_data; END IF; END getWebappComponentsData; -- getWebappAvailTransactionData -- -- Get the webapp availability transaction data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- -- OUT Parameters: -- avail_txn_perf_data p_cursor_type Cursor containing the current metric -- values for availability txn from availbility beacons and -- local beacon. -- beacon_name: VARCHAR2 beacon Name -- Composite_key: VARCHAR2 Composite key for the beacon and availability txn -- Collection_timestamp: VARCHAR2 Collection timestamp -- is_bcn : Char(1) 0 - Target; 1 - beacon -- met_col : VARCHAR2 Metric column name -- val : VARCHAR2 Metric Value -- str_val : VARCHAR2 Metric Value description. This contains the error -- description. -- is_old : CHAR(1) - 'Y' - if the data is old; otherwise 'N' - This column -- is not being used. This can be removed. -- avail_txn_sev_data p_cursor_type - CURSOR containing the alerts for availability txn -- beacon_name: VARCHAR2 beacon Name -- is_bcn : Char(1) 0 - Target; 1 - beacon -- sev_code: INTEGER -- -- avail_txn_chart_data: p_cursor_type - Cursor containing the chart data for -- avg_response_time metric for availability txn from all the -- beacons including LOCAL for last 24 hours. -- collection_timestamp: VARCHAR2 Collection timestamp -- bcn_name: VARCHAR2 beacon name -- val: VARCHAR2 metric value -- avail_txn_guid VARCHAR2 Availability transaction guid -- txn_name VARCHAR2 Availability Transaction Name -- tgt_tz NUMBER Target Timezone -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappAvailTransactionData( targetName IN VARCHAR2, targetType IN VARCHAR2, avail_txn_perf_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_sev_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_chart_data OUT EMD_BCNTXN.P_CURSOR_TYPE, avail_txn_guid OUT VARCHAR2, avail_txn_name OUT VARCHAR2, tgt_tz OUT NUMBER, result OUT NUMBER, err_desc OUT VARCHAR2 ) IS v_valid_time NUMBER := 60 * 60 * 1000; v_err_code NUMBER; BEGIN IF ( ( targetName is null ) OR ( targetType is null ) ) THEN result := EMD_BCNTXN.p_bcn_err_badparams; END IF; result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( targetName, targetType, EMD_BCNTXN.p_view_mntr_reports, err_desc ); IF result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; EMD_BCNTXN.EMD_BCN_GET_WEBSITE_HP_DATA ( targetName, targetType, v_valid_time, avail_txn_guid, avail_txn_name, tgt_tz, avail_txn_perf_data, avail_txn_sev_data, avail_txn_chart_data, result, err_desc ); v_err_code := result; -- The best approach would be to return all the error -- codes and display them on the UI. IF ( v_err_code <> EMD_BCNTXN.p_bcn_success ) THEN result := v_err_code; END IF; RETURN; EXCEPTION WHEN OTHERS THEN result := EMD_BCNTXN.p_bcn_err_oraerr; err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappAvailTransactionData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType ); IF avail_txn_perf_data%ISOPEN THEN CLOSE avail_txn_perf_data; END IF; IF avail_txn_sev_data%ISOPEN THEN CLOSE avail_txn_sev_data; END IF; IF avail_txn_chart_data%ISOPEN THEN CLOSE avail_txn_chart_data; END IF; END getWebappAvailTransactionData; -- getWebappWatchListData -- -- Get the webapp watchlist data. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- page_type: VARCHAR2 page type -- metric_name: VARCHAR2 Metric name for chronos data -- -- OUT Parameters: -- watchlist_data: MGMT_MNTR_USER_STATS_ARRAY Array of MGMT_MNTR_USER_STATS_OBJ -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappWatchListData( targetName IN VARCHAR2, targetType IN VARCHAR2, page_type IN VARCHAR2, metric_name IN VARCHAR2, watchlist_data OUT MGMT_MNTR_USER_STATS_ARRAY, homepage_url_prop OUT VARCHAR2, result OUT NUMBER, err_desc OUT VARCHAR2 ) IS v_valid_time NUMBER := 60 * 60 * 1000; v_show_chronos NUMBER(1) ; v_page_type VARCHAR2(32); v_metric_name VARCHAR2(32); v_err_code NUMBER; BEGIN IF ( ( targetName is null ) OR ( targetType is null ) ) THEN result := EMD_BCNTXN.p_bcn_err_badparams; END IF; v_page_type := page_type; v_metric_name := metric_name; IF ( v_page_type is null ) THEN v_page_type := 'byDay'; END IF; IF ( v_metric_name is null ) THEN v_metric_name := 'latency'; END IF; result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( targetName, targetType, EMD_BCNTXN.p_view_mntr_reports, err_desc ); IF result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; EMD_MNTR_USER.GETWATCHlISTSTATS ( targetName, targetType, v_page_type, v_metric_name, v_show_chronos, watchlist_data ); -- When target is web application IF (targetType <> MGMT_GLOBAL.G_FORMSAPP_TARGET_TYPE) THEN BEGIN SELECT property_value INTO homepage_url_prop FROM mgmt_target_properties WHERE property_name = 'homepageUrl' AND target_guid = ( SELECT target_guid FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType); EXCEPTION WHEN NO_DATA_FOUND THEN result := EMD_BCNTXN.p_err_homepage_prop_notfound; RETURN; END; -- when target is forms application ELSE BEGIN SELECT property_value INTO homepage_url_prop FROM mgmt_target_properties WHERE property_name = 'homepageUrl' AND target_guid = ( SELECT target_guid FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType); IF (homepage_url_prop is null) THEN homepage_url_prop := '/'; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN homepage_url_prop := '/'; END; END IF; RETURN; EXCEPTION WHEN OTHERS THEN result := EMD_BCNTXN.p_bcn_err_oraerr; err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappWatchListData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType ); END getWebappWatchListData; -- getWebappEndUserPerfData -- -- Get the website homepage data. This includes the data for all the -- components. -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- page_type: VARCHAR2 page type -- metric_name: VARCHAR2 Metric name for chronos data -- obj_type: VARCHAR2 Object type for chronos data -- -- OUT Parameters: -- url_perf_rows_data: cursorType -- Top 5 slowest URLs -- query_name: VARCHAR2 Url name -- display_name: VARCHAR2 display name of the Url -- visit_link: VARCHAR2 - Visit the page Url -- hits: NUMBER number of hits -- avg_resp_time: NUMBER Average response time -- min_resp_time: NUMBER Minimum response time -- max_resp_time: NUMBER Maximum response time -- filter_min_hits : the min hits filter -- result INTEGER - Error Code -- err_desc VARCHAR2 - Error Description PROCEDURE getWebappEndUserPerfData( targetName IN VARCHAR2, targetType IN VARCHAR2, page_type IN VARCHAR2, metric_name IN VARCHAR2, obj_type IN VARCHAR2, url_perf_rows_data OUT EMD_MNTR_USER.CURSORTYPE, filter_min_hits OUT NUMBER, result OUT NUMBER, err_desc OUT VARCHAR2 ) IS v_page_type VARCHAR2(32); v_metric_name VARCHAR2(32); v_obj_type VARCHAR2(32); v_err_code NUMBER; BEGIN IF ( ( targetName is null ) OR ( targetType is null ) ) THEN result := EMD_BCNTXN.p_bcn_err_badparams; END IF; v_page_type := page_type; v_metric_name := metric_name; v_obj_type := obj_type; IF ( v_page_type is null ) THEN v_page_type := 'byDay'; END IF; IF ( v_metric_name is null ) THEN v_metric_name := 'latency'; END IF; IF ( v_obj_type is null ) THEN v_obj_type := 'byUrl'; END IF; result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( targetName, targetType, EMD_BCNTXN.p_view_mntr_reports, err_desc ); IF result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; EMD_MNTR_USER.GETROWSBYOBJANDSTAT ( targetName, targetType, v_page_type, v_metric_name, v_obj_type, 'showPerf', null, 0, filter_min_hits, url_perf_rows_data ); RETURN; EXCEPTION WHEN OTHERS THEN result := EMD_BCNTXN.p_bcn_err_oraerr; err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappEndUserPerfData: db error: ' || 'target_name = '|| targetName || '; target_type = ' ||targetType ); IF url_perf_rows_data%ISOPEN THEN CLOSE url_perf_rows_data; END IF; END getWebappEndUserPerfData; -- getWebappOc4jTopData -- -- Get OC4J top data from the oc4j metrics. Here are the metric to display name -- mappings (UI column = metric name | metric column): -- Active Requests = servlet_rollup | service.time -- Avg Client Proc Time = servlet_delta_rollup | -- (service.deltaTime / service.deltaCompleted) -- Requests per Second = servlet_delta_rollup | -- (service.deltaCompleted / service.deltaTime) -- OBS: this metric is INCORRECTLY pulled out of the service.throughput -- column of the servlet_rollup metric in the IAS pages (K.Michaels) -- Requests processed = servlet_delta_rollup | service.deltaCompleted -- Total Client Processing Time = servlet_delta_rollup | service.deltaTime -- -- Sample of query generated in code below: -- SELECT t.member_target_name, ck.key_part1_value, ck.key_part2_value, -- ck.key_part3_value, m.metric_column, -- SUM(mt.value_average * mt.sample_count), -- SUM(mt.sample_count) -- FROM mgmt_target_memberships t, mgmt_metrics m, -- mgmt_metrics_composite_keys ck, mgmt_metrics_1day mt -- WHERE ck.target_guid = t.member_target_guid -- AND mt.target_guid = t.member_target_guid -- AND mt.rollup_timestamp > = SYSDATE - 31 -- AND mt.key_value = ck.composite_key -- AND m.metric_guid = mt.metric_guid -- AND ((m.metric_name = 'oc4j_servlet_rollup' -- AND m.metric_column = 'service.active') OR -- (m.metric_name = 'oc4j_servlet_delta_rollup' AND -- (m.metric_column = 'service.deltaCompleted' OR -- m.metric_column = 'service.deltaTime'))) -- AND m.target_type = 'oc4j' -- AND t.composite_target_guid = 'FFF' -- GROUP BY t.member_target_name, ck.key_part1_value, ck.key_part2_value, -- ck.key_part3_value, m.metric_column; -- -- IN Parameters: -- target_name: VARCHAR2 The name of the target -- target_type: VARCHAR2 The type of the target -- axis: VARCHAR2 The data axis (requests processed, processing time) -- component: VARCHAR2 The component type (JSP, SERVLET) -- time: VARCHAR2 The period for the data (24h, 7d, 31d) -- instance: VARCHAR2 The OC4J instance name or null for all -- -- OUT Parameters: -- data : cursorType -- Top 5 slowest of the type "component" by "axis" over the "time" period -- for "instance" (if provided else all) -- oc4j_list : cursorType -- All oc4j target names that belong to this webapp target (internal and -- display names). -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getWebappOc4jTopData( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_axis IN VARCHAR2, l_component IN VARCHAR2, l_time IN VARCHAR2, l_instance IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_oc4j_list OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2) IS v_axis VARCHAR2(32); v_table VARCHAR2(32); v_met VARCHAR(32); v_delta_met VARCHAR(32); v_query VARCHAR2(4000); v_time VARCHAR2(32); v_timestamp_expr VARCHAR2(128); v_oc4j_guid_expr VARCHAR2(256); v_bind_guid RAW(16); v_tgt_guid RAW(16); v_no_data BOOLEAN; v_val_col VARCHAR(64); v_cnt_col VARCHAR(64); BEGIN l_result := EMD_BCNTXN.p_bcn_success; -- Validate input parameters IF((l_target_name is null) OR (l_target_type is null) OR (l_axis is null) OR (l_component is null) OR (l_time is null)) THEN l_result := EMD_BCNTXN.p_bcn_err_badparams; END IF; -- Check for permissions l_result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( l_target_name, l_target_type, EMD_BCNTXN.p_view_mntr_reports, l_err_desc ); IF l_result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; -- Get the WebApp target GUID SELECT target_guid INTO v_tgt_guid FROM mgmt_targets WHERE target_name = l_target_name AND target_type = l_target_type; -- Set the DB equivalents for some input parameters -- Find out which column we want to order data on IF(l_axis = p_axis_req_processed) THEN v_axis := p_metcol_delta_comp; ELSE -- ELSIF(l_axis = p_axis_proc_time) THEN v_axis := p_metcol_delta_time; END IF; -- Set the appropriate tables we want to access based on the input time IF(l_time = p_time_24h) THEN v_time := 'mgmt_metrics_raw'; v_timestamp_expr := 'mt.collection_timestamp >= SYSDATE - 1'; v_val_col := 'SUM(mt.value)'; v_cnt_col := 'COUNT(mt.value)'; ELSIF(l_time = p_time_7d) THEN v_time := 'mgmt_metrics_1day'; v_timestamp_expr := 'mt.rollup_timestamp >= SYSDATE - 7'; v_val_col := 'SUM(mt.value_average * mt.sample_count)'; v_cnt_col := 'SUM(mt.sample_count)'; ELSE -- ELSIF(l_time = p_time_31d) THEN v_time := 'mgmt_metrics_1day'; v_timestamp_expr := 'mt.rollup_timestamp >= SYSDATE - 31'; v_val_col := 'SUM(mt.value_average * mt.sample_count)'; v_cnt_col := 'SUM(mt.sample_count)'; END IF; -- Set the appropriate metrics based on the component IF(l_component = p_component_jsp) THEN v_met := p_met_jsp_rollup; v_delta_met := p_met_jsp_delta_rollup; ELSE -- ELSIF(l_component = p_component_svlt) THEN v_met := p_met_servlet_rollup; v_delta_met := p_met_servlet_delta_rollup; END IF; -- DBMS_OUTPUT.PUT_LINE('l_instance is: ' || l_instance); -- Set the appropriate target(s) IF(l_instance IS null) THEN -- All oc4j targets belonging to WebApp v_oc4j_guid_expr := ' '; ELSE -- specific OC4J target -- NOTE: If a user has view priv on the composite target, it must have it -- also on the member targets (mboopati) SELECT target_guid INTO v_bind_guid FROM mgmt_targets WHERE target_name = l_instance AND target_type = p_oc4j_tgt_type; v_oc4j_guid_expr := 'AND assoc_target_guid = :memb_guid'; END IF; -- Set the basic query string -- Comments: -- We return the sum and average, because for some metrics we want the average -- while for others we want just the sum. -- The data is joined against itself so that we can order it by one of the -- metrics, this does yield in an extra column just with the values of the -- metric we want to order by. To reduce the total number of rows returned -- the rows with the metric used to order the data are droped (see that the -- value for this metric is given in the extra column added). For the -- metrics we order by we only need the sum (always) and not the average, we -- drop the average for the order by metrics. v_query := 'WITH data AS' || '(' || ' SELECT tg.target_name tgt_name, ck.key_part1_value node_id,' || ' ck.key_part2_value app_id, ck.key_part3_value web_mod,' || ' m.metric_column met_col, ' || v_val_col || ' sum, ' || v_cnt_col|| ' cnt' || ' FROM mgmt_target_assocs t, mgmt_metrics m, mgmt_targets tg,mgmt_target_assoc_defs d,' || ' mgmt_metrics_composite_keys ck, ' || v_time || ' mt' || ' WHERE ck.target_guid = t.assoc_target_guid' || ' AND mt.target_guid = t.assoc_target_guid' || ' AND t.assoc_guid = d.assoc_guid ' || ' AND d.assoc_def_name = ''supports_e2e_on''' || ' AND d.scope_target_type='' ''' || ' AND ' || v_timestamp_expr || ' AND mt.key_value = ck.composite_key' || ' AND m.metric_guid = mt.metric_guid' || ' AND ((m.metric_name = :met_rol AND' || ' m.metric_column = ''service.active'') OR' || ' (m.metric_name = :met_delta_rol AND' || ' (m.metric_column = ''service.deltaCompleted'' OR' || ' m.metric_column = ''service.deltaTime'')))' || ' AND m.target_type = ''oc4j''' || ' AND tg.target_guid = t.assoc_target_guid' || ' AND tg.target_type = m.target_type' || ' AND tg.type_meta_ver = m.type_meta_ver' || ' AND (tg.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = '' '')' || ' AND (tg.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = '' '')' || ' AND (tg.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = '' '')' || ' AND (tg.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = '' '')' || ' AND (tg.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = '' '')' || ' AND t.source_target_guid = :comp_guid ' || v_oc4j_guid_expr || ' GROUP BY tg.target_name, ck.key_part1_value,' || ' ck.key_part2_value, ck.key_part3_value, m.metric_column' || '),' || 'order_by AS ' || '(' || ' SELECT tgt_name, node_id, app_id, web_mod, sum' || ' FROM data' || ' WHERE met_col = :odr' || ')' || 'SELECT d.tgt_name, d.node_id, d.app_id, d.web_mod, d.met_col,' || ' d.sum, d.sum/d.cnt, o.sum' || ' FROM data d, order_by o' || ' WHERE d.tgt_name = o.tgt_name AND d.node_id = o.node_id' || ' AND d.app_id = o.app_id AND d.web_mod = o.web_mod' || ' ORDER BY o.sum DESC'; -- Get the data -- ATTENTION: Dynamic SQL STMTS bind by order IF(v_oc4j_guid_expr = ' ') THEN OPEN l_data FOR v_query USING v_met, v_delta_met, v_tgt_guid, v_axis; ELSE OPEN l_data FOR v_query USING v_met, v_delta_met, v_tgt_guid, v_bind_guid, v_axis; END IF; -- Get the list of OC4J's OPEN l_oc4j_list FOR SELECT tgt.target_name, tgt.display_name FROM mgmt_target_assocs memb, mgmt_targets tgt WHERE memb.source_target_guid = v_tgt_guid AND tgt.target_type = p_oc4j_tgt_type AND memb.assoc_guid = MGMT_WEBSITE_TARGET.supports_e2e_on_guid AND tgt.target_guid = memb.assoc_target_guid; RETURN; EXCEPTION WHEN OTHERS THEN l_result := EMD_BCNTXN.p_bcn_err_oraerr; l_err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); DBMS_OUTPUT.PUT_LINE('Error ' || l_err_desc); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappOc4jTopData: db error: ' || 'target_name = '|| l_target_name || '; target_type = ' ||l_target_type ); IF l_data%ISOPEN THEN CLOSE l_data; END IF; IF l_oc4j_list%ISOPEN THEN CLOSE l_oc4j_list; END IF; END getWebappOc4jTopData; -- getWebappConfigData -- -- The procedure returns a cursor with the following format: -- Target Real Name, Configure Link -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'website') -- component: VARCHAR2 The component type (oc4j, oracle_webcache, oracle_apache, generic_apache) -- -- OUT Parameters: -- data : cursorType -- The list of targets of type component that belong to the composite target -- and their respective properties (necessary to get their configure links). -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getWebappConfigData( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_component IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2) IS v_tgt_guid RAW(16); BEGIN l_result := EMD_BCNTXN.p_bcn_success; -- Validate input parameters IF((l_target_name is null) OR (l_target_type is null) OR (l_component is null)) THEN l_result := EMD_BCNTXN.p_bcn_err_badparams; END IF; -- Check for permissions l_result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( l_target_name, l_target_type, EMD_BCNTXN.p_view_mntr_reports, l_err_desc ); IF l_result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; -- Get the WebApp target GUID SELECT target_guid INTO v_tgt_guid FROM mgmt_targets WHERE target_name = l_target_name AND target_type = l_target_type; -- How the URL is formed was copied from IAS class ChronosViewModel.java -- ias_name not used in webcache case... IF(l_component = p_webserver_tgt_type) THEN OPEN l_data FOR SELECT ws.target_name, ws.emd_url, ias_prop.property_name, ias_prop.property_value, ias_tgt.target_name ias_name, ws.target_type, ws.type_display_name FROM mgmt_target_properties ias_prop, mgmt_targets ias_tgt, mgmt_target_assocs ias_assoc, ( SELECT ws_tgt.target_guid, ws_tgt.target_name, ws_tgt.display_name, ws_tgt.emd_url, ws_tgt.target_type, ws_tgt.type_display_name FROM mgmt_target_assocs webapp_assoc, mgmt_targets ws_tgt WHERE webapp_assoc.source_target_guid = v_tgt_guid AND webapp_assoc.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid AND webapp_assoc.assoc_target_guid = ws_tgt.target_guid AND ws_tgt.target_type = p_webcache_tgt_type ) ws WHERE ias_assoc.assoc_target_guid = ws.target_guid AND ias_assoc.assoc_guid = MGMT_ASSOC.g_contains_guid AND ias_assoc.source_target_guid = ias_tgt.target_guid AND ias_tgt.target_type = p_ias_tgt_type AND ias_prop.target_guid = ias_tgt.target_guid AND ias_prop.property_name = 'StandaloneConsoleURL' UNION SELECT wbs.target_name, wbs.emd_url, wbs_prop.property_name, wbs_prop.property_value, null, wbs.target_type, wbs.type_display_name FROM mgmt_targets wbs, mgmt_target_assocs webapp_assoc2, mgmt_target_properties wbs_prop WHERE webapp_assoc2.source_target_guid = v_tgt_guid AND webapp_assoc2.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid AND webapp_assoc2.assoc_target_guid = wbs.target_guid AND wbs_prop.target_guid = wbs.target_guid AND wbs_prop.property_name IN ('HTTPMachineForAdmin', 'AdminPort', 'HTTPProtocol', 'VersionCategory') AND wbs.target_type = p_webcache_tgt_type UNION SELECT aps.target_name, aps.emd_url, aps_prop.property_name, aps_prop.property_value, aps.target_name ias_name, aps.target_type, aps.type_display_name FROM mgmt_targets aps, mgmt_target_assocs webapp_assoc3, mgmt_target_properties aps_prop WHERE webapp_assoc3.source_target_guid = v_tgt_guid AND webapp_assoc3.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid AND webapp_assoc3.assoc_target_guid = aps.target_guid AND aps_prop.target_guid = aps.target_guid AND aps_prop.property_name = 'version' AND ((aps_prop.property_value LIKE '2.%' AND aps.target_type = 'generic_apache') OR (aps_prop.property_value LIKE 'stdApache%' AND aps.target_type = MGMT_GLOBAL.G_HTTP_SERVER_TARGET_TYPE)); ELSE -- (assume l_component = p_oc4j_tgt_type) -- This query assumes that each OC4J is member of only one IAS OPEN l_data FOR SELECT oc4j.target_name, oc4j.emd_url, ias_prop.property_name, ias_prop.property_value, ias_tgt.target_name ias_name, oc4j.target_type, oc4j.type_display_name, oc4j.display_name FROM mgmt_target_properties ias_prop, mgmt_targets ias_tgt, mgmt_target_assocs ias_assoc, ( SELECT oc4j_tgt.target_guid, oc4j_tgt.target_name, oc4j_tgt.display_name, oc4j_tgt.emd_url, oc4j_tgt.target_type, oc4j_tgt.type_display_name FROM mgmt_target_assocs webapp_assoc, mgmt_targets oc4j_tgt WHERE webapp_assoc.source_target_guid = v_tgt_guid AND webapp_assoc.assoc_guid = MGMT_WEBSITE_TARGET.supports_e2e_on_guid AND webapp_assoc.assoc_target_guid = oc4j_tgt.target_guid ) oc4j WHERE ias_assoc.assoc_target_guid = oc4j.target_guid AND ias_assoc.assoc_guid = MGMT_ASSOC.g_contains_guid AND ias_assoc.source_target_guid = ias_tgt.target_guid AND ias_tgt.target_type = p_ias_tgt_type AND ias_prop.target_guid = ias_tgt.target_guid AND ias_prop.property_name = 'StandaloneConsoleURL'; END IF; RETURN; EXCEPTION WHEN OTHERS THEN IF l_data%ISOPEN THEN CLOSE l_data; END IF; l_result := EMD_BCNTXN.p_bcn_err_oraerr; l_err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); DBMS_OUTPUT.PUT_LINE('Error ' || l_err_desc); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getWebappConfigData: db error: ' || 'target_name = '|| l_target_name || '; target_type = ' ||l_target_type ); END getWebappConfigData; -- getFormsConfigData -- -- The procedure returns a cursor with the following format: -- Target Real Name, Configure Link -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'formsapp') -- component: VARCHAR2 The component type (oc4j, oracle_webcache, oracle_apache, generic_apache) -- -- OUT Parameters: -- data : cursorType -- The list of targets of type component that belong to the composite target -- and their respective properties (necessary to get their configure links). -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getFormsConfigData( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_component IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2) IS v_tgt_guid mgmt_targets.target_guid%TYPE; BEGIN l_result := EMD_BCNTXN.p_bcn_success; -- Validate input parameters IF((l_target_name is null) OR (l_target_type is null) OR (l_component is null)) THEN l_result := EMD_BCNTXN.p_bcn_err_badparams; END IF; -- Check for permissions l_result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( l_target_name, l_target_type, EMD_BCNTXN.p_view_mntr_reports, l_err_desc ); IF l_result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; -- Get the WebApp target GUID v_tgt_guid := MGMT_TARGET.get_target_guid(l_target_name, l_target_type); -- How the URL is formed was copied from IAS class ChronosViewModel.java -- ias_name not used in webcache case... IF(l_component = p_webserver_tgt_type) THEN OPEN l_data FOR SELECT ws.target_name, ws.emd_url, ias_prop.property_name, ias_prop.property_value, ias_tgt.target_name ias_name, ws.target_type, ws.type_display_name FROM mgmt_target_properties ias_prop, mgmt_targets ias_tgt, mgmt_target_assocs ias_assoc, ( SELECT ws_tgt.target_guid, ws_tgt.target_name, ws_tgt.display_name, ws_tgt.emd_url, ws_tgt.target_type, ws_tgt.type_display_name FROM mgmt_target_assocs webapp_assoc, mgmt_targets ws_tgt WHERE webapp_assoc.source_target_guid = v_tgt_guid AND webapp_assoc.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid AND webapp_assoc.assoc_target_guid = ws_tgt.target_guid AND ws_tgt.target_type = p_webcache_tgt_type ) ws WHERE ias_assoc.assoc_target_guid = ws.target_guid AND ias_assoc.assoc_guid = MGMT_ASSOC.g_contains_guid AND ias_assoc.source_target_guid = ias_tgt.target_guid AND ias_tgt.target_type = p_ias_tgt_type AND ias_prop.target_guid = ias_tgt.target_guid AND ias_prop.property_name = 'StandaloneConsoleURL' UNION SELECT wbs.target_name, wbs.emd_url, wbs_prop.property_name, wbs_prop.property_value, null, wbs.target_type, wbs.type_display_name FROM mgmt_targets wbs, mgmt_target_assocs webapp_assoc2, mgmt_target_properties wbs_prop WHERE webapp_assoc2.source_target_guid = v_tgt_guid AND webapp_assoc2.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid AND webapp_assoc2.assoc_target_guid = wbs.target_guid AND wbs_prop.target_guid = wbs.target_guid AND wbs_prop.property_name IN ('HTTPMachineForAdmin', 'AdminPort', 'HTTPProtocol', 'VersionCategory') AND wbs.target_type = p_webcache_tgt_type UNION SELECT aps_t.target_name, aps_t.emd_url, aps_p.property_name, aps_p.property_value, aps_t.target_name ias_name, aps_t.target_type, aps_t.type_display_name FROM (SELECT at.target_guid, at.target_name, at.emd_url, at.target_type, at.type_display_name FROM mgmt_targets at, mgmt_target_assocs webapp_assoc3 WHERE webapp_assoc3.source_target_guid = v_tgt_guid AND webapp_assoc3.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid AND webapp_assoc3.assoc_target_guid = at.target_guid AND (NOT (at.target_type = p_webcache_tgt_type))) aps_t, (SELECT target_guid, property_name, property_value FROM mgmt_target_properties WHERE property_name = 'ebsConfigURL') aps_p WHERE aps_t.target_guid = aps_p.target_guid(+); ELSE -- (assume l_component = p_oc4j_tgt_type) -- This query assumes that each OC4J is member of only one IAS OPEN l_data FOR SELECT oc4j.target_name, oc4j.emd_url, ias_prop.property_name, ias_prop.property_value, ias_tgt.target_name ias_name, oc4j.target_type, oc4j.type_display_name FROM mgmt_target_properties ias_prop, mgmt_targets ias_tgt, mgmt_target_assocs ias_assoc, ( SELECT oc4j_tgt.target_guid, oc4j_tgt.target_name, oc4j_tgt.display_name, oc4j_tgt.emd_url, oc4j_tgt.target_type, oc4j_tgt.type_display_name FROM mgmt_target_assocs webapp_assoc, mgmt_targets oc4j_tgt WHERE webapp_assoc.source_target_guid = v_tgt_guid AND webapp_assoc.assoc_guid = MGMT_WEBSITE_TARGET.supports_e2e_on_guid AND webapp_assoc.assoc_target_guid = oc4j_tgt.target_guid ) oc4j WHERE ias_assoc.assoc_target_guid = oc4j.target_guid AND ias_assoc.assoc_guid = MGMT_ASSOC.g_contains_guid AND ias_assoc.source_target_guid = ias_tgt.target_guid AND ias_tgt.target_type = p_ias_tgt_type AND ias_prop.target_guid = ias_tgt.target_guid AND ias_prop.property_name = 'StandaloneConsoleURL'; END IF; RETURN; EXCEPTION WHEN OTHERS THEN IF l_data%ISOPEN THEN CLOSE l_data; END IF; l_result := EMD_BCNTXN.p_bcn_err_oraerr; l_err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); DBMS_OUTPUT.PUT_LINE('Error ' || l_err_desc); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getFormsConfigData: db error: ' || 'target_name = '|| l_target_name || '; target_type = ' ||l_target_type ); END getFormsConfigData; -- getFormsTargetsForEUM -- -- The procedure returns a list of Forms target that user -- needs to configure for enabling End-User Performance Monitoring -- for this particular Forms Application target -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'formsapp') -- -- OUT Parameters: -- data : cursorType -- The list of Forms targets that need to be configured -- for enabling End-User Performance Monitoring for this -- particular Forms Application target -- The Forms target should be either a key component of the -- Forms Application target, or member of key group component of the -- Forms Application target. If the Forms target has property -- ebsConfigURL, we need to get the property out as well. -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getFormsTargetsForEUM( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2) IS v_tgt_guid mgmt_targets.target_guid%TYPE; BEGIN l_result := EMD_BCNTXN.p_bcn_success; -- Validate input parameters IF((l_target_name is null) OR (l_target_type is null)) THEN l_result := EMD_BCNTXN.p_bcn_err_badparams; END IF; -- Check for permissions l_result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( l_target_name, l_target_type, EMD_BCNTXN.p_view_mntr_reports, l_err_desc ); IF l_result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; v_tgt_guid := MGMT_TARGET.get_target_guid(l_target_name, l_target_type); -- Get the list of Forms targets in this Forms application -- If a Forms target has property ebsConfigURL, get that property value too OPEN l_data FOR SELECT forms_t.target_name, forms_t.target_type, forms_t.status_code, forms_p.property_name, forms_p.property_value FROM ( SELECT forms1.target_guid, forms1.target_name, forms1.target_type, emd_mntr.getStatusCode(forms1.target_name, forms1.target_type) status_code FROM mgmt_target_assocs assocs1, mgmt_targets forms1, eume2e_assocs_lookup l1 WHERE assocs1.source_target_guid = v_tgt_guid AND assocs1.assoc_target_guid = forms1.target_guid AND assocs1.assoc_guid = MGMT_ASSOC.g_depends_on_guid AND forms1.target_type = l1.target_type AND l1.assoc_guid = MGMT_WEBSITE_TARGET.supports_forms_on_guid UNION SELECT forms2.target_guid, forms2.target_name, forms2.target_type, emd_mntr.getStatusCode(forms2.target_name, forms2.target_type) status_code FROM mgmt_flat_target_assoc flat_assocs, mgmt_target_assocs assocs2, mgmt_targets forms2, eume2e_assocs_lookup l2 WHERE flat_assocs.assoc_target_guid = forms2.target_guid AND flat_assocs.is_membership = 1 AND forms2.target_type = l2.target_type AND l2.assoc_guid = MGMT_WEBSITE_TARGET.supports_forms_on_guid AND flat_assocs.source_target_guid = assocs2.assoc_target_guid AND assocs2.source_target_guid = v_tgt_guid AND assocs2.assoc_guid = MGMT_ASSOC.g_depends_on_guid ) forms_t, (SELECT target_guid, property_name, property_value FROM mgmt_target_properties WHERE property_name = 'ebsConfigURL') forms_p WHERE forms_t.target_guid = forms_p.target_guid(+); RETURN; EXCEPTION WHEN OTHERS THEN IF l_data%ISOPEN THEN CLOSE l_data; END IF; l_result := EMD_BCNTXN.p_bcn_err_oraerr; l_err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getFormsTargetsForEUM: db error: ' || 'target_name = '|| l_target_name || '; target_type = ' || l_target_type ); END getFormsTargetsForEUM; -- getFormsTargetsForTxn -- -- The procedure returns a list of Forms target that user -- needs to configure for enabling Forms Transaction Monitoring -- for this particular Forms Application target -- -- -- IN Parameters: -- target_name: VARCHAR2 The name of the composite target -- target_type: VARCHAR2 The type of the target (most likely 'formsapp') -- -- OUT Parameters: -- data : cursorType -- The list of Forms targets that need to be configured -- for enabling Forms Transaction Monitoring for this -- particular Forms Application target -- The Forms target should be either a key component of the -- Forms Application target, or member of key group component of the -- Forms Application target. If the Forms target has property -- formsJarLocation, we need to get the property out as well. -- result : INTEGER -- Error Code. -- err_desc : VARCHAR2 -- Error Description PROCEDURE getFormsTargetsForTxn( l_target_name IN VARCHAR2, l_target_type IN VARCHAR2, l_data OUT EMD_MNTR_USER.CURSORTYPE, l_result OUT NUMBER, l_err_desc OUT VARCHAR2) IS v_tgt_guid mgmt_targets.target_guid%TYPE; BEGIN l_result := EMD_BCNTXN.p_bcn_success; -- Validate input parameters IF((l_target_name is null) OR (l_target_type is null)) THEN l_result := EMD_BCNTXN.p_bcn_err_badparams; END IF; -- Check for permissions l_result := EMD_BCNTXN.HAS_TGT_FUNCTION_PRIV ( l_target_name, l_target_type, EMD_BCNTXN.p_view_mntr_reports, l_err_desc ); IF l_result <> EMD_BCNTXN.p_bcn_success THEN RETURN; END IF; v_tgt_guid := MGMT_TARGET.get_target_guid(l_target_name, l_target_type); -- Get the list of Forms targets in this Forms application -- If a Forms target has property ebsConfigURL, get that property value too OPEN l_data FOR SELECT forms_t.target_name, forms_t.target_type, forms_t.status_code, forms_p.property_name, forms_p.property_value FROM ( SELECT forms1.target_guid, forms1.target_name, forms1.target_type, emd_mntr.getStatusCode(forms1.target_name, forms1.target_type) status_code FROM mgmt_target_assocs assocs1, mgmt_targets forms1, eume2e_assocs_lookup l1 WHERE assocs1.source_target_guid = v_tgt_guid AND assocs1.assoc_target_guid = forms1.target_guid AND assocs1.assoc_guid = MGMT_ASSOC.g_depends_on_guid AND forms1.target_type = l1.target_type AND l1.assoc_guid = MGMT_WEBSITE_TARGET.supports_forms_on_guid UNION SELECT forms2.target_guid, forms2.target_name, forms2.target_type, emd_mntr.getStatusCode(forms2.target_name, forms2.target_type) status_code FROM mgmt_flat_target_assoc flat_assocs, mgmt_target_assocs assocs2, mgmt_targets forms2, eume2e_assocs_lookup l2 WHERE flat_assocs.assoc_target_guid = forms2.target_guid AND flat_assocs.is_membership = 1 AND forms2.target_type = l2.target_type AND l2.assoc_guid = MGMT_WEBSITE_TARGET.supports_forms_on_guid AND flat_assocs.source_target_guid = assocs2.assoc_target_guid AND assocs2.source_target_guid = v_tgt_guid AND assocs2.assoc_guid = MGMT_ASSOC.g_depends_on_guid ) forms_t, (SELECT target_guid, property_name, property_value FROM mgmt_target_properties WHERE property_name = 'formsJarLocation') forms_p WHERE forms_t.target_guid = forms_p.target_guid(+); RETURN; EXCEPTION WHEN OTHERS THEN IF l_data%ISOPEN THEN CLOSE l_data; END IF; l_result := EMD_BCNTXN.p_bcn_err_oraerr; l_err_desc := SUBSTR(SQLERRM, 1, EMD_BCNTXN.p_err_maxlen - 50); MGMT_LOG.LOG_ERROR('website_homepage', NULL, 'getFormsTargetsForTxn: db error: ' || 'target_name = '|| l_target_name || '; target_type = ' || l_target_type ); END getFormsTargetsForTxn; end emd_mntr_website; / show errors;