Learn how to name the fields in the data file and specify how to find them in records using the field_definitions clause.
Overview of field_definitions Clause
In the field_definitions clause, you use the FIELDS parameter to name the fields in the data file, and specify how to find fields in records.
delim_spec
The delim_spec clause is used to find the end (and if ENCLOSED BY is specified, the start) of a field.
trim_spec
The trim_spec clause is used to specify that spaces should be trimmed from the beginning of a text field, the end of a text field, or both.
MISSING FIELD VALUES ARE NULL
The effect of MISSING FIELD VALUES ARE NULL depends on whether POSITION is used to explicitly state field positions.
field_list
The field_definitions field_list clause identifies the fields in the data file and their data types.
pos_spec Clause
The ORACLE_LOADER pos_spec clause indicates the position of the column within the record.
datatype_spec Clause
The ORACLE_LOADER datatype_spec clause describes the data type of a field in the data file if the data type is different than the default.
init_spec Clause
The init_spec clause for external tables is used to specify when a field should be set to NULL, or when it should be set to a default value.
LLS Clause
If a field in a data file is a LOB location Specifier (LLS) field, then you can indicate this by using the LLS clause.
Parent topic: The ORACLE_LOADER Access Driver
15.4.1 Overview of field_definitions Clause
In the field_definitions clause, you use the FIELDS parameter to name the fields in the data file, and specify how to find fields in records.
Default
If the field_definitions clause is omitted, then the following is assumed:
The fields are delimited by ‘,’
The fields are of data type CHAR
The maximum length of the field is 255
The order of the fields in the data file is the order in which the fields were defined in the external table
No blanks are trimmed from the field
Syntax
The syntax for the field_definitions clause is as follows:
Description of et_fields_clause.eps follows
Description of the illustration et_fields_clause.eps
Example 15-1 External Table Created Without Access Parameters (Default)
In this example, an external table is created without any access parameters. It is followed by a sample data file, info.dat, that can be used to load the table.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir LOCATION (‘info.dat’));
Alvin,Tolliver,1976
Kenneth,Baer,1963
Parameters to Specify Fields with field_definition
The sections that follow provide an overview of the field definitions that you can specify with the field_definition clause, and some examples of how to use these clauses.
IGNORE_CHARS_AFTER_EOR
This optional parameter specifies that if extraneous characters are found after the last end-of-record, but before the end of the file that do not satisfy the record definition, then they are ignored.
Error messages are written to the external tables log file if all four of the following conditions apply:
The IGNORE_CHARS_AFTER_EOR parameter is set, or the field allows free formatting. (Free formatting means either that the field is variable length, or the field is specified by a delimiter or enclosure characters, and is also variable length).
Characters remain after the last end-of-record in the file.
The access parameter MISSING FIELD VALUES ARE NULL is not set.
The field does not have absolute positioning.
The error messages that are written to the external tables log file are as follows:
KUP-04021: field formatting error for field Col1
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file /home/oracle/datafiles/example.dat
CSV
To direct external tables to access the data files as comma-separated-values format files, use the FIELDS CSV clause. To use this clause, the file should be a stream record format file with the normal carriage return string (for example, \n on Unix or Linux operating systems, and either \n or \r\n on Microsoft Windows operating systems). Record terminators can be included (embedded) in data values. The syntax for the FIELDS CSV clause is as follows:
FIELDS CSV [WITH EMBEDDED | WITHOUT EMBEDDED] [TERMINATED BY ‘,’] [OPTIONALLY ENCLOSED BY ‘"’]
When using the FIELDS CSV clause, note the following:
The default is to not use the FIELDS CSV clause.
The WITH EMBEDDED and WITHOUT EMBEDDED options specify whether record terminators are included (embedded) in the data. The WITH EMBEDDED option is the default.
If WITH EMBEDDED is used, then embedded record terminators must be enclosed, and intra-datafile parallelism is disabled for external table loads.
The TERMINATED BY ‘,’ and OPTIONALLY ENCLOSED BY ‘"’ options are the defaults. They do not have to be specified. You can override them with different termination and enclosure characters.
When the CSV clause is used, a delimiter specification is not allowed at the field level and only delimitable data types are allowed. Delimitable data types include CHAR, datetime, interval, and numeric EXTERNAL.
The TERMINATED BY and ENCLOSED BY clauses cannot be used at the field level when the CSV clause is specified.
When the CSV clause is specified, the default trimming behavior is LDRTRIM. You can override this default by specifying one of the other external table trim options (NOTRIM, LRTRIM, LTRIM, or RTRIM).
The CSV clause must be specified after the IGNORE_CHARS_AFTER_EOR clause, and before the delim_spec clause.
delim_spec Clause
The delim_spec clause is used to identify how all fields are terminated in the record. The delim_spec specified for all fields can be overridden for a particular field as part of the field_list clause. For a full description of the syntax, refer to the delim_spec clause description.
trim_spec Clause
The trim_spec clause specifies the type of whitespace trimming to be performed by default on all character fields. The trim_spec clause specified for all fields can be overridden for individual fields by specifying a trim_spec clause for those fields. For a full description of the syntax, refer to the trim_spec clause description.
ALL FIELDS OVERRIDE
The ALL FIELDS OVERRIDE clause specifies to the access driver that all fields are present, and that they are in the same order as the columns in the external table. You only need to specify fields that have a special definition. This clause must be specified after the optional trim_spec clause, and before the optional MISSING FIELD VALUES ARE NULL clause.
The following is a sample use of the ALL FIELDS OVERRIDE clause. The only field in this example that requires specification is HIREDATE, which requires data format mask. All the other fields take default values.
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘"’ LDRTRIM
ALL FIELDS OVERRIDE
REJECT ROWS WITH ALL NULL FIELDS
(
HIREDATE CHAR(20) DATE_FORMAT DATE MASK “DD-Month-YYYY”
)
MISSING FIELD VALUES ARE NULL
MISSING FIELD VALUES ARE NULL sets to null any fields for which position is not explicitly stated and there is not enough data to fill them. For a full description the description for MISSING FIELD VALUES ARE NULL.
REJECT ROWS WITH ALL NULL FIELDS
REJECT ROWS WITH ALL NULL FIELDS indicates that a row will not be loaded into the external table if all referenced fields in the row are null. If this parameter is not specified, then the default value is to accept rows with all null fields. The setting of this parameter is written to the log file either as “reject rows with all null fields” or as “rows with all null fields are accepted.”
DATE_FORMAT
The DATE_FORMAT clause enables you to specify a datetime format mask once at the fields level, and then have that format apply to all fields of that type that do not have their own mask specified. The datetime format mask must be specified after the optional REJECT ROWS WITH ALL NULL FIELDS clause, and before the fields_list clause.
The DATE_FORMAT can be specified for the following datetime types:
DATE
TIME
TIME
WITH TIME ZONE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
The following example shows a sample use of the DATE_FORMAT clause that applies a date mask of DD-Month-YYYY to any DATE type fields:
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘"’ LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
DATE_FORMAT DATE MASK “DD-Month-YYYY”
( EMPNO, ENAME, JOB, MGR, HIREDATE CHAR(20), SAL, COMM, DEPTNO, PROJNO, ENTRYDATE CHAR(20) )复制
NULLIF | NO NULLIF
The NULLIF clause applies to all character fields (for example, CHAR, VARCHAR, VARCHARC, external NUMBER, and datetime).
The syntax is as follows:
NULLIF {=|!=}{“char_string”|x’hex_string’|BLANKS}
If there is a match using the equal or not equal specification for a field, then the field is set to NULL for that row.
The char_string and hex_string must be enclosed in single- or double-quotation marks.
If a NULLIF specification is specified at the field level, then it overrides this NULLIF clause.
If there is a field to which you do not want the NULLIF clause to apply, then you can specify NO NULLIF at the field level.
The NULLIF clause must be specified after the optional REJECT ROWS WITH ALL NULL FIELDS clause and before the fields_list clause.
The following is an example of using the NULLIF clause in which you specify a field to which you do not want the NULLIF clause to apply. The MGR field is set to NO NULLIF, which means that the NULLIF=“NONE” clause does not apply to that field.
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘"’ LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
NULLIF = “NONE”
(
EMPNO,
ENAME,
JOB,
MGR
)
field_list Clause
The field_list clause identifies the fields in the data file and their data types. For a full description of the syntax, see the description of the field_list clause.
Parent topic: field_definitions Clause
15.4.2 delim_spec
The delim_spec clause is used to find the end (and if ENCLOSED BY is specified, the start) of a field.
Syntax
Description of et_delim_spec.eps follows
Description of the illustration et_delim_spec.eps
Usage Notes
If you specify ENCLOSED BY, then the ORACLE_LOADER access driver starts at the current position in the record, and skips over all whitespace looking for the first delimiter. All whitespace between the current position and the first delimiter is ignored. Next, the access driver looks for the second enclosure delimiter (or looks for the first one again if a second one is not specified). Everything between those two delimiters is considered part of the field.
If TERMINATED BY string is specified with the ENCLOSED BY clause, then the terminator string must immediately follow the second enclosure delimiter. Any whitespace between the second enclosure delimiter and the terminating delimiter is skipped. If anything other than whitespace is found between the two delimiters, then the row is rejected for being incorrectly formatted.
If TERMINATED BY is specified without the ENCLOSED BY clause, then everything between the current position in the record and the next occurrence of the termination string is considered part of the field.
If OPTIONALLY is specified, then TERMINATED BY must also be specified. The OPTIONALLY parameter means the ENCLOSED BY delimiters can either both be present or both be absent. The terminating delimiter must be present, regardless of whether the ENCLOSED BY delimiters are present. If OPTIONALLY is specified, then the access driver skips over all whitespace, looking for the first non-blank character. After the first non-blank character is found, the access driver checks to see if the current position contains the first enclosure delimiter. If it does, then the access driver finds the second enclosure string. Everything between the first and second enclosure delimiters is considered part of the field. The terminating delimiter must immediately follow the second enclosure delimiter (with optional whitespace allowed between the second enclosure delimiter and the terminating delimiter). If the first enclosure string is not found at the first non-blank character, then the access driver looks for the terminating delimiter. In this case, leading blanks are trimmed.
After the delimiters have been found, the current position in the record is set to the spot after the last delimiter for the field. If TERMINATED BY WHITESPACE was specified, then the current position in the record is set to after all whitespace following the field.
To find out more about the access driver’s default trimming behavior, refer to “Trimming Whitespace.” You can override this behavior by using with LTRIM and RTRIM.
A missing terminator for the last field in the record is not an error. The access driver proceeds as if the terminator was found. It is an error if the second enclosure delimiter is missing.
The string used for the second enclosure can be included in the data field by including the second enclosure twice. For example, if a field is enclosed by single quotation marks, then it could contain a single quotation mark by specifying two single quotation marks in a row, as shown in the word don’t in the following example:
‘I don’‘t like green eggs and ham’
There is no way to quote a terminator string in the field data without using enclosing delimiters. Because the field parser does not look for the terminating delimiter until after it has found the enclosing delimiters, the field can contain the terminating delimiter.
In general, specifying single characters for the strings is faster than multiple characters. Also, searching data in fixed-width character sets is usually faster than searching data in varying-width character sets.
Note:The use of the backslash character () within strings is not supported in external tables.
Example: External Table with Terminating Delimiters
Example: External Table with Enclosure and Terminator Delimiters
Example: External Table with Optional Enclosure Delimiters
Related Topics
Trimming Whitespace
Parent topic: field_definitions Clause
15.4.2.1 Example: External Table with Terminating Delimiters
The following is an example of an external table that uses terminating delimiters. It is followed by a sample of the data file that can be used to load it.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (FIELDS TERMINATED BY WHITESPACE)
LOCATION (‘info.dat’));
Alvin Tolliver 1976
Kenneth Baer 1963
Mary Dube 1973
Parent topic: delim_spec
15.4.2.2 Example: External Table with Enclosure and Terminator Delimiters
The following is an example of an external table that uses both enclosure and terminator delimiters. Remember that all whitespace between a terminating string and the first enclosure string is ignored, as is all whitespace between a second enclosing delimiter and the terminator. The example is followed by a sample of the data file that can be used to load it.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (FIELDS TERMINATED BY “,” ENCLOSED BY “(” AND “)”)
LOCATION (‘info.dat’));
(Alvin) , (Tolliver),(1976)
(Kenneth), (Baer) ,(1963)
(Mary),(Dube) , (1973)
Parent topic: delim_spec
15.4.2.3 Example: External Table with Optional Enclosure Delimiters
The following is an example of an external table that uses optional enclosure delimiters. Note that LRTRIM is used to trim leading and trailing blanks from fields. The example is followed by a sample of the data file that can be used to load it.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20), year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘(’ and ‘)’
LRTRIM)
LOCATION (‘info.dat’));
Alvin , Tolliver , 1976
(Kenneth), (Baer), (1963)
( Mary ), Dube , (1973)
Parent topic: delim_spec
15.4.3 trim_spec
The trim_spec clause is used to specify that spaces should be trimmed from the beginning of a text field, the end of a text field, or both.
Description
Directs the ORACLE_LOADER access driver to trim spaces from the beginning of a text field, the end of a text field, or both. Spaces include blanks and other non-printing characters, such as tabs, line feeds, and carriage returns.
Default
The default is LDRTRIM. Specifying NOTRIM yields the fastest performance.
Syntax
Description of et_trim_spec.eps follows
Description of the illustration et_trim_spec.eps
Options
NOTRIM Indicates that you want no characters trimmed from the field.
LRTRIM Indicates that you want both leading and trailing spaces trimmed.
LTRIM Indicates that you want leading spaces trimmed.
RTRIM Indicates that you want trailing spaces trimmed.
LDRTRIM Provides compatibility with SQL*Loader trim features. It is the same as NOTRIM except in the following cases:
If the field is not a delimited field, then spaces will be trimmed from the right.
If the field is a delimited field with OPTIONALLY ENCLOSED BY specified, and the optional enclosures are missing for a particular instance, then spaces are trimmed from the left.
Usage Notes
The trim_spec clause can be specified before the field list to set the default trimming for all fields. If trim_spec is omitted before the field list, then LDRTRIM is the default trim setting. The default trimming can be overridden for an individual field as part of the datatype_spec.
If trimming is specified for a field that is all spaces, then the field will be set to NULL.
In the following example, all data is fixed-length; however, the character data will not be loaded with leading spaces. The example is followed by a sample of the data file that can be used to load it.
CREATE TABLE emp_load (first_name CHAR(15), last_name CHAR(20),
year_of_birth CHAR(4))
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (FIELDS LTRIM)
LOCATION (‘info.dat’));
Alvin, Tolliver,1976
Kenneth, Baer, 1963
Mary, Dube, 1973
Parent topic: field_definitions Clause
