CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_EXPFIL
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/exfpbs.sql
Demo {ORACLE_HOME}/rdbms/demo/exfdemo.sql

Dependencies
ALL_COL_TYPES DBMS_EXPFIL_DR EXF$EXPISVALID
ALL_INDEXES DBMS_EXPFIL_IR EXF$INDEXOPER
ALL_OBJECTS DBMS_EXPFIL_UTL EXF$TABLE_ALIAS
ALL_SYNONYMS DBMS_OUTPUT EXF$TEXT
ALL_TAB_COLUMNS DBMS_RLM4J_DICTMAINT EXF$XPATH_TAGS
ALL_TYPES DBMS_RLMGR NAMELIST
ALL_TYPE_ATTRS DBMS_RLMGR_IR PLITBLM
ALL_TYPE_VERSIONS EXF$ATTRIBUTE_LIST PUBLIC_DEPENDENCY
ALL_USERS EXF$CURRUSER USER_TAB_COLUMNS
DBMS_ASSERT EXF$EXPFUNCREFS USER_TYPE_ATTRS
 
ALL_EXPFIL_ASET_FUNCTIONS Functions and packages approved for the attribute set
ALL_EXPFIL_ATTRIBUTES Elementary attributes of the attribute set
ALL_EXPFIL_ATTRIBUTE_SETS Attribute set
ALL_EXPFIL_DEF_INDEX_PARAMS Default index parameters
ALL_EXPFIL_EXPRESSION_SETS Expression sets
ALL_EXPFIL_EXPRSET_STATS Predicate statistics for the expression sets
ALL_EXPFIL_INDEX_PARAMS Index parameters assigned to the expression set
ALL_EXPFIL_INDEXES Expression filter indexes
ALL_EXPFIL_PREDTAB_ATTRIBUTES Stored and indexed attributes for the indexes
USER_EXPFIL_PRIVILEGES Expression privileges of the current user
Evaluate Operator EVALUATE (expression_column, <dataitem>)
See expression filtering demo
Security Model execute is granted to PUBLIC. Runs under AUTHID CURRENT_USER.
 
ADD_ELEMENTARY_ATTRIBUTE (new 11g parameter and overload ?)

Adds the specified attribute to the attribute set

Overload 1
dbms_expfil.add_elementary_attribute (
attr_set   IN VARCHAR2,               -- attribute set name
attr_name  IN VARCHAR2,               -- attribute name
attr_type  IN VARCHAR2,               -- attribute type
attr_defvl IN VARCHAR2 DFEAULT NULL); -- default value for attr
exec dbms_expfil.create_attribute_set('UW_Set');

desc user_expfil_attribute_sets

SELECT * 
FROM user_expfil_attribute_sets;

set linesize 121
col data_type format a30

SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes
WHERE attribute_set_name = 'UW_Set';

exec dbms_expfil.add_elementary_attribute('UW_Set', 'PCP_NAME', 'VARCHAR2(30)');

SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes
WHERE attribute_set_name = 'UW_Set';

exec dbms_expfil.add_elementary_attribute ('UW_Set', 'PCP_ID', 'NUMBER(5)');

SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes
WHERE attribute_set_name = 'UW_Set';
Overload 2 dbms_expfil.add_elementary_attribute (
attr_set  IN VARCHAR2,         -- attribute set name
attr_name IN VARCHAR2,         -- table alias (name)
tab_alias IN exf$table_alias); -- table alias for
TBD
Overload 3 dbms_expfil.add_elementary_attribute (
attr_set  IN VARCHAR2,   -- attribute set name
attr_name IN VARCHAR2,   -- table alias (name)
attr_type IN VARCHAR2,   -- attr type
text_pref IN exf$text);  -- table alias for
TBD
 
ADD_FUNCTIONS

Adds a user-defined function, package, or type representing a set of functions to the attribute set
dbms_expfil.add_functions (
attr_set   IN VARCHAR2,  -- attribute set name 
funcs_name IN VARCHAR2); -- function/package/type name
CREATE OR REPLACE FUNCTION age_val (valin NUMBER) RETURN INTEGER IS

BEGIN
  RETURN TRUNC(valin);
END age_val;
/

set linesize 121
col attribute_set_name format a20
col udf_name format a15
col object_owner format a20
col object_name format a15
col object_type format 15

SELECT *
FROM user_expfil_aset_functions
;

exec dbms_expfil.add_functions('CBC', 'AGE_VAL');

SELECT *
FROM user_expfil_aset_functions
;
 
ASSIGN_ATTRIBUTE_SET

Assigns an attribute set to a VARCHAR2 column in a user table to create an Expression column
dbms_expfil.assign_attribute_set (
attr_set IN VARCHAR2,                  -- attribute set name
expr_tab IN VARCHAR2,                  -- name of the table
expr_col IN VARCHAR2,                  -- exp column in the table 
force    IN VARCHAR2 DEFAULT 'FALSE')  -- use existing expressions
CREATE TABLE cbc_watch (
watch_id  NUMBER(10),
watch     VARCHAR2(160));

exec dbms_expfil.assign_attribute_set('CBC', 'CBC_WATCH', 'WATCH');

set linesize 121
col exsowner format a20
col exstabnm format a20
col exscolnm format a20
col exsatsnm format a20
col exsprvtrig format a20

SELECT exsowner, exstabnm, exscolnm, exsatsnm, exsprvtrig
FROM exfsys.exf$exprset;
 
BUILD_EXCEPTIONS_TABLE
Create exception table used in filter validation dbms_expfil.build_exceptions_table (exception_tab IN VARCHAR2);
exec dbms_expfil.build_exceptions_table('ExpFilExceptions');

SELECT table_name
FROM user_tables;

desc ExpFilExceptions
 
CLEAR_EXPRSET_STATS
Clears the predicate statistics for the expression set stored in a table column dbms_expfil.clear_exprset_stats (
expr_tab IN VARCHAR2,  -- table storing expression set
expr_col IN VARCHAR2); -- column in the table with set
exec dbms_expfil.clear_exprset_stats('CBC_WATCH', 'WATCH');
 
COPY_ATTRIBUTE_SET
Copies an attribute set along with its user-defined function list and default index parameters to another set dbms_expfil.copy_attribute_set (
from_set IN VARCHAR2,  -- name of an existing attribute set
to_set   IN VARCHAR2); -- new set name
exec dbms_expfil.copy_attribute_set('CBC', 'CBC_NEW');
 
CREATE_ATTRIBUTE_SET

Creates an empty attribute set or an attribute set with a complete set of elementary attributes derived from an object type with a matching name
dbms_expfil.create_attribute_set (
attr_set  IN VARCHAR2,               -- attribute set name
from_type IN VARCHAR2 DEFAULT 'NO'); -- options YES | NO
CREATE OR REPLACE TYPE cbc AS OBJECT (
machine_id VARCHAR2(10),
pid        NUMBER,
gender     VARCHAR2(1),
age        NUMBER,
gbc        NUMBER,
hmo        NUMBER,
rus        NUMBER,
mit        NUMBER,
smoker     VARCHAR2(1),
risk       NUMBER);
/

desc user_expfil_attribute_sets

SELECT *
FROM user_expfil_attribute_sets;

exec dbms_expfil.create_attribute_set('CBC', 'YES');

SELECT *
FROM user_expfil_attribute_sets;

desc user_expfil_attributes

SELECT attribute_set_name, attribute, data_type
FROM user_expfil_attributes;
 
DEFAULT_INDEX_PARAMETERS
Assigns default index parameters to an attribute set and adds or drops a partial list of stored and indexed attributes to or from the default list associated with the attribute list exec dbms_expfil.default_index_parameters (
attr_set  IN VARCHAR2,                -- attribute set name
attr_list IN EXF$ATTRIBUTE_LIST,      -- stored & indexed attrs
operation IN VARCHAR2 DEFAULT 'ADD'); -- to ADD or DROP
TBD 
 
DEFAULT_XPINDEX_PARAMETERS
Adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set exec dbms_expfil.default_xpindex_parameters (
attr_set   IN VARCHAR2,       -- attribute set 
xmlt_attr  IN VARCHAR2,       -- XMLType attribute name
xptag_list IN EXF$XPATH_TAGS, -- common XPath expr elements/attributes
operation IN VARCHAR2 DEFAULT 'ADD'); -- to ADD/DROP
See demo at tahiti.oracle.com
 
DEFRAG_INDEX
Rebuilds the bitmap indexes online and thus reduces the fragmentation exec dbms_expfil.defrag_index(idx_name IN VARCHAR2); 
TBD 
 
DROP_ATTRIBUTE_SET
Drops an attribute set not being used for an expression set exec dbms_expfil.drop_attribute_set(attr_set IN VARCHAR2); -- set name
SELECT * FROM user_expfil_attribute_sets;

exec dbms_expfil.drop_attribute_set('CBC');

SELECT * FROM user_expfil_attribute_sets;
 
GET_EXPRSET_STATS
Computes the predicate statistics for an expression set and stores them in the expression filter dictionary exec dbms_expfil.get_exprset_stats (
expr_tab IN VARCHAR2,  -- table storing expression set
expr_col IN VARCHAR2); -- column in the table with set
exec dbms_expfil.get_exprset_stats('CBC_WATCH', 'WATCH');
 
GRANT_PRIVILEGE

Grants privileges on one or more Expression columns to users
exec dbms_expfil.grant_privilege (
expr_tab  IN VARCHAR2,  -- table w/ the expr column
expr_col  IN VARCHAR2,  -- column storing the expressions
priv_type IN VARCHAR2,  -- type of priv to be granted
to_user   IN VARCHAR2); -- user to which the priv is granted

Privilege Types: INSERT EXPRESSION, UPDATE EXPRESSION, ALL
exec dbms_expfil.grant_privilege('CBC_WATCH', 'WATCH', 'ALL', 'PSOUG');

desc exfsys.exf$expsetprivs

set linesize 121
col esowner format a15
col esexptab format a15
col esexpcol format a15
col esgrantee format a15

SELECT * FROM exfsys.exf$expsetprivs;
 
INDEX_PARAMETERS
Fine-tunes the index parameters for each expression set before index creation exec dbms_expfil.index_parameters (
expr_tab  IN VARCHAR2,               -- expression set table
expr_col  IN VARCHAR2,               -- expression set column
attr_list IN EXF$ATTRIBUTE_LIST DEFAULT NULL,
operation IN VARCHAR2 DEFAULT 'ADD') -- options ADD/DROP/CLEAR
exec dbms_expfil.index_parameters('CBC_WATCH', 'WATCH', 
 
MODIFY_OPERATOR_LIST
Undocumented exec dbms_expfil.modify_operator_list (
attr_set  IN VARCHAR2,       -- attribute set name
attr_name IN VARCHAR2,       -- attribute to be modified
attr_oper IN EXF$INDEXOPER); -- list of new operators
The developer advises that it not be used.
 
REVOKE_PRIVILEGE
Revokes an expression privilege previously granted by the owner exec dbms_expfil.revoke_privilege (
expr_tab  IN VARCHAR2,  -- table with the expr column
expr_col  IN VARCHAR2,  -- column storing the expressions
priv_type IN VARCHAR2,  -- type of privilege to be granted
from_user IN VARCHAR2); -- revoke privilege from username
exec dbms_expfil.revoke_privilege('CBC_WATCH', 'WATCH', 'ALL', 'PSOUG');
 
SYNC_TEXT_INDEXES (new in 11g ?)
Undocumented exec dbms_expfil.sync_text_indexes(expr_tab IN VARCHAR2);
TBD
 
UNASSIGN_ATTRIBUTE_SET
Unassigns an attribute set from a column storing expressions exec dbms_expfil.unassign_attribute_set (
expr_tab IN VARCHAR2,  -- table with expr. column
expr_col IN VARCHAR2); -- column storing expr. set
exec dbms_expfil.unassign_attribute_set('CBC_WATCH', 'WATCH');
 
VALIDATE_EXPRESSIONS
Validates the expressions in a set exec dbms_expfil.validate_expressions (
expr_tab      IN VARCHAR2,               -- expressions table 
expr_col      IN VARCHAR2,               -- column storing expressions
exception_tab IN VARCHAR2 DEFAULT NULL); -- exception table
exec dbms_expfil.validate_expressions('CBC_WATCH', 'WATCH', 'ExpFilExceptions');
 
XPINDEX_PARAMETERS
Used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XPath-specific index parameters for each expression set exec dbms_expfil.xpindex_parameters (
expr_tab   IN VARCHAR2,       -- expression set table 
expr_col   IN VARCHAR2,       -- expression set column
xmlt_attr  IN VARCHAR2,       -- XMLType attribute name
xptag_list IN EXF$XPATH_TAGS, -- common xpath expr elements/attributes
operation  IN VARCHAR2 DEFAULT 'ADD'); -- to ADD/DROP default ADD
See demo at tahiti.oracle.com
 
Demo

Expression Filtering Demo
Definition Abbrev. F Rng M Rng
Hematocrit HCT 37 - 47 40 - 54
Hemoglobin HGB 12 - 16 14 - 18
Red Blood Cell Count RBC 3.9 - 5.2 4.2 - 5.6

-- create attribute set
CREATE OR REPLACE TYPE bchem AS OBJECT (
gender     VARCHAR2(1),
hct        NUMBER,
hgb        NUMBER,
rbc        FLOAT(126));
/

exec dbms_expfil.create_attribute_set('BCHEM', 'YES');

-- add function ?

-- create table
CREATE TABLE test_rng (
test_name   VARCHAR2(20),
test_abbrev VARCHAR2(3),
rule_set    VARCHAR2(250));

-- assign attribute set to result column
-- converting it into an expression column

exec dbms_expfil.assign_attribute_set('BCHEM', 'TEST_RNG', 'RULE_SET');

INSERT INTO test_rng VALUES ('Anemic', '3', 'Gender=''F'' AND HCT < 37 AND HGB < 12 AND RBC < 3.9');

INSERT INTO test_rng VALUES ('Less Anemic','2', 'Gender=''F'' AND HCT < 39 AND HGB < 13 AND RBC < 4.5');

INSERT INTO test_rng VALUES ('VS Anemic', '1', 'Gender=''F'' AND HCT < 41 AND HGB < 15 AND RBC < 5.6');

-- more inserts

COMMIT;

SELECT *
FROM test_rng;

SELECT *
FROM test_rng
WHERE EVALUATE(test_rng.rule_set,'gender=>''F'',HCT=>36, HGB=>11, RBC=>3.8') = 1;

SELECT *
FROM test_rng
WHERE EVALUATE(test_rng.rule_set, 'gender=>''F'',
hct=>40, hgb=>14, rbc=>4.6') = 1;

-- exec dbms_expfil.unassign_attribute_set('bchem', 'RESULT');

exec dbms_expfil.drop_attribute_set('bchem');
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [154 users online]    © 2010 psoug.org