OraFAQ Forum: Server Utilities » sqlldr non-ascii loading errors

Home » RDBMS Server » Server Utilities » sqlldr non-ascii loading errors

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr non-ascii loading errors [message #141907]

Wed, 12 October 2005 15:02

cumin
Messages: 82
Registered: August 2005

Member

23 rows of my textfile were flagged as errors in the log file; same field, same error every time:

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 in 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:

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:

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

[Updated on: Wed, 12 October 2005 15:21]

Report message to a moderator