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!