SQL*Loader and Tab Delimited Input Data

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

Warning: Undefined array key "ssba_bar_buttons" in /home/techdribble/public_html/wp-content/plugins/simple-share-buttons-adder/php/class-buttons.php on line 598

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.