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
thecentrecannothold
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
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted

Fields in Data Source:

ACTION VARCHAR (2, 128)
Terminated by "09"
AFCOOKIE VARCHAR (2, 256)
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:

CREATE TABLE x_test (
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'
notrim
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
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What happens if you use VARCHAR2 instead of a VARCHAR?
Previous Topic: There's no tkprof utility in my computer
Next Topic: nls_numeric_characters
Goto Forum:
  


Current Time: Mon Dec 05 21:24:31 CST 2016

Total time taken to generate the page: 0.05821 seconds