oracle SQL 加载程序错误：“可变长度字段超过最大长度。”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10386020/
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
SQL Loader Error: "Variable length field exceeds maximum length."
提问by tjsimmons
I have a SQL Loader Control file,
我有一个 SQL 加载程序控制文件，
LOAD DATA
INFILE 'test.txt'
INTO TABLE TEST replace
fields terminated "|" optionally enclosed by '"' TRAILING NULLCOLS
( DOCUMENTID INTEGER(10),
CUSTID INTEGER(10),
USERID INTEGER(10),
FILENAME VARCHAR(255),
LABEL VARCHAR(50),
DESCRIPTION VARCHAR(2000),
POSTDATE DATE "YYYY-MM-DD HH24:MI:SS" NULLIF POSTDATE="",
USERFILENAME VARCHAR(50),
STORAGEPATH VARCHAR(255)
)
and it's giving me an error when I run SQL Loader on it,Record 1: Rejected - Error on table TEST, column FILENAME.
Variable length field exceeds maximum length.
当我在它上面运行 SQL Loader 时它给了我一个错误，Record 1: Rejected - Error on table TEST, column FILENAME.
Variable length field exceeds maximum length.
Here's that row.. the length of that column is way under 255..
这是那一行..该列的长度远低于 255..
1|5001572|2|/Storage/Test/5001572/test.pdf|test.pdf||2005-01-13 11:47:49||
1|5001572|2|/Storage/Test/5001572/test.pdf|test.pdf||2005-01-13 11:47:49||
And here's an oddity I noticed within the log file
这是我在日志文件中注意到的一个奇怪之处
Column Name | Position | Len | Term | Encl | DatatypeFILENAME | NEXT | 257 | | | VARCHAR
Column Name | Position | Len | Term | Encl | DatatypeFILENAME | NEXT | 257 | | | VARCHAR
I define the length as 255 in both my table and control file. Yet the log spits it out as 257? I've tried knocking down the length in the control file to 253, so it appears as 255 in the log file, but the same issue.
我在表和控制文件中都将长度定义为 255。然而日志将其吐出为 257？我试过将控制文件中的长度减少到 253，所以它在日志文件中显示为 255，但同样的问题。
Any help? This has bugged me for two days now.
有什么帮助吗？这已经困扰了我两天了。
Thanks.
谢谢。
回答by DCookie
Don't define your data fields as VARCHAR2 and INTEGER. Use CHAR. Most of the time, when loading data from a text file, you want to use CHAR, or perhaps DATE, although even that is converted from a text form. Most of the time you don't even need a length specifier. The default length for a CHAR field is 255. Your control file should look something like:
不要将数据字段定义为 VARCHAR2 和 INTEGER。使用字符。大多数情况下，当从文本文件加载数据时，您希望使用 CHAR 或 DATE，即使它是从文本格式转换而来的。大多数情况下，您甚至不需要长度说明符。CHAR 字段的默认长度为 255。您的控制文件应如下所示：
LOAD DATA
INFILE "test.txt"
INTO TABLE TEST replace
fields terminated "|" optionally enclosed by '"' TRAILING NULLCOLS
(
DOCUMENTID,
CUSTID,
USERID ,
FILENAME,
LABEL,
DESCRIPTION CHAR(2000),
POSTDATE DATE "YYYY-MM-DD HH24:MI:SS" NULLIF POSTDATE=BLANKS,
USERFILENAME,
STORAGEPATH
)
回答by David Aldridge
+1 for DCookie, but to expand on that it's important to distinguish between data types as specified in a table and data types in a SQL*loader control file as they mean rather different things, confusingly.
+1 表示 DCookie，但要扩展这一点，区分表中指定的数据类型和 SQL*loader 控制文件中的数据类型很重要，因为它们的含义相当不同，令人困惑。
Start with a look at the the documentation, and note that when loading regular text files you need to be using the "portable" data types.
首先查看文档，并注意加载常规文本文件时需要使用“可移植”数据类型。
Varchar is a "non-portable" type, in which:
Varchar 是一种“不可移植”类型，其中：
... consists of a binary length subfield followed by a character string of the specified length
... 由一个二进制长度的子字段和一个指定长度的字符串组成
So as DCookie says, CHAR is the thing to go for, and INTEGER EXTERNAL is a very commonly used SQL*Loader data type which you'd probably want to specify for DOCUMENTID etc.
因此，正如 DCookie 所说，CHAR 是要使用的，而 INTEGER EXTERNAL 是一种非常常用的 SQL*Loader 数据类型，您可能希望为 DOCUMENTID 等指定它。
