table based rules

Posted on 2011-03-23
Last Modified: 2012-05-11
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?
Question by:mahjag
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35202214
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?

Author Comment

ID: 35202405
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
LVL 34

Expert Comment

ID: 35202496
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.
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 35202740
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.

Author Comment

ID: 35203944
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..

LVL 34

Expert Comment

ID: 35206915
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 (  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.

Author Comment

ID: 35209959
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 34

Expert Comment

ID: 35210538
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


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.

Author Comment

ID: 35218365
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
LVL 34

Expert Comment

ID: 35218648
As stated, it is a simple example of what you could do.  If a second row was added to the table like this:


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.

Author Comment

ID: 35221776
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.
LVL 34

Expert Comment

ID: 35223347
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?

Author Comment

ID: 35225694
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...
LVL 34

Accepted Solution

johnsone earned 125 total points
ID: 35226066
Like we have said, that can certainly be dangerous.  What is going to prevent a user from submitting a delete or truncate or alter or ....

Instead of starting with a base query, you can have nothing.  Assuming we are talking strictly about select statements, you could add columns to be selected.  The where portion would not contain join conditions anymore.  You would need a separate table that lists the possible joins and what their join conditions would be.  Then if a rule get submitted that doe not have a corresponding join condition you would have to raise an appropriate error.

Building things this dynamic, you want to take all the control away from the user.  You may also have to build in some logic for performance tuning as building queries this way can lead to sub-optimal plans.

So, your 2 tables would look something like this:

RULE_ID - ID of rule set - part of composite PK
RULE_SEQ - Sequence to be processed in - part of composite PK
RULE_TABLE - Table rule acts on
RULE_WHERE - Any where condition associated with the table, do not include joins here

RULE_COL - Column to be selected from table specified in RULE_TABLE
RULE_COL_SEQ - Sequence of column to be selected

TABLE1 - First table in join
TABLE2 - Second table in join
JOIN_CONDITION - Where condition to apply for the join

Now you should have all the pieces to build the query.  You have to loop through all of these and look up what pieces you need to build the query.  The tricky part is the joins, because you have to start to look at different combinations to get what you need.  However, this should be the basic idea of where to start.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
alter database link to change the password 2 48
PLSQL procedure help with decode and null input params 5 45
Oracle Pivot Question 8 44
SQL Query 34 80
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now