Rem drv: <create type="pkgdefs" pos="collections/collections_meteval_pkgdef.sql+"/>
Rem
Rem $Header: collections_poleval_pkgdef.sql 03-jul-2006.00:06:55 jsadras Exp $
Rem
Rem collections_poleval_pkgdef.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      collections_poleval_pkgdef.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     02/14/06 - add get_set_composite_key for 
Rem                           mgmt_metric_raw_table 
Rem    jsadras     02/07/06 - Enhance evaluate policy direct 
Rem    jsadras     07/17/06 - Backport jsadras_bug-5002887 from main 
Rem    jsadras     07/03/06 - add policy type to policy rec 
Rem    jsadras     07/13/06 - Backport jsadras_bug-5046257 from main 
Rem    niramach    01/09/06 - 10.3 Work start: Add p_config_std_guid to 
Rem                           evaluate_policy to evaluate policies only part 
Rem                           of the given config standard. 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    jsadras     01/05/05 - remove clear_all_violations 
Rem    jsadras     12/29/04 - clear message 
Rem    jsadras     12/22/04 - result_key
Rem    jsadras     12/16/04 - target_list in policy 
Rem    jsadras     11/10/04 - clear_all_violations
Rem    jsadras     10/19/04 - repo_timing
Rem    jsadras     10/14/04 - policy_timing
Rem    jsadras     10/07/04 - policy_rec
Rem    jsadras     10/06/04 - short_string_table
Rem    jsadras     10/01/04 - eval_condition_text
Rem    jsadras     09/23/04 - target_name
Rem    jsadras     09/21/04 - format
Rem    jsadras     09/17/04 - violation_type
Rem    jsadras     09/15/04 - key_column_type
Rem    jsadras     09/02/04 - get_set_composite_key
Rem    jsadras     08/26/04 - 
Rem    jsadras     08/16/04 - expose key_match 
Rem    jsadras     08/13/04 - 
Rem    jsadras     07/24/04 - jsadras_repocollect1
Rem    jsadras     07/06/04 - Created
Rem

CREATE OR REPLACE PACKAGE em_policy_eval
AS

G_MODULE_NAME CONSTANT VARCHAR2(30) := 'EM_POLICY_EVAL' ;

TYPE g_integer_array_table IS TABLE OF mgmt_integer_array ;

TYPE EM_POLICY_EVAL_REC IS RECORD
(
 target_guid  mgmt_targets.target_guid%type,
 next_target_guid mgmt_targets.target_guid%type, 
 policy_guid mgmt_policies.policy_guid%type,
 next_policy_guid mgmt_policies.policy_guid%type,
 policy_name mgmt_policies.policy_name%type,
 policy_type mgmt_policies.policy_type%type,
 message mgmt_policies.message%type ,
 message_nlsid mgmt_policies.message_nlsid%type ,
 clear_message mgmt_policies.clear_message%type ,
 clear_message_nlsid mgmt_policies.clear_message_nlsid%type ,
 repo_timing_enabled mgmt_policies.repo_timing_enabled%type,
 coll_name   mgmt_collections.coll_name%type,
 violation_level mgmt_violations.violation_level%type,
 violation_type mgmt_violations.violation_type%type,
 condition_type mgmt_policies.condition_type%type,
 condition  mgmt_policies.condition%type,
 condition_operator mgmt_policies.condition_operator%type,
 key_value mgmt_policy_assoc_cfg.key_value%type,
 key_operator mgmt_policy_assoc_cfg.key_operator%type,
 is_exception mgmt_policy_assoc_cfg.is_exception%type,
 num_occurrences  mgmt_policy_assoc_cfg.num_occurrences%type,
 evaluation_date date,
 param_values  mgmt_policy_param_val_array, 
 bind_columns  mgmt_namevalue_array,
 key_values    mgmt_medium_string_array
) ;

--evaluate_policy
--
--Purpose
-- To evaluate the policy for a task or for a specific target
-- 
-- Parameters
-- p_task_id  : Task Id of the collection
-- p_metric_guid : Metric guid of the main record of the metric
-- p_coll_name   : Collection Item name
-- p_key_cols    : key Columns of the metric
-- p_met_cols    : Metric column Name and metric type
-- p_metric_values: Metric results to be evaluated
--                  Also used for target_guid
-- p_eval_mode    : TASK_EVAL_MODE (1) uses task_id to get 
--                  policy information
--                  TARGET_EVAL_MODE uses the target_guid in results
--                   to get policy information
-- p_config_std_guid: Configuration standard guid if only policies part of 
--                    config standard should be evaluated. 
--
PROCEDURE evaluate_policy(p_task_id          IN NUMBER,
                          p_metric_info      IN EM_METRIC_EVAL.METRIC_INFO_REC,
                          p_target_guids     IN mgmt_target_guid_array := NULL,
                          p_metric_values    IN mgmt_metric_value_array,
                          p_result_key_array IN mgmt_medium_string_table,
                          p_key_index_array  IN g_integer_array_table,
                          p_eval_mode        IN NUMBER 
                                    DEFAULT  EM_METRIC_EVAL.G_TASK_EVAL_MODE,
                          p_coll_name        IN VARCHAR2 DEFAULT ' ',
                          p_config_std_guid  IN RAW DEFAULT NULL
                         ) ;

--
-- PURPOSE
-- To evaluate policy/thresholds directly for given metric results
-- 
-- PARAMETERS
--
-- p_target_guids : List of targets  for which thresholds have to be evaluated
-- p_metric_guid  : The metric for which threshold needs to be evaluated
-- p_coll_name    : The collection with which the thresholds are evaluated
-- p_metric_values: Metric value array ordered by target_guid
--                  if it is not ordered by target_guid, results will be
--                  erroneous
--
PROCEDURE evaluate_policy(p_target_guids   IN mgmt_target_guid_array,
                          p_metric_guid   IN RAW,
                          p_coll_name     IN VARCHAR2 DEFAULT ' ',
                          p_metric_values IN mgmt_metric_value_array
                         ) ;

--
-- Internal procedure to evaluate thresholds on metric raw records that 
-- are not inserted by agent or repository
-- Example:BAM metrics, External metrics
-- Note: Since we do not have access to overall record for the metric
-- we cannot evaluate complex conditions or store violation context
--
PROCEDURE evaluate_thresholds(p_metric_raw_records IN mgmt_metric_raw_table,
                              p_coll_name          IN VARCHAR2
                             ) ;

-- is_valid_sql
--
-- Purpose
-- Function to verify if the condition text is valid
-- To be used before policy creation since we do not have a 
-- policy guid at that point
-- A condition text is valid iff
-- it is a valid sql expression (where clause)
-- It references only metric column or policy parameters
--
-- Parameters
-- p_condition_Text : Condition text of the policy
-- p_param_columns  : Param Name and Param Datatype array
-- p_metric_columns : metric Column Name and Metric Datatype array
--
-- Return Data:
-- Returns 0 If it is a valid bind
-- Returns 1 if the condition text has invalid syntax
-- Returns 2 if the condition text has bind columns which are not
--           policy parameters or metric columns
-- Returns SQLCODE of the error otherwise
--
-- To reference a param or metric column the column is 
-- prefixed with :, For example :PASSWORD where PASSWORD
-- is a metric column or :MINPASSLEN when MINPASSLEN is
-- a policy param column
-- Sample Condition text=> length(:PASSWORD) < :MINPASSLEN

FUNCTION is_valid_condition(p_condition_text IN VARCHAR2,
                            p_param_columns  IN mgmt_short_string_array,
                            p_metric_columns IN mgmt_short_string_array,
                            p_bind_columns   OUT mgmt_namevalue_array,
                            p_error_message  OUT VARCHAR2
                            )
RETURN BOOLEAN ;

 -- GET_NEW_SEVERITY_CODE
--
-- Purpose
--  Gets the new severity_code/violation_level given current conditions
--  Code taken from collections engine
--  updates the counters p_crit_counter,p_warn_counter and p_info_counter
--  returns new severity code
PROCEDURE get_new_violation_status(p_crit_violation IN BOOLEAN,
                                   p_warn_violation IN BOOLEAN,
                                   p_info_violation IN BOOLEAN,
                                   p_num_occurrences IN NUMBER,
                                   p_last_violation_level  IN NUMBER,
                                   p_crit_counter       IN OUT NUMBER,
                                   p_warn_counter       IN OUT NUMBER,
                                   p_info_counter       IN OUT NUMBER,
                                   p_new_violation_level   OUT NUMBER
                                  ) ;
-- 
-- log violation code exposed for testing purpose
--
PROCEDURE log_violation
            (p_policy_rec           IN EM_POLICY_EVAL_REC,
             p_target_name          IN VARCHAR2,
             p_result_key_value     IN VARCHAR2,
             p_crit_violation       IN BOOLEAN,
             p_warn_violation       IN BOOLEAN,
             p_info_violation       IN BOOLEAN,
             p_metric_info          IN EM_METRIC_EVAL.METRIC_INFO_REC,
             p_metric_value         IN mgmt_metric_value_obj,
             p_value                IN NUMBER,
             p_string_value         IN VARCHAR2,
             p_violation_counter    IN OUT NOCOPY NUMBER,
             p_violation_context    IN OUT NOCOPY mgmt_short_string_array,
             p_construct_context    IN BOOLEAN := TRUE
            ) ;
--
-- evaluate condition text exposed for testing purpose
--
--Parameters
--p_condition_text: The text to be evaluated
--p_param_values  : Parameter values
--p_metric_value  : metric results
--p_bind_columns  : Bind Columns/Types in the condition text
--                  Name will be the name of the bind column
--                  For policy parameters value would be 1
--                  For metric columns value would be 2
--p_metric_columns: Name and metric type of metric columns
--p_crit_violation: TRUE if critical violation ELSE FALSE
--p_warn_violation: TRUE if warning violation ELSE FALSE
--p_info_violation: TRUE if info violation ELSE FALSE
--
PROCEDURE eval_condition_text(p_condition_text IN VARCHAR2,
                              p_param_values   IN  mgmt_policy_param_val_array,
                              p_metric_value   IN  mgmt_metric_value_obj,
                              p_bind_columns   IN  mgmt_namevalue_array,
                              p_metric_columns IN  mgmt_namevalue_array,
                              p_crit_violation OUT BOOLEAN,
                              p_warn_violation OUT BOOLEAN,
                              p_info_violation OUT BOOLEAN) ;
--
-- get_set_composite_key exposed for testing purposes
--
PROCEDURE get_set_composite_key(p_metric_value    IN mgmt_metric_value_obj,
                                p_key_index_array IN mgmt_integer_array,
                                p_num_keys        IN NUMBER,
                                p_composite_key   OUT RAW
                               ) ;
--
-- Procedure to get the key values for mgmt_metric_raw_table object
-- for each row in the object
-- If the key count=0 then key_value=' '
-- if key_count=1 then key_value=key_value(1) from record
-- if key_count>1 then 
--     get the composite key for the key parts
--     if no existing composite key, create
--
PROCEDURE get_set_composite_key(p_metric_raw_records IN mgmt_metric_raw_table,
                                p_key_values         OUT mgmt_medium_string_table
                               ) ;

-- Purpose
-- Initialize the index array which keep tracks of where a key value is 
-- found in each metric result. 
-- For example there are 2 records and the number of keys is 2. 
-- In the first metric_value_obj record ,key 1 is found at position 3 in the 
-- metric_values array, key 2 is found at position 1
-- In the second metric_value_obj record ,key 1 is found at position 2 in the 
-- metric_values array, key 2 is found at position 4
-- key_index_array would contain 2 rows ( number of metric_value_obj records)
-- each row with number of columns = num_keys
-- key_index(1,1)=3 key_index(1,2)=1
-- key_index(2,1)=2 key_index(2,2)=4
--
-- Initialize key value array
-- which stores the key_value (composite_key_value if p_num_keys>1) for each 
-- metric_value record
--
-- Parameters
-- p_metric_values    : Metric values array consists of 1 or more metric_value_obj
-- p_key_columns      : Key column list
-- p_key_index_tab    : key index array
-- p_key_value_tab    : Key values for each record


PROCEDURE initialize_counters(p_metric_values      IN  OUT mgmt_metric_value_array,
                              p_key_columns        IN  mgmt_namevalue_array,
                              p_key_index_array    OUT NOCOPY g_integer_array_table,
                              p_result_key_array   OUT NOCOPY mgmt_medium_string_table
                              ) ;
--
-- key match function exposed for testing purpose.
--
-- Purpose:
-- Match the key defined in policy against metric result
-- Return TRUE if key matches else FALSE
-- 
-- Parameters
-- p_policy_key : Key value defined in the policy configuration
-- p_metric_value : The current metric  value object 
-- p_key_index_array :Array containing positions of key columns
-- p_key_operator : key operator of the configuration
-- p_num_keys : Number of keys for the metrica
--
--Exceptions
-- POLICY_EVAL_ERR
--   Missing key value:if the key column is missing in the record
--   Invalid key value:If there is no policy key configuration
--

FUNCTION key_match(p_policy_key       IN mgmt_medium_string_array,
                   p_metric_value     IN mgmt_metric_value_obj,
                   p_key_index_array  IN mgmt_integer_array,
                   p_key_operator     IN VARCHAR2,
                   p_num_keys         IN NUMBER
                  )
RETURN BOOLEAN ;

END em_policy_eval ;
/

show err