Expression Filters are a new feature that was introduced with Oracle 10g. Unfortunately, the number of examples and information about this feature are still hard to find.
They're actually an enhancement to SQL that allow much more complex queries than was possible with straight WHERE conditions. Expression Filters allow conditions to be stored, indexed, and referenced inside a table on a per-row basis. This ability is often required to maintain a table of business rules—the core of most business rule management systems.
When you enter a traditional SQL WHERE condition, you specify that you want the database to return a subset of data based on comparing the same columns in every row against the same values. For example, I might write WHERE COMM > 2000 to select rows that meet that condition. If you want to add a simple exception (such as "ignore Bob's commission"), you need to modify your application's SQL statement to WHERE EMPNO=8890 OR COMM>2000. You would have to patch or recompile a production application for this single exception. The more exceptions and rules, the slower, more complex, and harder to maintain the application becomes.
Expression Filters allow you to create a column that contains the conditions that must be met for a single row. The EVALUATE function will compare the compiled version of this column's expression against a specific object and return the row if the expression holds true. To update the application, you merely need to update that single column or row in the database; you could do this with a simple end-user interface. You wouldn't need to modify the application for every new business rule or exception.
As an extremely simple example of how you can use Expression Filters to build a Business Rule Management System, let's imagine that I run the company Lemonade Stand, Inc. I want to manage my inventory (lemons, sugar, and water) using simple business rules that drive my application logic elsewhere. The functions I want to drive are "order more lemons," "order more sugar," and "order more water." I won't worry about how to do this, but I'll just write some stubs for now. See Listing A.
To create an expression filter, I need to create an Oracle object type to hold the possible combination of attributes (namely, my three ingredients) and map it to an attribute set. See Listing B. The attribute set function calls built a function index that will contain compiled versions of my business rules. In Listing C, I create a table that holds the business rule and the action that it will invoke when the action is met. I can now generate actions based on a sample inventory level report. See Listing D. I can even wrap this logic up and generate a PL/SQL anonymous block automatically that will perform whatever actions are needed based on any given inventory. See Listing E.
My Business Rule Management System is now in place. I can maintain the combinations of rules and actions easily without touching the SQL code. Simply insert, update, or delete some rows, and my new logic will start working immediately. I will have to maintain the application though if I need to track more or different attributes and add business functionality in the backend.
TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!