Link to home
Start Free TrialLog in
Avatar of mahjag
mahjag

asked on

table based rules

I have a datawarehouse report that sends to the internal users a list of cases for each month and they are divided based on severity. Severity A cases will have to respond within a day and severity B cases has to be responded by 2-3 days. I want to convert this as table based rules so that if and when a B case happen to be A it is easily configurable. My design for this to implement a table that has type A or B and then rules having id 1,2 and then third column will be the actual sql statement. Then I will have a package that will read this table and execute the sql statement (execute immediate) to produce results.

Is there any other better way to design the above requirement?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Typically storing SQL in tables to be executed later isn't a real good design.

I'm not fully understanding the end result here but how different are the two queries in their final form?
Avatar of mahjag

ASKER

the first query might have more join conditions and filter data conditions than the second one. Also if there is a new rule to be added it is easy to configure that to a table once it is identified by adding a sql to that rule and adding the actual sql statement to the table. This is what I thought the usefulness of the table based rule design
I have designed a couple of table based rules engines before and they are pretty tricky.  Not the same as what you describe, but similar.  I would not store the full SQL in the table.

Your PL/SQL package needs to know how to join the tables together.  Then your table stores the rules.  Each rule could have multiple rows.  Each row would detail which table to join in and what the where clause is, if any (remember the base package knows how to do the join).

The PL/SQL package has to have a known place to start from and then adds to its base query to give you the results you want.

What you end up with is extremely flexible, but you will probably end up sacrificing some performance for that flexibility.
The method you are using is from Oracle side.
Another method is from OS side, where you can create sql files based on tables.
Once you check everything is okay you can run those files and stored for documentation.
May be you dont like this solution.
Avatar of mahjag

ASKER

Thanks for the response.

Hi Johnsone - can you describe in detail your solution - I want to see an example. Also in my case there are some statements in the sql that will not change. I want to do combinations of sql query from the table (that do not change) and some more additions to sql like joining to another table which I want to write in the package.An example of successful execution of a sample code will help - Also let me know performance is impacted..

Thanks
I do not have a working example of what you are trying to do.  Without your full requirements, it would be tough to give you everything.  Even with the requirements it may not be possible with a simple example.  I would discourage you from putting the entire SQL statement into a table.  That opens you up to someone updating the table and making a mistake, or a malicious change to delete all your data.

I encourage you to look at the PL/SQL documentation on dynamic SQL (http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/dynamic.htm#CACDDACH).  There are examples there of what you are trying to do.

Also look at the DBMS_SQL package.  You may not need DBMS_SQL and should probably be able to do everything you need with EXECUTE IMMEDIATE, but be aware the DBMS_SQL is there and what it can do beyond EXECUTE IMMEDIATE.

Using dynamic SQL opens you up to a performance hit as the SQL cannot be parsed ahead of time and needs to be at least soft parsed every time it is called.  Using bind variables (as described in the documentation) and making sure the SQL statement is constructed the same way every time can cut down on hard parsing.

What you are doing can be done.  If built and tested correctly, as business rules change no new code should have to be written, you will just need to update your rules table.

What I would see in a rules table is are the following columns:

RULE_ID - could be a name or a number, not unique
RULE_SEQ - sequence in which this rule is applied within this RULE_ID
RULE_TAB - table that the rule is on
RULE_WHERE - where clause that the rule adds

Your PL/SQL package takes in a RULE_ID and whatever else it needs to get started.  Then it will use that RULE_ID to construct whatever joins are necessary and build the SQL statement.  You could eliminate the RULE_TAB column and just use RULE_SEQ to indicate a sequence and a table name.  The purpose of the sequence is really to get things in the same order every time.
Avatar of mahjag

ASKER

Hi Johnsone

I appreciate your response. the main reason was to avoid creating static sql statement in the code but to have that outside the code in a  table. How do you construct sql based on rule_id and sequence? I am not sure I understand that - also my query is something like this..

slect a.order,a.customer from
( select order, customer, count(*) over partition by (order) cnt,from customer_master, order_master
  where order = 'LOCAL' and customer = 'SPECIAL'
   and i_date = '01-MAR-2011' and type in ('I','U')
   group by  order,customer)
where cnt > 1
and this would be rule for one of the type A reports and then I may have to join the above sql to another table to check whether the customer is active  from another instance - I prefer to do this part in the package . let me know how do I construct above query from your design
If you want to store the SQL in a table, that is fine, but just be aware, that someone can come along and update your select statement to 'DELETE FROM CUSTOMER_MASTER'.  This is them major reason for the section in the documentation on SQL injection and how to avoid it.

What I have built in the past had a result of one field or a true/false value.  It was not designed to return more than 1 row.  It was designed to do some work and return a result or a true/false value as to whether a rule was successful or not.

So, basically, the query that you supplied is your base query.  You may have to split that up into multiple strings in your code to facilitate adding to it.  Maybe the first chunk would be everything up through the table names.  The second chunk would the the base where clause you have.  The third chunk would be everything after the where.

Then a row in the table would have

RULE_ID = 1
RULE_SEQ = 1
RULE_TABLE = CUSTOMER_STATUS
RULE_WHERE = CUSTOMER_MASTER.CUSTOMER_ID = CUSTOMER_STATUS.CUSTOMER_ID

Then when your code goes to run rule 1, it would put it together like:

chunk1 || ',' || RULE_TABLE || chunk 2 || ' AND ' || RULE_WHERE || ' ' || chunk3

That would construct your new query and you could run that.

That is a simple example of what you could do.
Avatar of mahjag

ASKER

how would the above be flexible to add any new rules without aksing for a code change - whole point is to avoid a code change so as to not pass into releas cycle so the users will go the table and add a rule and increment the sequence. The code will pick the rule and execute it.

Also would it be better to ask the users to compile the rules in a procedure and the package code will then execute the procedure? this is just a thought
As stated, it is a simple example of what you could do.  If a second row was added to the table like this:

RULE_ID = 1
RULE_SEQ = 2
RULE_TABLE = ORDERS
RULE_WHERE = CUSTOMER_MASTER.CUSTOMER_ID = ORDER.CUSTOMER_ID

Then the procedure would add these conditions in the loop.

I am trying to give you an example to get you started.  I do not know all the parameters of what you are trying to solve and I don't want to know.  It is a very large project that will probably take a few months to complete and get all the kinks worked out.
Avatar of mahjag

ASKER

Yes I understand where you are going - in our environment every code change has to be scheduled and released in a release cycle and the users would not want that if there is a rule that can be identified in a simple query and they can write. The advantage of adding a rule and letting the proess to execute also provides them way to get emails or alerting about a problem. That was the reason I thought to use rule based table and add the sql directly.

Let me know how I can acheive above without leading to a code change every time when a new rule is added.
It all depends on what the full requirement is for the system you are building.

Can you build something that can anticipate absolutely everything that someone could do?  Probably not.

You stated problem was to have a base query and then add additional tables to it based on user requirements.  I believe that what has been presented here can do that.

What exactly do you need to do that is not covered here?
Avatar of mahjag

ASKER

Hi Johnsone

Yes I agree you did provide a start, but in my scenario where new sqls can be added and executed does not work well with your design. Any new tables that are added to rule_tab will have to be coded to construct the sql. I was thinking is there any other way that when sqls are provided the code would just execute. Let me know if this explains...
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial