Home » RDBMS Server » Server Utilities » sql ldr
sql ldr [message #434240] Tue, 08 December 2009 01:43 Go to next message
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 #434242 is a reply to message #434240] Tue, 08 December 2009 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what should i do?

Learn to count the column offsets?
Read the documentation about SQL*Loader control file as it is obious from your data that column limits are not fixed adn so you can't use POSITION. There are many examples in the documentation.

Regards
Michel
Re: sql ldr [message #434278 is a reply to message #434240] Tue, 08 December 2009 05:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous message
Barbara Boehmer
Messages: 8636
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.

Previous Topic: Unescaped quotes & External Tables
Next Topic: import a schema
Goto Forum:
  


Current Time: Sat Dec 10 20:36:08 CST 2016

Total time taken to generate the page: 0.12637 seconds