Expression Filters (10 g )

1. Expression Filters (10g) • What is an expression filter ? • Feature to manage and evaluate conditional expressions that describe users interests in data • Allows you to store conditional expressions in a column, which you can then use in the WHERE clause of a database query • Expression Filter includes: • Expression datatype, • SQL EVALUATE operator, • Expression indexing(Enterprise Edition only) • Administrative utilities

2. Expression Filters (10g) • The Expression Attribute Set • Defined as special Oracle Object Type that carries all valid variables for the set • Implicitly includes all the built-in functions • Possible to add user-defined functions • May contain XPath predicates defined on XMLType attributes • Expressions must adhere to SQL Where Clause format • Subqueries are not allowed! • Not required to use all the defined elementary attributes (null is acceptable) • Two ways to create an attribute set • use dbms_expfil.create_attribute_set in combination with dbms_expfil.add_elementary_attribute (automatically creates object) • use an existing object type to create an attribute set dbms_expfil.create_attribute_set(attr_set => 'car4sale',from_type => 'yes'))

3. Expression Filters (10g) • Regular Expression datatype • Always a varchar2 column (converted into column of expression datatype when assigning expression attribute set to it) • How to define Expression column ? assign an attribute set to varchar2 column and it will create an expression column • The attribute set determines which elementary attributes an user-defined functions can be used in the expression set • Possible to create multiple columns of Expression datatype in the same table and in other tables in the same schema • Checked for correct syntax and constrained to use attributes and functions defined in the corresponding attribute set • Expression data can be loaded using SQL-loader!

4. Expression Filters (10g) • Basic steps to create and use an expression column • Define an attribute set • Create user table with expression column (or add new varchar2 column to existing table) • Assign attribute set to the expression column in the table • Insert expressions in the table • Apply the SQL EVALUATE operator • Applying the SQL evaluate Operator • The EVALUATE operator returns 1 for an expression that evaluates to true for the data item and 0 otherwise. • The EVALUATE operator is used in the WHERE clause of a standard SQL query or DML (UPDATE or DELETE) statement.

5. Steps to set up Expression Filters • Create an object type as source for the Expression Attribute Set • Defining an attribute set based on the type • CREATE OR REPLACE TYPE Car4Sale AS OBJECT • ( Model VARCHAR2(20) • , Year NUMBER • , Price NUMBER • , Mileage NUMBER); • BEGIN • DBMS_EXPFIL.CREATE_ATTRIBUTE_SET ( attr_set => 'Car4Sale‘ • , from_type => 'YES‘ • ); • END;

6. Steps to set up Expression Filters • Create user table with expression column • Assign attribute set to the expression column in the table • CREATE TABLE Consumer (Id NUMBER • , Zipcode NUMBER • , Phone VARCHAR2(12) • , Interest VARCHAR2(200) • ); • BEGIN • DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET ( attr_set => 'Car4Sale' • , expr_tab => 'Consumer' • , expr_col => 'Interest' • ); • END;

7. Steps to set up Expression Filters • Insert expressions into expression column • INSERT INTO Consumer VALUES (1, 32611, '917 768 4633‘,'Model=''Taurus'' and Price < 15000 and Mileage < 25000'); • INSERT INTO Consumer VALUES (2, 03060, '603 983 3464‘,'Model=''Mustang'' and Year > 1999 and Price < 20000');

8. Expression Filters (10g) • The SQL EVALUATE operator has two arguments: • the name of the column storing the expressions • the data item to which the expressions are compared. • In the data item argument, values MUST provided for all elementary attributes in the attribute set associated with the expression column. Null is an acceptable value. • Sample Queries • Data Item Formatted as a String: • SELECT * FROM Consumer WHERE • EVALUATE (Consumer.Interest, • 'Model=>''Mustang'', • Year=>2000, • Price=>18000, • Mileage=>22000‘ ) = 1;

9. Expression Filters (10g) • Batch Evaluation of Expressions • For each inventory item,return the number of consumers whose Car interest matches the item • SELECT DISTINCT Inventory.Model, count(*) as Demand • FROM Consumer, Inventory • WHERE EVALUATE ( Consumer.Interest, • , Car4Sale.getVarchar ( Inventory.Model • , Inventory.Year • , Inventory.Price • , Inventory.Mileage • ) ) = 1 • GROUP BY Inventory.Model • ORDER BY Demand DESC

10. Expression Filters (10g) • Application Example: list all consumers with an interest in the newly inserted (or updated) inventory item – and contact them? • CREATE TRIGGER activechk AFTER insert OR update ON Inventory • FOR EACH ROW • DECLARE • cursor c1 (ditem VARCHAR2) is • SELECT c.id, c.phone FROM Consumer c WHERE EVALUATE (c.Interest, ditem) = 1; • ditem VARCHAR2(200); • BEGIN ditem := Car4Sale.getVarchar ( :new.Model, :new.Year , :new.Price, :new.Mileage); • for cur in c1(ditem) loop • DBMS_OUTPUT.PUT_LINE (' For Model '||:new.Model||' Call '||cur.Id||'@'||cur.phone); • end loop; • END;

11. Typical Usage of Expression Filters • To screen incoming data • Find matches with expressed interests or conditions • We have found an item that may be exactly what you’re looking for • A suspect has just entered the country • Find non-matches • This new piece of data does not meet one of (y)our standards • This record does not adhere to this business rule • To screen existing data for new interests, conditions, standards or rules • Because of this new EU regulation, we have to redesign these products…

12. Business Rule example Deptno Emp • CREATE OR REPLACE TYPE BusinessRuleConditions AS OBJECT • ( Condition1 VARCHAR2(20) • , Condition2 VARCHAR2(20) • , Condition3 VARCHAR2(20) • , Condition4 VARCHAR2(20) • , Condition5 VARCHAR2(20) • ); deptno dname loc empno ename job sal Business Rules • BEGIN • DBMS_EXPFIL.CREATE_ATTRIBUTE_SET • ( attr_set => 'BusinessRuleConditions' • , from_type => 'YES' • ); • END; label table msg expression EMPDEPTEMPDEPTBONUS EMP1DPT1EMP2DPT2BOS1 001011002012021 • BEGIN • DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET • ( attr_set => 'BusinessRuleConditions' • , expr_tab => 'Business_Rules' • , expr_col => 'br_expression' • ); • END;

13. Inserting the business rules as Expressions Deptno Emp deptno dname loc empno ename job sal • INSERT INTO Business_rules VALUES ('EMP1', '001','EMP', 'Condition1 =''SALESMAN'' or Condition2 is null'); • INSERT INTO Business_rules VALUES ('EMP2', '002','EMP', '(sysdate - to_date(condition3)) <8500');INSERT INTO Business_rules VALUES ('DPT1', '001','DEPT', 'Condition2 <>''SALES'' or Condition3 <> ''PARIS'' '); Business Rules label table msg expression EMPDEPTEMPDEPTBONUS EMP1DPT1EMP2DPT2BOS1 001011002012021 Condition1 =''SALESMAN'' or Condition2 is nullCondition2 <>''SALES'' or Condition3 <> ''PARIS''(sysdate - to_date(condition3)) <8500012021

14. Find violations of the Business Rules • create or replace type • ename_type as table of varchar2(30) • / • SELECT br.br_expression Business_Rule • , count(e.empno) • , cast( collect(ename) as ename_type ) enames • FROM Business_Rules br • , Emp e • WHERE EVALUATE • ( br.br_expression • , BusinessRuleConditions.getVarchar • ( e.job • , e.comm • , e.hiredate • , e.deptno • , e.mgr • ) • ) = 0 • AND br.br_tbl = 'EMP' • GROUP BY br.br_expression • BUSINESS_RULE • COUNT(E.EMPNO) • ENAMES • -------------------- (sysdate - to_date(condition3)) <8500 • 5 • ENAME_TYPE('SMITH', 'ALLEN', 'WARD', 'JONES', 'BLAKE')

15. Find violations of the Business Rules • SELECT br.br_expression Business_Rule • , count(d.deptno) • , cast( collect(dname) as ename_type ) dnames • FROM Business_Rules br • , Dept d • WHERE EVALUATE • ( br.br_expression • , BusinessRuleConditions.getVarchar • ( d.deptno • , d.dname • , d.loc • , null • , null • ) • ) = 0 • AND br.br_tbl = 'DEPT' • GROUP BY br.br_expression