Blog Oracle

sqlldr - error field in data file exceeds maximum length

One of our migration consultants is asking our Siebel Administrator as to why his sqlldr is not working as expected. I pitched in because I thought the problem is interesting. His problem seems trivial as he's just trying to load a rather long string from his text file into a varchar2 field with a length of 1000. However, one of his records gets rejected even with only 340 characters in his input data.

Here's a representation of this control file.


LOAD DATA
APPEND INTO TABLE SOME_SIEBEL_TEMP_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
DOC_ID ,
DOC_TYPE ,
SALUTATION ,
GENDER ,
NATIONALITY ,
COB ,
DOB ,
EMAIL ,
CONTACT_MOBILE,
OCCUPATION ,
MARITAL_STATUS,
INTERESTS
)

Based on the log, loading fails on INTERESTS with "Field in data file exceeds maximum length" error message. The data being loaded is around 340 characters long which is well within the 1000 limit. According to him, it loads with no issues if he trims the data. So obviously, there's some kind of maximum length sqlldr reads.

Here's how part of the log looks.


Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_ID NEXT * , O(") CHARACTER
DOC_TYPE NEXT * , O(") CHARACTER
SALUTATION NEXT * , O(") CHARACTER
GENDER NEXT * , O(") CHARACTER
NATIONALITY NEXT * , O(") CHARACTER
COB NEXT * , O(") CHARACTER
DOB NEXT * , O(") CHARACTER
EMAIL NEXT * , O(") CHARACTER
CONTACT_MOBILE NEXT * , O(") CHARACTER
OCCUPATION NEXT * , O(") CHARACTER
MARITAL_STATUS NEXT * , O(") CHARACTER
INTERESTS NEXT * , O(") CHARACTER

As it turns out, SQLLDR reads a maximum of 255 characters by default unless specified in the control file. So if you are trying to load a larger string, then you need to override it by specifying the maximum length to read. So here's how it should look.


LOAD DATA
APPEND INTO TABLE SOME_SIEBEL_TEMP_TABLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
DOC_ID ,
DOC_TYPE ,
SALUTATION ,
GENDER ,
NATIONALITY ,
COB ,
DOB ,
EMAIL ,
CONTACT_MOBILE,
OCCUPATION ,
MARITAL_STATUS,
INTERESTS CHAR(1000)
)

Here's how it looks in the logs after updating the control file.


Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC_ID NEXT * , O(") CHARACTER
DOC_TYPE NEXT * , O(") CHARACTER
SALUTATION NEXT * , O(") CHARACTER
GENDER NEXT * , O(") CHARACTER
NATIONALITY NEXT * , O(") CHARACTER
COB NEXT * , O(") CHARACTER
DOB NEXT * , O(") CHARACTER
EMAIL NEXT * , O(") CHARACTER
CONTACT_MOBILE NEXT * , O(") CHARACTER
OCCUPATION NEXT * , O(") CHARACTER
MARITAL_STATUS NEXT * , O(") CHARACTER
INTERESTS NEXT 1000 , O(") CHARACTER

If you notice, the len for INTERESTS is no longer set to * (Default). Instead, the length is set to 1000 which is the same number we put in the control file. That will direct sqlldr to read maximum of 1000 characters instead of the 255 default.

So yes, it's solved!

This was explained by AskTom in this link.