数据文件中的字段超过最大长度-错误 [英] Field in data file exceeds maximum length - error
问题描述
当我尝试将数据插入表中大小为varchar2(4000)的列中时，出现错误"Field in data file exceeds maximum length".
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".
数据=
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`
请帮助我解决此错误.
控制文件:
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"
)
表结构.
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_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
推荐答案
错误消息是因为从数据文件中读取的数据大于sqlldr的默认字符缓冲区255，如果未指定CHAR和大小，则使用该默认字符缓冲区.请注意，这与字段所对应的列的大小不同.例如，如果我有一个VARCHAR2(4000)的表列，但未在控制文件中明确给出大小
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,
，并且数据文件中的数据超过255但长度少于4000，您将收到错误.
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就像创建一个更大的缓冲区(此处与列大小匹配)一样会很好.因此，养成始终包括列大小的习惯.节省您一些麻烦，并创建一个函数为您生成默认控制文件...等我为您发布我的文件后，尝试一下: https://stackoverflow.com/a/37947714/2543416
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
这篇关于数据文件中的字段超过最大长度-错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
