Home » RDBMS Server » Server Utilities » sqlldr non-ascii loading errors
sqlldr non-ascii loading errors [message #141907] |
Wed, 12 October 2005 15:02 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
Goto Forum:
Current Time: Fri Sep 06 17:00:06 CDT 2024
|