Home » RDBMS Server » Server Utilities » Malformed UTF-8 in external table load causes invalid number errors
icon5.gif  Malformed UTF-8 in external table load causes invalid number errors [message #187610] Mon, 14 August 2006 12:33
Messages: 3
Registered: August 2006
Location: Santa Cruz, CA
Junior Member

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:

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'
missing field values are null
location ('data.txt')
reject limit unlimited;

Here's how the fields appear in the log file:
Terminated by "09"
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

Previous Topic: Split record in SQL*Loader
Next Topic: There's no tkprof utility in my computer
Goto Forum:

Current Time: Tue Oct 25 04:17:50 CDT 2016

Total time taken to generate the page: 0.19396 seconds