I am trying to load a .csv using SQL*Loader and every row is rejected with an invalid number error. The .csv column being loaded contains only numeric characters, the control file specifies datatype DECIMAL EXTERNAL, and the datatype of the column in the target table is NUMBER(8,2). I can't figure out why it won't accept the data.
Here is the control file:
REPLACE INTO TABLE SHI_CLAIMS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
, AMT DECIMAL EXTERNAL
Here is the first few lines of data in the .csv (note the control file skips the first line):
Here is the structure of the table I am trying to load:
CREATE TABLE UST_GLOBAL.SHI_CLAIMS
ID VARCHAR2(9 CHAR),
Here is the .log file that results. Every row winds up in the .bad file.
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Mar 7 16:34:09 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: /u02/sct/ust/shi_claims.ctl
Data File: ././data.7609732
Bad File: 7609732.bad
Discard File: discard.7609732
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table SHI_CLAIMS, loaded from every logical record.
Insert option in effect for this table: REPLACE TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , O(") CHARACTER
AMT NEXT * , O(") CHARACTER
Record 1: Rejected - Error on table SHI_CLAIMS, column AMT.
ORA-01722: invalid number
Record 2: Rejected - Error on table SHI_CLAIMS, column AMT.
ORA-01722: invalid number
(...and so on for every row).
I note that in the copy of the control file that appears in the .log file, the AMT column is shown as CHARACTER, while my control file designates DECIMAL EXTERNAL. Why, and could that be part of the problem?
Thanks for any help...
... the AMT column is shown as CHARACTER, while my control file designates DECIMAL EXTERNAL
That's correct, numeric external means number represented in characters,not in binary format (in other words human readable).
What's your default decimal delimiter, '.' or ','? Your external numbers show '.', but when your delimiter is ',', ORA-01722 is thrown.
Thanks, I already got this question answered in a different forum, and forgot to close this one. It turns out I had hidden newline characters at the end of each line. I loaded the numbers as VARCHAR2, stripped off the final newline, and then converted to NUMBER.
I still don't know why the newline characters were there though. I created my .csv via Excel 2007, which I have done many times before with no problems.