Rem drv: Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_meteval_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/12 04:40:23 jsadras Exp $ Rem Rem collections_meteval_pkgbody.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem collections_meteval_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem jsadras 06/11/09 - Bug:8592049, remove noop Rem jsadras 07/17/06 - Backport jsadras_bug-5002887 from main Rem jsadras 02/07/06 - keys_from_mult_colls Rem hbadheka 01/06/06 - add dummy eval proc Rem gsbhatia 07/01/05 - New repmgr header impl Rem jsadras 07/01/05 - Bug:4459652, do not update last_load_time for Rem agent targets Rem jsadras 06/07/05 - Bug:4331955, perf fix for sql generation Rem jsadras 05/19/05 - security fix Rem jsadras 12/22/04 - key lenght Rem jsadras 12/17/04 - bulk_collections Rem jsadras 12/15/04 - accept list of targets Rem rpinnama 12/02/04 - Use object type constants. Rem jsadras 11/15/04 - status Rem jsadras 11/02/04 - handle errors Rem jsadras 10/25/04 - get_metric_info_err Rem jsadras 10/19/04 - repo_timing Rem jsadras 10/13/04 - store_metric_data_debug Rem jsadras 10/07/04 - mgmt_short_string_table Rem jsadras 10/04/04 - last_load_time Rem jsadras 09/23/04 - metric_info Rem jsadras 09/21/04 - labels Rem jsadras 09/17/04 - metric_errors Rem jsadras 09/15/04 - key_column_types Rem jsadras 09/09/04 - eval_proc_signature Rem jsadras 09/08/04 - metric_column Rem jsadras 09/03/04 - remove ora20601 Rem jsadras 08/26/04 - Rem jsadras 08/06/04 - remove metric_guid from collection tasks Rem jsadras 07/24/04 - jsadras_repocollect1 Rem jsadras 07/06/04 - Created Rem CREATE OR REPLACE PACKAGE BODY EM_METRIC_EVAL AS G_SQL_ONE_TARGET CONSTANT VARCHAR2(400) := ' WHERE target_guid = :p_target_guid ' ; G_SQL_TARGET CONSTANT VARCHAR2(400) := ' WHERE target_guid IN (SELECT column_value FROM TABLE(CAST(:p_target_guids AS mgmt_target_guid_array)) ) ORDER BY target_guid' ; G_VERIFY_EVAL_MODE CONSTANT NUMBER := 3 ; G_SQL_VERIFY CONSTANT VARCHAR2(200) := ' WHERE rownum = 1 ' ; G_SQL_TASK CONSTANT VARCHAR2(250) := ' WHERE target_guid IN (SELECT target_guid FROM MGMT_COLLECTION_METRIC_TASKS WHERE TASK_ID = :task_id) order by target_guid' ; G_ERROR_LEN CONSTANT NUMBER := 256 ; --get_target_list --Purpose --Private procedure to get the list of targets for a given collection task -- --Parameters: -- p_task_id : Task id of the collection -- p_target_list : The target list of the task PROCEDURE get_target_list(p_task_id IN NUMBER, p_target_list OUT NOCOPY mgmt_target_guid_array ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_target_list:Enter',G_MODULE_NAME) ; END IF ; SELECT target_guid BULK COLLECT INTO p_target_list FROM MGMT_COLLECTION_METRIC_TASKS WHERE task_id = p_task_id ORDER BY target_guid ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_target_list:Exit Normal Count='|| to_char(p_target_list.COUNT), G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.INFO('get_target_list:Exit exception:'||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' (get_target_list)') ; END get_target_list; -- --Routines for dumping data -- Has to be called only if debug set -- dumps the values in the string array PROCEDURE dump_values(p_string_array IN mgmt_short_string_array) IS BEGIN IF p_string_array IS NOT NULL AND p_string_array.COUNT > 0 THEN FOR i in p_string_array.FIRST..p_string_array.LAST LOOP EMDW_LOG.DEBUG(' '||p_string_array(i),G_MODULE_NAME) ; END LOOP ; END IF ; EXCEPTION WHEN OTHERS THEN NULL ; END dump_values; -- -- Dumps the values in the namevalue pair -- PROCEDURE dump_namevalues(p_namevalue_pair IN mgmt_namevalue_array) IS BEGIN IF p_namevalue_pair IS NOT NULL AND p_namevalue_pair.COUNT > 0 THEN FOR i in p_namevalue_pair.FIRST..p_namevalue_pair.LAST LOOP EMDW_LOG.DEBUG(' '|| rpad(p_namevalue_pair(i).name,20)||'='|| p_namevalue_pair(i).value, G_MODULE_NAME) ; END LOOP ; END IF ; EXCEPTION WHEN OTHERS THEN NULL ; END dump_namevalues; -- -- Routine to dump the metric results -- PROCEDURE dump_metric_results( p_metric_values IN mgmt_metric_value_array) IS l_prev_target_guid mgmt_targets.target_guid%type ; BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('DumpMetricResults:***** Metric results Start *******', G_MODULE_NAME) ; IF p_metric_values IS NOT NULL AND p_metric_values.COUNT > 0 THEN FOR i IN p_metric_values.FIRST..p_metric_values.LAST LOOP IF p_metric_values.EXISTS(i) THEN IF p_metric_values(i).target_guid != nvl(l_prev_target_guid,'X') THEN EMDW_LOG.DEBUG('Target GUID='|| p_metric_values(i).target_guid, G_MODULE_NAME) ; END IF ; l_prev_target_guid := p_metric_values(i).target_guid ; EMDW_LOG.DEBUG('Record#'||i,G_MODULE_NAME) ; IF p_metric_values(i).collection_timestamp IS NOT NULL THEN EMDW_LOG.DEBUG(' Timestamp='|| to_char(p_metric_values(i).collection_timestamp,'DD-MON-YY HH24:MI'), G_MODULE_NAME) ; END IF ; IF p_metric_values(i).status IS NOT NULL THEN EMDW_LOG.DEBUG(' Status='||p_metric_values(i).status||' '|| p_metric_values(i).status_message,G_MODULE_NAME) ; END IF ; dump_namevalues(p_metric_values(i).metric_values) ; END IF ; END LOOP ; END IF ; EMDW_LOG.DEBUG('DumpMetricResults:***** Metric results END *******', G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN NULL ; END ; --get_identifying_info -- -- Purpose: -- Get the target category properties to be used later on for -- getting the metric information -- -- Parameters: -- p_target_guid : target_guid for which the properties are required -- p_category_props: Category prop list -- -- Exceptions -- MGMT_GLOBAL.COLLECTION_ERR -- PROCEDURE get_identifying_info(p_target_guid IN RAW, p_target_type OUT NOCOPY VARCHAR2, p_type_meta_ver OUT NOCOPY VARCHAR2, p_category_props OUT NOCOPY mgmt_short_string_array) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_identifying_info:Enter (Target)',G_MODULE_NAME) ; END IF ; p_category_props := mgmt_short_string_array() ; p_category_props.extend(5) ; SELECT target_type, type_meta_ver, nvl(category_prop_1,' '), nvl(category_prop_2,' '), nvl(category_prop_3,' '), nvl(category_prop_4,' '), nvl(category_prop_5,' ') INTO p_target_type, p_type_meta_ver, p_category_props(1), p_category_props(2), p_category_props(3), p_category_props(4), p_category_props(5) FROM MGMT_TARGETS WHERE target_guid = p_target_guid ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_identifying_info:Exit Normal(Target)',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('get_identifying_info:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; RAISE_APPLICATION_ERROR(MGMT_GLOBAL.COLLECTION_ERR, ' Invalid Target (get_identifying_info) ') ; END get_identifying_info; -- generate_select_clause -- -- Purpose -- Private procedure to generate the select clause for given set of columns -- for performance tuning the sql statement may be generated and stored in -- mgmt_metrics table which could mean extra steps to make sure the select -- clause and the metric information is in sync -- -- Parameters -- p_key_columns : Key columns of the metric -- p_metric_columns : Non key columns -- p_select_clause : The generated select clause -- -- Exceptions: -- MGMT_GLOBAL.COLLECTION_ERR -- metric should have at least 1 metric column -- Example: -- If key_column = TABLESPACE_NAME -- if metric_columns are pctused and pctfree -- The generated SELECT is -- select mgmt_metric_value_obj.new(target_guid,null,null, -- mgmt_namevalue_array( -- mgmt_namevalue_obj.new('TABLESPACE_NAME',tablespace_name), -- mgmt_namevalue_obj.new('PCTUSED',pctused), -- mgmt_namevalue_obj.new('PCTFREE',pctused), -- ) ) PROCEDURE generate_select_clause(p_key_columns IN mgmt_namevalue_array := NULL, p_metric_columns IN mgmt_namevalue_array := NULL, p_select_clause OUT NOCOPY VARCHAR2) IS p_comma varchar2(1) := null ; p_first_time BOOLEAN := FALSE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('generate_select_clause:Enter',G_MODULE_NAME) ; END IF ; IF p_metric_columns.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Metric has no metric columns defined (generate_select_clause)') ; END IF ; p_select_clause := ' SELECT mgmt_metric_value_obj.new(target_guid,null,null, mgmt_namevalue_array(' ; IF p_key_columns IS NOT NULL AND p_key_columns.count > 0 THEN FOR i in p_key_columns.FIRST..p_key_columns.LAST LOOP p_select_clause := p_select_clause|| p_comma||'mgmt_namevalue_obj.new('''||p_key_columns(i).name||''',' ||p_key_columns(i).name||')' ; p_comma := ',' ; END LOOP ; END IF ; FOR i in p_metric_columns.FIRST..p_metric_columns.LAST LOOP p_select_clause := p_select_clause|| p_comma||'mgmt_namevalue_obj.new('''||p_metric_columns(i).name||''',' ||p_metric_columns(i).name||')' ; p_comma := ',' ; END LOOP ; p_select_clause := p_select_clause||'))' ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.DEBUG('generate_select_clause:Select Clause:'||p_select_clause, G_MODULE_NAME) ; EMDW_LOG.INFO('generate_select_clause:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_INFO_SET THEN emdw_log.info('generate_select_clause:Exit Exception'||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||' (generate_select_clause)') ; END IF ; END generate_select_clause; -- -- Store metric errors -- PROCEDURE store_metric_errors(p_task_id IN NUMBER := NULL, p_target_guids IN mgmt_target_guid_array := NULL, p_metric_guid IN RAW := NULL, p_coll_name IN VARCHAR2 := ' ', p_timezone_region IN VARCHAR2, p_error_message IN VARCHAR2) IS l_sysdate DATE := CAST(systimestamp at time zone p_timezone_region AS DATE) ; CURSOR current_metric_errors_cur(p_task_id IN NUMBER, p_target_guids IN mgmt_target_guid_array, p_target_count IN NUMBER) IS SELECT tasks.target_guid, tasks.metric_guid, tasks.coll_name FROM mgmt_collection_metric_tasks tasks WHERE tasks.task_id = p_task_id AND p_task_id IS NOT NULL UNION ALL SELECT column_value, p_metric_guid, p_coll_name FROM TABLE(CAST(p_target_guids AS mgmt_target_guid_array)) WHERE p_target_count >0 AND p_task_id IS NULL ; l_target_count NUMBER := 0 ; l_err_count NUMBER ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_errors:Enter',G_MODULE_NAME) ; END IF ; -- task has already been optimized not to call on duplicate error message -- we handle targets here IF p_task_id IS NULL THEN IF p_target_guids IS NULL OR p_target_guids.COUNT = 0 THEN RETURN ; ELSE l_target_count := p_target_guids.COUNT ; END IF ; -- see if we need to clear, if no previous error records -- then return IF p_error_message IS NULL THEN SELECT NVL(COUNT(*),0) INTO l_err_count FROM mgmt_current_metric_errors WHERE target_guid IN (SELECT column_value FROM TABLE(CAST(p_target_guids AS mgmt_target_guid_array))) AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND agent_guid = G_REPO_AGENT_GUID ; IF l_err_count =0 THEN RETURN ; END IF ; END IF ; -- if p_error_message is null END IF ; -- task_is is null FOR rec in current_metric_errors_cur(p_task_id,p_target_guids,l_target_count) LOOP BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('store_metric_errors:Metric error record Tgt='|| rec.target_guid||' Metric= '||rec.metric_guid|| ' Coll='||rec.coll_name,G_MODULE_NAME) ; END IF ; INSERT INTO mgmt_metric_errors (target_guid,metric_guid,coll_name,agent_guid, collection_timestamp,metric_error_message) VALUES (rec.target_guid,rec.metric_guid,rec.coll_name,G_REPO_AGENT_GUID, l_sysdate,p_error_message) ; EXCEPTION --ignore duplicate errors WHEN MGMT_GLOBAL.DUPLICATE_RECORD THEN NULL ; WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_errors:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('store_metric_errors:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm|| ' (store_metric_errors) ') ; END store_metric_errors; -- -- GET_METRIC_INFO -- PURPOSE: -- Private procedure which returns the metric information object for use by -- Collections engine, Metric evaluation and policy evaluation -- Parameters -- p_task_id : Task Id of the collection task -- p_target_guid : Target Guid of the target for which the metric -- Information is to be retrieved -- p_metric_guid : Metric Guid -- p_eval_mode : IF it is TASK_EVAL_MODE (1) means it -- is called by collection engine -- IF it is target_eval_mode(2) means -- evaluation is to be done for a -- particular target --p_metric_info : Metric information for use by collection engine -- -- ASSUMPTIONS: -- source record is populated on the row where metric_column=' ' -- for number/string metrics also the metric_column should be ' ' -- EXCEPTIONS : -- MGMT_GLOBAL.COLLECTION_ERR -- Missing Metric Data: num_keys or source or metric_type is NULL -- Missing key columns: If key column records are missing -- Plus exceptions by called procedures -- generate select clause PROCEDURE get_metric_info( p_target_guid IN RAW, p_metric_guid IN RAW, p_eval_mode IN NUMBER, p_metric_info OUT NOCOPY METRIC_INFO_REC, p_repo_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE ) IS l_target_guid mgmt_targets.target_guid%type ; l_category_props mgmt_short_string_array := mgmt_short_string_array() ; l_type_meta_ver mgmt_metrics.type_meta_ver%type ; l_target_type mgmt_targets.target_type%type ; PROCEDURE dump_metric_info IS BEGIN EMDW_LOG.DEBUG(' Metric Name='||p_metric_info.metric_name, G_MODULE_NAME) ; EMDW_LOG.DEBUG(' Mguid='||p_metric_info.metric_guid|| ' Metric type='|| p_metric_info.metric_type|| ' Num keys='||p_metric_info.num_keys,G_MODULE_NAME) ; IF p_metric_info.metric_guid != p_metric_info.eval_metric_guid THEN EMDW_LOG.DEBUG(' Eval mguid='||p_metric_info.eval_metric_guid, G_MODULE_NAME) ; END IF ; EMDW_LOG.DEBUG(' Source('||p_metric_info.source_type||')= '|| p_metric_info.source,G_MODULE_NAME) ; dump_namevalues(p_metric_info.key_cols) ; dump_namevalues(p_metric_info.metric_cols) ; END ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_metric_info:Enter',G_MODULE_NAME) ; END IF ; p_metric_info.key_cols := mgmt_namevalue_array() ; p_metric_info.metric_cols := mgmt_namevalue_array() ; p_metric_info.all_cols := mgmt_namevalue_array() ; p_metric_info.column_labels := mgmt_namevalue_array() ; p_metric_info.column_nlsids := mgmt_namevalue_array() ; l_category_props.extend(5) ; get_identifying_info(p_target_guid=>p_target_guid, p_target_type=>l_target_type, p_type_meta_Ver=>l_type_meta_ver, p_category_props=>l_category_props) ; BEGIN -- Loop through and get all metric information -- Metric information is from the row where metric_column=' ' -- For service metrics the metric information will come from -- the metric column record. -- The inner subquery is needed to take care of service metrics -- which will pass in the metric guid of the column -- The ordering is very important, please do not change -- first orders by table descriptor record, then by key columns -- and then by metric columns. Ordering by metric columns -- so that we consistently get metric columns in same order FOR metric_rec IN ( SELECT metric_guid, source_type,metric_type , metric_name, source, num_keys, key_order, metric_column, decode(key_order,1,null,',') comma, nvl(decode(metric_label,' ',NULL,metric_label), metric_name) metric_label, nvl(decode(column_label,' ',NULL,column_label), metric_column) column_label, metric_label_nlsid, column_label_nlsid, repo_timing_enabled, keys_from_mult_colls FROM MGMT_METRICS WHERE metric_name IN (SELECT metric_name FROM mgmt_metrics WHERE metric_guid = p_metric_guid AND target_type = l_target_type AND type_meta_ver = l_type_meta_ver ) AND target_type = l_target_type AND type_meta_ver = l_type_meta_ver AND (category_prop_1 = ' ' OR category_prop_1 = l_category_props(1)) AND (category_prop_2 = ' ' OR category_prop_2 = l_category_props(2)) AND (category_prop_3 = ' ' OR category_prop_3 = l_category_props(3)) AND (category_prop_4 = ' ' OR category_prop_4 = l_category_props(4)) AND (category_prop_5 = ' ' OR category_prop_5 = l_category_props(5)) ORDER BY decode(metric_column,' ',1,2), decode(key_order,0,1,0) , key_order,metric_column ) LOOP IF metric_rec.metric_column != ' ' THEN p_metric_info.all_cols.extend(1) ; p_metric_info.all_cols(p_metric_info.all_cols.COUNT) := mgmt_namevalue_obj.new( metric_rec.metric_column,metric_rec.metric_type) ; p_metric_info.column_labels.extend(1) ; p_metric_info.column_labels(p_metric_info.column_labels.COUNT):= mgmt_namevalue_obj.new(metric_rec.metric_column, metric_rec.column_label) ; p_metric_info.column_nlsids.extend(1) ; p_metric_info.column_nlsids(p_metric_info.column_nlsids.COUNT):= mgmt_namevalue_obj.new(metric_rec.metric_column, metric_rec.column_label_nlsid) ; END IF ; IF metric_rec.metric_column = ' ' THEN p_metric_info.num_keys := metric_rec.num_keys ; p_metric_info.source := metric_rec.source ; p_metric_info.source_type := metric_rec.source_type ; p_metric_info.metric_type := metric_rec.metric_type ; p_metric_info.metric_name := metric_rec.metric_name ; p_metric_info.metric_guid := metric_rec.metric_guid ; p_metric_info.eval_metric_guid := metric_rec.metric_guid ; p_metric_info.metric_label := metric_rec.metric_label ; p_metric_info.metric_label_nlsid := metric_rec.metric_label_nlsid ; p_metric_info.repo_timing_enabled := metric_rec.repo_timing_enabled; p_metric_info.keys_from_mult_colls := metric_rec.keys_from_mult_colls; ELSIF metric_rec.key_order > 0 THEN p_metric_info.key_cols.extend(1) ; p_metric_info.key_cols(p_metric_info.key_cols.count) := mgmt_namevalue_obj.new( metric_rec.metric_column,metric_rec.metric_type) ; ELSE -- service metrics may be called with a metric guid -- of the metric column. In which case we use the source from -- the metric column IF metric_rec.metric_guid = p_metric_guid AND metric_rec.source IS NOT NULL AND metric_rec.source_type IS NOT NULL THEN p_metric_info.eval_metric_guid := metric_rec.metric_guid ; p_metric_info.source := metric_rec.source ; p_metric_info.source_type := metric_rec.source_type ; p_metric_info.metric_cols := mgmt_namevalue_array( mgmt_namevalue_obj.new( metric_rec.metric_column,metric_rec.metric_type) ); p_metric_info.repo_timing_enabled := metric_rec.repo_timing_enabled; EXIT ; ELSE p_metric_info.metric_cols.extend(1) ; p_metric_info.metric_cols(p_metric_info.metric_cols.count) := mgmt_namevalue_obj.new( metric_rec.metric_column,metric_rec.metric_type) ; END IF ; END IF ; END LOOP ; -- if called for non repository metrics , do not do anything more IF p_repo_metric = MGMT_GLOBAL.G_TRUE THEN -- we generate the select clause here, it would be performance wise -- efficient to store in mgmt_metrics, but that would involve -- maintenance. IF p_metric_info.source_type = MGMT_GLOBAL.G_METRIC_SOURCE_QUERY THEN generate_select_clause(p_metric_info.key_cols, p_metric_info.metric_cols, p_metric_info.select_clause) ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN dump_metric_info ; END IF ; IF p_metric_info.source is NULL OR p_metric_info.source_type IS NULL THEN RAISE_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Missing Metric source or source type (get_metric_info)') ; END IF ; IF p_metric_info.num_keys>0 AND p_metric_info.key_cols.count!=p_metric_info.num_keys THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Missing Metric Key Columns (get_metric_info)') ; END IF ; END IF ; END ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('get_metric_info:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('get_metric_info:Exit Exception'||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||'(get_metric_info)') ; END IF ; END ; --convert_results -- -- Purpose -- Private procedure to convert the results in the old format of mgmt_metric_results -- to mgmt_metric_values format -- Ideally we should switch over to mgmt_metric_values format -- and remove this procedure -- -- Parameters: -- p_target_guid : Target guid -- p_mgmt_metric_results : results in old format -- p_mgmt_metric_value s : results in NEW format -- Psuedo-Logic: -- Loop through the results ordered by key column -- If key same as previous -- Append the metric_column,metric_value to name/value array -- Else -- Append key values to array -- create metric result -- Initialize array PROCEDURE convert_results(p_target_guid IN RAW, p_num_keys IN NUMBER, p_key_cols IN mgmt_namevalue_array, p_mgmt_metric_results IN mgmt_metric_results, p_mgmt_metric_values OUT NOCOPY mgmt_metric_value_array) IS l_prev_key_value mgmt_severity.key_value%type ; l_met_column_values mgmt_namevalue_array := mgmt_namevalue_array(); type mgmt_composite_keys is VARRAY(5) of mgmt_metrics_composite_keys.key_part1_value%type ; l_key_values mgmt_composite_keys := mgmt_composite_keys() ; l_metric_values mgmt_namevalue_array := mgmt_namevalue_array() ; BEGIN p_mgmt_metric_values := mgmt_metric_value_array() ; IF p_mgmt_metric_results is NULL or p_mgmt_metric_results.COUNT=0 THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,'No Metric values fetched (convert_result)') ; END IF ; l_prev_key_value := NULL ; IF p_num_keys > 1 THEN l_key_values.extend(5) ; END IF ; FOR rec in ( SELECT 1 col1, a.key_column_value,a.metric_column,a.metric_value FROM TABLE(CAST(p_mgmt_metric_results AS mgmt_metric_results)) a UNION SELECT 2,null,null,null FROM DUAL) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('convert_results:'||to_char(rec.col1)||': Key='|| rec.key_column_value||' '||rec.metric_column||'='|| rec.metric_value,'G_MODULE_NAME') ; END IF ; IF p_num_keys=0 THEN IF rec.col1 =1 THEN l_metric_values.extend ; l_metric_values(l_metric_values.count) := mgmt_namevalue_obj.new (rec.metric_column,rec.metric_value) ; ELSE p_mgmt_metric_values.extend(1) ; p_mgmt_metric_values(1):= mgmt_metric_value_obj.NEW (p_target_guid,0,null,l_metric_values) ; END IF; ELSE -- num_keys > 0 IF rec.key_column_value = nvl(l_prev_key_value,rec.key_column_value) THEN l_met_column_values.extend ; l_met_column_values(l_met_column_values.COUNT) := mgmt_namevalue_obj.new(rec.metric_column,rec.metric_value) ; ELSE IF p_num_keys=1 THEN l_met_column_values.extend ; l_met_column_values(l_met_column_values.COUNT) := mgmt_namevalue_obj.new(p_key_cols(1).name,l_prev_key_value) ; ELSE BEGIN SELECT nvl(key_part1_value,' '), nvl(key_part2_value,' '), nvl(key_part3_value,' '), nvl(key_part4_value,' '), nvl(key_part5_value,' ') INTO l_key_values(1),l_key_values(2), l_key_values(3),l_key_values(4), l_key_values(5) FROM mgmt_metrics_composite_keys WHERE target_guid = p_target_guid AND composite_key = l_prev_key_value ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,'Invalid Key Value (convert_results)') ; END ; FOR i in p_key_cols.first..p_key_cols.last LOOP l_met_column_values.extend ; l_met_column_values(l_met_column_values.COUNT) := mgmt_namevalue_obj.new(p_key_cols(i).name,l_key_values(i)) ; END LOOP ; END IF ; p_mgmt_metric_values.EXTEND ; p_mgmt_metric_values(p_mgmt_metric_values.COUNT) := mgmt_metric_value_obj.NEW(p_target_guid,0,null,l_met_column_values) ; l_met_column_values := mgmt_namevalue_array() ; l_met_column_values.extend ; l_met_column_values(l_met_column_values.COUNT) := mgmt_namevalue_obj.new(rec.metric_column,rec.metric_value) ; END IF ; l_prev_key_value := rec.key_column_value ; END IF ; END LOOP ; EXCEPTION WHEN OTHERS THEN IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||'(convert_results)') ; END IF ; END convert_results; -- -- Convert results from mgmt_metric_results_table format -- to mgmt_metric_value_array format -- PROCEDURE convert_results(p_target_guid IN RAW, p_metric_results IN mgmt_metric_results_table, p_collection_timestamp IN DATE, p_metric_info IN METRIC_INFO_REC, p_metric_values OUT mgmt_metric_value_array ) IS l_namevalue_array mgmt_namevalue_array := mgmt_namevalue_array() ; BEGIN p_metric_values := mgmt_metric_value_array() ; IF p_metric_results IS NOT NULL AND p_metric_results.COUNT > 0 THEN p_metric_values.EXTEND(p_metric_results.COUNT) ; FOR i IN p_metric_results.FIRST..p_metric_results.LAST LOOP -- check for null metric columns IF p_metric_results(i).metric_values IS NULL OR p_metric_results(i).metric_values.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'missing metric column value in record '||i) ; END IF ; l_namevalue_array := mgmt_namevalue_array() ; -- Generate Key Column values IF p_metric_info.num_keys > 0 THEN IF p_metric_results(i).key_value IS NULL OR p_metric_results(i).key_value.COUNT != p_metric_info.num_keys THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Invalid number of keys in record number '||i) ; END IF ; l_namevalue_array.EXTEND(p_metric_info.num_keys) ; FOR j IN 1..p_metric_info.num_keys LOOP l_namevalue_array(j) := mgmt_namevalue_obj.NEW (p_metric_info.key_cols(j).name, p_metric_results(i).key_value(j) ) ; END LOOP ; END IF ; -- Generate metric column values l_namevalue_array.EXTEND(p_metric_results(i).metric_values.COUNT) ; FOR k IN 1..p_metric_results(i).metric_values.COUNT LOOP l_namevalue_array(p_metric_info.num_keys+k) := mgmt_namevalue_obj.NEW (p_metric_results(i).metric_values(k).name, p_metric_results(i).metric_values(k).value) ; END LOOP ; p_metric_values(i) := mgmt_metric_value_obj.NEW (p_target_guid,NULL,NULL,l_namevalue_array,p_collection_timestamp) ; END LOOP ; END IF ; END convert_results ; -- check_target_list -- -- Purpose -- Check the following -- All the input target list should be in the results -- Result is ordered the same as input target list -- Parameters -- p_target_list :Input list of targets ordered by target_guid -- p_metric_values:Result array -- Assumptions: -- p_target_list is ordered -- Exceptions -- MGMT_GLOBAL.COLLECTION_ERR -- PROCEDURE check_target_list(p_target_list IN mgmt_target_guid_array, p_metric_values IN mgmt_metric_value_array) IS l_prev_target_guid mgmt_targets.target_guid%type ; l_curr_pointer NUMBER := p_target_list.FIRST ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('check_target_list:Enter',G_MODULE_NAME) ; END IF ; -- not checking for IS NULL or count, expect the caller to check FOR rec in p_metric_values.FIRST..p_metric_values.LAST LOOP IF p_metric_values.EXISTS(rec) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('check_target_list:Metric record('|| rec||') Target='||p_metric_values(rec).target_guid, G_MODULE_NAME) ; END IF ; IF p_metric_values(rec).target_guid < nvl(l_prev_target_guid,p_metric_values(rec).target_guid) THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Metric list is not ordered by target guid (check_target_list)') ; END IF ; l_prev_target_guid := p_metric_values(rec).target_guid ; -- skip through records in target list WHILE l_curr_pointer IS NOT NULL AND p_target_list(l_curr_pointer) < p_metric_values(rec).target_guid LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('check_target_list:Skipping Target Record('|| l_curr_pointer||')='|| p_target_list(l_curr_pointer), G_MODULE_NAME) ; END IF ; l_curr_pointer := p_target_list.NEXT(l_curr_pointer) ; END LOOP ; -- at this point the target record has to match -- metric result record else error IF l_curr_pointer IS NULL OR p_metric_values(rec).target_guid != p_target_list(l_curr_pointer) THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Target list contains extra target(s) (check_target_list)'); END IF ; END IF ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('check_target_list:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('check_target_list:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE= MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||' (check_target_list)') ; END IF ; END check_target_list; -- -- store the collected metric data -- PROCEDURE store_metric_data (p_metric_values IN OUT mgmt_metric_value_array, p_metric_guid IN RAW, p_metric_name IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT NULL, p_metric_type IN NUMBER, p_num_keys IN NUMBER, p_metric_columns IN mgmt_namevalue_array, p_result_key_array IN mgmt_medium_string_table, p_store_metric IN NUMBER DEFAULT NULL ) IS l_prev_target_guid mgmt_targets.target_guid%type := NULL; l_target_name mgmt_targets.target_name%type := NULL; l_target_type mgmt_targets.target_type%type := NULL; l_emd_url mgmt_targets.emd_url%type := NULL; l_result_key_value mgmt_metrics_raw.key_value%type ; l_target_sysdate DATE ; l_timezone_region mgmt_targets.timezone_region%type ; l_sysdate_utc DATE := MGMT_GLOBAL.SYSDATE_UTC ; l_current_rec NUMBER := p_metric_values.FIRST ; l_store_metric mgmt_collections.store_metric%type ; l_timestamp DATE ; l_string_value mgmt_metrics_raw.string_value%type ; l_position NUMBER ; l_update_target BOOLEAN := FALSE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data:Enter',G_MODULE_NAME) ; END IF ; WHILE l_current_rec IS NOT NULL LOOP IF p_metric_values(l_current_rec).target_guid != nvl(l_prev_target_guid, p_metric_values(l_current_rec).target_guid||'1') THEN -- Need to be tackled during performance tuning phase -- to see if this can be fetched and stored upfront. -- TODO: Calculate sysdate_utc at start and cast it to target timezone BEGIN SELECT targets.target_name, targets.target_type, targets.timezone_region, targets.emd_url, nvl(cast(systimestamp at time zone targets.timezone_region as date), l_sysdate_utc) INTO l_target_name, l_target_type, l_timezone_region, l_emd_url, l_target_sysdate FROM mgmt_targets targets WHERE targets.target_guid = p_metric_values(l_current_rec).target_guid ; -- Update last load time only for repository targets l_update_target := (l_emd_url IS NULL); EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Invalid Target In metric results (store_metric_data)') ; END ; IF p_store_metric IS NULL THEN BEGIN SELECT store_metric INTO l_store_metric FROM mgmt_collections WHERE object_guid = p_metric_values(l_current_rec).target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND coll_name = p_coll_name ; EXCEPTION WHEN OTHERS THEN l_store_metric := MGMT_GLOBAL.G_TRUE ; END ; ELSE l_store_metric := p_store_metric ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('store_metric_data:Store='||l_store_metric|| ' For target='||p_metric_values(l_current_rec).target_guid ,G_MODULE_NAME); END IF ; END IF ; IF l_store_metric = MGMT_GLOBAL.G_TRUE THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('store_metric_data:Storing record#'||l_current_rec, G_MODULE_NAME) ; END IF ; IF l_update_target THEN UPDATE mgmt_targets SET last_load_time=l_target_sysdate WHERE target_guid = p_metric_values(l_current_rec).target_guid AND (last_load_time < l_target_sysdate OR last_load_time IS NULL); l_update_target := FALSE ; END IF ; l_timestamp := nvl(p_metric_values(l_current_rec).collection_timestamp, l_target_sysdate) ; IF p_metric_values(l_current_rec).status = G_METRIC_EVAL_ERROR THEN store_metric_errors(p_task_id=>NULL, p_target_guids=>mgmt_target_guid_array( p_metric_values(l_current_rec).target_guid), p_metric_guid=>p_metric_guid, p_timezone_region=>l_timezone_region, p_error_message=>p_metric_values(l_current_rec).status_message) ; p_metric_values.DELETE(l_current_rec) ; ELSIF p_metric_type IN (MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE, MGMT_GLOBAL.G_METRIC_TYPE_TABLE) THEN IF p_num_keys >0 THEN l_result_key_value := p_result_key_array(l_current_rec) ; ELSE l_result_key_value := ' ' ; END IF ; FOR col in p_metric_columns.FIRST..p_metric_columns.LAST LOOP l_position := p_metric_values(l_current_rec).index_position (p_metric_columns(col).name) ; -- l_position>0 means the metric column is found IF l_position > 0 THEN l_string_value := p_metric_values(l_current_rec). metric_values(l_position).value ; IF p_metric_columns(col).value IN (MGMT_GLOBAL.G_METRIC_TYPE_NUMBER, MGMT_GLOBAL.G_METRIC_TYPE_REPOS_NUMBER) THEN BEGIN EMD_SCHEMA.ADD_METRIC_DATA (v_target_name=>l_target_name, v_target_type=>l_target_type, v_metric_name=>p_metric_name, v_key_value=>l_result_key_value, v_metric_column=>p_metric_columns(col).name, v_value=>to_number(l_string_value), v_timestamp=>l_timestamp) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; ELSIF p_metric_columns(col).value IN (MGMT_GLOBAL.G_METRIC_TYPE_REPOS_STRING, MGMT_GLOBAL.G_METRIC_TYPE_STRING) THEN BEGIN EMD_SCHEMA.ADD_METRIC_DATA (v_target_name=>l_target_name, v_target_type=>l_target_type, v_metric_name=>p_metric_name, v_key_value=>l_result_key_value, v_metric_column=>p_metric_columns(col).name, v_string_value=> l_string_value, v_timestamp=>l_timestamp) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; END IF ; END IF ; -- position is not null END LOOP ; -- If number of string metric ELSE IF p_metric_values(l_current_rec).metric_values.EXISTS(1) THEN l_string_value := p_metric_values(l_current_rec). metric_values(1).value ; IF p_metric_type IN (MGMT_GLOBAL.G_METRIC_TYPE_REPOS_NUMBER, MGMT_GLOBAL.G_METRIC_TYPE_NUMBER) THEN BEGIN EMD_SCHEMA.ADD_METRIC_DATA (v_target_name=>l_target_name, v_target_type=>l_target_type, v_metric_name=>p_metric_name, v_value=>to_number(l_string_value), v_timestamp=>l_timestamp) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; ELSIF p_metric_type IN (MGMT_GLOBAL.G_METRIC_TYPE_REPOS_STRING, MGMT_GLOBAL.G_METRIC_TYPE_STRING) THEN BEGIN EMD_SCHEMA.ADD_METRIC_DATA (v_target_name=>l_target_name, v_target_type=>l_target_type, v_metric_name=>p_metric_name, v_string_value=>l_string_value, v_timestamp=>l_timestamp) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; END IF ; END IF ; END IF ; END IF ; l_prev_target_guid := p_metric_values(l_current_rec).target_guid ; l_current_rec := p_metric_values.next(l_current_rec) ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('store_metric_data:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||'(store_metric_data)') ; END store_metric_data; -- -- Internal procedure to insert records into mgmt_metrics_raw table -- TBD: will cutover to use the new insertinto metrics raw by perf team -- PROCEDURE store_metric_data(p_metric_raw_records IN mgmt_metric_raw_table, p_key_values IN mgmt_medium_string_table) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data:Enter',G_MODULE_NAME) ; END IF ; IF p_metric_raw_records IS NOT NULL AND p_metric_raw_records.COUNT > 0 THEN -- There is a Implementation restriction on using fields of records -- in bulk binds, so not used. If large number of records are sent -- and performance is not good, then we can convert to multiple scalar -- arrays and use bulk binds -- Right now we assume the trigger is IN place to store data in -- mgmt_string_history and current metrics. Once the trigger is removed -- need to call emd_loader APIS instead of direct insert. FOR i IN p_metric_raw_records.FIRST..p_metric_raw_records.LAST LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('store_metric_data:processing record'||i,G_MODULE_NAME) ; END IF ; BEGIN INSERT INTO MGMT_METRICS_RAW (TARGET_GUID,METRIC_GUID,KEY_VALUE,COLLECTION_TIMESTAMP, VALUE,STRING_VALUE) VALUES (p_metric_raw_records(i).target_guid, p_metric_raw_records(i).metric_guid, p_key_values(i), p_metric_raw_records(i).collection_timestamp, p_metric_raw_records(i).value, p_metric_raw_records(i).string_value ) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE mgmt_metrics_raw SET value = p_metric_raw_records(i).value, string_value = p_metric_raw_records(i).string_value WHERE target_guid = p_metric_raw_records(i).target_guid AND metric_guid = p_metric_raw_records(i).metric_guid AND key_value = p_key_values(i) AND collection_timestamp = p_metric_raw_records(i).collection_timestamp ; END ; END LOOP ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data:Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, SQLERRM||'(store_metric_data)') ; END store_metric_data ; -- -- verify_sql_text -- -- Purpose -- private procedure -- Verifies that the sql test is secure -- -- Parameters -- p_sql_text The sql text needing to be verified -- p_all_columns the columns that are present in the generated select clause PROCEDURE verify_sql_text(p_sql_text IN VARCHAR2) IS l_sql_text mgmt_metrics.source%TYPE := UPPER(LTRIM(p_sql_text,'( ')) ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('verify_sql_text:sql= '|| p_sql_text, G_MODULE_NAME); END IF; IF NOT ( (l_sql_text LIKE 'SELECT%' OR l_sql_text LIKE 'WITH%' ) AND INSTR(l_sql_text,';') = 0 ) THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('verify_sql_text:SQL not starting with SELECT/WITH or includes ;', G_MODULE_NAME) ; END IF; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'verify_sql_text: Invalid SQL Text'); END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('verify_sql_text:exit', G_MODULE_NAME) ; END IF ; END verify_sql_text; -- -- evaluate_sql -- -- Purpose -- private procedure -- Evaluate the sql and gets the results ordered by target_guid -- -- Parameters -- p_task_id : task_id of the collection -- p_target_guid : specific target_guid for which sql has to be evaluated -- p_eval_mode : Evaluation mode -- 1:task_eval_mode : get all targets for the task (p_task_id) -- 2:target_eval_mode:get only for the specified target (p_target_guid) -- 3:verify_mode : get only first row -- p_select_clause: the select clause that has to be prepended to the sql query -- to get it in the format of mgmt_metric_values -- p_sql_text : Original SQL text -- p_metric_values : output result -- PROCEDURE evaluate_sql( p_task_id IN NUMBER := NULL, p_target_guids IN MGMT_TARGET_GUID_ARRAY := NULL, p_eval_mode IN NUMBER, p_select_clause IN VARCHAR2, p_sql_text IN VARCHAR2, p_metric_values OUT NOCOPY mgmt_metric_value_array) IS l_sql VARCHAR2(32767) ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_sql:Enter',G_MODULE_NAME) ; END IF ; -- do security checks on the sql before executing verify_sql_text(p_sql_text=>p_sql_text) ; l_sql := p_select_clause||' from ( '||p_sql_text||')' ; IF p_eval_mode = G_TASK_EVAL_MODE THEN l_sql := l_sql|| g_sql_task ; EXECUTE IMMEDIATE l_sql BULK COLLECT INTO p_metric_values USING p_task_id ; ELSIF p_eval_mode = G_TARGET_EVAL_MODE THEN IF p_target_guids.COUNT = 1 THEN l_sql := l_sql|| g_sql_one_target ; EXECUTE IMMEDIATE l_sql BULK COLLECT INTO p_metric_values USING p_target_guids(1); ELSE l_sql := l_sql|| g_sql_target ; EXECUTE IMMEDIATE l_sql BULK COLLECT INTO p_metric_values USING p_target_guids; END IF ; ELSIF p_eval_mode = G_VERIFY_EVAL_MODE THEN l_sql := l_sql|| g_sql_verify ; EXECUTE IMMEDIATE l_sql BULK COLLECT INTO p_metric_values; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.DEBUG('evaluate_sql:SQL='||l_sql,G_MODULE_NAME) ; EMDW_LOG.INFO('evaluate_sql:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_sql:Exit Exception SQL='||l_sql,G_MODULE_NAME) ; EMDW_LOG.ERROR('evaluate_sql:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||' (evaluate_sql)') ; END evaluate_sql; --evaluate_plsql -- -- Purpose -- private procedure to evaluate plsq/sql source -- Parameters -- p_target_list : list of targets -- p_metric_guid : metric guid for which evaluation is done -- p_metric_info : Metric information -- p_metric_value : output metric values ordered by target_guid -- Exceptions -- MGMT_GLOBAL.COLLECTION_ERR PROCEDURE evaluate_plsql(p_target_list IN mgmt_target_guid_array, p_metric_info IN METRIC_INFO_REC, p_coll_name IN VARCHAR2, p_metric_values OUT NOCOPY mgmt_metric_value_array) IS l_metric_results mgmt_metric_results := mgmt_metric_results() ; l_metric_values mgmt_metric_value_array := mgmt_metric_value_array() ; l_error_message VARCHAR2(1000) ; l_result_count NUMBER := 0 ; l_idx NUMBER ; l_metric_source mgmt_metrics.source%type := EM_CHECK.QUALIFIED_SQL_NAME(p_metric_info.source) ; -- copies the from array to to_array -- increments p_count PROCEDURE copy_array(p_from_array IN OUT NOCOPY mgmt_metric_value_array, p_to_array IN OUT NOCOPY mgmt_metric_value_array, p_count IN OUT NUMBER ) IS l_idx NUMBER ; BEGIN IF p_from_array IS NOT NULL AND p_from_array.COUNT > 0 THEN p_to_array.extend(p_from_array.COUNT) ; l_idx := p_from_array.FIRST ; WHILE l_idx IS NOT NULL LOOP p_count := p_count + 1 ; p_to_array(p_count) := p_from_array(l_idx) ; l_idx := p_from_array.next(l_idx) ; END LOOP ; END IF ; END copy_array ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Evaluate_plsql:Enter',G_MODULE_NAME) ; END IF ; l_idx := p_target_list.FIRST ; p_metric_values := mgmt_metric_value_array() ; IF p_metric_info.source_type = MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL THEN WHILE l_idx IS NOT NULL LOOP l_metric_values := mgmt_metric_value_array() ; EXECUTE IMMEDIATE 'CALL '||l_metric_source|| '(:target_guid,:metric_guid,:coll_name,:metric_values)' USING IN p_target_list(l_idx), IN p_metric_info.eval_metric_guid, IN p_coll_name, OUT l_metric_values ; copy_array(l_metric_values,p_metric_values,l_result_count) ; l_idx := p_target_list.NEXT(l_idx) ; END LOOP ; ELSIF p_metric_info.source_type = MGMT_GLOBAL.G_METRIC_SOURCE_OLD_PLSQL THEN WHILE l_idx IS NOT NULL LOOP EXECUTE IMMEDIATE 'CALL '||l_metric_source|| '(:target_guid,:metric_guid,:metric_values)' USING IN p_target_list(l_idx), IN p_metric_info.eval_metric_guid, OUT l_metric_results ; IF l_metric_results IS NOT NULL AND l_metric_results.COUNT > 0 THEN convert_results(p_target_list(1), p_metric_info.num_keys, p_metric_info.key_cols, l_metric_results,l_metric_values) ; copy_array(l_metric_values,p_metric_values,l_result_count) ; l_metric_results.DELETE ; END IF ; l_idx := p_target_list.NEXT(l_idx) ; END LOOP ; ELSIF p_metric_info.source_type = MGMT_GLOBAL.G_METRIC_SOURCE_BULK_PLSQL THEN EXECUTE IMMEDIATE 'CALL '||l_metric_source|| '(:target_guid,:metric_guid,:coll_name,:metric_values)' USING IN p_target_list, IN p_metric_info.eval_metric_guid, IN p_coll_name, OUT p_metric_values ; END IF ; IF p_metric_values IS NOT NULL AND p_metric_values.COUNT > 0 THEN check_target_list(p_target_list=>p_target_list, p_metric_values=>p_metric_values) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_plsql:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_plsql:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE = MGMT_GLOBAL.COLLECTION_ERR THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, sqlerrm||'(evaluate_plsql)') ; END IF ; END evaluate_plsql; -- is_valid_sql -- -- Purpose: -- Public procedure to validate if a given sql is valid -- for metric evaluation -- Parameters -- p_sql_text : Sql text to be validated -- p_all_columns : metric and key column list -- p_error_message : error message if sql is not valid -- Returns -- TRUE if sql is syntactically valid and -- contains all the columns in the output -- FALSE otherwise -- FUNCTION is_valid_sql(p_sql_text IN VARCHAR2, p_all_columns IN mgmt_short_string_array, p_error_message OUT VARCHAR2 ) RETURN BOOLEAN IS l_select_clause varchar2(4000) ; l_metric_values mgmt_metric_value_array ; l_met_columns mgmt_namevalue_array := mgmt_namevalue_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('is_valid_sql:Enter',G_MODULE_NAME) ; END IF ; IF p_all_columns IS NULL or p_all_columns.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot validate sql, column list is null') ; END IF ; l_met_columns.extend(p_all_columns.count) ; FOR i in p_all_columns.first..p_all_columns.last LOOP l_met_columns(i) := mgmt_namevalue_obj.new(p_all_columns(i),'0') ; END LOOP ; generate_select_clause(p_metric_columns=>l_met_columns, p_select_clause=>l_select_clause) ; evaluate_sql(p_eval_mode=>G_VERIFY_EVAL_MODE, p_select_clause=>l_select_clause, p_sql_text=>p_sql_text, p_metric_values=>l_metric_values) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('is_valid_sql:Exit Normal',G_MODULE_NAME) ; END IF ; RETURN TRUE ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('is_valid_sql:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; -- mask out error messages for security reasons p_error_message := 'Invalid SQL' ; RETURN FALSE ; END is_valid_sql; -- evaluate_metric -- -- Purpose -- Main private procedure to evaluate the metric. -- The public procedures evaluate_metric call this -- Parameters -- p_task_id : task id of the collection if this is called from collections -- p_target_guid : Target guid of specific collection if called from realtime -- p_eval_mode : 1 (task_eval_mode) means use task_id -- 2 (target_eval_mode) means use target_guid -- p_metric_info : Metric information -- p_metric_values: Metric values -- Exceptions -- Captures all exceptions PROCEDURE evaluate_metric(p_task_id IN NUMBER := NULL, p_target_guids IN MGMT_TARGET_GUID_ARRAY := NULL, p_eval_mode IN NUMBER := G_TASK_EVAL_MODE, p_metric_info IN METRIC_INFO_REC, p_coll_name IN VARCHAR2, p_metric_values OUT NOCOPY mgmt_metric_value_array) IS l_target_guid_list mgmt_target_guid_array := mgmt_target_guid_array() ; l_error_message VARCHAR2(256) ; l_start_time DATE := SYSDATE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_metric:Enter Collection='||p_coll_name|| ' mode='||to_char(p_eval_mode), G_MODULE_NAME) ; END IF ; p_metric_values := mgmt_metric_value_array() ; IF p_metric_info.source_type IN (MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL, MGMT_GLOBAL.G_METRIC_SOURCE_OLD_PLSQL, MGMT_GLOBAL.G_METRIC_SOURCE_BULK_PLSQL) THEN IF p_eval_mode = G_TARGET_EVAL_MODE THEN l_target_guid_list := p_target_guids ; ELSE get_target_list(p_task_id,l_target_guid_list) ; END IF ; evaluate_plsql(p_target_list=>l_target_guid_list, p_metric_info=>p_metric_info, p_coll_name=>p_coll_name, p_metric_values=>p_metric_values) ; ELSIF p_metric_info.source_type = MGMT_GLOBAL.G_METRIC_SOURCE_QUERY THEN evaluate_sql(p_task_id=>p_task_id, p_target_guids=>p_target_guids, p_eval_mode=>p_eval_mode, p_select_clause=>p_metric_info.select_clause, p_sql_text=>p_metric_info.source, p_metric_values=>p_metric_values) ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid Parameter:Source Type (evaluate_metric)') ; END IF ; IF p_metric_info.repo_timing_enabled = MGMT_GLOBAL.G_TRUE THEN MGMT_LOG.log_performance (v_job_name_in=>G_MODULE_NAME, v_duration_in=> (SYSDATE - l_start_time)*86400000, v_time_in=>SYSDATE, v_is_total_in=>'Y', v_name_in=>p_metric_info.metric_name, v_value_in=>p_metric_values.COUNT) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN dump_metric_results(p_metric_values=>p_metric_values) ; EMDW_LOG.INFO('evaluate_metric:Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_metric:Exit Exception'||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE IN (MGMT_GLOBAL.COLLECTION_ERR,MGMT_GLOBAL.INVALID_PARAMS_ERR) THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||'(evaluate_metric)') ; END IF ; END evaluate_metric; --evaluate_metric -- --Purpose -- Public procedure called from collection task -- Calls the private procedure with metric information -- Parameters -- p_task_id : task_id of the collection -- p_metric_info : metric information -- p_metric_values : metric values -- PROCEDURE evaluate_metric(p_task_id IN NUMBER, p_metric_info OUT NOCOPY METRIC_INFO_REC, p_metric_values OUT NOCOPY mgmt_metric_value_array, p_coll_name OUT NOCOPY VARCHAR2 ) 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('evaluate_metric(task):Enter',G_MODULE_NAME) ; END IF ; -- The target guid is required for cat props -- metric_guid/collection name will remain same for the task. BEGIN SELECT target_guid, metric_guid, coll_name INTO l_target_guid, l_metric_guid, p_coll_name FROM mgmt_collection_metric_tasks WHERE task_id = p_task_id AND rownum = 1 ; EXCEPTION WHEN NO_DATA_FOUND THEN -- This would happen if the target was deleted -- So we remove the task. EM_TASK.REMOVE_TASK(p_task_id=>p_task_id, p_remove_aq=>FALSE); RETURN ; END ; get_metric_info( p_target_guid=>l_target_guid, p_metric_guid=>l_metric_guid, p_eval_mode=>G_TASK_EVAL_MODE, p_metric_info=>p_metric_info ) ; evaluate_metric(p_task_id=>p_task_id, p_eval_mode=>G_TASK_EVAL_MODE, p_metric_info=>p_metric_info, p_coll_name=>p_coll_name, p_metric_values=>p_metric_values) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_metric(task):Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_metric(task):Exit Exception'||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE IN (MGMT_GLOBAL.COLLECTION_ERR,MGMT_GLOBAL.INVALID_PARAMS_ERR) THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||'(evaluate_metric)') ; END IF ; END evaluate_metric; --evaluate_metric -- --Purpose -- Public procedure called for realtime collections -- Calls the private procedure with metric information -- Parameters -- p_target_guids : target_guids for which metric is to be evaluated -- p_metric_info : metric information -- p_metric_values : metric values -- -- Assumption: at least 1 target guid is present, validated by calling code -- PROCEDURE evaluate_metric(p_target_guids IN MGMT_TARGET_GUID_ARRAY, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_metric_info OUT NOCOPY METRIC_INFO_REC, p_metric_values OUT NOCOPY mgmt_metric_value_array) IS l_metric_guid mgmt_metrics.metric_guid%type ; l_target_type mgmt_targets.target_type%type ; l_type_meta_ver mgmt_targets.type_meta_Ver%type ; l_category_props mgmt_short_string_array := mgmt_short_string_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_metric(target):Enter',G_MODULE_NAME) ; END IF ; p_metric_values := mgmt_metric_value_array() ; get_metric_info( p_target_guid=>p_target_guids(1), p_metric_guid=>p_metric_guid, p_eval_mode=>G_TARGET_EVAL_MODE, p_metric_info=>p_metric_info ) ; evaluate_metric(p_target_guids=>p_target_guids, p_eval_mode=>G_TARGET_EVAL_MODE, p_metric_info=>p_metric_info, p_coll_name=>p_coll_name, p_metric_values=>p_metric_values) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('evaluate_metric(target):Exit Normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_metric(target):Exit Exception'||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE IN (MGMT_GLOBAL.COLLECTION_ERR,MGMT_GLOBAL.INVALID_PARAMS_ERR) THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.COLLECTION_ERR,sqlerrm||'(evaluate_metric)') ; END IF ; END evaluate_metric; -- -- Dummy helper evaluation function for registering repository metrics -- which will never be evaluated: Ex: external metrics -- PROCEDURE dummy_eval_proc(p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_values OUT mgmt_metric_value_array) IS BEGIN NULL ; END dummy_eval_proc; END em_metric_eval; / show err