Field in data file exceeds maximum length - error

Question

when i am trying to insert data in to a column of size varchar2(4000) in table i am getting error as "Field in data file exceeds maximum length".

the data =

1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,31,33,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,111,112,121,654,666,667,1001,1100,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1123,1124,1211,2001,2002,2003,2004,2028,2101,2102,2201,2202,2301,2302,2303,2401,2402,3001,3002,3003,3004,3010,3011,3012,3013,3020,3021,3022,3023,3024,3025,3030,3031,3032,3040,3041,3042,3043,3044,3045,3046,3047,3050,3051,3052,3053,3054,3055,3060,3061,3062,3070,3071,3072,3080,3081,3082,3083,3084,3090,3091,3092,3100,3102,3103,3110,3111,3112,3113,3120,3121,3122,3123,3130,3131,3132,5656,8040,9000`

Please help me to resolve this error.

control file:

LOAD DATA
INFILE '$IN_DIR/$FILENAME'
BADFILE '$REJ_DIR/$FILENAME.bad'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
  (
   FILE_RECORD_DESCRIPTOR               POSITION(1),
   LINE_NO                              FILLER,
   DEPT                                 TERMINATED BY "\n",
   SEQ_NO                               CONSTANT "1",
   DEPT_NO                              CONSTANT "0",
   STATUS                               CONSTANT "U",
   PROCESS_ID                           CONSTANT "2"
  )

table structure.

  CREATE TABLE TEST
   (
   FILE_RECORD_DESCRIPTOR VARCHAR2(5)    NOT  NULL, 
   DEPT                   VARCHAR2(4000) NOT NULL, 
   SEQ_NO                 NUMBER         NOT NULL, 
   DEPT_NO                NUMBER(4,0),
   STATUS                 VARCHAR2(10), 
   ERROR_DETAIL           VARCHAR2(3000), 
   PROCESS_ID             NUMBER(8,0)
   );

file:

FILE_RECORD_DESCRIPTOR,LINE_NO,DEPT
EXDEP,2,1,2,3,4,5,6,7,8,9,10,11,12,13,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,31,33,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,111,112,121,654,666,667,1001,1100,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1119,1120,1121,1123,1124,1211,2001,2002,2003,2004,2028,2101,2102,2201,2202,2301,2302,2303,2401,2402,3001,3002,3003,3004,3010,3011,3012,3013,3020,3021,3022,3023,3024,3025,3030,3031,3032,3040,3041,3042,3043,3044,3045,3046,3047,3050,3051,3052,3053,3054,3055,3060,3061,3062,3070,3071,3072,3080,3081,3082,3083,3084,3090,3091,3092,3100,3102,3103,3110,3111,3112,3113,3120,3121,3122,3123,3130,3131,3132,5656,8040,9000

Answer 1

The error message is because the data read in from the data file is larger that sqlldr's default character buffer of 255 which is used if no CHAR and size is specified. Note this is different then the size of the column that the field corresponds to. For example, if I have a table column of VARCHAR2(4000), but do not explicitly give a size in the control file

cola  not null,

and the data in the data file exceeds 255 but is less than 4000 in length, you'll get the error.

However, if the control file states the buffer size like this:

cola char(4000) not null,

all will be good as if creates a larger buffer (here it matches the column size). So, just get in the habit of always including the column sizes. Save yourself some hassle and create a function to generate a default control file for you...wait I posted mine for you, give it a try: https://stackoverflow.com/a/37947714/2543416

Answer 2

Use CHAR(NN) in your control file for a column which exceeds its length 255. So your control file should like this;

LOAD DATA
INFILE '$IN_DIR/$FILENAME'
BADFILE '$REJ_DIR/$FILENAME.bad'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
  (
   FILE_RECORD_DESCRIPTOR               POSITION(1),
   LINE_NO                              FILLER,
   DEPT                                 TERMINATED BY "\n",
   SEQ_NO                               CONSTANT "1",
   DEPT_NO                              CHAR(4000),
   STATUS                               CONSTANT "U",
   PROCESS_ID                           CONSTANT "2"
  )

I do not know how to use CHAR(4000) with CONSTANT "0". Bu this should solve the problem.