Re: non-ascii characters causing prob in sql*loader

cumin (jkilbourne_at_gmail.com) wrote:
: 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=3Dtrue, errors=3D100)
: 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|MTHU0407=
: 80|Kakk=E8|3|N|

     ^^^^^^^

I'm not clear what data is actually in this field. I also notice a questionable =80 before that in another field.

Is that what is really in the data, or are the =80 and the =E8 the newsreader's rendering of non-ascii bytes?

If so I suspect those bytes are the problem. The log file below says "Character Set UTF8 specified for all input", BUT those bytes would mess up the utf-8 decoding of the input.

I can't say for sure since I can't dump the data to examine the bytes myself, so it's just a guess at this stage.

You could either specify the correct character set for the data, or encode the data to be valid utf-8.

: C0013076|ENG|S|L0383027|PF|S3402559|Y|A3548489|26396013|15566009||SNOMEDCT|=
: SY|15566009|Mal
: du co=EFt|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

: Copyright (c) 1982, 2005, Oracle. All rights reserved.

: 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
: SAB NEXT 20 | CHARACTER
: TTY NEXT 20 | CHARACTER
: CODE NEXT 50 | CHARACTER
: STR NEXT 3000 | CHARACTER
: SRL NEXT * | CHARACTER
: SUPPRESS NEXT 1 | CHARACTER
: CVF NEXT 50 | CHARACTER

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

: 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

--

This programmer available for rent.