Field in data file exceeds maximum length

最近使用sqlldr 导入数据，总是报Field in data file exceeds maximum length 这个错误，很是奇怪

最后上网查询，发现需要指定列的长度和类型 如char(4000)

Symptoms

Loading long columns using sqlldr,the following error is reported:

" Record 1: Rejected - Error on table CRM_ATIC_HEADER_DIF, column LONG_DESC.
Field in data file exceeds maximum length "

Cause

In the control file,char(n) had to be specified for all the "varchar2" and "long" columns that were there in the table definition
.
E.g. Control file:
LOAD DATA
INFILE data.dat
APPEND INTO TABLE sload
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
PROCESS_DT,
PROCESS_ID,
PROCESS_FLG,
TICKET_ID ,
TICKET_TYPE ,
TECHNOLOGY ,
SUBFLAVOR ,
LIB_VER_NUM ,
PRIORITY ,
STATE ,
OPEN_DATE DATE 'DD-MON-YY',
REV_RELEASE ,
SHORT_DESC ,
LONG_DESC
)

Note that LONG_DESC is a long type described in the table definition of sload.
There are many other columns that are described as varchar2 in the table definition.

But in the control file char(n) where n is an integer is not specified.

Fix

1.Check if there are any LONG or VARCHAR2 columns in the table where the data has to be loaded

2.Check the control file to make sure whether char(n) has been included for those corresponding columns.

Taking the example of the above control file,if these changes are made:
3.Changing the control file by including char(n) will resolve the issue

LOAD DATA
INFILE data.dat
APPEND INTO TABLE sload
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
PROCESS_DT,
PROCESS_ID,
PROCESS_FLG,
TICKET_ID char(50),
TICKET_TYPE char(50),
TECHNOLOGY char(50),
SUBFLAVOR char(50),
LIB_VER_NUM char(50),
PRIORITY char(50),
STATE char(50),
OPEN_DATE DATE 'DD-MON-YY',
REV_RELEASE char(50),
SHORT_DESC char(50),
LONG_DESC char(1000)
)