Representing Conditional Expressions as Data in Oracle10g

In oracle10g the Oracle Expression Filter feature enables storing of conditional expressions as data within the database. Using this filter, user’s can also ensure that values stored are valid SQL WHERE clause expressions by insertion of a constraint on the VARCHAR2 column. The set of attributes that are authorized to be used in conditional expressions are also identified by this mechanism.

Example demonstrating the usage of this feature is explained by the creation of a traders_info table where row contains data for a stock trading account holder. Here a column that stores information about the stock traded by each trader can be stored as a conditional expression.

The table is created as below

CREATE TABLE traders_info
(
trader_name VARCHAR2(100),
email_id VARCHAR2(50),
stk_traded VARCHAR2(100)
);

A user defined datatype stock_info with attributes for the trading symbol, limit price and fluctuation amount in stock price is defined.

CREATE OR REPLACE TYPE stock_info
AS OBJECT
( stk_symbol VARCHAR2(20),
stk_price NUMBER,
stk_change NUMBER
);

A PL/SQL block creating an attribute set stock_info based on the stock_info data type is defined.

BEGIN

DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => ‘stock_info’,
from_type => ‘YES’ );
END;

The attribute set is associated with the expression set stored in the database column traders_info.stk_traded as follows.

BEGIN
DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET(attr_set => ‘stock_info’,
expr_tab => ‘traders_info’,
expr_col => ‘stk_traded’);
END;

This makes sure that the column stk_traded stores valid conditional expressions by placing a constraint on the stk_traded column.

The table can then be populated with data. Example
INSERT INTO traders_info (trader_name,email_id,stk_traded)
VALUES (‘John’, ’[email protected]’, ’stk_symbol = ‘’XYZ’’ AND stk_price > 100’);

The EVALUATE operator identifies conditional expression that evaluate to TRUE for a given data item. Example the following query returns information about traders interested in a given stock quote.(stk_symbol =’XYZ’, stk_price =101, stk_change = 10.2):

SELECT trader_name, email_id
FROM traders_info
WHERE EVALUATE ( stk_traded ,
‘stk_symbol =>’ ’XYZ’ ’,
stk_price =>101,
stk_change => 10.2’
)=1;

This query can be optimized by creation of Oracle Expression Filter index on the stk_traded column.

Thus the feature of representation of conditional expressions as data, supports expression data type in a relational database management system. Expressions can be interpreted as descriptions, queries and filters and this considerably broadens the usage of a relational database system for supporting new applications pertaining to information analysis in an easy manner.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |