SQL Loader错误:“变量长度字段超过最大长度." [英] SQL Loader Error: "Variable length field exceeds maximum length."

问题描述

我有一个SQL Loader控制文件，

I have a SQL Loader Control file,

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

，当我在上面运行SQL Loader时，它给我一个错误，
Record 1: Rejected - Error on table TEST, column FILENAME. Variable length field exceeds maximum length.

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.

这是该行..该列的长度小于255..

Here's that row.. the length of that column is way under 255..

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 | Datatype
FILENAME | NEXT | 257 | | | VARCHAR

我在表和控制文件中都将长度定义为255.可是日志却显示为257?我尝试将控制文件的长度减少到253，因此在日志文件中显示为255，但这是同样的问题.

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.

有帮助吗?这已经困扰了我两天了.

Any help? This has bugged me for two days now.

谢谢.

推荐答案

不要将数据字段定义为VARCHAR2和INTEGER.使用CHAR.在大多数情况下，从文本文件加载数据时，您希望使用CHAR或DATE，尽管即使从文本格式转换过来也是如此.大多数时候，您甚至不需要长度说明符. CHAR字段的默认长度为255.您的控制文件应类似于:

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:

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
)

这篇关于SQL Loader错误:“变量长度字段超过最大长度."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！