 Malformed UTF-8 in external table load causes invalid number errors [message #187610] |
Mon, 14 August 2006 12:33 |
thecentrecannothold
Messages: 3
Registered: August 2006
Location: Santa Cruz, CA
|
Junior Member |
|
|
Hi,
I'm loading some data using an external table. Some of the data has UTF-8 characters. I've noticed that when the UTF-8 doesn't look well formed, I get an error about a column that is fine.
Here are the errors I get:
error processing column TIMESTAMPX in row 72 for datafile /datadir/4/data.txt
ORA-01722: invalid number
error processing column TIMESTAMPX in row 7199 for datafile /datadir/4/data.txt
ORA-01722: invalid number
The values for those rows are 1151756671 and 1151757336, respectively. I suspect that the error is caused by bad UTF-8 in an earlier column page_params_query. The column timestampx is defined as an INTEGER and the column page_params_query is defined as a VARCHAR(2000).
Here is my external table definition:
CREATE TABLE x_test (
action VARCHAR(128),
afcookie VARCHAR(256),
..
page_params_query VARCHAR(2000),
..
srcpvid VARCHAR(40),
timestampx INTEGER,
type VARCHAR(64),
..
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
)
location ('data.txt')
)
reject limit unlimited;
Here's how the fields appear in the log file:
PAGE_PARAMS_QUERY CHAR (2000)
Terminated by "09"
TIMESTAMPX CHAR (255)
Terminated by "09"
Rows that do not have malformed UTF-8 in the page_params_query field load fine with no timestampx errors. Any ideas? I'm running Oracle 10.1 with RHEL4. Thanks!
[Updated on: Mon, 14 August 2006 12:34] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
