Field in data file exceeds maximum length

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

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

Symptoms

Loading long columnsusing sqlldr,the following error is reported:

 

" Record 1:Rejected - Error on table CRM_ATIC_HEADER_DIF, column LONG_DESC.

Field in data fileexceeds maximum length "

Cause

In the controlfile,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 TABLEsload

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_DESCis a long type described in the table definition of sload.

There are many othercolumns that are described as varchar2 in the table definition.

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

Fix

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

 

2.Check the controlfile to make sure whether char(n) has been included for those correspondingcolumns.

Taking the exampleof the above control file,if these changes are made:

3.Changing thecontrol file by including char(n) will resolve the issue

LOAD DATA

INFILE data.dat

APPEND INTO TABLEsload

FIELDS TERMINATED BY","

TRAILING NULLCOLS

(

PROCESS_DT, 

PROCESS_ID, 

PROCESS_FLG, 

TICKET_ID char(50),

TICKET_TYPEchar(50),

TECHNOLOGY char(50),

SUBFLAVOR char(50),

LIB_VER_NUMchar(50),

PRIORITY char(50),

STATE char(50),

OPEN_DATE DATE'DD-MON-YY',

REV_RELEASEchar(50),

SHORT_DESC char(50),

LONG_DESCchar(1000) 

)