-------------------------------------------------------------------------------
---File:        $RCSfile: jrrule.jpb $
---Author:      $Author: wstallar $
---Date:        $Date: 1998/12/16 15:50:10 $
---Version:     $Revision: 1.6 $
---Status:      $State: Exp $
---Locked_by:   $Locker: wstallar $
---Description: <Enter global description here>
---Notes:       <Global notes>
---RealAuth:    <Full real name of code author>
---IncepDate:   <nnth Xxxember 1998>
---Copyright:   (c) Oracle Corporation 1998.  All Rights Reserved.
-------------------------------------------------------------------------------
---Log of Changes from RCS
-------------------------------------------------------------------------------
--- $Log: jrrule.jpb $
--- Revision 1.6  1998/12/16  15:50:10  wstallar
---   Moved code for parsing text based lists of rules (specs) from JR_WORKAREA
---  Added code to allow comments to be included in specs.
---
--- Revision 1.5  1998/09/25  08:48:56  wstallar
--- Fixed a number of compile errors.
---
--- Revision 1.4  1998/09/25  08:28:18  wstallar
--- Added more API methods for manipulationg user-defined rules.
--- Added method to test validity of user-defined rule SQL.
---
--- Revision 1.3  1998/09/17  15:58:20  cwilliam
--- added newline at end of file to avoid > in sqlplus when running script
---
--- Revision 1.2  1998/09/17  13:11:56  cwilliam
--- added prompt describing the package
---
---Revision 1.1  1998/09/17  10:31:47  wstallar
---Initial revision
---
-------------------------------------------------------------------------------
--- 

prompt Package Body: jr_rule

create or replace package body jr_rule is
  -------------------------------------------
  --- Private member function declarations
  -------------------------------------------
  function test_sql(rule_sql in varchar2
                   ,param1_name in varchar2
				   ,param2_name in varchar2
				   ,param3_name in varchar2) return BOOLEAN; 				   
                   
  function newline return varchar2;
  function cr      return varchar2;

  procedure strip_comments(spec       in out varchar2
                          ,start_char in     varchar2
                          ,stop_char  in     varchar2);
                          
  function find_char(string in varchar2
                    ,CHR    in varchar2
					,n      in pls_integer := 1) return number;
					
  function validate_object_name(param_val in varchar2) return boolean;
  function validate_branch_name(param_val in varchar2) return boolean;
  
  --------------------------------------------------------
  --- functions to return characters used to delimit
  --- parameters in text based rule specifications
  --- (can't use package variables because of 
  ---  pragama problems)
  --------------------------------------------------------
  function params_start return varchar2
  is
  begin
   return params_start_char;
  end;  
  function params_end return varchar2
  is
  begin
   return params_end_char;
  end;
  function params_sep return varchar2
  is
  begin
   return params_sep_char;
  end;

  --------------------------------------------------------
  --- get_IRID()
  --- Gets the IRID of a rule from its name. 
  --------------------------------------------------------
  function get_IRID(rule_name in varchar2) return number
  is
   rule_irid number;
  begin
    select IRID 
	  into rule_irid
	  from I$SDD_RULES
	 where name=rule_name;
	
	return rule_irid;
  exception
   when NO_DATA_FOUND then
    --Rule named %0!s does not exist
    rmmes.post('CDR',1010,rule_name);
	return NULL;
  end;

  --------------------------------------------------------
  --- get_param_names()
  --- Gets the names of a rules parameters from its irid. 
  --------------------------------------------------------
 procedure get_param_names(rule_id in number
                          ,param_name1 out varchar2
                          ,param_name2 out varchar2
                          ,param_name3 out varchar2)
  is
  begin
   begin
    select name
      into param_name1
      from I$SDD_RULE_PARAMS
     where rule_irid=rule_id
       and param_seq=1;
    exception
     when NO_DATA_FOUND then
      param_name1:=NULL;
    end;   
    
    begin
     select name
       into param_name2
       from I$SDD_RULE_PARAMS
      where rule_irid=rule_id
        and param_seq=2;
    exception
     when NO_DATA_FOUND then
      param_name2:=NULL;
    end;      
    
    begin
     select name
       into param_name3
       from I$SDD_RULE_PARAMS
      where rule_irid=rule_id
        and param_seq=3;
    exception
     when NO_DATA_FOUND then
      param_name3:=NULL;
    end;   
  end;
                 
  --------------------------------------------------------
  --- get_nls_param_names()
  --- Gets the NLS names of a rules parameters from its irid. 
  --------------------------------------------------------
  procedure  get_nls_param_names(rule_id in number
                                ,nls_param_name1 out varchar2
                                ,nls_param_name2 out varchar2
                                ,nls_param_name3 out varchar2)
  is
  begin
   begin
    select nls_name
      into nls_param_name1
      from I$SDD_RULE_PARAMS
     where rule_irid=rule_id
       and param_seq=1;
    exception
     when NO_DATA_FOUND then
      nls_param_name1:=NULL;
    end;   
    
    begin
     select nls_name
       into nls_param_name2
       from I$SDD_RULE_PARAMS
      where rule_irid=rule_id
        and param_seq=2;
    exception
     when NO_DATA_FOUND then
      nls_param_name2:=NULL;
    end;      
    
    begin
     select nls_name
       into nls_param_name3
       from I$SDD_RULE_PARAMS
      where rule_irid=rule_id
        and param_seq=3;
    exception
     when NO_DATA_FOUND then
      nls_param_name3:=NULL;
    end;   
  end;
  
  --------------------------------------------------------
  --- get_param_values()
  --- Gets the values of parameters defined against
  --- spec entry for a particular rule,
  --- from the rule irid and the entry seq
  --------------------------------------------------------
 procedure get_param_values(entry_id in number
                           ,rule_id  in number
                           ,param_value1 out varchar2
                           ,param_value2 out varchar2
                           ,param_value3 out varchar2)
  is
  begin
   begin
    select value
      into param_value1
      from I$SDD_RULE_PARAM_VALUES
     where rule_irid=rule_id
       and entry_seq=entry_id
       and param_seq=1;
    exception
     when NO_DATA_FOUND then
      param_value1:=NULL;
    end;   
    
   begin
    select value
      into param_value2
      from I$SDD_RULE_PARAM_VALUES
     where rule_irid=rule_id
       and entry_seq=entry_id
       and param_seq=2;
    exception
     when NO_DATA_FOUND then
      param_value2:=NULL;
    end;   
    
   begin
    select value
      into param_value3
      from I$SDD_RULE_PARAM_VALUES
     where rule_irid=rule_id
       and entry_seq=entry_id
       and param_seq=3;
    exception
     when NO_DATA_FOUND then
      param_value3:=NULL;
    end;   
  end;
  --------------------------------------------------------
  --- get_param_type()
  --- Returns the type of a rule parameter
  --------------------------------------------------------
  function get_param_type(rule_id in number
                         ,param_name in varchar2) return varchar2
  is
    param_type varchar2(30);
  begin
    select type
      into param_type
      from I$SDD_RULE_PARAMS
     where rule_irid=rule_id
       and name=param_name;
       
     return param_type;
  end;
  
  --------------------------------------------------------
  -- validate_param()
  -- Validates the supplied value for the given rule
  -- and parameter. Will post errors and raise exceptions.
  -- For date parameters, passes back the supplied date in
  -- the standard rule format (DD-MON-YYYY HH24:MI:SS) 
  --------------------------------------------------------
  procedure validate_param(rule_id in number
                          ,param_seq in number
						  ,param_val in out varchar2)
  is
   rule_name      varchar2(30);
   param_type     varchar2(30);
   param_nls_name varchar2(60);
  begin
   select ru.name,par.type,par.nls_name
     into rule_name,param_type,param_nls_name
     from I$SDD_RULES ru
	     ,I$SDD_RULE_PARAMS par
    where par.rule_irid=ru.irid
	  and par.param_seq=validate_param.param_seq
	  and ru.irid=rule_id;
	  
	--Ensure parameter value is no longer than permitted maximum (300)
	if LENGTHB(param_val) > 300 then
	 --Value for parameter %0!s on rule %1!s is too long
	 rmmes.post('CDR',1065,param_nls_name,rule_name);
	 raise RULE_ERROR;
	end if;
	if param_type = 'OBJECT_NAME' then
	 if not validate_object_name(param_val) then
	  --Rule %1!s has an invalid path (%0!s) for %2!s parameter 
	  rmmes.post('CDR',1059,param_val,rule_name,param_nls_name);
	  raise RULE_ERROR;
	 end if;
	elsif param_type = 'DATE' then
	 if not validate_date(param_val) then
	  --Rule %1!s has an invalid date value (%0!s) for %2!s parameter
      rmmes.post('CDR',1040,param_val,rule_name,param_nls_name);
	  raise RULE_ERROR;
	 end if;
	elsif param_type = 'BRANCH_NAME' then
	 if not validate_branch_name(param_val) then
	  --Rule %1!s has an invalid branch name (%0!s) for %2!s parameter 
	  rmmes.post('CDR',1060,param_val,rule_name,param_nls_name);
	  raise RULE_ERROR;
	 end if;
	end if;
  end;
	  
  --------------------------------------------------------
  --- validate_object_name()
  --- Validates that an object name parameter is a valid
  --- repository path to an object visible to the current
  --- user.
  --------------------------------------------------------
  function validate_object_name(param_val in varchar2) return boolean
  is
   objects  jr_name.objver_list:=jr_name.objver_list();
   i        pls_integer;
   param_ok boolean:=TRUE;
   ov_state varchar2(1);
  begin
   --When validating path, disable access rights checking, as spec
   --may contain valid entries which current user does not have
   --access on (B1293640)
   jr_context.disable(JR_CONTEXT.ACCESS_RIGHTS);
   --Attempt to recover the object using the given path
   objects:=jr_name.get_objects(param_val);
   jr_context.enable(JR_CONTEXT.ACCESS_RIGHTS);
   if objects.last is null then
    --Couldn't find an object - path is invalid
    return FALSE;
   else
    --Found object, path is OK, so long as object(s) are checked in
	i:=objects.first;
	while i is not null loop
	 select ov.state
	   into ov_state
	   from I$SDD_OBJECT_VERSIONS ov
	  where ov.ivid=objects(i).ivid;
	  
	 if ov_state!='I' then
	  --%0!s is not checked in
	  rmmes.post('CDR',1063,jr_name.get_path(objects(i).ivid,'NAME'));
	  param_ok:=FALSE;
	 end if;
	 i:=objects.next(i);
	end loop;
    return param_ok;
   end if;
  exception
   when OTHERS then
    --If the naming service threw an exception,
	--the path is definitely invalid!
    jr_context.enable(JR_CONTEXT.ACCESS_RIGHTS);
	return FALSE;
  end;
   
  --------------------------------------------------------
  --- validate_branch_name()
  --- Validates that a branch name parameter is a valid
  --- name of a branch label visible to the current
  --- user.
  --------------------------------------------------------
  function validate_branch_name(param_val in varchar2) return boolean
  is
   branch_id number;
  begin
   branch_id:=jr_version.get_branch_id(param_val);
   if branch_id is null then
    return FALSE;
   else
    return TRUE;
   end if;
  exception
   when OTHERS then
    --If the version service threw an exception,
	--the path is definitely invalid!
	return FALSE;
  end;
  --------------------------------------------------------
  --- validate_date()
  --- Validates that a date parameter value is in a format
  --- that the rules package understands. Passes back the
  --- supplied date in the standard format 
  --- (DD-MON-YYYY HH24:MI:SS)
  --- Returns TRUE if date is in valid format.
  --------------------------------------------------------
  function validate_date(param_val in out varchar2) return boolean
  is
    param_date date;
  begin
    --Dates should be DD-MON-YYYY HH24:MI:SS, but we will convert
    -- from DD-MON-YYYY, and the current database default   
    begin
     --Use RRRR year format to prevent dates like 25-DEC-99
     --being intepreted as 25-DEC-0099
     param_date:=TO_DATE(param_val,'DD-MON-RRRR HH24:MI:SS');
     param_val:=TO_CHAR(param_date,'DD-MON-YYYY HH24:MI:SS');
     return TRUE;
    exception
     when OTHERS then
      begin
       --Now try DD-MON-YYYY, and convert to DD-MON-YYYY HH24:MI:SS
       param_val:=TO_CHAR(TO_DATE(param_val,'DD-MON-RRRR'),'DD-MON-YYYY HH24:MI:SS');
       return TRUE;
      exception
       when OTHERS then
        --Now try current default format, and convert to DD-MON-YYYY HH24:MI:SS
        param_val:=TO_CHAR(TO_DATE(param_val),'DD-MON-YYYY HH24:MI:SS');
        return TRUE;
      end;
     end;
   exception
    when OTHERS then
     --Couldn't convert, so return false
     return FALSE;
   end;
  
  --------------------------------------------------------
  --- populate_config()
  --- Puts an object set into the given configuration.
  --- Checks to prevent clashes occuring.
  --- If configuration is not empty, and the object set is
  --- for an exclude rule, then we delete any config members
  --- in the set.
  --------------------------------------------------------
  procedure populate_config (config_ivid  in number
                            ,exclude_rule in varchar2 default 'N') is
   ov_irid     number;
   ov_ivid     number;
   config_irid number;
   config_kind varchar2(32);
   el_tab      varchar2(40);
   object_name varchar2(300);
   can_sel     varchar2(1);
   owning_fol  number;

   i           integer;
   clashes     number;
   dummy       pls_integer;
  begin
    --Get the config irid and kind
    begin
       select co.irid, co.kind
         into config_irid, config_kind
         from I$SDD_CONFIGURATIONS co
        where co.ivid=populate_config.config_ivid;
    exception
     when NO_DATA_FOUND then
      --Configuration with IVID = %0!s does not exist.
      rmmes.post('CDR',104,TO_CHAR(config_ivid));
      raise EXECUTE_ERROR;
    end;
    
    --Check that we can alter configuration...
    jr_configuration_def.check_config_for_update(config_irid,config_ivid);

    i:=object_set.first;
    
    while i is not null 
    loop
      ov_irid:=object_set(i).irid;
      ov_ivid:=object_set(i).ivid;
 
      rmdbg.trace('ov_irid('||i||') = '||ov_irid);
      rmdbg.trace('ov_ivid('||i||') = '||ov_ivid);

      begin
       if exclude_rule='Y' 
       and ((config_kind is null) or (config_kind!='SYSTEM')) 
       then
         --If object set is for exclude rule, then remove appropriate
         --config memebers. NOTE: SYSTEM configs are used to maintain
         --lists of object versions to include/exclude from workareas, 
         --so SHOULD be populated for exclude rules.
         --Note we perform delete directly to avoid additional 
         --checks made in jr_configuration_def.remove_member
         delete I$SDD_CONFIGURATION_MEMBERS cm
         where cm.config_ivid=populate_config.config_ivid
         and cm.object_ivid=ov_ivid;
         
         rmdbg.trace('deleting members');
       else
         if NOT jr_version.is_checked_in(ov_irid,ov_ivid) 
         then
          object_name:=jr_name.get_CRN(ov_ivid,'NAME');
          --Cannot add %0!s to configuration. Version not checked in.
          rmmes.post('CDR',1042,object_name);
          RAISE EXECUTE_ERROR;
         end if;
         
         --Ensure we don't insert any configurations in the object set 
         --into the configuration
         select tab.name
         into el_tab
         from rm_sql_tables tab
             ,I$SDD_OBJECT_VERSIONS ov
         where ov.ivid=ov_ivid
         and tab.id=ov.table_irid;
         
         rmdbg.trace('rm_sql_tables.name = '||el_tab);
            
         if el_tab != 'SDD_CONFIGURATIONS' 
         then
            --Check we have SELECT access on element before adding
            if el_tab='SDD_FOLDERS' then
             owning_fol:=ov_irid;
            else
             owning_fol:=jr_acc_Rights.get_owning_container(ov_irid);
            end if;
            
            rmdbg.trace('owning_fol = '||owning_fol);
            rmdbg.trace('USER = '||USER);

            begin
             select 1
             into dummy 
             from SDW_ACCESS_RIGHTS 
             where object_reference = owning_fol
             and grantee_reference in (USER,'PUBLIC');
             
             can_sel:='Y';
            exception
             when NO_DATA_FOUND then
              can_sel:='N';
             when TOO_MANY_ROWS then
              can_sel:='Y';
            end;

            if can_sel='Y' 
            then
              rmdbg.trace('inserting config_irid '||populate_config.config_irid);
              rmdbg.trace('inserting config_ivid '||populate_config.config_ivid);
              rmdbg.trace('inserting object_irid '||ov_irid);
              rmdbg.trace('inserting object_ivid '||ov_ivid);
              
              -- B3499782 : Since only one version of each object is allowed in a configuration
              --            (enforced by index UK_SDD_CFGMBR_NO_DUP_VERS) we need to delete existing
              --            versions before insertion.
              --
              delete I$SDD_CONFIGURATION_MEMBERS 
              where  config_ivid = populate_config.config_ivid
              and    object_irid = ov_irid;
                
               -- We perform insert directly to avoid additional checks made in jr_configuration_def.add_member
               --
               insert into I$SDD_CONFIGURATION_MEMBERS
                   (config_irid
                   ,config_ivid
                   ,object_irid
                   ,object_ivid)
               values
                  (populate_config.config_irid
                  ,populate_config.config_ivid
                  ,ov_irid
                  ,ov_ivid);
                  
                rmdbg.trace('insert complete');
            end if;--can_sel
         end if;--not a config
       end if;--not an exclude rule
      exception
       -- If we're trying to insert a duplicate object into the
       -- configuration, ignore the error, and skip this object
       when DUP_VAL_ON_INDEX then
         rmdbg.trace('DUP_VAL_ON_INDEX');
         null;
      end;

      --Next object in the set...
      i:=object_set.next(i);
      
    end loop;
    
    --Maintain OBJ_NOTM for configuration
    update I$SDD_OBJECT_VERSIONS o
    set    o.obj_notm = o.obj_notm + 1
    where  o.ivid     = config_ivid;
    
  end;

        

  --------------------------------------------------------
  --- is_select_statement()
  --- Determines whether a given sql statement is a select
  --- statement.
  --------------------------------------------------------
  function is_select_statement (sql_statement in varchar2) return boolean 
  is
   pos integer;
  begin
    pos:=INSTR(UPPER(sql_statement),'SELECT ');
    if (pos > 0) then
      return TRUE;
    else
      return FALSE;
    end if;
  end;

  -------------------------------------------------------------
  --- build_proc_call()
  --- PRIVATE METHOD: Builds the SQL to call a rule  which is 
  --- based on a function in the jr_rule_procs package.
  -------------------------------------------------------------
  function build_proc_call(i_rule_irid in number
                          ,workarea_id in number) return varchar2
  is
    sql_statement varchar2(3000);
    rule_name     varchar2(30);
    param_name    varchar2(30);
    finished      boolean := FALSE;
    param_num     integer := 1;
  begin
  
  --Build anonymous PL/SQL block that looks like:
  --(for populating configs):
  --BEGIN
  --  jr_rule.object_set:=jr_rule_procs.RULE_NAME(:param1,:param2,:param3);
  --  jr_rule.populate_config(:config,:exclude_rule);
  --END;
  --
  --(for refreshing workareas):
  --BEGIN
  --  jr_rule.object_set:=jr_rule_procs.RULE_NAME(:param1,:param2,:param3);
  --  jr_rule.sort_object_set;
  --  jr_workarea.refresh_from_rule(:seq,WA_IRID,:exclude_rule);
  --END;
  
   sql_statement:='BEGIN' ||
                  ' jr_rule.object_set:=jr_rule_procs.';
  
   --Add the rule (and hence the function) name to the sql              
   select ru.name
     into rule_name
     from I$SDD_RULES ru
    where ru.irid=i_rule_irid;
    
   sql_statement:=sql_statement||rule_name;
  
   --Now add bind variables for each parameter
   while not finished loop
     begin
      select param.name
        into param_name
        from I$SDD_RULE_PARAMS param
       where param.rule_irid=i_rule_irid
         and param.param_seq=param_num;
     exception 
      when NO_DATA_FOUND then
       finished:=TRUE;
     end;
     
     if NOT finished then
       if param_num=1 then 
         sql_statement:=sql_statement||'('||':'||param_name;
       else 
         sql_statement:=sql_statement||','||':'||param_name;
       end if;
       
       param_num:=param_num+1;
     else
       --Finished, so close the bracket, if neccesary
       if param_num>1 then
         sql_statement:=sql_statement||')';
        end if;
     end if;
   end loop;
   
   sql_statement:=sql_statement||';';
   
   --Add the last part of the statement - either populate config, or
   --refresh workarea...
   if workarea_id is null then
    sql_statement:=sql_statement||
                   '  jr_rule.populate_config(:config,:exclude_rule);'||
                   ' END;';
   else
    sql_statement:=sql_statement||
	               '  jr_rule.sort_object_set;'||
                   '  jr_workarea.refresh_from_rule(:seq'||
                                                   ','||TO_CHAR(workarea_id)||
                                                   ',:exclude_rule);'||
                   ' END;';
   end if;
   return sql_statement;
  end; 
    
  
   

  -------------------------------------------------------------
  --- execute_rule()
  --- Wrapper for execute_rule that accepts names rather than
  --- irid/ivids.
  -------------------------------------------------------------
  procedure execute_rule(rule_name   in varchar2
                        ,config_name in varchar2
                        ,param1 in varchar2 := NULL
                        ,param2 in varchar2 := NULL
                        ,param3 in varchar2 := NULL) 
  is
  rule_irid   number;
  config_ivid number;
  begin
    rule_irid:=get_IRID(rule_name);
    config_ivid:=jr_name.get_VID(config_name);
   
    if (rule_irid is NULL) or (config_ivid is NULL) then
      --Error occured whilst executing rule %0!s
      rmmes.post('CDR',108,TO_CHAR(rule_irid));
      raise EXECUTE_ERROR;
    end if;
     
    execute_rule(rule_irid
                ,config_ivid
     			,param1
 			    ,param2
 			    ,param3);
  end;			   
  
  -------------------------------------------------------------
  --- execute_rule()
  --- execute a given rule using the supplied parameters
  --- The results will be stored in the specified configuration
  --- or used to refresh the specified worakrea.   
  -------------------------------------------------------------
  procedure execute_rule(rule_irid   in number
                        ,config_ivid in number
                        ,param1 in varchar2 := NULL
                        ,param2 in varchar2 := NULL
                        ,param3 in varchar2 := NULL
                        ,workarea_id in number := NULL
                        ,entry_seq   in number := NULL) is

   rule_sql            varchar2(3000);
   rule_name           varchar2(30);
   rule_text           varchar2(1000);
   param_name          varchar2(30);
   is_exclude_rule     varchar2(1);
   refresh_wa          boolean;
   is_select_statement boolean:=TRUE;
   
   l_param1 varchar2(3000);
   l_param2 varchar2(3000);
   l_param3 varchar2(3000);
   
   finished   boolean := FALSE;
   param_num  integer := 1;
   date_valid boolean;
   
   cid          integer;
   exec         integer;
   ov_irid      number;
   ov_ivid      number;

  begin    
    rmdbg.trace('---------------JR_RULE.EXECUTE_RULE STARTED-------------');
    rmdbg.trace('rule_irid  ='||TO_CHAR(rule_irid));
    rmdbg.trace('config_ivid='||TO_CHAR(config_ivid));
    rmdbg.trace('param1     ='||param1);
    rmdbg.trace('param2     ='||param2);
    rmdbg.trace('param3     ='||param3); 
    rmdbg.trace('workarea_id='||TO_CHAR(workarea_id));
    rmdbg.trace('entry_seq  ='||TO_CHAR(entry_seq));
    
	--Initialize the object set
    object_set:=jr_name.objver_list();
    
    --If workarea id is supplied, then rule is to be used to refresh a 
    --workarea, rather than populate a config
    if workarea_id is not null then
     refresh_wa:=TRUE;
    else
     refresh_wa:=FALSE;
    end if;
    
    rmdbg.trace('Getting rule info'); 
    
    --Get hold of the rule info
    begin
       select ru.name,ru.rule_sql,ru.exclude_rule
       into rule_name,rule_sql,is_exclude_rule
       from I$SDD_RULES ru
       where ru.irid = rule_irid; 
    exception
     when NO_DATA_FOUND then 
      --Rule with IRID = %0!s does not exist.
      rmmes.post('CDR',105,TO_CHAR(rule_irid));
      raise EXECUTE_ERROR;
    end;
    
    l_param1:=param1;
    l_param2:=param2;
    l_param3:=param3;
    
    --If no sql is supplied for the rule, then it must be a procedure call.
    --Build the sql up based on the rule definition.
    if rule_sql is null 
    then
      rule_sql:=build_proc_call(rule_irid,workarea_id);
	    is_select_statement:=FALSE;
    else
      --Otherwise, it is based on a SQL statement - ensure we get
      --rows back in order of IVID.
     rule_sql:=rule_sql||' ORDER BY OV_IVID';
     is_select_statement:=TRUE;
    end if;
   
    --Open the cursor
    cid:=dbms_sql.open_cursor;
    --Parse the SQL
    begin
       dbms_sql.parse(cid,rule_sql,dbms_sql.NATIVE);
    exception
     when OTHERS then
     --Rule IRID: %0!s. Error parsing SQL: %1!s
     rmmes.post('CDR',106,TO_CHAR(rule_irid),SQLERRM);
     raise EXECUTE_ERROR;
    end;

    --Define the columns
    if (is_select_statement) then
      dbms_sql.define_column(cid,1,ov_irid);
      dbms_sql.define_column(cid,2,ov_ivid);
    end if;

  rmdbg.trace('Binding variables'); 
	begin
     --Bind any parameters...
     while not finished 
     loop
     
      begin
        select param.name 
         into param_name
         from I$SDD_RULE_PARAMS param
        where param.rule_irid=execute_rule.rule_irid
          and param.param_seq=param_num;
       exception
        when NO_DATA_FOUND then
          finished:=TRUE;
       end;
       
       --Ensure that parameters are valid
       if not finished then
         --l_param values will get converted to standard date format for date parameters
         if param_num=1 then
          validate_param(rule_irid,1,l_param1);
         elsif param_num=2 then
          validate_param(rule_irid,2,l_param2);
         elsif param_num=3 then
          validate_param(rule_irid,3,l_param3);
         end if;
       end if;

       --This code needs changing when we adapt interface to 
       --allow more than 3 parameters... 
       if not finished 
       then
        rmdbg.trace('Binding :'||param_name);
        
        if param_num = 1 then
         dbms_sql.bind_variable(cid,':'||param_name,l_param1);
        elsif param_num = 2 then
         dbms_sql.bind_variable(cid,':'||param_name,l_param2);
        elsif param_num = 3 then
         dbms_sql.bind_variable(cid,':'||param_name,l_param3);
        end if;
        
        param_num:=param_num+1;
       end if;
     end loop;  --Bind any parameters..
     
     --Need to bind variables for config_ivid and exclude_rule if rule uses procedure...
     if (not is_select_statement) 
     then
      if workarea_id is NULL 
      then
       --Populating config
       dbms_sql.bind_variable(cid,':config',config_ivid);
       dbms_sql.bind_variable(cid,':exclude_rule',is_exclude_rule);
      else
       --Refreshing workarea
       dbms_sql.bind_variable(cid,':seq',entry_seq);
       dbms_sql.bind_variable(cid,':exclude_rule',is_exclude_rule);
      end if;
     end if;
          
  exception
	 when RULE_ERROR then
      --Don't post error for something we've already handled
      RAISE;
	 when OTHERS then
	  --Rule: %0!s. Error binding variables: %1!s
	  rmmes.post('CDR',107,rule_name,SQLERRM);
	  raise EXECUTE_ERROR;
	end;
  --Bind the variables
  
  rmdbg.trace('Executing SQL:');
  rmdbg.trace(SUBSTR(rule_sql,1,250));
  rmdbg.trace(SUBSTR(rule_sql,251,250));
  rmdbg.trace(SUBSTR(rule_sql,501,250));
  rmdbg.trace(SUBSTR(rule_sql,751,250));
	
  --Execute the query, and get the rows
  exec:=dbms_sql.execute(cid);
    
    if (is_select_statement) 
    then
      --Rule is a SQL statement, we need to process the data returned...
      rmdbg.trace('Fetching rows from SQL statement');
	   
      while dbms_sql.fetch_rows(cid)!=0 loop
        -- Get the data
        dbms_sql.column_value(cid,1,ov_irid);
        dbms_sql.column_value(cid,2,ov_ivid); 

        -- Add to object set
        object_set.extend();
        object_set(object_set.last).irid:=ov_irid;
        object_set(object_set.last).ivid:=ov_ivid;   
      end loop;
      
      rmdbg.trace(TO_CHAR(object_set.COUNT)||' rows fetched');
      --Close the cursor
      dbms_sql.close_cursor(cid);
      rmdbg.trace('Cursor closed');
      --Put the rule object set into the configuration, or use to refresh
      --workarea, as apropriate...
      if workarea_id is null then
       populate_config(config_ivid,is_exclude_rule);
      else
       jr_workarea.refresh_from_rule(entry_seq
                                    ,workarea_id
                                    ,is_exclude_rule);
      end if;
    else
      --Close the cursor
      dbms_sql.close_cursor(cid);
      rmdbg.trace('Cursor closed');
    end if;
    
    --We've finished with the object set...
    rmdbg.trace('Emptying object set'); 
    object_set:=jr_name.objver_list();
    rmdbg.trace('---------------JR_RULE.EXECUTE_RULE COMPLETED-------------');

  exception
    when NO_DATA_FOUND then
     --Query found no objects - close the cursor and finish.
     dbms_sql.close_cursor(cid);
    when EXECUTE_ERROR then
     --We already handled the error - close the cursor pass on exception
     if DBMS_SQL.IS_OPEN(cid) then
      DBMS_SQL.CLOSE_CURSOR(cid);
     end if;
     RAISE;
    when OTHERS then
      --Something more serious happened - close the cursor and raise an error.
      dbms_sql.close_cursor(cid);	  
	  --Build up complete rule text (rule name and parameters)
	  rule_text:=rule_name||'(';
	  if param1 is not null then
	   rule_text:=rule_text||param1;
	  end if;
	  if param2 is not null then
	   rule_text:=rule_text||','||param2;
	  end if;
	  if param3 is not null then
	   rule_text:=rule_text||','||param3;
	  end if;
	  rule_text:=rule_text||')';
	  --Don't display oracle error message user defined exception was raised
	  if SQLCODE=1 then
  	   --Error occured whilst executing rule %0!s
	   rmmes.post('CDR',108,rule_text);
      else
	   --Error occured whilst executing rule %0!s %1!s
	   rmmes.post('CDR',108,rule_text,SQLERRM);
	  end if;
	  raise EXECUTE_ERROR;
  end;

  -------------------------------------------------------------
  --- sort_object_set()
  --- Sorts the object set produced by a rule into order of 
  --- ascending ivid.
  -------------------------------------------------------------  
  procedure sort_object_set 
  is
   this_irid number;
   this_ivid number;
   i pls_integer; 
   sorted boolean:=FALSE;
  begin
   rmdbg.trace('---------------JR_RULE.SORT_OBJECT_SET STARTED-------------');
   while not sorted loop
    i:=object_set.first;
	sorted:=TRUE;
	while i < object_set.last loop
	 this_irid:=object_set(i).irid;
	 this_ivid:=object_set(i).ivid;
	 if this_ivid > object_set(i+1).ivid then
	  --Current ivid greater than next in set, swap them over
	  object_set(i).irid:=object_set(i+1).irid;
	  object_set(i).ivid:=object_set(i+1).ivid;
	  object_set(i+1).irid:=this_irid;
	  object_set(i+1).ivid:=this_ivid;
	  --set isn't in sorted order...
	  sorted:=FALSE;
	 end if;
	 i:=object_set.next(i);
	end loop;
   end loop;
   rmdbg.trace('---------------JR_RULE.SORT_OBJECT_SET COMPLETED-------------');   
  end;

---------------------------------------------------------
--- Methods for user-defined rules
---------------------------------------------------------

  -------------------------------------------------------
  --- jr_rule.CREATE_RULE()
  --- Create a user-defined rule with the given name and
  --- parameters. Return the IRID of the new rule.
  ------------------------------------------------------- 
  function create_rule(rule_name       in varchar2 
                      ,parameter_name1 in varchar2 
                      ,parameter_name2 in varchar2 
                      ,parameter_name3 in varchar2 
                      ,param_nls_name1 in varchar2 
                      ,param_nls_name2 in varchar2 
                      ,param_nls_name3 in varchar2 
                      ,rule_sql        in varchar2
					  ,exclude         in char default 'N'
                      ,param1_type     in varchar2 default NULL
                      ,param2_type     in varchar2 default NULL
                      ,param3_type     in varchar2 default NULL) return number
  is
   new_irid number;
   u_rule_name   varchar2(30);
   u_param_name1 varchar2(30);
   u_param_name2 varchar2(30);
   u_param_name3 varchar2(30);
   u_param1_type varchar2(30);
   u_param2_type varchar2(30);
   u_param3_type varchar2(30);
   dummy number;
  begin
	
    --Put rule and parameter names into upper case
    u_rule_name  :=UPPER(SUBSTR(rule_name,1,30));
    u_param_name1:=UPPER(SUBSTR(parameter_name1,1,30));
    u_param_name2:=UPPER(SUBSTR(parameter_name2,1,30));
	u_param_name3:=UPPER(SUBSTR(parameter_name3,1,30));
    u_param1_type:=UPPER(SUBSTR(param1_type,1,30));
    u_param2_type:=UPPER(SUBSTR(param2_type,1,30));
	u_param3_type:=UPPER(SUBSTR(param3_type,1,30));

	
    select COUNT(*) into dummy
	from I$SDD_RULES
	where name=u_rule_name;
	
    if dummy > 0 then
	  --Rule with name %0!s already exists
	  rmmes.post('CDR',1023,u_rule_name);
	  raise RULE_ERROR;
	end if;

    --Ensure that the rule SQL is valid...
    if test_sql(rule_sql
	           ,u_param_name1
			   ,u_param_name2
			   ,u_param_name3) = FALSE then
	  --Invalid SQL statement for rule: %0!s
	  rmmes.post('CDR',1025,rule_sql);
	  return NULL;
	end if;

    --Get sequence number for new rule.
    new_irid:=jr_util.get_new_irid;
	
    insert into I$SDD_RULES
	(IRID
    ,NAME 
	,USER_DEFINED
	,EXCLUDE_RULE
	,RULE_SQL)
	values
	(new_irid
    ,u_rule_name
	,'Y'
	,exclude
	,rule_sql);

    if u_param_name1 is not null then    
     insert into I$SDD_RULE_PARAMS
      (RULE_IRID
      ,PARAM_SEQ
      ,NAME
      ,NLS_NAME
      ,TYPE)
     values
      (new_irid
      ,1
      ,u_param_name1
      ,param_nls_name1
      ,u_param1_type);
    end if;
    
    if u_param_name2 is not null then
     insert into I$SDD_RULE_PARAMS
      (RULE_IRID
      ,PARAM_SEQ
      ,NAME
      ,NLS_NAME
      ,TYPE)
     values
      (new_irid
      ,2
      ,u_param_name2
      ,param_nls_name2
      ,u_param2_type);
    end if;

    if u_param_name3 is not null then
     insert into I$SDD_RULE_PARAMS
      (RULE_IRID
      ,PARAM_SEQ
      ,NAME
      ,NLS_NAME
      ,TYPE)
     values
      (new_irid
      ,3
      ,u_param_name3
      ,param_nls_name3
      ,u_param3_type);
    end if;

    return new_irid;	
  end;
  
  -------------------------------------------------------
  --- jr_rule.TEST_SQL()
  --- Private function to test that the specified rule SQL
  --- and parameters are valid.
  --- Returns TRUE if OK, false if errors occured.
  -------------------------------------------------------   
  function test_sql(rule_sql in varchar2
                   ,param1_name in varchar2
				   ,param2_name in varchar2
				   ,param3_name in varchar2) return BOOLEAN
  is
  cid         number;
  ov_irid     number;
  ov_ivid     number;
  config_ivid number;
  begin
  
    if rule_sql is NULL then 
     --No rule SQL specified, then this is 
     --a method based rule, no SQL to check
     return TRUE;
    end if;
   
    --Open a cursor
    cid:=dbms_sql.open_cursor;

    --Parse the SQL
	begin
     dbms_sql.parse(cid,rule_sql,dbms_sql.NATIVE);
	exception
	 when OTHERS then
	 --Could not parse rule SQL, Oracle error: %0!s
	 rmmes.post('CDR',1026,SQLERRM);
	 return FALSE;
	end;

    --Define the columns
    if (is_select_statement(rule_sql)) then
	 begin
      dbms_sql.define_column(cid,1,ov_irid);
      dbms_sql.define_column(cid,2,ov_ivid);
	 exception
	  when OTHERS then
	   --Invalid columns defined in rule SQL select statement.
	   rmmes.post('CDR',1027);
	   return FALSE;
	 end;
    end if;
	
    --Need to bind variable for config_ivid if rule uses procedure...
    if (not is_select_statement(rule_sql)) then
     begin
      dbms_sql.bind_variable(cid,':config_ivid',config_ivid);
	 exception
	   when OTHERS then
		--Could not bind config_ivid variable in rule SQL procedure call.
		rmmes.post('CDR',1028);
  	    return FALSE;
	 end;
    end if;
	
    --Bind any parameters...
    if param1_name is not null then
     begin
      dbms_sql.bind_variable(cid,':'||param1_name,'TEST');
	 exception	  
     when OTHERS then
	  --Could not bind parameter %0!s in rule SQL statement.
	  rmmes.post('CDR',1029,param1_name);
	  return FALSE;	   
	 end;
    end if;  
    if param2_name is not null then
     begin
      dbms_sql.bind_variable(cid,':'||param2_name,'TEST');
	 exception
	 when OTHERS then
	  --Could not bind parameter %0!s in rule SQL statement.
	  rmmes.post('CDR',1029,param2_name);
	  return FALSE;
	 end;
    end if;  
    if param3_name is not null then
	 begin
      dbms_sql.bind_variable(cid,':'||param3_name,'TEST');
	 exception	  
	 when OTHERS then
	  --Could not bind parameter %0!s in rule SQL statement.
	  rmmes.post('CDR',1029,param3_name);
	  return FALSE;	   
	 end;
    end if;  
	
    --Rule SQL statement appears to be OK...
	return TRUE;
  end;
		
  ----------------------------------------------------------------
  -- jr_rule.RENAME_RULE() 
  -- Renames an existing user defined rule.
  ---------------------------------------------------------------- 
  procedure rename_rule(old_name in varchar2 
                       ,new_name in varchar2)
  is
   u_new_rule_name varchar2(30);
   l_user_defined  char(1);
   dummy number;

  begin
    --Put rule name into upper case
    u_new_rule_name:=UPPER(SUBSTR(new_name,1,30));
	
    --Get current details of rule
   begin
	select user_defined
	  into l_user_defined
   	  from I$SDD_RULES
	 where name=UPPER(old_name);
   exception
    when NO_DATA_FOUND then
	 --Rule named %0!s does not exist
     rmmes.post('CDR',1010,old_name);
	 raise RULE_ERROR;
   end;
   
    --Check that rule is user-defined   
    if l_user_defined = 'N' then
	  --Rule %0!s is not a user-defined rule.
	  rmmes.post('CDR',1024,old_name);
	  raise RULE_ERROR;
	end if;
	
    --Check new name does not already exist.
    select COUNT(*) into dummy
	from I$SDD_RULES 
	where name=u_new_rule_name;
	
    if dummy > 0 then 
	  --Rule with name %0!s already exists
	  rmmes.post('CDR',1023,u_new_rule_name);
	  raise RULE_ERROR;
	end if;
	 
    --Do the update. 
    update I$SDD_RULES
	   set name=u_new_rule_name
	 where name=old_name;
  end;
  ------------------------------------------------------------------
  --- jr_rule.CHANGE_SQL() 
  --- Changes the SQL of the specified rule, and the parameter names
  --- if supplied.
  ------------------------------------------------------------------
  procedure change_sql(rule_name in varchar2
                      ,new_sql   in varchar2
					  ,param1_name     in varchar2 default NULL
                      ,param1_nls_name in varchar2 default NULL
                      ,param1_type     in varchar2 default NULL
					  ,param2_name     in varchar2 default NULL
                      ,param2_nls_name in varchar2 default NULL
                      ,param2_type     in varchar2 default NULL
					  ,param3_name     in varchar2 default NULL
                      ,param3_nls_name in varchar2 default NULL
                      ,param3_type     in varchar2 default NULL
					  ,exclude         in char default 'N')                       
  is
  rule_id number;
  begin
    rule_id:=get_IRID(rule_name);
	if rule_id is NULL then
	  --Couldn't find rule with supplied name
	  raise RULE_ERROR;
	end if;
	change_sql(rule_id
              ,new_sql
		      ,param1_name     
              ,param1_nls_name 
              ,param1_type     
			  ,param2_name     
              ,param2_nls_name 
              ,param2_type     
  		      ,param3_name     
              ,param3_nls_name 
              ,param3_type     
			  ,exclude);
  end;
					  
  ------------------------------------------------------------------
  --- jr_rule.CHANGE_SQL() 
  --- Changes the SQL of the specified rule, and the parameter names
  --- if supplied.
  ------------------------------------------------------------------
  procedure change_sql(rule_id in number
                      ,new_sql in varchar2
					  ,param1_name     in varchar2 default NULL
                      ,param1_nls_name in varchar2 default NULL
                      ,param1_type     in varchar2 default NULL
					  ,param2_name     in varchar2 default NULL
                      ,param2_nls_name in varchar2 default NULL
                      ,param2_type     in varchar2 default NULL
					  ,param3_name     in varchar2 default NULL
                      ,param3_nls_name in varchar2 default NULL
                      ,param3_type     in varchar2 default NULL
					  ,exclude         in char default 'N')                       
  is
   u_param1_name varchar2(30);
   u_param2_name varchar2(30);
   u_param3_name varchar2(30);
   old_param_name1 varchar2(30);
   old_param_name2 varchar2(30);
   old_param_name3 varchar2(30);  
   old_param_nls_name1 varchar2(60);    
   old_param_nls_name2 varchar2(60);
   old_param_nls_name3 varchar2(60);   
   old_param_type1 varchar2(30);
   old_param_type2 varchar2(30);
   old_param_type3 varchar2(30);      
   l_user_defined char(1);
  begin                      
    --Put parameter names into upper case
    u_param1_name:=UPPER(SUBSTR(param1_name,1,30));
    u_param2_name:=UPPER(SUBSTR(param2_name,1,30));
	u_param3_name:=UPPER(SUBSTR(param3_name,1,30));
	
   --Get current details of rule
   begin
	select user_defined
	  into l_user_defined
   	  from I$SDD_RULES
	 where irid=rule_id;
   exception
    when NO_DATA_FOUND then
	 --Rule with IRID = %0!s does not exist.
     rmmes.post('CDR',105,TO_CHAR(rule_id));
	 raise RULE_ERROR;
   end;
   
   --Get the old paramter names, nls names and types...
   get_param_names(rule_id,old_param_name1,old_param_name2,old_param_name3);
   get_nls_param_names(rule_id,old_param_nls_name1,old_param_nls_name2,old_param_nls_name3);
   old_param_type1:=get_param_type(rule_id,old_param_name1);
   old_param_type2:=get_param_type(rule_id,old_param_name2);
   old_param_type3:=get_param_type(rule_id,old_param_name3);      
   
    --Check that rule is user-defined   
    if l_user_defined = 'N' then
	  --Rule %0!s is not a user-defined rule.
	  rmmes.post('CDR',1024,TO_CHAR(rule_id));
	  raise RULE_ERROR;
	end if;
		 	
    --Ensure that the new rule SQL is valid...
    if test_sql(new_sql
	           ,u_param1_name
			   ,u_param2_name
			   ,u_param3_name) = FALSE then
	  --Invalid rule SQL statement : %0!s
	  rmmes.post('CDR',1025,new_sql);
	  raise RULE_ERROR;
	end if;
	
    --Do the update. 
    update I$SDD_RULES
	   set rule_SQL=new_SQL	  		  
	 where irid=rule_id;

    --Update parameters if they already exsisted, 
    --otherwise, insert new parameters.
    if old_param_name1 is not null then 
      update I$SDD_RULE_PARAMS
         set name     = NVL(u_param1_name,old_param_name1)
            ,nls_name = NVL(param1_nls_name,old_param_nls_name1)
            ,type     = NVL(param1_type,old_param_type1) 
       where rule_irid=rule_id
         and param_seq=1;
    elsif u_param1_name is not null then
     insert into I$SDD_RULE_PARAMS
      (PARAM_SEQ
      ,RULE_IRID
      ,NAME
      ,NLS_NAME
      ,TYPE)
     values
      (1
      ,rule_id
      ,u_param1_name
      ,param1_nls_name
      ,param1_type);
    end if;

    if old_param_name2 is not null then 
      update I$SDD_RULE_PARAMS
         set name     = NVL(u_param2_name,old_param_name2)
            ,nls_name = NVL(param2_nls_name,old_param_nls_name2)
            ,type     = NVL(param2_type,old_param_type2) 
       where rule_irid=rule_id
         and param_seq=2;
    elsif u_param1_name is not null then
     insert into I$SDD_RULE_PARAMS
      (PARAM_SEQ
      ,RULE_IRID
      ,NAME
      ,NLS_NAME
      ,TYPE)
     values
      (2
      ,rule_id
      ,u_param1_name
      ,param2_nls_name
      ,param2_type);
    end if;
    
    if old_param_name3 is not null then 
      update I$SDD_RULE_PARAMS
         set name     = NVL(u_param3_name,old_param_name3)
            ,nls_name = NVL(param3_nls_name,old_param_nls_name3)
            ,type     = NVL(param3_type,old_param_type3) 
       where rule_irid=rule_id
         and param_seq=3;
    elsif u_param1_name is not null then
     insert into I$SDD_RULE_PARAMS
      (PARAM_SEQ
      ,RULE_IRID
      ,NAME
      ,NLS_NAME
      ,TYPE)
     values
      (3
      ,rule_id
      ,u_param3_name
      ,param3_nls_name
      ,param3_type);
    end if;  
  end;

  ------------------------------------------------------------------
  --- jr_rule.DELETE_RULE() 
  --- Deletes a user defined rule 
  ------------------------------------------------------------------
  procedure delete_rule(rule_name in varchar2) 
  is
    rule_irid number;
  begin
    rule_irid:=get_IRID(rule_name);
	if rule_irid is NULL then
	  --Couldn't find rule with supplied name
	  raise RULE_ERROR;
	end if;
	delete_rule(rule_irid);
  end;
  ------------------------------------------------------------------
  --- jr_rule.DELETE_RULE() 
  --- Deletes a user defined rule 
  ------------------------------------------------------------------  
  procedure delete_rule(rule_irid in number) 
  is
     l_user_defined char(1);
     rule_name varchar2(30);
  begin
    --Check if rule is user-defined
	select name, user_defined 
	  into rule_name,l_user_defined
	  from I$SDD_RULES
	 where IRID=rule_irid;

	if l_user_defined = 'N' then
	  --Rule %0!s is not a user-defined rule.
	  rmmes.post('CDR',1024,rule_name);
	  raise RULE_ERROR;
	end if;
	
    --Delete the rule.
    delete I$SDD_RULES
     where irid=rule_irid;
	 
  exception
    when NO_DATA_FOUND then
	  --Rule with IRID = %0!s does not exist.
	  rmmes.post('CDR',105,TO_CHAR(rule_irid));
	  raise RULE_ERROR;
  end;
  
  -----------------------------------------------------------------------------
  -- jr_rule.FIND_CHAR()
  -- PRIVATE HELPER METHOD 
  -- 
  -- Get hold of n th non-escaped instance of specified character in supplied
  -- string. Escape character is '^'
  -- 
  function find_char(string in varchar2
                    ,CHR    in varchar2
					,n      in pls_integer := 1) return number
  is
   char_pos pls_integer;
   prev_pos pls_integer:=0;
   escape_char varchar2(1);
   dummy varchar2(1);
   i     pls_integer:=0;
  begin
  jr_name.special_chars(escape_char
                       ,dummy
                       ,dummy
                       ,dummy
                       ,dummy
                       ,dummy
                       ,dummy
                       ,dummy
                       ,dummy
                       ,dummy);
                       
   while i < n loop
    --Get hold of the character pos (if any)
    char_pos := INSTR(string,CHR,prev_pos+1);
    --Ignore type characters preceded by an escape character...
    while (char_pos > 0)
	   and (SUBSTR(string,char_pos-1,1)=escape_char) loop
	    char_pos := INSTR(string,CHR,char_pos+1);
    end loop;
    if char_pos = 0 then
	 --failed to find the character
	 EXIT;
	else
	 --look for next non-escaped occurance
     i:=i+1;
	 prev_pos:=char_pos;
	end if;
   end loop;
   return char_pos;
  end;
    
  -----------------------------------------------------------------------------
  -- jr_rule.PARSE_SPEC()
  --
  -- Recover a rule and its parameters from a text-based list.
  -- Foramat is RULE_NAME(PARAM1_VALUE,PARAM2_VALUE,PARAM3_VALUE)
  -- Comments may be incuded using 'REM','//','--' and '/*' '*/'
  
  procedure parse_spec(spec   in out varchar2
                      ,rule   out varchar2
                      ,param1 out varchar2
                      ,param2 out varchar2
                      ,param3 out varchar2) 
   is
    open_bracket  number;
    close_bracket number;
    first_comma   number;
    second_comma  number;
    entry         varchar2(2000);
    white_space   varchar2(6):=' '||newline||cr;
   begin
     --Remove any comments from the spec
     strip_comments(spec,'/*','*/');
     strip_comments(spec,'REM ',newline);
     strip_comments(spec,'--',newline);
     strip_comments(spec,'//',newline);
     --Trim any leading or trailing white space from the spec
     spec:=LTRIM(spec,white_space);
     spec:=RTRIM(spec,white_space);

     open_bracket :=find_char(spec,params_start);
     close_bracket:=find_char(spec,params_end);

     --Check the format of the entry
     if ((open_bracket = 0)
       or(close_bracket= 0)
       or(open_bracket > close_bracket)) then
       --Invalid format for rule spec entry %0!s.
       rmmes.post('CDR',1008,spec);
       raise SPEC_PARSE_ERROR;
     end if;
     
     --Strip the leading spec entry from the given spec.
     entry:=SUBSTR(spec,1,close_bracket);
     spec :=SUBSTR(spec,close_bracket+1,LENGTH(spec));
     --Get the rule name for the entry
     open_bracket:=find_char(entry,params_start);
     close_bracket:=find_char(entry,params_end);
     rule :=UPPER(SUBSTR(entry,1,open_bracket-1));
     
     if (rule is NULL) OR close_bracket=0 then
       --Invalid format for rule spec entry
       rmmes.post('CDR',1008,entry);
       raise SPEC_PARSE_ERROR;
     end if;

     --Get the parameters for the entry
     first_comma  :=find_char(entry,params_sep);
     second_comma :=find_char(entry,params_sep,2);
     --If couldn't find comma, we're at the last parameter, get the final bracket
     if first_comma = 0 then
       first_comma:=close_bracket;
     elsif second_comma = 0 then
       second_comma:=close_bracket;
     end if;
     --First parameter
     if first_comma > 0 then
       param1:=SUBSTR(entry,open_bracket+1,((first_comma-1)-(open_bracket)));
       --Second parameter
       if second_comma > 0 then
         param2:=SUBSTR(entry,first_comma+1,((second_comma-1)-(first_comma)));
         --Third parameter
         if close_bracket > second_comma then
           param3:=SUBSTR(entry,second_comma+1,((close_bracket-1)-(second_comma)));
         end if;
       end if;
     end if;
   end;
   
  -----------------------------------------------------------------------------
  -- jr_rule.NEWLINE()
  --
  -- Private method that returns appropriate single newline character for the 
  -- character set of the database
  --
  function newline return varchar2
  is
   nl_code    NUMBER := 10;      -- Newline ASCII code
   nl_char    varchar2( 5 );     -- Newline ASCII character
   language   varchar2( 50 );    -- NLS Language
   tocharset  varchar2( 50 );    -- Character set to covert to
  begin
    --Retrieve character version of ASCII code for newline 
    nl_char := CHR( nl_code );

    --Get the charset for the database
    language := USERENV( 'LANGUAGE' );
    tocharset := SUBSTR( language, INSTR( language, '.' ) +1 );

    --Convert ASCII newline to db character set newline 
    nl_char := CONVERT( nl_char, 'US7ASCII', tocharset );

    return nl_char;
  end;

  -----------------------------------------------------------------------------
  -- jr_rule.CR()
  --
  -- Private method that returns appropriate single carriage return character 
  -- for the character set of the database
  --
  function cr return varchar2
  is
   cr_code    NUMBER := 13;      -- Newline ASCII code
   cr_char    varchar2( 5 );     -- Newline ASCII character
   language   varchar2( 50 );    -- NLS Language
   tocharset  varchar2( 50 );    -- Character set to covert to
  begin
    --Retrieve character version of ASCII code for newline 
    cr_char := CHR( cr_code );

    --Get the charset for the database
    language := USERENV( 'LANGUAGE' );
    tocharset := SUBSTR( language, INSTR( language, '.' ) +1 );

    --Convert ASCII newline to db character set newline 
    cr_char := CONVERT( cr_char, 'US7ASCII', tocharset );

    return cr_char;
  end;

  -----------------------------------------------------------------------------
  -- jr_rule.STRIP_COMMENTS()
  --
  -- Private method to remove comments from a text based list of
  -- rules.
  
  procedure strip_comments(spec       in out varchar2
                          ,start_char in     varchar2
                          ,stop_char  in     varchar2)
  is
   comment_start number;
   comment_stop  number;
   left_str      varchar2(30000);
   right_str     varchar2(30000);
  begin
   --Find the start of the first comment
   comment_start:=INSTR(spec,start_char);
   
   while (comment_start > 0) loop
     --Find the end of the comment.
     comment_stop:=INSTR(spec,stop_char,comment_start);
     --If no stop_char, then comment comprises of the rest of the spec
     if (comment_stop = 0) then
       comment_stop:=LENGTH(spec);
     end if;
     --Bit before comment
     left_str:=SUBSTR(spec,1,comment_start-1);
     --Bit after comment
     right_str:=SUBSTR(spec,comment_stop+LENGTH(stop_char),LENGTH(spec));
     --Re-assemble spec from bits either side of comment.
     spec:=left_str||right_str;
     --Find start of next comment
     comment_start:=INSTR(spec,start_char);
   end loop;
 end;	 
end;

/   

---  
--- ----------------------------------------------------------------------------
--- $$Header_is_done
---  End of file $RCSfile: jrrule.jpb $
--- ----------------------------------------------------------------------------