non-ascii characters causing prob in sql*loader

From: cumin <jkilbourne_at_gmail.com>
Date: 14 Oct 2005 09:52:53 -0700
Message-ID: <1129308773.271092.222470@g47g2000cwa.googlegroups.com>

23 rows of my textfile (out of 1,813,915) were flagged as errors in the log file; same field, same error every time, and each has a non-ascii character:

Record 22606: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

My .ctl file looks like this:

options (direct=true, errors=100)
load data
CHARACTERSET UTF8
infile 'snoconsoAB.dat' "str X'0d0a'"
badfile 'snoconsoAB.bad'
discardfile 'snoconsoAB.dsc'
truncate
into table snoconsoAB
fields terminated by '|'
trailing nullcols
(CUI char(8),

LAT char(3),
TS char(1),

LUI char(8),
STT char(3),
SUI char(8),

ISPREF char(1),
AUI char(8),

SAUI char(50),
SCUI char(50),
SDUI char(50),

SAB char(20),
TTY char(20),
CODE char(50),
STR char(3000),
SRL integer external,
SUPPRESS char(1),
CVF char(50)
)

Here are two examples from the .bad file; notice that in the field right before the SRL field, where the error was flagged, there is a non-ascii character, but the field with the error (SRL, third from the right) *does* contain a number, contrary to the .log file indication. The last field is null:

C0005122|ENG|S|L3542581|VO|S4062201|Y|A4394823||||ICPC2ICD10ENG|PT|MTHU040780|Kakkè|3|N| C0013076|ENG|S|L0383027|PF|S3402559|Y|A3548489|26396013|15566009||SNOMEDCT|SY|15566009|Mal du coït|4|N|

Finally, here is the entire log file if that would be helpful. Thank you for any help.

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Oct 12 15:33:33 2005

Control File: snoconsoAB.ctl
Character Set UTF8 specified for all input.

Data File: snoconsoAB.dat
File processing option string: "str X'0d0a'" Bad File: snoconsoAB.bad
Discard File: snoconsoAB.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 100

Continuation: none specified
Path used: Direct

Table SNOCONSOAB, loaded from every logical record. Insert option in effect for this table: TRUNCATE TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ----
---------------------

CUI FIRST 8 | CHARACTER
LAT NEXT 3 | CHARACTER
TS NEXT 1 | CHARACTER

LUI NEXT 8 | CHARACTER
STT NEXT 3 | CHARACTER
SUI NEXT 8 | CHARACTER

ISPREF NEXT 1 | CHARACTER
AUI NEXT 8 | CHARACTER

SAUI NEXT 50 | CHARACTER
SCUI NEXT 50 | CHARACTER
SDUI NEXT 50 | CHARACTER

Record 57129: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 84156: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 84160: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 172882: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 340528: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 340532: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 340539: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 359234: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 435809: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 436933: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 478682: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 500198: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 500200: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 558201: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 620419: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 623224: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 630830: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 648133: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 735947: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 1005170: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 1464521: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Record 1680435: Rejected - Error on table SNOCONSOAB, column SRL. ORA-01722: invalid number

Table SNOCONSOAB:
1813892 Rows successfully loaded.
23 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.

Bind array size not used in direct path. Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 1813915
Total logical records rejected: 23
Total logical records discarded: 0
Direct path multithreading optimization is disabled

Run began on Wed Oct 12 15:33:33 2005
Run ended on Wed Oct 12 15:34:33 2005

Elapsed time was: 00:00:59.69
CPU time was: 00:00:06.75 Received on Fri Oct 14 2005 - 11:52:53 CDT