sqlldr: ield in data file exceeds maximum length in sqlldr datafile.

Find the error from log file:

Record 1: Rejected - Error on table JPA, column FIELD4.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table JPA, column FIELD4.
Field in data file exceeds maximum length

 

This is caused by ading long,clob,blog columns using sqlldr,the following error is reported:

 

SQL> desc jpa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 UUID                                      NOT NULL VARCHAR2(100)
 FIELD1                                    NOT NULL VARCHAR2(100)
 FIELD2                                    NOT NULL VARCHAR2(100)
 FIELD3                                             VARCHAR2(4000)
 FIELD4                                             CLOB
 FIELD5                                             NUMBER(20)
 FIELD6                                             VARCHAR2(100)
 FIELD7                                    NOT NULL NUMBER(20)
 FIELD8                                             NUMBER(3)

Modify the control file and add char(2000) to the columns.

$ more jpa.ctl
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=1)
LOAD DATA
INFILE '/opt/app/oracle/henry/pv/jpa_data.csv'
APPEND INTO TABLE Jpa
FIELDS TERMINATED BY ',' Optionally enclosed by '"' TRAILING NULLCOLS
(
  uuid,
  field1,
  field2,
  field3 char(1000),
  field4 char(2000),
  field5,
  field6,
  field7,
  field8
)

Then it works well.

In the control file I did not specificy the column longth with char(n) where n is an integer. The default will work in most time but this times it caused the issue.