Database Software

Hi there,

I am having problem doing the following:
Load tab delimited data file with null fields to a table using SQL Loader. It seems that SQL Loader will only recognize one whitespace when there are in fact two or more whitespaces(one tab and one or more null fields), so the null fields are ignored and the next field that does have a value, is moved up, resulting data being inserted to wrong columns or rejections if the value in the next field is larger than the null fields.

My control file:

LOAD DATA
INFILE 'invoice.csv'
INTO TABLE TB__Load_Invoices
FIELDS TERMINATED BY WHITESPACE
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(ID,
NUMBER,
TICKET,
CODE
)

For example, here is my data file
ID NUMBER TICKET(VARCHAR2(3)) CODE(VARCHAR2(8))
1 999 123 CC345999
2 879 Null AA999999
3 Null 555 BB1

In this case, while loading the above file using the control file, the second record is rejected because there are two whitespaces between NUMBER and CODE (one is a tab and the other is a null field for TICKET. SQL Loader would recognize only one whitespace and try to insert CODE 'AA999999' to TICKET, resulting rejection because the value is too large. For the third record, the value of TICKET will be inserted to NUMBER and value of CODE inserted to TICKET.

Are there any ways to make SQL Loader recognize that there are two whitespaces, and insert a null value to the null field, instead of trying to insert the value of the next field to the null field?

Could anyone help on this one? Thanks a lot in advance.