SQL loader error: "Variable length field exceeds maximum length."

Question

I have an 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) ) 

and this gives me an error when running SQL Loader on it,
Record 1: Rejected - Error on table TEST, column FILENAME. Variable length field exceeds maximum length.

Here is this row .. the length of this column is 255 ..

1|5001572|2|/Storage/Test/5001572/test.pdf|test.pdf||2005-01-13 11:47:49||

And here is the strangeness that I noticed in the log file

Column Name | Position | Len | Term | Encl | Datatype
FILENAME | NEXT | 257 | | | VARCHAR

I define the length as 255 both in my table and in the control file. But does the magazine spit it out like 257? I tried to reduce the length in the control file to 253, so it appears as 255 in the log file, but the same problem.

Any help? This has been bothering me for two days.

Thanks.

Answer 1

Do not define your data fields as VARCHAR2 and INTEGER. Use CHAR. In most cases, when loading data from a text file, you want to use CHAR or, possibly, DATE, although even this is converted from a text form. In most cases, you don't even need a length specifier. The default length for the CHAR field is 255. Your control file should look something like this:

 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 ) 

Answer 2

+1 for DCookie, but in order to expand it, it is important to distinguish between the data types indicated in the table and the data types in the SQL * loader control file, since they mean quite different things, vaguely.

Start with a document and note that when loading regular text files you need to use "portable" data types.

Varchar is a "non-portable" type in which:

... consists of a binary length subfield followed by a character string of the specified length

Since DCookie says, CHAR is what you need, and INTEGER EXTERNAL is a very often used SQL * Loader data type, which you probably want to specify for DOCUMENTID, etc.