| sqlldr invalid number problem [message #164105] |
Tue, 21 March 2006 18:09  |
chaisell
Messages: 1
Registered: March 2006
Location: Toronto
|
Junior Member |
|
|
Haven't used sqlldr since version 8 but this is making me crazy.
Have tried this numerous ways but no results. I never had to set the datatype for numeric before but i keep getting the invalid number error. (see log file below). I have tried using the INTEGER EXTERNAL keyword with the same result. What is it? I did get the data to load into the table using just the INTEGER datatype but this is what get in the table:
select * from test;
STHR OTHR OTHHR TMST TMOT TMDT
808791602 808333436 774929456 914108464 892743220 774912380
774929460 813445168 2083532846 908997686 809073717 2083599662
Does this have to do with NLS or charactersets or encoding.
I am using Oracle Express 10.2, the text file is ANSI and my db characterset is WE8MSWIN1252.
my table:
CREATE TABLE "TEST"
( "STHR" NUMBER(18,2),
"OTHR" NUMBER(18,2),
"OTHHR" NUMBER(18,2),
"TMST" NUMBER(18,2),
"TMOT" NUMBER(18,2),
"TMDT" NUMBER(18,2)
)
/
My control File:
LOAD DATA
INFILE *
APPEND
INTO TABLE bm.test
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(sthr INTEGER EXTERNAL,
othr INTEGER EXTERNAL,
othhr INTEGER EXTERNAL,
tmst INTEGER EXTERNAL,
tmot INTEGER EXTERNAL,
tmdt INTEGER EXTERNAL)
BEGINDATA
2.50|0.00|0.00|64.65|90.51|119.50
4|0.00|0.00|64.65|90.51|119.6
The Log File:
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Mar 21 18:57:59 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: c:\ctl\test.ctl
Data File: c:\ctl\test.ctl
Bad File: c:\ctl\test.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table BM.TEST, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
STHR FIRST * | CHARACTER
OTHR NEXT * | CHARACTER
OTHHR NEXT * | CHARACTER
TMST NEXT * | CHARACTER
TMOT NEXT * | CHARACTER
TMDT NEXT * | CHARACTER
Record 1: Rejected - Error on table BM.TEST, column STHR.
ORA-01722: invalid number
Record 2: Rejected - Error on table BM.TEST, column OTHR.
ORA-01722: invalid number
Table BM.TEST:
0 Rows successfully loaded.
2 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.
Space allocated for bind array: 99072 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 2
Total logical records rejected: 2
Total logical records discarded: 0
Run began on Tue Mar 21 18:57:59 2006
Run ended on Tue Mar 21 18:58:01 2006
Elapsed time was: 00:00:02.39
CPU time was: 00:00:00.06
|
|
|
|
| Re: sqlldr invalid number problem [message #164115 is a reply to message #164105] |
Tue, 21 March 2006 20:13  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Just a guess, but it could be that the numbers in you data are Decimals, not INTEGER.
Try DECIMAL EXTERNAL.
Since you're not using any non-portable data types, fixed-width data, or stings > 255 chars, you can dispense with the data type altogether and Oracle will work it out.
LOAD DATA
INFILE *
APPEND
INTO TABLE bm.test
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(sthr,
othr,
othhr,
tmst,
tmot,
tmdt)
BEGINDATA
2.50|0.00|0.00|64.65|90.51|119.50
4|0.00|0.00|64.65|90.51|119.6
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
