| sql ldr [message #434240] |
Tue, 08 December 2009 01:43  |
mohan1760
Messages: 59
Registered: June 2008
|
Member |
|
|
Hi all
I am trying to import records using sqlloader.
but getting following error as
ORA-01858: a non-numeric character was found where a numeric was expected
what should i do?
Thanks in advance.
----my control file
LOAD DATA
INFILE 'C:\Documents and Settings\mohan_patil\Desktop\raw data migr\02_Extract.txt'
BADFILE 'C:\Documents and Settings\mohan_patil\Desktop\raw data migr\02_Extract.bad'
DISCARDFILE 'C:\Documents and Settings\mohan_patil\Desktop\raw data migr\02_Extract.dsc'
INTO TABLE "CSP"."T_AIRPORT_MIGR"
INSERT
(IATA_CODE POSITION (3:7),
NAME POSITION (8:32),
CITY_CODE POSITION (33:37),
UPDATE_DATE POSITION (38:45),
UPDATE_TIME POSITION (46:53),
USER_ID POSITION (54:61),
STATION_ID CONSTANT 1)
---data to be imported
LQN QALA NAU LQN 3/12/1997 12:22:26 PM 3/12/1997 12:22:26 PM SQL
MQN MO I RANA MQN 3/12/1997 12:22:27 PM 3/12/1997 12:22:27 PM SQL
NQN NEUQUEN NQN 3/12/1997 12:22:27 PM 3/12/1997 12:22:27 PM SQL
SQN SANANA SQN 3/12/1997 12:22:29 PM 3/12/1997 12:22:29 PM SQL
error i am getting is.......
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
IATA_CODE 3:7 5 CHARACTER
NAME 8:32 25 CHARACTER
CITY_CODE 33:37 5 CHARACTER
UPDATE_DATE 38:45 8 CHARACTER
UPDATE_TIME 46:53 8 CHARACTER
USER_ID 54:61 8 CHARACTER
STATION_ID CONSTANT
Value is '1'
Record 1: Rejected - Error on table "CSP"."T_AIRPORT_MIGR", column UPDATE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 2: Rejected - Error on table "CSP"."T_AIRPORT_MIGR", column UPDATE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 3: Rejected - Error on table "CSP"."T_AIRPORT_MIGR", column UPDATE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected
Record 4: Rejected - Error on table "CSP"."T_AIRPORT_MIGR", column UPDATE_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected.
[mod-edit: code tags added by bb]
[Updated on: Tue, 08 December 2009 14:40] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: sql ldr [message #434278 is a reply to message #434240] |
Tue, 08 December 2009 05:39  |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
Or change the data you want to import so that it's wrapped in double quotes or some other delimiter to make your life easy.
|
|
|
|
| Re: sql ldr [message #434347 is a reply to message #434240] |
Tue, 08 December 2009 14:47 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Once code tags have been added to your post to preerve the formatting, it can be seen that you do have fixed position data. However, you have neglected to count the spaces between the columns when calculating your positions. You have not provided your table structure, so we can only guess that your date and time column are varchar2. If not, then that would cause an additional problem the way you are loading. It would be a better design to have a date column and load both the date and time into it using a date format.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
