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.

One Thought on “SQLLDR – Error Field in data file exceeds maximum length

  1. Miguel Barrera on June 10, 2015 at 9:11 am said:

    Hi, I’m facing the exceeds maximum length problem. I did find that post in AskTom’s website. However my problem has the pecularity that the data field in the table is a VARCHAR2(10), so it doesn’t exceed SQLLDR’s default 255 and in the control file I do specify the length, it looks like this:

    REPLACE
    INTO TABLE TEMP_TABLE
    Fields Terminated by ‘,’
    Optionally enclosed by ‘”‘
    Trailing Nullcols
    (
    RECORD_ID char(100),
    APP_NAME char(100),
    RELEASED_DATE DATE,
    HCA char(10),
    FLAG char(10),
    RESP_MANAGER char(100),
    )

    Both fields are normally filled with an X or nothing. The first record is rejected already and the value of HCA is X and FLAG is empty. The first record in the .csv file looks as follows:
    41075,X-BOX,16.09.2014 12:31:41,X,,”Doe, John”

    I did however, remove the char(10) part and HCA passes. But I don’t understand why this is happening. I do want to prevent longer data from causing errors in the database.
    Any idea about why I’m getting this behavior?
    How can I solve this problem?

    Thanks for your help

Post Navigation