If no data type is specified for a field, then it is assumed to be
CHAR(1) for a nondelimited field, and
CHAR(255)for a delimited field.
If no field list is specified, then the fields in the data file are assumed to be in the same order as the fields in the external table. The data type for all fields is
CHAR(255) unless the column in the database is
VARCHAR. If the column in the database is
VARCHAR, then the data type for the field is still
CHAR but the length is either 255 or the length of the column, whichever is greater.
If no field list is specified and no
delim_spec clause is specified, then the fields in the data file are assumed to be in the same order as fields in the external table. All fields are assumed to be
CHAR(255) and terminated by a comma.
This example shows the definition for an external table with no
field_list and a
delim_spec. 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 INT) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir ACCESS PARAMETERS (FIELDS TERMINATED BY "|") LOCATION ('info.dat')); Alvin|Tolliver|1976 Kenneth|Baer|1963 Mary|Dube|1973
The syntax for the
field_list clause is as follows:
field_name is a string identifying the name of a field in the data file. If the string is not within quotation marks, then the name is uppercased when matching field names with column names in the external table.
field_name matches the name of a column in the external table that is referenced in the query, then the field value is used for the value of that external table column. If the name does not match any referenced name in the external table, then the field is not loaded but can be used for clause evaluation (for example
pos_spec clause indicates the position of the column within the record. For a full description of the syntax, see "pos_spec Clause".
datatype_spec clause indicates the data type of the field. If
datatype_spec is omitted, then the access driver assumes the data type is
CHAR(255). For a full description of the syntax, see "datatype_spec Clause".
init_spec clause indicates when a field is
NULL or has a default value. For a full description of the syntax, see "init_spec Clause".