Home » SQL & PL/SQL » SQL & PL/SQL » Feasibility of dynamic conditional rule (PL/SQL 8.1.7.4.1 and 9.2.0.7.0)
Feasibility of dynamic conditional rule [message #336223] Fri, 25 July 2008 05:16 Go to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I'm trying to work out if I can dynamically test a variables value by constructing an if statement from a table.

For example, if a database field is filled in with an age, I want to obtain a rule from a table and check that criteria against the database field.

For example, a customer age is stored as 60 and their favorite colour is stored as blue.
I have a rule that says we block customers over 55 with a favorite colour of blue. However I need to be able to alter this rule without having to change code (for example to change the colour to red).

I need to take the rule from the table and check it against the applicable database field(s) and determine the outcome.

The rules will be grouped together (over 55 and blue/under 40 and own a parrot - these are just examples by the way, not real data Shocked ).

I've given it a go with dynamic sql but in constructing the statement I have to code in the database field name - drv_age in my example (or I do the way I have tried it).

Can anyone give me options/alternatives on how to take a rule from a table and use purely that rule to check database values for fields named in the rule.

I've attached my small test code that I've been trying. In this example drv_age is my problem as I don't want to specify the field name in the code (I want to get it from my table).

Thanks in advance if anyone can help me.

drop table quote_block_rules
/
create table quote_block_rules 
(block_set number(5),
 tia_field varchar2(50),
 internet_field varchar2(50),
 datatype varchar2(15),
 blocking_text varchar2(500))
/
insert into quote_block_rules 
values (1,'drv_age','drv_age','number','> 60')
/
insert into quote_block_rules 
values (2,'drv_age','drv_age','number','> 70')
/

declare

   cursor main_cur is
   select distinct block_set from quote_block_rules;
   
   cursor block_set(p_block_set in number) is
   select internet_field, blocking_text, datatype
   from quote_block_rules
   where block_set = p_block_set;
   
   drv_age number(2);
   v_test varchar2(1000);
   local_var VARCHAR2(10);
   
begin

   -- drv_age simulates my database field that I need to check
   drv_age := 65;
   
   for main_rec in main_cur
   loop
   
      local_var := 'false';
      
      for block_rec in block_set(main_rec.block_set)
      loop
               
        -- I don't want to have to specify drv_age in this statement?                
         v_test := 'declare ' || block_rec.internet_field || ' ' || block_rec.datatype || '  := ' || drv_age || ';' ||
                   ' begin if ' || block_rec.internet_field || ' ' || block_rec.blocking_text ||
                   ' then :local_var := ''true''; end if; end;';

      end loop;
   
      dbms_output.put_line(v_test);
      execute immediate (v_test) using out local_var;
      dbms_output.put_line(local_var);
      
   end loop;

end;
Re: Feasibility of dynamic conditional rule [message #336227 is a reply to message #336223] Fri, 25 July 2008 05:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I do something similar to that, where I put a formula into a table and then execute that formula with dynamic SQL.

The way I handled variables was to write them in [] in the formula, and then replace them before the execution.

You could do the same here, like :

declare

   cursor main_cur is
   select distinct block_set from quote_block_rules;
   
   cursor block_set(p_block_set in number) is
   select internet_field, blocking_text, datatype
   from quote_block_rules
   where block_set = p_block_set;
   
   drv_age number(2);
   animal VARCHAR2(100);
   color  VARCHAR2(100);
   v_test varchar2(1000);
   local_var VARCHAR2(10);
   
begin

   -- drv_age simulates my database field that I need to check
   drv_age := 65;
   

   
   for main_rec in main_cur
   loop
   
      local_var := 'false';
      
      for block_rec in block_set(main_rec.block_set)
      loop
               
        -- I don't want to have to specify drv_age in this statement?                
         v_test := 'declare ' || block_rec.internet_field || ' ' || block_rec.datatype || '  := [' || block_rec.internet_field || '];' ||
                   ' begin if ' || block_rec.internet_field || ' ' || block_rec.blocking_text ||
                   ' then :local_var := ''true''; end if; end;';

         /* Replace all 'rule Variables' here */
         v_test := REPLACE(v_test,'[drv_age]' ,drv_age);
         v_test := REPLACE(v_test,'[color]'   ,color);
         v_test := REPLACE(v_test,'[animal]'  ,animal);

      end loop;
   
      dbms_output.put_line(v_test);
      execute immediate (v_test) using out local_var;
      dbms_output.put_line(local_var);
      
   end loop;

end;


Re: Feasibility of dynamic conditional rule [message #336236 is a reply to message #336223] Fri, 25 July 2008 06:19 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
That is just what I need, thankyou.
Previous Topic: PL/SQL table VS Nested Table
Next Topic: Record sysdate to TIMESTAMP field in to UTC format
Goto Forum:
  


Current Time: Sun Dec 04 08:17:57 CST 2016

Total time taken to generate the page: 0.09408 seconds