SQL Loader Error: "Variable length field exceeds maximum length."

Question

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

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.

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

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.

Thanks.

Rema

Answer 1

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
)

Superstratum

And.. that was it. Specifying lengths with char wasn't doing it for me either, but what you posted worked perfectly. Thank you! – Rema

Answer 2

+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.

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:

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

Vaulted

Thanks! I'll also keep that in mind. I didn't realize that they were two separate things - I assumed they should math. Binary length subfield + specified length explains why 255 became 257, as well. – Rema

+1, Agree with David here. The distinction between SQL Loader control file type specifications and the spec in the tables themselves always seems to confuse folks. I don't think I've ever used anything besides CHAR and DATE in the control file. – Superstratum

By the way, do you know why there are these different types in control files and tables themselves? – Sandhog

The variety of control file formats represent the various ways in which values like numerics might be sorted in an external file. When they are loaded there are really only floats and numbers in Oracle, but their representation in a data file or stream might vary widely. – Vaulted