Home » RDBMS Server » Server Utilities » Using VARCHAR in external table field list results in "field too long for datatype"
icon5.gif  Using VARCHAR in external table field list results in "field too long for datatype" [message #187613] Mon, 14 August 2006 12:44 Go to next message
Messages: 3
Registered: August 2006
Location: Santa Cruz, CA
Junior Member
When I use VARCHAR instead of CHAR in defining the input for an external table (or SQL*Loader for that matter), I get an invalid error "field too long for datatype".

From the log file:

Field Definitions for table X_TEST
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

Terminated by "09"
Terminated by "09"
KUP-04021: field formatting error for field ACTION
KUP-04026: field too long for datatype
KUP-04101: record 2 rejected in file /datadir/4/data.txt

The error repeats for every row. I know for a fact that the action field is a very short text string, only 4 or 5 characters. However, unless I use CHAR instead of VARCHAR, this entire load fails. I had the same problem with SQL*Loader.

Here's my table definition:

action VARCHAR(128),
afcookie VARCHAR(256),
organization external
type oracle_loader
default directory x_test_dir
access parameters
records delimited by newline
badfile x_test_dir:'loadext'
logfile x_test_dir:'loadext'
characterset 'al32utf8'
skip 1
fields terminated by 0X'09'
missing field values are null (
action VARCHAR(128),
afcookie VARCHAR(256),
... )
location ('data.txt')
reject limit unlimited;

Why can't I use VARCHAR without getting this error? I'm using Oracle 10.1 with RHEL4. Thanks!

[Updated on: Mon, 14 August 2006 12:45]

Report message to a moderator

Re: Using VARCHAR in external table field list results in "field too long for datatype" [message #187643 is a reply to message #187613] Mon, 14 August 2006 15:40 Go to previous message
Messages: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you use VARCHAR2 instead of a VARCHAR?
Previous Topic: Malformed UTF-8 in external table load causes invalid number errors
Next Topic: Loading default value when exists null or blanks SQL Loader
Goto Forum:

Current Time: Tue Aug 22 06:18:04 CDT 2017

Total time taken to generate the page: 0.04166 seconds