The optional ORACLE_LOADER access drive COLUMN TRANSFORMS clause provides transforms that you can use to describe how to load columns in the external table that do not map directly to columns in the data file.
Syntax
The syntax for the column_transforms clause is as follows:
Description of et_column_trans.eps follows
Description of the illustration et_column_trans.eps
Note:The COLUMN TRANSFORMS clause does not work in conjunction with the PREPROCESSOR clause.
transform
Each transform specified in the transform clause identifies a column in the external table and then a specifies how to calculate the value of the column.
Parent topic: The ORACLE_LOADER Access Driver
15.5.1 transform
Each transform specified in the transform clause identifies a column in the external table and then a specifies how to calculate the value of the column.
The syntax is as follows:
Description of et_transform.eps follows
Description of the illustration et_transform.eps
The NULL transform is used to set the external table column to NULL in every row. The CONSTANT transform is used to set the external table column to the same value in every row. The CONCAT transform is used to set the external table column to the concatenation of constant strings and/or fields in the current record from the data file. The LOBFILE transform is used to load data into a field for a record from another data file. Each of these transforms is explained further in the following sections.
column_name FROM
The column_name uniquely identifies a column in the external table that you want to be loaded.
NULL
When the NULL transform is specified, every value of the field is set to NULL for every record.
CONSTANT
The CONSTANT clause transform uses the value of the string specified as the value of the column in the record.
CONCAT
The CONCAT transform concatenates constant strings and fields in the data file together to form one string.
LOBFILE
The LOBFILE transform is used to identify a file whose contents are to be used as the value for a column in the external table.
lobfile_attr_list
The lobfile_attr_list lists additional attributes of the LOBFILE.
STARTOF source_field (length)
The STARTOF keyword allows you to create an external table in which a column can be a substring of the data in the source field.
Parent topic: column_transforms Clause
15.5.1.1 column_name FROM
The column_name uniquely identifies a column in the external table that you want to be loaded.
Note that if the name of a column is mentioned in the transform clause, then that name cannot be specified in the FIELDS clause as a field in the data file.
Parent topic: transform
15.5.1.2 NULL
When the NULL transform is specified, every value of the field is set to NULL for every record.
Parent topic: transform
15.5.1.3 CONSTANT
The CONSTANT clause transform uses the value of the string specified as the value of the column in the record.
If the column in the external table is not a character string type, then the constant string will be converted to the data type of the column. This conversion will be done for every row.
The character set of the string used for data type conversions is the character set of the database.
Parent topic: transform
15.5.1.4 CONCAT
The CONCAT transform concatenates constant strings and fields in the data file together to form one string.
Only fields that are character data types and that are listed in the fields clause can be used as part of the concatenation. Other column transforms cannot be specified as part of the concatenation.
Parent topic: transform
15.5.1.5 LOBFILE
The LOBFILE transform is used to identify a file whose contents are to be used as the value for a column in the external table.
All LOBFILEs are identified by an optional directory object and a file name in the form directory object:filename. The following rules apply to use of the LOBFILE transform:
Both the directory object and the file name can be either a constant string or the name of a field in the field clause.
If a constant string is specified, then that string is used to find the LOBFILE for every row in the table.
If a field name is specified, then the value of that field in the data file is used to find the LOBFILE.
If a field name is specified for either the directory object or the file name and if the value of that field is NULL, then the column being loaded by the LOBFILE is also set to NULL.
If the directory object is not specified, then the default directory specified for the external table is used.
If a field name is specified for the directory object, then the FROM clause also needs to be specified.
Note that the entire file is used as the value of the LOB column. If the same file is referenced in multiple rows, then that file is reopened and reread in order to populate each column.
Parent topic: transform
15.5.1.6 lobfile_attr_list
The lobfile_attr_list lists additional attributes of the LOBFILE.
The syntax is as follows:
Description of et_lobfile_attr.eps follows
Description of the illustration et_lobfile_attr.eps
The FROM clause lists the names of all directory objects that will be used for LOBFILEs. It is used only when a field name is specified for the directory object of the name of the LOBFILE. The purpose of the FROM clause is to determine the type of access allowed to the named directory objects during initialization. If directory object in the value of field is not a directory object in this list, then the row will be rejected.
The CLOB attribute indicates that the data in the LOBFILE is character data (as opposed to RAW data). Character data may need to be translated into the character set used to store the LOB in the database.
The CHARACTERSET attribute contains the name of the character set for the data in the LOBFILEs.
The BLOB attribute indicates that the data in the LOBFILE is raw data.
If neither CLOB nor BLOB is specified, then CLOB is assumed. If no character set is specified for character LOBFILEs, then the character set of the data file is assumed.
Parent topic: transform
15.5.1.7 STARTOF source_field (length)
The STARTOF keyword allows you to create an external table in which a column can be a substring of the data in the source field.
The length is the length of the substring, beginning with the first byte. It is assumed that length refers to a byte count and that the external table column(s) being transformed use byte length and not character length semantics. (Character length semantics might give unexpected results.)
Only complete character encodings are moved; characters are never split. So if a substring ends in the middle of a multibyte character, then the resulting string will be shortened. For example, if a length of 10 is specified, but the 10th byte is the first byte of a multibyte character, then only the first 9 bytes are returned.
The following example shows how you could use the STARTOF keyword if you only wanted the first 4 bytes of the department name (dname) field:
SQL> CREATE TABLE dept (deptno NUMBER(2),
2 dname VARCHAR2(14),
3 loc VARCHAR2(13)
4 )
5 ORGANIZATION EXTERNAL
6 (
7 DEFAULT DIRECTORY def_dir1
8 ACCESS PARAMETERS
9 (
10 RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ','
12 (
13 deptno CHAR(2),
14 dname_source CHAR(14),
15 loc CHAR(13)
16 )
17 column transforms
18 (
19 dname FROM STARTOF dname_source (4)
20 )
21 )
22 LOCATION ('dept.dat')
23 );
Table created.
If you now perform a SELECT operation from the dept table, only the first four bytes of the dname field are returned:
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCO NEW YORK
20 RESE DALLAS
30 SALE CHICAGO
40 OPER BOSTON
4 rows selected.
