Oracle SQL Loader Whitespaces

I’ve recently used Oracle’s SQL Loader with a control file like this:

1
2
3
4
5
6
7
8
9
10
11
12

OPTIONS (BINDSIZE=10000000, READSIZE=10000000, SKIP=1, ROWS=1000, PARALLEL=TRUE )
LOAD DATA
INFILE 'TEMP_DELETE.CSV' "STR '\N'"
APPEND
INTO TABLE TEMP_DELETE
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
          ( CREATION_DATE DATE "dd.mm.yyyy",
            NAME CHAR(4000),
            SERIAL_NUMBER CHAR(4000)
          )

SQL Loader gave me the error message ORA-01722: invalid number.

The definition of the TEMP_DELETE table is

1
2
3
4
5

CREATE TABLE TEMP_DELETE (
     CREATION_DATE DATE,
     NAME VARCHAR2(255),
     SERIAL_NUMBER NUMBER(38,0)
);

And the input file looks like

1
2
3
4
5
6

CREATION_DATE;NAME;SERIAL_NUMBER
03.04.2011;"Name 1";87364
04.04.2011;"Name 2";65765
05.04.2011;"Name 3";23497
06.04.2011;"Name 4";43657
...

Although the files look perfectly fine, I finally figured out the problem. SQL Loader tries to interpret the line breaks in the input file along with the preceding number, i.e. 87364 becomes 87364\n which obviously isn’t a valid number.

To fix this, I modified the control file and added TERMINATED BY WHITESPACE after the last column definition.

1
2
3
4
5
6
7
8
9
10
11
12

OPTIONS (BINDSIZE=10000000, READSIZE=10000000, SKIP=1, ROWS=1000, PARALLEL=TRUE )
LOAD DATA
INFILE 'TEMP_DELETE.CSV' "STR '\N'"
APPEND
INTO TABLE TEMP_DELETE
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
          ( CREATION_DATE DATE "dd.mm.yyyy",
            NAME CHAR(4000),
            SERIAL_NUMBER CHAR(4000) TERMINATED BY WHITESPACE
          )

Comments