Rem drv: <create type="pkgbodys" pos="ecm/ecm_pkgbodys.sql+"/>
Rem
Rem $Header: esm_pkgbodys.sql 07-dec-2005.04:08:06 dkjain Exp $
Rem
Rem esm_pkgbodys.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      esm_pkgbodys.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    dkjain      12/07/05 - Backport dkjain_bug-4741787 from main 
Rem    dkjain      11/16/05 - 
Rem    dsukhwal    09/07/05 - proc for console home page 
Rem    dkjain      07/28/05 - Adding modify_policy 
Rem    dkjain      08/15/05 - Adding policy_eval for unlimited login policy 
Rem    dsukhwal    08/05/05 - add get_repository_databases 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    dkjain      04/17/05 - Add call to auto_enable_existing_targets 
Rem                           procedure 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    dkjain      01/31/05 - Added clear param 
Rem    dkjain      01/19/05 - Fixed Bug-4128312(added support to old agent 
Rem                           version) 
Rem    dkjain      01/05/05 - Adding author constant 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    dkjain      11/08/04 - Added p_impact_nlsid 
Rem    dkjain      11/07/04 - p_auto_enable param added to the create_policy of esm 
Rem    dkjain      10/08/04 - ESA 10gR2 Impl 
Rem    eujang      09/27/03 - Changed ECM snapshot type to be oracle_security
Rem    eujang      09/04/03 - eujang_esm_init_no_intgr 
Rem    anajmi      08/26/03 - Created
Rem
set echo on;

CREATE OR REPLACE PACKAGE BODY ESM AS

FUNCTION GET_REPOSITORY_TARGETS
RETURN MGMT_USER_GUID_ARRAY 
IS
  rep_guid    RAW(16);
  rep_connect VARCHAR2(1024);
  l_host MGMT_TARGETS.target_name%TYPE := '';
  l_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
  guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY();
  l_this_guid RAW(16);
  l_database_array SMP_EMD_STRING_ARRAY;
  l_found INTEGER :=0;
  l_domain_index INTEGER :=0;
  counter INTEGER  := 0;
  counter1 INTEGER := 0;

BEGIN

  -- Get the target guid of the repository target. There should only be
  -- one repository target but just make sure by using rownum
  SELECT target_guid INTO rep_guid FROM mgmt_targets
  WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND
        rownum = 1;

  BEGIN
    -- Get the ConnectDescriptor of the repository target
    SELECT LOWER(property_value) INTO rep_connect FROM mgmt_target_properties 
    WHERE property_name = 'ConnectDescriptor' 
      AND target_guid = rep_guid;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
      rep_connect := '';
  END;

  -- get targets associated with repository database
  IF rep_connect IS NOT NULL AND LENGTH(rep_connect) > 0
  THEN
    FOR crec IN
      (SELECT /*+ ORDERED INDEX(a) INDEX(b) */
        b.target_guid, b.host_name
        FROM mgmt_target_properties a, mgmt_targets b
        WHERE b.target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE
        AND a.property_name = 'ConnectDescriptor'
        AND LOWER(a.property_value) = rep_connect
        AND a.target_guid = b.target_guid)
    LOOP
      guids.extend(1);
      guids(guids.LAST) := crec.target_guid;
    END LOOP;
  END IF;

  -- Now locate targets monitoring instances and add them to our arrays
  SELECT  LOWER(instance_name) 
    BULK COLLECT INTO  l_database_array
    FROM gv$instance;

  -- Find instance database targets and associated agents
  FOR i IN l_database_array.FIRST..l_database_array.LAST
  LOOP    
    FOR crec IN    
      (SELECT a.target_guid
        FROM mgmt_targets a, 
          mgmt_target_properties b,
          mgmt_target_properties c
        WHERE a.target_type 
              IN (MGMT_GLOBAL.G_DATABASE_TARGET_TYPE,
                  MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE)
          AND a.target_guid = b.target_guid
          AND b.property_name = 'MachineName'
          AND LOWER(b.property_value) LIKE (l_host||'%')
          AND a.target_guid = c.target_guid
          AND c.property_name  = 'SID'
          AND LOWER(c.property_value) = l_database_array(i)
          AND a.target_guid = b.target_guid)
    LOOP
      guids.extend(1);
      guids(guids.LAST) := crec.target_guid;
    END LOOP;
  END LOOP;

  RETURN guids;
 
EXCEPTION
WHEN NO_DATA_FOUND
THEN
  RETURN MGMT_USER_GUID_ARRAY();

END GET_REPOSITORY_TARGETS;

  PROCEDURE CREATE_POLICY(
  --Policies parameters
  p_target_type                 	      		  IN VARCHAR2,
  p_policy_name   		                          IN VARCHAR2,
  p_metric_name    			                  IN VARCHAR2,
  p_policy_label_nlsid       			          IN VARCHAR2 DEFAULT NULL,
  p_description  			                  IN VARCHAR2 DEFAULT ' ',
  p_description_nlsid    			          IN VARCHAR2 DEFAULT NULL,
  p_impact     			                          IN VARCHAR2 DEFAULT ' ',
  p_impact_nlsid 		                          IN VARCHAR2 DEFAULT NULL,
  -- p_clear_message 					  IN VARCHAR2 DEFAULT NULL,
  -- p_clear_message_nlsid 				  IN VARCHAR2 DEFAULT NULL,
  p_recommendation   		                          IN VARCHAR2 DEFAULT ' ',
  p_recommendation_nlsid   			          IN VARCHAR2 DEFAULT NULL,
  p_violation_level    			                  IN NUMBER DEFAULT NULL,
  p_condition_type  		                          IN NUMBER DEFAULT 1,
  p_condition   		                          IN VARCHAR2 DEFAULT NULL,
  p_condition_operator       		                  IN NUMBER DEFAULT 0,
  p_message  			                          IN VARCHAR2 DEFAULT NULL,
  p_message_nlsid      			                  IN VARCHAR2 DEFAULT NULL,
  p_clear_message				          IN VARCHAR2 DEFAULT NULL,
  p_clear_message_nlsid			                  IN VARCHAR2 DEFAULT NULL,

  p_auto_enable 				          IN NUMBER := 1, 
  p_cs_consider_percentage			          IN NUMBER DEFAULT 0,
  p_start_type_meta_ver  				  IN VARCHAR2 DEFAULT '1.0',
  p_end_type_meta_ver    		                  IN VARCHAR2 DEFAULT NULL,
  p_policy_param_list 			                  IN MGMT_POLICY_PARAM_DEF_ARRAY DEFAULT NULL,
  p_dflt_param_val_list   		                  IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL,
  p_viol_ctxt_list     			                  IN MGMT_VIOL_CTXT_DEF_ARRAY DEFAULT NULL
  ) AS

    l_category_list            			          MGMT_CATEGORY_ARRAY;
    l_author           			                  VARCHAR2(256) ;

 BEGIN
  l_category_list := MGMT_CATEGORY_ARRAY(
        MGMT_CATEGORY_OBJ.NEW(
                p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
                p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY));

  l_author := 'ORACLE' ;

  MGMT_POLICY.CREATE_POLICY(
  p_target_type => p_target_type,
  p_policy_name => p_policy_name,
  p_metric_name => p_metric_name,
  p_author =>l_author,
  p_policy_label_nlsid => p_policy_label_nlsid,
  p_description =>  p_description,
  p_description_nlsid => p_description_nlsid,
  p_impact => p_impact,
  p_impact_nlsid => p_impact_nlsid,
  p_recommendation => p_recommendation,
  p_recommendation_nlsid => p_recommendation_nlsid,
  p_violation_level => p_violation_level,
  p_condition_type => p_condition_type,
  p_condition => p_condition,
  p_condition_operator => p_condition_operator,
  p_message => p_message,
  p_message_nlsid => p_message_nlsid,
  p_clear_message => p_clear_message,
  p_clear_message_nlsid	=> p_clear_message_nlsid,
  p_auto_enable => p_auto_enable,
  p_cs_consider_percentage => p_cs_consider_percentage,
  p_start_type_meta_ver => p_start_type_meta_ver,
  p_end_type_meta_ver => p_end_type_meta_ver,
  p_category_list => l_category_list,
  p_policy_param_list => p_policy_param_list,
  p_dflt_param_val_list => p_dflt_param_val_list,
  p_viol_ctxt_list => p_viol_ctxt_list
 );

 COMMIT ;
 IF p_auto_enable = 1
 THEN
    MGMT_POLICY.AUTO_ENABLE_EXISTING_TARGETS(
         p_target_type => p_target_type,
         p_policy_name => p_policy_name );
    COMMIT ;
 END IF ;
 END;
 
 --ESM Modify Policy
  PROCEDURE MODIFY_POLICY(
  --Policies parameters
  p_target_type                 	      		  IN VARCHAR2,
  p_policy_name   		                          IN VARCHAR2,
  p_metric_name    			                  IN VARCHAR2,
  p_policy_label_nlsid       			          IN VARCHAR2 DEFAULT NULL,
  p_description  			                  IN VARCHAR2 DEFAULT ' ',
  p_description_nlsid    			          IN VARCHAR2 DEFAULT NULL,
  p_impact     			                          IN VARCHAR2 DEFAULT ' ',
  p_impact_nlsid 		                          IN VARCHAR2 DEFAULT NULL,
  -- p_clear_message 					  IN VARCHAR2 DEFAULT NULL,
  -- p_clear_message_nlsid 				  IN VARCHAR2 DEFAULT NULL,
  p_recommendation   		                          IN VARCHAR2 DEFAULT ' ',
  p_recommendation_nlsid   			          IN VARCHAR2 DEFAULT NULL,
  p_violation_level    			                  IN NUMBER DEFAULT NULL,
  p_condition_type  		                          IN NUMBER DEFAULT 1,
  p_condition   		                          IN VARCHAR2 DEFAULT NULL,
  p_condition_operator       		                  IN NUMBER DEFAULT 0,
  p_message  			                          IN VARCHAR2 DEFAULT NULL,
  p_message_nlsid      			                  IN VARCHAR2 DEFAULT NULL,
  p_clear_message				          IN VARCHAR2 DEFAULT NULL,
  p_clear_message_nlsid			                  IN VARCHAR2 DEFAULT NULL,

  p_auto_enable 				          IN NUMBER := 1, 
  p_cs_consider_percentage			          IN NUMBER DEFAULT 0,
  p_start_type_meta_ver  				  IN VARCHAR2 DEFAULT '1.0',
  p_end_type_meta_ver    		                  IN VARCHAR2 DEFAULT NULL,
  p_policy_param_list 			                  IN MGMT_POLICY_PARAM_DEF_ARRAY DEFAULT NULL,
  p_dflt_param_val_list   		                  IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL,
  p_viol_ctxt_list     			                  IN MGMT_VIOL_CTXT_DEF_ARRAY DEFAULT NULL
  ) AS

    l_category_list            			          MGMT_CATEGORY_ARRAY;
    l_author           			                  VARCHAR2(256) ;

 BEGIN
  l_category_list := MGMT_CATEGORY_ARRAY(
        MGMT_CATEGORY_OBJ.NEW(
                p_class_name => MGMT_GLOBAL.G_CATEGORY_CLASS_FUNCTIONAL,
                p_category_name => MGMT_GLOBAL.G_FUNC_CATEGORY_SECURITY));

  l_author := 'ORACLE' ;
  MGMT_POLICY.MODIFY_POLICY(
  p_target_type => p_target_type,
  p_policy_name => p_policy_name,
  p_metric_name => p_metric_name,
  p_author =>l_author,
  p_policy_label_nlsid => p_policy_label_nlsid,
  p_description =>  p_description,
  p_description_nlsid => p_description_nlsid,
  p_impact => p_impact,
  p_impact_nlsid => p_impact_nlsid,
  p_recommendation => p_recommendation,
  p_recommendation_nlsid => p_recommendation_nlsid,
  p_violation_level => p_violation_level,
  p_condition_type => p_condition_type,
  p_condition => p_condition,
  p_condition_operator => p_condition_operator,
  p_message => p_message,
  p_message_nlsid => p_message_nlsid,
  p_clear_message => p_clear_message,
  p_clear_message_nlsid	=> p_clear_message_nlsid,
  p_auto_enable => p_auto_enable,
  p_cs_consider_percentage => p_cs_consider_percentage,
  p_start_type_meta_ver => p_start_type_meta_ver,
  p_end_type_meta_ver => p_end_type_meta_ver,
  p_category_list => l_category_list,
  p_policy_param_list => p_policy_param_list,
  p_dflt_param_val_list => p_dflt_param_val_list,
  p_viol_ctxt_list => p_viol_ctxt_list
 );
 COMMIT ;
 END;
 
 PROCEDURE DELETE_POLICY(
  p_target_type VARCHAR2,
  p_policy_name VARCHAR2
 ) AS

 BEGIN
  MGMT_POLICY.DELETE_POLICY(
   p_target_type => p_target_type,
   p_policy_name => p_policy_name
  ); 
  COMMIT ;
 END ;
 PROCEDURE DELETE_POLICY_METADATA(
  p_target_type VARCHAR2,
  p_policy_name VARCHAR2
 ) AS

 BEGIN
  MGMT_POLICY.DELETE_POLICY_METADATA(
   p_target_type => p_target_type,
   p_policy_name => p_policy_name
  ); 
  COMMIT ;
 END ;

    
  PROCEDURE EVAL_POLICY10GR2(
    p_target_guid IN RAW,
    p_metric_guid IN RAW,
    p_coll_name IN VARCHAR2,
    p_metric_values OUT mgmt_metric_Value_array
  ) AS 
  BEGIN
        p_metric_values := mgmt_metric_Value_array() ;
        SELECT 
            mgmt_metric_value_obj.new(
                    target_guid,0 , 
                    'status_message' , 
                    mgmt_namevalue_array(
                        mgmt_namevalue_obj.new('profile', value),
                        mgmt_namevalue_obj.new('limit', value2)
                     )
                 )
        BULK COLLECT INTO p_metric_values 
        FROM esm_collection_latest e
        WHERE property='Unlimited_login_attempts' AND 
              ( (value not in (select profile from dba_users where username=user)) OR  (p_target_guid not in (SELECT DISTINCT * FROM TABLE(CAST(GET_REPOSITORY_TARGETS AS MGMT_USER_GUID_ARRAY))))
              ) AND
        e.target_guid = p_target_guid
        order by target_guid ;
    END EVAL_POLICY10GR2;


  PROCEDURE EVAL_POLICY10GR1(
    p_target_guid IN RAW,
    p_metric_guid IN RAW,
    p_coll_name IN VARCHAR2,
    p_metric_values OUT mgmt_metric_Value_array
  ) AS 
  BEGIN
        p_metric_values := mgmt_metric_Value_array() ;
        SELECT 
            mgmt_metric_value_obj.new(
                    target_guid,0 , 
                    'status_message' , 
                    mgmt_namevalue_array(
                        mgmt_namevalue_obj.new('dbuser', value),
                        mgmt_namevalue_obj.new('dummy', value2)
                     )
                 )
        BULK COLLECT INTO p_metric_values 
        FROM esm_collection_latest e
        WHERE property='Unlimited login attempts' AND 
              ( (value != user) OR  (p_target_guid not in (SELECT DISTINCT * FROM TABLE(CAST(GET_REPOSITORY_TARGETS AS MGMT_USER_GUID_ARRAY))))
              ) AND
        e.target_guid = p_target_guid
        order by target_guid ;
    END EVAL_POLICY10GR1;

    PROCEDURE get_console_homepage_info(
       p_time_period IN VARCHAR2,
       p_ext_viol_cur_out OUT cursorType, --existing violations by severity
       p_new_viol_cur_out OUT cursorType  --new violations in last p_time_period days
    )
    IS
       cnt NUMBER;
       sec_policies GUID_ARRAY;
       CURSOR c IS SELECT p.policy_guid as policy_guid 
                   FROM mgmt_policies p, mgmt_category_map c
                   WHERE p.policy_guid = c.object_guid
                     AND c.category_name = 'Security' 
                     AND c.class_name = 'Functional'
                     AND c.object_type    = 2;
    BEGIN
        OPEN c;
        FETCH c BULK COLLECT INTO sec_policies;
        --current violations by severity
        OPEN p_ext_viol_cur_out FOR
        SELECT
            NVL(SUM(DECODE(e.max_violation_level,18,e.non_exempt_violations_logged,0)),0) information_count, 
            NVL(SUM(DECODE(e.max_violation_level,20, e.non_exempt_violations_logged,0)),0) warning_count,
            NVL(SUM(DECODE(e.max_violation_level,25,e.non_exempt_violations_logged,0)),0) critical_count
        FROM mgmt_policy_assoc_eval_summ e
        WHERE EXISTS
            (SELECT 1 FROM TABLE(CAST (sec_policies AS GUID_ARRAY)) g WHERE g.column_value = e.policy_guid)
          AND EXISTS 
            (SELECT 1 FROM mgmt_targets tgt WHERE tgt.target_guid  = e.target_guid);

        --New violations in the last 24 hrs
        OPEN p_new_viol_cur_out FOR
        SELECT 
           NVL(SUM(DECODE(s.violation_level,18,1,20,1,25,1,0)),0) new
        FROM
            TABLE(CAST (sec_policies AS GUID_ARRAY)) g,
            mgmt_violations s
        WHERE 
          s.policy_guid = g.column_value
          AND s.violation_type = 3 
          AND s.collection_timestamp >= sysdate - p_time_period
          AND EXISTS 
            (SELECT 1 FROM mgmt_targets t WHERE t.target_guid = s.target_guid);
    END get_console_homepage_info;
     
 END ESM;
/
COMMIT ;
show errors;

/*

*/


/*
create or replace package body ESM as


  CONS_SECURITY_POLICY_AUTHOR constant MGMT_POLICY_GROUP.AUTHOR%TYPE:= 'Oracle';
  CONS_SECURITY_POLICY_NAME constant MGMT_POLICY_GROUP.POLICY_NAME%TYPE := 'Security Best Practices';
  CONS_SECURITY_POLICY_VERSION constant MGMT_POLICY_GROUP.VERSION%TYPE:= '1.0';
  CONS_SECURITY_POLICY_CATEGORY constant MGMT_POLICY_RULE.CATEGORY%TYPE := 'Security';
  CONS_SECURITY_SNAPSHOT_TYPE constant MGMT_ECM_SNAPSHOT_METADATA.SNAPSHOT_TYPE%TYPE := 'oracle_security';

-- This procedure creates a single policy for security called CONS_SECURITY_POLICY_NAME.
-- This policy covers all rules from CONS_SECURITY_POLICY_AUTHOR of category CONS_SECURITY_POLICY_CATEGORY.
-- It further registers this policy to execute automatically for a target of any type.
-- This procedure must be invoked AFTER adding a new target type, if the user
-- wishes to define Security policies on this new target.
-- NOTE: Commit is not called automatically.

  procedure CREATE_POLICY as
    policyId MGMT_POLICY_GROUP.POLICY_ID%TYPE;
  begin
	policyId := MGMT_ECM_POLICY.CREATE_POLICY_GROUP(
		CONS_SECURITY_POLICY_AUTHOR,			-- p_author
		CONS_SECURITY_POLICY_NAME,			-- p_policyName
		CONS_SECURITY_POLICY_VERSION,			-- p_policyVersion
		'Common security best practices from Oracle',	-- p_description
								-- p_ruleCriteria
		ECM_POLICY_RULE_CRITERIA(
			ECM_POLICY_RULE_CRITERION(
				CONS_SECURITY_POLICY_AUTHOR, '%', CONS_SECURITY_POLICY_CATEGORY, '%'
			)
		),
								-- p_targetCriteria
		ECM_POLICY_TARGET_CRITERIA(
			ECM_POLICY_TARGET_CRITERION(
				'N', '%', '%', 'LIKE'
			)
		),
		NULL,						-- p_paramList
		NULL						-- p_displayId
		);


     -- Insert this policy id for all snapshots of type CONS_SECURITY_SNAPSHOT_TYPE
     -- for any existing target type associated with this snapshot type
        
	 insert into mgmt_policy_snapshot_criteria
          (target_type, policy_id, snapshot_type) 
          (select distinct(target_type), policyId, snapshot_type
           from mgmt_ecm_snapshot_metadata
	   where snapshot_type = CONS_SECURITY_SNAPSHOT_TYPE);

  end;

  procedure CREATE_RULE(
    test_name VARCHAR2,				-- Name of test
    test_description VARCHAR2,			-- Text description of test
    severity VARCHAR2,				-- Severity: 'C' | 'W' | 'I'
    impact VARCHAR2,				-- Text description of impact of violation
    recommendation VARCHAR2,			-- Text description of recommendation
    target_type VARCHAR2,			-- EM Target Type from MGMT_TARGETS
    esm_metric_name VARCHAR2,			-- Name of metric which collects property below (for future use)
    property_name VARCHAR2,			-- Name of property used by the rule
    value_expression VARCHAR2 := 'VALUE',	-- Expression for the column value shown in UI
    value_expression_type VARCHAR2 := 'T',	-- Type: 'T' for text, 'N' for numeric, 'D' for date    
    property_display_name VARCHAR2 := null,	-- Default display name for the property (used if no i18n)
    value_selection_expression VARCHAR2 := null,-- Further filter using VALUE
    URL VARCHAR2 := null,			-- Not sure what to do with this yet
    rule_display_Id VARCHAR2 := null,   	-- for i18n support
    property_display_Id VARCHAR2 := null	-- for i18n support
  ) as
    ruleText MGMT_POLICY_RULE.RULE_OBJECT%TYPE;
    priority number;
    format number;
    column_name  VARCHAR2(31); --MGMT_ECM_POLICY.ECM_RULE_COLUMN.COLUMN_NAME%TYPE;
    ecm_rule ECM_POLICY_RULE;
  begin

    if (property_display_Id is not null) then
       column_name := property_display_Id;
    else
       column_name := 'EXPRESSION';
    end if;


    ruleText := 'select TARGET_GUID, '||value_expression||
                ' as '||column_name||
		' from esm_collection_latest'||
             	' where PROPERTY = '''||property_name||'''';

    if (value_selection_expression is not null) then
        ruleText := ruleText||' and '||value_selection_expression;
    end if;

    -- Set the severity --
    if (severity = 'C') then
        priority := MGMT_ECM_POLICY.CONS_HIGH;
    elsif (severity = 'W') then
        priority := MGMT_ECM_POLICY.CONS_MEDIUM;
    else
        priority := MGMT_ECM_POLICY.CONS_INFORMATIONAL;
    end if;

    -- Set the format --
    if (value_expression_type = 'N') then
        format := MGMT_ECM_POLICY.CONS_NUMBER_FORMAT;
    elsif (value_expression_type = 'D') then
        format := MGMT_ECM_POLICY.CONS_DATE_FORMAT;
    else
        format := MGMT_ECM_POLICY.CONS_TEXT_FORMAT;
    end if;


    ecm_rule := MGMT_ECM_POLICY.CREATE_POLICY_RULE(
                    CONS_SECURITY_POLICY_AUTHOR, 	-- p_author
		    target_type,   			-- p_targetType
                    CONS_SECURITY_POLICY_CATEGORY, 	-- p_category
                    test_name,    			-- p_ruleName
                    CONS_SECURITY_POLICY_VERSION, 	-- p_ruleVersion
                    test_description,   		-- p_description
                    'QUERY',    			-- p_ruleType
                    ruleText,    			-- p_ruleObject
                    'rownum > 0',   			-- p_test
                    priority,    			-- p_priority
                    impact,    				-- p_impactOfProblem
                    recommendation,   			-- p_recommendation
					  		-- p_columnList
                    ECM_RULE_COLUMN_LIST(
                      ECM_RULE_COLUMN(column_name, 'Y', 'Y', URL,
				      NVL(property_display_name, 'Expression'),
				      format
                                     )
                    ),
                    NULL,    				-- p_paramList
     		    rule_display_Id,   			-- p_displayId
      		    NULL,    				-- p_updateComment
      		    true);    				-- p_replaceIfExists

  -- For debugging purposes, print out the exact text of the rule
  -- dbms_output.put_line('Rule [' || ruleText || ']');

  end;


end ESM;
/
show errors;
*/