This chapter describes the use of SQL*Loader and Data Pump Export and Import utilities in the presence of one or more Expression columns.
For SQL*Loader operations, the expression data is treated as strings loaded into a
VARCHAR2 column of a database table. The data file can hold the expression data in any format allowed for
VARCHAR2 data, and the control file can refer to the column storing expressions as a column of a
VARCHAR2 data type.
A sample control file used to load a few rows into the
Consumer table is shown in the following example:
LOAD DATA INFILE * INTO TABLE Consumer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (CId, Zipcode, Phone, Interest) BEGINDATA 1,32611,"917 768 4633","Model='Taurus' and Price < 15000 and Mileage < 25000" 2,03060,"603 983 3464","Model='Mustang' and Year > 1999 and Price < 20000" 3,03060,"603 484 7013","HorsePower(Model, Year) > 200 and Price < 20000"
The data loaded into an Expression column is automatically validated using the attribute set associated with the column. This validation is done by the
BEFORE ROW trigger defined on the column storing expressions. Therefore, a direct load cannot be used when the table has one or more Expression columns.
If an Expression Filter index is defined on the column storing expressions, it is automatically maintained during the SQL*Loader operations.To achieve faster bulk loads, the expression validation can be bypassed by following these steps:
DROP INDEX InterestIndex;
BEGIN DBMS_EXPFIL.UNASSIGN_ATTRIBUTE_SET (expr_tab => 'Consumer', expr_col => 'Interest'); END; /
Perform the bulk load operation. Because the Expression columns are converted to
VARCHAR2 columns in the previous step, a direct load is possible in this step.
BEGIN DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET ( attr_set => 'Car4Sale', expr_tab => 'Consumer', expr_col => 'Interest', force => 'TRUE'); END; /
BEGIN DBMS_EXPFIL.VALIDATE_EXPRESSIONS (expr_tab => 'Consumer', expr_col => 'Interest'); END; /
Re-create the indexes on the Expression columns.
A table with one or more Expression columns can be exported and imported back to the same database or a different Oracle database. If a table with Expression columns is being imported into an Oracle database, ensure Expression Filter is installed.
The following guidelines and known behavior associated with exporting and importing tables containing Expression columns will assist you in this operation.
When a table with one or more Expression columns is exported, the corresponding attribute set definitions, along with their object type definitions, are placed in the export dump file. An attribute set definition placed in the dump file includes its default index parameters and the list of approved user-defined functions. However, definitions for the user-defined functions are not placed in the export dump file.
While importing a table with one or more Expression columns from the export dump file, the attribute set creation may fail if a matching attribute set exists in the destination schema. If the attribute set is defined with one or more (embedded) object typed attributes, these types should exist in the database importing the attribute set.
While importing the default index parameters and user-defined function list, the import driver continues the import process if it encounters missing dependent objects. For example, if the function
HorsePower does not exist in the schema importing the
Consumer table, the import of the table and the attribute set proceeds without errors. However, the corresponding entries in the Expression Filter dictionary display null values for object type or output data type fields, an indication the import process was incomplete.
When the Expression Filter index defined on an Expression column is exported, all its metadata is placed in the export dump file. This metadata includes a complete list of stored and indexed attributes configured for the index. During import, this list is used. The attributes are not derived from the default index parameters. If one or more stored attributes use object references (functions) that are not valid in the schema importing the index, the index creation fails with an error. However, the index metadata is preserved in the Expression Filter dictionary.
A table imported incompletely due to broken references to dependent schema objects (in the function list, default index parameters list, and exact index parameters list) may cause runtime errors during subsequent expression evaluation or expression modifications (through DML). Import of such tables can be completed from a SQL*Plus session by resolving all the broken references. Running the Expression Validation utility (
DBMS_EXPFIL.VALIDATE_EXPRESSIONS procedure) can identify errors in the expression metadata and the expressions. You can create any missing objects identified by this utility and repeat the process until all the errors in the expression set are resolved. Then, you can recover the Expression Filter index with the SQL
ALTER INDEX ... REBUILD statement.
In addition to exporting tables and indexes defined in the schema, export of a user places the definitions for attribute sets that are not associated with any Expression column into the export dump file. All the restrictions that apply to the export of tables also apply to the export of a user.