 Using VARCHAR in external table field list results in "field too long for datatype" [message #187613] |
Mon, 14 August 2006 12:44  |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
