Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

39 DBMS_EXPFIL

The DBMS_EXPFIL package contains all the procedures used to manage attribute sets, expression sets, expression indexes, optimizer statistics, and privileges by Expression Filter.

See Also:

Oracle Database Application Developer's Guide - Rules Manager and Expression Filter for more information.

This chapter contains the following topics:


Summary of Expression Filter Subprograms

Table 39-1 describes the subprograms in the DBMS_EXPFIL package.

All the values and names passed to the procedures defined in the DBMS_EXPFIL package are not case sensitive, unless otherwise mentioned. To preserve the case, you use double quotation marks around the values.

Table 39-1 DBMS_EXPFIL Package Subprograms

Subprogram Description

ADD_ELEMENTARY_ATTRIBUTE Procedures

Adds the specified attribute to the attribute set

ADD_FUNCTIONS Procedure

Adds a function, type, or package to the approved list of functions with an attribute set

ASSIGN_ATTRIBUTE_SET Procedure

Assigns an attribute set to a column storing expressions

BUILD_EXCEPTIONS_TABLE Procedure

Creates an exception table to hold references to invalid expressions

CLEAR_EXPRSET_STATS Procedure

Clears the predicate statistics for an expression set

COPY_ATTRIBUTE_SET Procedure

Makes a copy of the attribute set

CREATE_ATTRIBUTE_SET Procedure

Creates an attribute set

DEFAULT_INDEX_PARAMETERS Procedure

Assigns default index parameters to an attribute set

DEFAULT_XPINDEX_PARAMETERS Procedure

Assigns default XPath index parameters to an attribute set

DEFRAG_INDEX Procedure

Rebuilds the bitmap indexes online to reduce fragmentation

DROP_ATTRIBUTE_SET Procedure

Drops an unused attribute set

GET_EXPRSET_STATS Procedure

Collects predicate statistics for an expression set

GRANT_PRIVILEGE Procedure

Grants an expression DML privilege to a user

INDEX_PARAMETERS Procedure

Assigns index parameters to an expression set

MODIFY_OPERATOR_LIST Procedure

Modifies the list of common operators used in predicates with a certain attribute

REVOKE_PRIVILEGE Procedure

Revokes an expression DML privilege from a user

UNASSIGN_ATTRIBUTE_SET Procedure

Breaks the association between a column storing expressions and the attribute set

VALIDATE_EXPRESSIONS Procedure

Validates expression metadata and the expressions stored in a column

XPINDEX_PARAMETERS Procedure

Assigns XPath index parameters to an expression set



ADD_ELEMENTARY_ATTRIBUTE Procedures

This procedure adds the specified attribute to the attribute set. The procedure is overloaded. The different functionality of each form of syntax is presented along with the definitions.

Syntax

Adds the specified elementary attribute to the attribute set:

DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
   attr_set   IN   VARCHAR2, 
   attr_name  IN   VARCHAR2, 
   attr_type  IN   VARCHAR2, 
   attr_defv1 IN   VARCHAR2 DEFAULT NULL);

Identifies the elementary attributes that are table aliases and adds them to the attribute set:

DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
   attr_set   IN   VARCHAR2,
   attr_name  IN   VARCHAR2,
   tab_alias  IN   exf$table_alias);

Parameters

Table 39-2 ADD_ELEMENTARY_ATTRIBUTE Procedure Parameters

Parameter Description

attr_set

Name of the attribute set to which this attribute is added

attr_name

Name of the elementary attribute to be added. No two attributes in a set can have the same name.

attr_type

Datatype of the attribute. This argument accepts any standard SQL datatype or the name of an object type that is accessible to the current user.

attr_defv1

Default value for the elementary attribute

tab_alias

The type that identifies the database table to which the attribute is aliased


Usage Notes

Examples

The following commands add two elementary attributes to an attribute set:

BEGIN
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                               attr_set   => 'HRAttrSet',
                               attr_name  => 'HRREP',
                               attr_type  => 'VARCHAR2(30)'
                               attr_defv1 => 'Betty Smith');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                               attr_set  => 'HRAttrSet',
                               attr_name => 'DEPT',
                               tab_alias => exf$table_alias('DEPT'));
END;

The following commands define a CreationTime elementary attribute that takes the database time as the default value.

BEGIN
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                          attr_set   => 'PurchaseOrder',
                          attr_name  => 'CreationTime',
                          attr_type  => 'DATE',
                          attr_defvl => 'SYSDATE');
END;

Alternately, the following commands initialize the CreationTime attribute to a specific value when it is not explicitly specified in the data item passed to the EVALUATE operator.

BEGIN
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE (
                          attr_set   => 'PurchaseOrder',
                          attr_name  => 'CreationTime',
                          attr_type  => 'DATE',
                          attr_defvl => 'to_date(''01-01-2004'',''DD-MM-YYYY'')');
END;

ADD_FUNCTIONS Procedure

This procedure adds a user-defined function, package, or type representing a set of functions to the attribute set.

Syntax

DBMS_EXPFIL.ADD_FUNCTIONS (
   attr_set   IN   VARCHAR2, 
   funcs_name IN   VARCHAR2);

Parameters

Table 39-3 ADD_FUNCTIONS Procedure Parameters

Parameter Description

attr_set

Name of the attribute set to which the functions are added

funcs_name

Name of a function, package, or type (representing a function set) or its synonyms


Usage Notes

Examples

The following commands add two functions to the attribute set:

BEGIN 
  DBMS_EXPFIL.ADD_FUNCTIONS (
   attr_set   => 'Car4Sale', 
   funcs_name => 'HorsePower');
  DBMS_EXPFIL.ADD_FUNCTIONS (
   attr_set   => 'Car4Sale', 
   funcs_name => 'Scott.CrashTestRating');
END;

ASSIGN_ATTRIBUTE_SET Procedure

This procedure assigns an attribute set to a VARCHAR2 column in a user table to create an Expression column.

Syntax

DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET (
   attr_set   IN   VARCHAR2,
   expr_tab   IN   VARCHAR2,
   expr_col   IN   VARCHAR2,
   force      IN   VARCHAR2 DEFAULT 'FALSE');

Parameters

Table 39-4 ASSIGN_ATTRIBUTE_SET Procedure Parameters

Parameter Description

attr_set

The name of the attribute set

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions

force

Argument used to trust the existing expressions in a table (and skip validation)


Usage Notes

Examples

The following command assigns the attribute set to a column storing expressions. The expression set should be empty at the time of association.

BEGIN
  DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET (attr_set => 'Car4Sale', 
                                    expr_tab => 'Consumer',
                                    expr_col => 'Interest');
END;

BUILD_EXCEPTIONS_TABLE Procedure

This procedure creates the exception table, used in validation, in the current schema.

Syntax

DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE (
   exception_tab IN VARCHAR2);

Parameters

Table 39-5 BUILD_EXCEPTIONS_TABLE Procedure Parameter

Parameter Description

exception_tab

The name of the exception table


Usage Notes

Examples

The following command creates the exception table, InterestExceptions, in the current schema:

BEGIN
  DBMS_EXPFIL.BUILD_EXCEPTIONS_TABLE (
                              exception_tab => 'InterestExceptions');
END;

CLEAR_EXPRSET_STATS Procedure

This procedure clears the predicate statistics for the expression set stored in a table column.

Syntax

DBMS_EXPFIL.CLEAR_EXPRSET_STATS (
   expr_tab   IN   VARCHAR2,
   expr_col   IN   VARCHAR2);

Parameters

Table 39-6 CLEAR_EXPRSET_STATS Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions


Usage Notes

Examples

The following command clears the predicate statistics for the expression set stored in Interest column of the Consumer table:

BEGIN
  DBMS_EXPFIL.CLEAR_EXPRSET_STATS (expr_tab => 'Consumer',
                                    expr_col => 'Interest');
END;

COPY_ATTRIBUTE_SET Procedure

This procedure copies an attribute set along with its user-defined function list and default index parameters to another set.

Syntax

DBMS_EXPFIL.COPY_ATTRIBUTE_SET (
   from_set   IN   VARCHAR2,
   to_set     IN   VARCHAR2);

Parameters

Table 39-7 COPY_ATTRIBUTE_SET Procedure Parameters

Parameter Description

from_set

Name of an existing attribute set to be copied

to_set

Name of the new attribute set


Usage Notes

Examples

The following command makes a copy of the Car4Sale attribute set:

BEGIN
  DBMS_EXPFIL.COPY_ATTRIBUTE_SET (from_set => 'Car4Sale', 
                                  to_set   => 'Vehicle');
END;

CREATE_ATTRIBUTE_SET Procedure

This procedure 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.

Syntax

DBMS_EXPFIL.CREATE_ATTRIBUTE_SET (
   attr_set   IN   VARCHAR2,
   from_type  IN   VARCHAR2 DEFAULT 'NO');

Parameters

Table 39-8 CREATE_ATTRIBUTE_SET Procedure Parameters

Parameter Description

attr_set

The name of the attribute set to be created

from_type

YES, if the attributes for the attribute set should be derived from an existing object type


Usage Notes

Examples

The following commands create an attribute set with all the required elementary attributes derived from the Car4Sale 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;

 

Assuming that the Car4Sale type does not exist, the attribute set can be created from scratch as shown in the following example:

BEGIN
  DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'Car4Sale');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE( 
                               attr_set  => 'Car4Sale',
                               attr_name => 'Model',
                               attr_type => 'VARCHAR2(20)');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                               attr_set  => 'Car4Sale',
                               attr_name => 'Year',
                               attr_type => 'NUMBER');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                               attr_set  => 'Car4Sale',
                               attr_name => 'Price',
                               attr_type => 'NUMBER');
  DBMS_EXPFIL.ADD_ELEMENTARY_ATTRIBUTE(
                               attr_set  => 'Car4Sale',
                               attr_name => 'Mileage',
                               attr_type => 'NUMBER');
END;

DEFAULT_INDEX_PARAMETERS Procedure

This procedure assigns default index parameters to an attribute set. It also adds or drops a partial list of stored and indexed attributes to or from the default list associated with the attribute list.

Syntax

DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS (
   attr_set   IN   VARCHAR2,
   attr_list  IN   EXF$ATTRIBUTE_LIST,
   operation  IN   VARCHAR2 DEFAULT 'ADD');

Parameters

Table 39-9 DEFAULT_INDEX_PARAMETERS Procedure Parameters

Parameter Description

attr_set

The name of the attribute set

attr_list

An instance of EXF$ATTRIBUTE_LIST with a partial list of (default) stored and indexed attributes for an Expression Filter index

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.


Usage Notes

Examples

The following command adds the specified stored and indexed attributes to the attribute set's default index parameters list:

BEGIN
 DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERs(
    attr_set  => 'Car4Sale', 
    attr_list => exf$attribute_list (
       exf$attribute (attr_name => 'Model',            
                      attr_oper => exf$indexoper('='),
                      attr_indexed => 'TRUE'),         
       exf$attribute (attr_name => 'Price', 
                      attr_oper => exf$indexoper('all'), 
                      attr_indexed => 'TRUE'), 
       exf$attribute (attr_name => 'HorsePower(Model, Year)', 
                      attr_oper => exf$indexoper('=','<','>','>=','<='), 
                      attr_indexed => 'FALSE'),
       exf$attribute (attr_name => 'CrashTestRating(Model, Year)', 
                      attr_oper => exf$indexoper('=','<','>','>=','<='), 
                      attr_indexed => 'FALSE')),
    operation => 'ADD');
END;

The following command drops the CrashTestRating(Model, Year) attribute (stored or indexed) from the previous list.

BEGIN
  DBMS_EXPFIL.DEFAULT_INDEX_PARAMETERS(
      attr_set  => 'Car4Sale', 
      attr_list => exf$attribute_list (
         exf$attribute (attr_name => 'CrashTestRating(Model, Year)')),
      operation => 'DROP');
END;

DEFAULT_XPINDEX_PARAMETERS Procedure

This procedure adds (or drops) a partial list of XPath parameters to the default index parameters associated with the attribute set.

Syntax

DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS (
   attr_set   IN   VARCHAR2,
   xmlt_attr  IN   VARCHAR2,
   xptag_list IN   EXF$XPATH_TAGS,
   operation  IN   VARCHAR2 DEFAULT 'ADD');

Parameters

Table 39-10 DEFAULT_XPINDEX_PARAMETERS Procedure Parameters

Parameter Description

attr_set

The name of the attribute set

xmlt_attr

The name of the attribute with the XMLType datatype

xptag_list

An instance of EXF$XPATH_TAGS type with a partial list of XML elements and attributes to be configured for the Expression Filter index

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.


Usage Notes


Note:

The values assigned to the tag_name argument of exf$xpath_tag type are case sensitive.

Examples

The following command adds the specified XML tags to the default index parameters list along with their preferences such as positional or value filter and indexed or stored predicate group:

BEGIN
  DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS(
       attr_set   => 'Car4Sale',
       xmlt_attr  => 'Details',
       xptag_list =>                                    --- XPath tag list 
         exf$xpath_tags(
           exf$xpath_tag(tag_name    => 'stereo@make',  --- XML attribute
                         tag_indexed => 'TRUE',
                         tag_type    => 'VARCHAR(15)'), --- value filter
           exf$xpath_tag(tag_name    => 'stereo',       --- XML element
                         tag_indexed => 'FALSE',
                         tag_type    => null),          --- positional filter
           exf$xpath_tag(tag_name    => 'memory',       --- XML element
                         tag_indexed => 'TRUE',
                         tag_type    => 'VARCHAR(10)'), --- value filter
           exf$xpath_tag(tag_name    => 'GPS',
                         tag_indexed => 'TRUE',
                         tag_type    => null)
          )
        );
END;

The following command drops the stereo@make tag from the default index parameters:

BEGIN
  DBMS_EXPFIL.DEFAULT_XPINDEX_PARAMETERS(
       attr_set   => 'Car4Sale',
       xmlt_attr  => 'Details',
       xptag_list =>                                    --- XPath tag list 
         exf$xpath_tags(
           exf$xpath_tag(tag_name    => 'stereo@make')
         ),
       operation => 'DROP'
       );
END;

DEFRAG_INDEX Procedure

This procedure rebuilds the bitmap indexes online and thus reduces the fragmentation.

Syntax

DBMS_EXPFIL.DEFRAG_INDEX (
   idx_name   IN  VARCHAR2);

Parameters

Table 39-11 DEFRAG_INDEX Procedure Parameter

Parameter Description

idx_name

The name of the Expression Filter index


Usage Notes

Examples

The following command is issued to defragment the bitmap indexes associated with the Expression Filter index:

BEGIN
  DBMS_EXPFIL.DEFRAG_INDEX (idx_name => 'InterestIndex');
END;

DROP_ATTRIBUTE_SET Procedure

This procedure drops an attribute set not being used for any expression set.

Syntax

DBMS_EXPFIL.DROP_ATTRIBUTE_SET (
   attr_set   IN   VARCHAR2);

Parameters

Table 39-12 DROP_ATTRIBUTE_SET Procedure Parameter

Parameter Description

attr_set

The name of the attribute set to be dropped


Usage Notes

Examples

Assuming that the attribute set is not used by an Expression column, the following command drops the attribute set:

BEGIN
  DBMS_EXPFIL.DROP_ATTRIBUTE_SET(attr_set => 'Car4Sale');  
END;

GET_EXPRSET_STATS Procedure

This procedure computes the predicate statistics for an expression set and stores them in the expression filter dictionary.

Syntax

DBMS_EXPFIL.GET_EXPRSET_STATS (
   expr_tab   IN   VARCHAR2,
   expr_col   IN   VARCHAR2);

Parameters

Table 39-13 GET_EXPRSET_STATS Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions


Usage Notes

Examples

The following command computes the predicate statistics for the expressions stored in the Interest column of the Consumer table:

BEGIN
  DBMS_EXPFIL.GET_EXPRSET_STATS (expr_tab => 'Consumer',
                                 expr_col => 'Interest');
END;

GRANT_PRIVILEGE Procedure

This procedure grants privileges on one or more Expression columns to other users.

Syntax

DBMS_EXPFIL.GRANT_PRIVILEGE (
   expr_tab   IN  VARCHAR2,
   expr_col   IN  VARCHAR2,
   priv_type  IN  VARCHAR2,
   to_user    IN  VARCHAR2);

Parameters

Table 39-14 GRANT_PRIVILEGE Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions

priv_type

Type of the privilege to be granted. Valid values: INSERT EXPRESSION, UPDATE EXPRESSION, ALL.

to_user

The user to whom the privilege is to be granted


Usage Notes

Examples

The owner of Consumer table can grant INSERT EXPRESSION privileges to user SCOTT with the following command. User SCOTT should also have INSERT privileges on the table so that he can add new expressions to the set.

BEGIN
  DBMS_EXPFIL.GRANT_PRIVILEGE (expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               priv_type => 'INSERT EXPRESSION',
                               to_user   => 'SCOTT');
END;

INDEX_PARAMETERS Procedure

This procedure fine-tunes the index parameters for each expression set before index creation.

Syntax

DBMS_EXPFIL.INDEX_PARAMETERS (
   expr_tab   IN   VARCHAR2,
   expr_col   IN   VARCHAR2,
   attr_list  IN   EXF$ATTRIBUTE_LIST,
   operation  IN   VARCHAR2 DEFAULT 'ADD');

Parameters

Table 39-15 INDEX_PARAMETERS Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions.

attr_list

An instance of EXF$ATTRIBUTE_LIST with a partial list of stored and indexed attributes

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.


Usage Notes

Examples

The following command synchronizes the expression set's index parameters with the defaults associated with the corresponding attribute set:

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list => null, 
                               operation => 'DEFAULT');
END;

The following command adds a stored attribute to the expression set's index parameters.

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list =>
                                exf$attribute_list (
                                 exf$attribute (
                                   attr_name => 'CrashTestRating(Model, Year)',
                                   attr_oper => exf$indexoper('all'),
                                   attr_indexed => 'FALSE')),
                               operation => 'ADD');
END;

The following command clears the index parameters associated with the expression set:

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list => null, 
                               operation => 'CLEAR');
END;

A subsequent index creation will use the default index parameters assigned to the corresponding attribute set.


MODIFY_OPERATOR_LIST Procedure

This procedure modifies the list of common operators associated with a certain attribute in the attribute set.

Syntax

DBMS_EXPFIL.MODIFY_OPERATOR_LIST (
   attr_set   IN   VARCHAR2,
   attr_name  IN   VARCHAR2,
   attr_oper  IN   EXF$INDEXOPER);

Parameters

Table 39-16 MODIFY_OPERATOR_LIST Procedure Parameters

Parameter Description

attr_set

The name of the attribute set

attr_name

The name of the stored or indexed attribute being modified

attr_oper

The new list of operators that are frequently used in the predicates with the attribute


Usage Notes

Examples

The following command modifies the operator list associated with the HorsePower(Model,Year) attribute defined in the Car4Sale attribute set.

BEGIN
  DBMS_EXPFIL.MODIFY_OPERATOR_LIST (
    attr_set => 'Car4Sale',
    attr_name => 'HorsePower(Model, Year)',
    attr_oper => exf$indexoper('=','<','>', 'between'));
END;

REVOKE_PRIVILEGE Procedure

This procedure revokes an expression privilege previously granted by the owner.

Syntax

DBMS_EXPFIL.REVOKE_PRIVILEGE (
   expr_tab   IN  VARCHAR2,
   expr_col   IN  VARCHAR2,
   priv_type  IN  VARCHAR2,
   from_user  IN  VARCHAR2);

Parameters

Table 39-17 REVOKE_PRIVILEGE Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions

priv_type

Type of privilege to be revoked

from_user

The user from whom the privilege is to be revoked


Usage Notes

Examples

The following command revokes the INSERT EXPRESSION privilege on the Interest column of the Consumer table from user SCOTT:

BEGIN
  DBMS_EXPFIL.REVOKE_PRIVILEGE (expr_tab  => 'Consumer',
                                expr_col  => 'Interest',
                                priv_type => 'INSERT EXPRESSION',
                                 from_user => 'SCOTT');
END;

UNASSIGN_ATTRIBUTE_SET Procedure

This procedure unassigns an attribute set from a column storing expressions.

Syntax

DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (
   expr_tab   IN   VARCHAR2,
   expr_col   IN   VARCHAR2);

Parameters

Table 39-18 UNASSIGN_ATTRIBUTE_SET Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions


Usage Notes

Examples

The following command unassigns the attribute set previously assigned to the Interest column of the Consumer table. (See "Bulk Loading of Expression Data" in Oracle Database Application Developer's Guide - Rules Manager and Expression Filter.)

BEGIN
  DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'Consumer',
                                      expr_col => 'Interest');
END;

VALIDATE_EXPRESSIONS Procedure

This procedure validates all the expressions in a set.

Syntax

DBMS_EXPFIL.VALIDATE_EXPRESSIONS (
   expr_tab      IN  VARCHAR2,
   expr_col      IN  VARCHAR2,
   exception_tab IN  VARCHAR2 DEFAULT NULL);

Parameters

Table 39-19 VALIDATE_EXPRESSIONS Procedure Parameters

Parameter Description

expr_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions

exception_tab

The name of the exception table. This table is created using the BUILD_EXCEPTIONS_TABLE procedure.


Usage Notes

Examples

The following command validates the expressions stored in the Interest column of the Consumer table.

BEGIN
  DBMS_EXPFIL.VALIDATE_EXPRESSIONS (expr_tab => 'Consumer',
                                    expr_col => 'Interest');
END;

XPINDEX_PARAMETERS Procedure

This procedure is used in conjunction with the INDEX_PARAMETERS procedure to fine-tune the XPath-specific index parameters for each expression set.

Syntax

DBMS_EXPFIL.XPINDEX_PARAMETERS (
   expr_tab   IN   VARCHAR2,
   expr_col   IN   VARCHAR2,
   xmlt_attr  IN   VARCHAR2,
   xptag_list IN   EXF$XPATH_TAGS,
   operation  IN   VARCHAR2 DEFAULT 'ADD');

Parameters

Table 39-20 XPINDEX_PARAMETERS Procedure Parameters

Parameter Description

exp_tab

The table storing the expression set

expr_col

The column in the table that stores the expressions

xmlt_attr

The name of the attribute with the XMLType datatype

xptag_list

An instance of EXF$XPATH_TAGS type with a partial list of XML elements and attributes

operation

The operation to be performed on the list of index parameters. Default value: ADD. Valid values: ADD and DROP.


Usage Notes

Examples

The following command synchronizes the expression set's index parameters (XPath and non-XPath) with the defaults associated with the corresponding attribute set:

BEGIN
  DBMS_EXPFIL.INDEX_PARAMETERS(expr_tab  => 'Consumer',
                               expr_col  => 'Interest',
                               attr_list => null, 
                               operation => 'DEFAULT');
END;

The following command adds an XPath-specific index parameter to the expression set:

BEGIN
  DBMS_EXPFIL.XPINDEX_PARAMETERS(expr_tab   => 'Consumer',
                                 expr_col   => 'Interest',
                                 xmlt_attr  => 'Details',
                                 xptag_list =>
                                  exf$xpath_tags(
                                   exf$xpath_tag(tag_name    => 'GPS',
                                                 tag_indexed => 'TRUE',
                                                 tag_type    => NULL)),
                                 operation  => 'ADD');
END;