sqlldr匯入資料包錯 Field in data file exceeds maximum length

value used for ROWS parameter changed from 64 to 19
Record 1: Rejected - Error on table DELLYSD.SHIPLABEL, column TNINTERNALID.
Field in data file exceeds maximum length
Record 2: Rejected - Error on table DELLYSD.SHIPLABEL, column TNINTERNALID.
Field in data file exceeds maximum length
Record 4: Rejected - Error on table DELLYSD.SHIPLABEL, column TNINTERNALID.
Field in data file exceeds maximum length
Record 5: Rejected - Error on table DELLYSD.SHIPLABEL, column TNINTERNALID.
Field in data file exceeds maximum length
Record 3: Rejected - Error on table DELLYSD.SHIPLABEL, column TNINTERNALID.
ORA-12899: value too large for column "DELLYSD"."SHIPLABEL"."TNINTERNALID" (actual: 965, maximum: 60)

Record 6: Rejected - Error on table DELLYSD.SHIPLABEL, column MESGID.
ORA-01400: cannot insert NULL into ("DELLYSD"."SHIPLABEL"."MESGID")

 

 

 

 

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)
)