field_definitions clause you use the
FIELDS parameter to name the fields in the data file and specify how to find them in records.
field_definitions clause is omitted, then the following is assumed:
The fields are delimited by ','
The fields are of data type
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
The following is an example of an external table created without any access parameters. It is followed by a sample data file,
info.dat, 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 LOCATION ('info.dat')); Alvin,Tolliver,1976 Kenneth,Baer,1963
The syntax for the
field_definitions clause is as follows:
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, they will be ignored.
Error messages are written to the external tables log file if all four of the following conditions apply:
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 get 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
To direct external tables to access the data files as comma-separated-values format files, use the
CSV clause. This assumes that the file is a stream record format file with the normal carriage return string (for example,
\n on UNIX or Linux operating systems and either
\r\n on Windows operating systems). Record terminators can be included (embedded) in data values. The syntax for the
CSV clause is as follows:
FIELDS CSV [WITH EMBEDDED | WITHOUT EMBEDDED] [TERMINATED BY ','] [OPTIONALLY ENCLOSED BY '"']
The following are key points regarding the
The default is to not use the
EMBEDDED options specify whether record terminators are included (embedded) in the data. The
EMBEDDED option is the default.
EMBEDDED is used, then embedded record terminators must be enclosed, and intra-datafile parallelism is disabled for external table loads.
TERMINATED BY ',' and
OPTIONALLY ENCLOSED BY '"' options are the defaults and do not have to be specified. You can override them with different termination and enclosure characters.
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
BY clauses cannot be used at the field level when the CSV clause is specified.
CSV clause is specified, the default trimming behavior is
LDRTRIM. You can override this by specifying one of the other external table trim options (
CSV clause must be specified after the
IGNORE_CHARS_AFTER_EOR clause and before 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, see "delim_spec".
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, see "trim_spec".
ALL FIELDS OVERRIDE
ALL FIELDS OVERRIDE clause tells 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 thee
OVERRIDE clause. The only field that had to be specified was the hiredate, which required a data format mask. All the other fields took 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 see "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 clause allows you to specify a datetime format mask once at the fields level, and have it apply to all fields of that type which 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
DATE_FORMAT can be specified for the following datetime types:
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
NULLIF clause applies to all character fields (for example,
NUMBER, and datetime).
The syntax is as follows:
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.
hex_string must be enclosed in single or double quotation marks.
NULLIF specification is specified at the field level, it overrides this
If there is a field to which you do not want the NULLIF clause to apply, you can specify
NULLIF at the field level (as shown in the following example).
NULLIF clause must be specified after the optional
REJECT ROWS WITH ALL NULL FIELDS clause and before the
The following is an example of using the
NULLIF clause. The
MGR field is set to
NULLIF which means that the
NULLIF="NONE" clause will 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 identifies the fields in the data file and their data types. For a full description of the syntax, see "field_list".