SQL*Loader and Tab Delimited Input Data
There’s a great article by Tom Kyte about loading a tab delimited file into Oracle using SQL*Loader which can be viewed here. It talks about using the hexadecimal character X’9′ (tab character) as the delimiter when implementing your control file.
Although, it failed to add that if you add the
OPTIONALLY ENCLOSED BY '"' option in your control file, it WILL NOT WORK. That is, if your file does not contain the specified enclosing character.
For example the following will not work, as it will still lump all tab delimiters into one if there are null values:
LOAD DATA INFILE * INTO TABLE DEPT replace FIELDS TERMINATED BY X'9' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12 SARATOGA 10 ACCOUNTING CLEVELAND 11 ART SALEM 13 FINANCE BOSTON 21 SALES PHILA. 22 SALES ROCHESTER 42 INT'L SAN FRAN
(note: there are two tabs between 12 and SARATOGA in the above)
Either change the file so each null field is enclosed by the character specified in the control file, or get rid of the option altogether (if your file values aren’t enclosed by a character). In conclusion, be careful when using the
OPTIONALLY ENCLOSED BY '"' option as this appears to be an Oracle bug.