The following steps take place when a field uses an OPTIONALLY ENCLOSED BY
clause and a TERMINATED BY
clause.
Any whitespace at the beginning of the field is skipped.
The parser checks to see if the first non-whitespace character found is the start of a string that matches the first OPTIONALLY ENCLOSED BY
delimiter. If it is not, and the OPTIONALLY ENCLOSED BY
delimiters are not present in the data, then the data for the field is read from the current position of the field up to, but not including, the first occurrence of the TERMINATED BY
delimiter. If the TERMINATED BY
delimiter is found in the first column position, then the field is null. If the end of the record is found before the TERMINATED BY
delimiter, then all data up to the end of the record is considered part of the field.
If the first OPTIONALLY ENCLOSED BY
delimiter is found, then the search for the second OPTIONALLY ENCLOSED BY
delimiter begins.
If two of the second OPTIONALLY ENCLOSED BY
delimiters are found adjacent to each other, then they are interpreted as a single occurrence of the delimiter and included as part of the data for the field. The search then continues for the second OPTIONALLY ENCLOSED BY
delimiter.
If the end of the record is found before the second OPTIONALLY ENCLOSED BY
delimiter is found, then the row is rejected.
If the OPTIONALLY ENCLOSED BY
delimiter is present in the data, then the parser looks for the TERMINATED BY
delimiter. If the TERMINATED BY
delimiter is anything other than WHITESPACE
, then whitespace found between the end of the second OPTIONALLY ENCLOSED BY
delimiter and the TERMINATED BY
delimiter is skipped over.
The row is not rejected if the end of record is found before the TERMINATED BY
delimiter is found.
Be careful when you specify whitespace characters as the TERMINATED BY
delimiter and are also using OPTIONALLY ENCLOSED BY
. SQL*Loader strips off leading whitespace when looking for an OPTIONALLY ENCLOSED BY
delimiter. If the data contains two adjacent TERMINATED BY
delimiters in the middle of a record (usually done to set a field in the record to NULL), then the whitespace for the first TERMINATED BY
delimiter will be used to terminate a field, but the remaining whitespace will be considered as leading whitespace for the next field rather than the TERMINATED BY
delimiter for the next field. If you want to load a NULL value, then you must include the ENCLOSED BY
delimiters in the data.