0

I am trying to import data from .dat file to a table in oracle database, using SQL loader.

control file: 

LOAD DATA
infile 'E:\labs\labs\lab_18_06.dat'
INTO TABLE INVENTORY.PRODUCT_ON_HAND
APPEND
FIELDS TERMINATED BY ','
(on_hand_id,
product_id,
quantity,
warehouse_city char(30),
last_update date(12) 'DD-MON-YYYY')

data file contains records like this one:

5001,1001,7,Atlanta,01-FEB-2004

table in database:

SQL> desc inventory.product_on_hand;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ON_HAND_ID                                NOT NULL NUMBER(5)
 PRODUCT_ID                                         NUMBER(7)
 QUANTITY                                           NUMBER(5)
 WAREHOUSE_CITY                                     VARCHAR2(30)
 LAST_UPDATE                                        DATE

I am getting the following error in the .log file:

Record 1: Failed - Error in the table INVENTORY.PRODUCT_ON_HAND, column LAST_UPDATE.
ORA-01843: month not valid

Please help to sort out my issue!

UPDATE:

SQL> select sysdate from dual;

SYSDATE
-----------
06-OCT-2013

I set nls_lang='american' and nls_date_format='DD-MON-YYYY', but this didn`t help/

BAD FILE:

5001,1001,7,Atlanta,01-FEB-2004
5002,1001,7,Chicago,01-FEB-2004
5003,1001,7,Orlando,01-FEB-2004
5004,1002,7,Atlanta,01-FEB-2004
5005,1002,7,Chicago,01-FEB-2004
5006,1002,7,Orlando,01-FEB-2004
5007,1003,7,Atlanta,01-FEB-2004
5008,1003,7,Chicago,01-FEB-2004
5009,1003,7,Orlando,01-FEB-2004
5010,1004,7,Atlanta,01-FEB-2004
5011,1004,7,Chicago,01-FEB-2004
5012,1004,7,Orlando,01-FEB-2004
5013,1005,7,Atlanta,01-FEB-2004
5014,1005,7,Chicago,01-FEB-2004
5015,1005,7,Orlando,01-FEB-2004
5016,1006,7,Atlanta,01-FEB-2004
5017,1006,7,Chicago,01-FEB-2004
5018,1006,7,Orlando,01-FEB-2004
5019,1007,7,Chicago,01-FEB-2004
5020,1007,7,Orlando,01-FEB-2004
5021,1007,7,Atlanta,01-FEB-2004
5022,1008,7,Chicago,01-FEB-2004
5023,1008,7,Orlando,01-FEB-2004
5024,1008,7,Atlanta,01-FEB-2004
5025,1009,7,Chicago,01-FEB-2004
5026,1009,7,Orlando,01-FEB-2004
5027,1009,7,Atlanta,01-FEB-2004
5028,1010,7,Chicago,01-FEB-2004
5029,1010,7,Orlando,01-FEB-2004
5030,1010,7,Atlanta,01-FEB-2004
5031,1010,7,Chicago,01-FEB-2004
5032,1011,7,Orlando,01-FEB-2004
5033,1011,7,Atlanta,01-FEB-2004
5034,1012,7,Chicago,01-FEB-2004
5035,1012,7,Orlando,01-FEB-2004
5036,1012,7,Atlanta,01-FEB-2004
5037,1013,7,Chicago,01-FEB-2004
5038,1013,7,Orlando,01-FEB-2004
5039,1014,7,Atlanta,01-FEB-2004
5040,1014,7,Chicago,01-FEB-2004
5041,1015,7,Atlanta,01-FEB-2004
5042,1015,7,Chicago,01-FEB-2004
5043,1015,7,Orlando,01-FEB-2004
5044,1016,7,Atlanta,01-FEB-2004
5045,1016,7,Chicago,01-FEB-2004
5046,1016,7,Orlando,01-FEB-2004
5047,1017,7,Atlanta,01-FEB-2004
5048,1017,7,Chicago,01-FEB-2004
5049,1017,7,Orlando,01-FEB-2004
5050,1018,7,Atlanta,01-FEB-2004
5051,1018,7,Chicago,01-FEB-2004
spin_eight
  • 3,925
  • 10
  • 39
  • 61

2 Answers2

0

try 

alter session set nls_date_language='american'; 

LOAD DATA
infile 'E:\labs\labs\lab_18_06.dat'
INTO TABLE INVENTORY.PRODUCT_ON_HAND
APPEND
FIELDS TERMINATED BY ','
(on_hand_id,
product_id,
quantity,
warehouse_city char(30),
last_update date(12) 'DD-MON-YYYY')
sunysen
  • 2,265
  • 1
  • 12
  • 13
  • result: instead of expected number non numerical symbol was found. Due to MM - is 2 digits, representing number of month. So you solution failed – spin_eight Oct 06 '13 at 09:16
  • alter session set nls_date_language='american'; – sunysen Oct 06 '13 at 09:20
  • using SQL plus I altered session, but the same error remains. Maybe DATA format and 'DD-MON-YYYY' differ and conversion needed? – spin_eight Oct 06 '13 at 09:28
  • data file contains records like this one: 5001,1001,7,Atlanta,01-FEB-2004 - see in my question – spin_eight Oct 06 '13 at 09:39
  • The `alter session` won't change anything because SQL*Loader creates a separate connection (you can't specify the `alter session` in the control file). This must be controlled through an environment variable. –  Oct 06 '13 at 10:24
  • @a_horse_with_no_name I used Oracle manager to edit spfile and to set nls parameters but still error appears and I can`t import data to the table – spin_eight Oct 06 '13 at 11:08
  • select * from v$nls_parameters where parameter like '%DATE%'; show query result – sunysen Oct 06 '13 at 13:01
0

Problem is that you have specified 12 number in date : last_update date(12) 'DD-MON-YYYY' in your control file, thats incorrect.

You should have it as last_update date 'DD-MON-YYYY'

SO control file shld be:

LOAD DATA
infile 'E:\labs\labs\lab_18_06.dat'
INTO TABLE INVENTORY.PRODUCT_ON_HAND
APPEND
FIELDS TERMINATED BY ','
(on_hand_id,
product_id,
quantity,
warehouse_city char(30),
last_update date 'DD-MON-YYYY')
Lokesh
  • 7,810
  • 6
  • 48
  • 78