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?
Who is Participating?
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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.
slightwv (䄆 Netminder) Commented:
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?
mahjagAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

johnsoneSenior Oracle DBACommented:
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.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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.
mahjagAuthor Commented:
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..

johnsoneSenior Oracle DBACommented:
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.
mahjagAuthor Commented:
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
johnsoneSenior Oracle DBACommented:
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.
mahjagAuthor Commented:
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
johnsoneSenior Oracle DBACommented:
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.
mahjagAuthor Commented:
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.
johnsoneSenior Oracle DBACommented:
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?
mahjagAuthor Commented:
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.