oracle 数据文件中的字段超过最大长度 - 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31578781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Field in data file exceeds maximum length - error
提问by user3195273
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".
当我尝试将数据插入varchar2(4000)表中大小的列时，我收到错误为"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
回答by Gary_W
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
该错误消息是因为从数据文件中读取的数据大于 sqlldr 的默认字符缓冲区 255，如果未指定 CHAR 和大小，则使用该缓冲区。请注意，这与字段对应的列的大小不同。例如，如果我有一个 VARCHAR2(4000) 的表列，但没有在控制文件中明确给出大小
cola not null,
and the data in the data file exceeds 255 but is less than 4000 in length, you'll get the error.
如果数据文件中的数据超过255但长度小于4000，则会出现错误。
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
一切都会好起来，好像创建了一个更大的缓冲区（这里它与列大小匹配）。因此，请养成始终包含列大小的习惯。为您省去一些麻烦并创建一个函数来为您生成默认控制文件......等等我为您发布了我的，试试看：https: //stackoverflow.com/a/37947714/2543416
回答by ridonekorkmaz
Use CHAR(NN)in your control file for a column which exceeds its length 255.
So your control file should like this;
使用CHAR(NN)在超过其长度为255所以，你的控制文件应该像这样的列控制文件;
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.
我不知道如何使用CHAR(4000)with CONSTANT "0"。但这应该可以解决问题。
