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. The syntax for the
column_transforms clause is as follows:
TRANSFORMS clause does not work in conjunction with the
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:
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 uniquely identifies a column in the external table 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.
NULL transform is specified, every value of the field is set to
NULL for every record.
CONSTANT 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.
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.
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
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
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.
lobfile_attr_list lists additional attributes of the LOBFILE. The syntax is as follows:
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.
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.
CHARACTERSET attribute contains the name of the character set for the data in the LOBFILEs.
BLOB attribute indicates that the data in the LOBFILE is raw data.
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.
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 (
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.