Home » RDBMS Server » Server Utilities » SQL Loader Error
SQL Loader Error [message #632795] Thu, 05 February 2015 13:52 Go to next message
VIP2013
Messages: 91
Registered: June 2013
Member
Hi I am using Release 11.2.0.3.0 version of oracle and i am trying to push the record using sql loader utility to my DB table, below is my table structure, two of the column will have constant valuee as mentioned below in the table structure. And the column File_DATE should be combination of two fields(Date and Time) from flat file to have the proper date column format. I am getting error and not able to upload the data using below control file, so need help.


sample file data
Orderdate,Name,Size,Records,Date,Time
06202014,authlogfile06202014.txt,40777214,198915,Jun 21,03:51
06202014,transferfile06202014.txt,372144,2255,Jun 21,01:34



Table structure

Create table file_stats
   (Systemtypecode VARCHAR2(4000), -- this will be a hard code value 'CBD'
    ODATE Date ,
    FILENAME VARCHAR2(4000),
    Filesize Number(20,0),
    Noofrecords NUMBER(20,0),
    File_DATE DATE,
created_date Date -- this will be populated with SYSDATE value
);



below is my control file

OPTIONS
(SKIP=1) 
LOAD DATA
  INFILE 'files.csv'
  APPEND
  INTO table file_stats
  FIELDS TERMINATED BY ','
  ( Systemtypecode CONSTANT "CBD",
    ODATE  DATE 'MMDDYYYY' ,
    FILENAME CHAR,
    Filesize INTEGER,
    Noofrecords INTEGER,
    file_DATE_DDmon  boundfiller CHAR
,file_DATE  "to_date('2014' || :file_DATE_DDmon|| :file_DATE, 'YYYYMON DDHH24:MI')"
   ,created_date CONSTANT "sysdate"
)



when executing below command , all of the records errored out as below


sqlldr schema1/pwd@db1 control=file_stats.ctl log=file_stats.log bad=file_stats.bad


ERROR:
Record 1: Rejected - Error on table FILE_STATS, column FILE_DATE.
ORA-01843: not a valid month

--moderator update: added code tags. jw.

[Updated on: Thu, 05 February 2015 13:59] by Moderator

Report message to a moderator

Re: SQL Loader Error [message #632796 is a reply to message #632795] Thu, 05 February 2015 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>so need help.

If the data file resides on the DB Server, you could utilize EXTERNAL TABLE instead of SQL*Loader

consider the following maxim:
First make it work, then make it fancy.

As interim step load the two parts as VARCHAR2 & then populate DATE column from them using UPDATE statement.
Re: SQL Loader Error [message #632797 is a reply to message #632795] Thu, 05 February 2015 15:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change the control file to:
OPTIONS
(SKIP=1) 
LOAD DATA
  INFILE 'files.csv'
  APPEND
  INTO table file_stats
  FIELDS TERMINATED BY ',' TRAILING NULLCOLS
  ( Systemtypecode CONSTANT "CBD",
    ODATE  DATE 'MMDDYYYY' ,
    FILENAME CHAR,
    Filesize CHAR,
    Noofrecords CHAR,
    file_DATE_DDmon  boundfiller CHAR
,file_DATE  "to_date('2014 ' || :file_DATE_DDmon||' '|| :file_DATE, 'YYYY Mon DDHH24:MI')"
   ,created_date "SYSDATE"
)

And be sure your NLS_DATE_LANGUAGE is set to English or equivalent.
Re: SQL Loader Error [message #632805 is a reply to message #632795] Thu, 05 February 2015 22:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Please see my response on the OTN forum:

https://community.oracle.com/message/12885377#12885377
Re: SQL Loader Error [message #633730 is a reply to message #632805] Tue, 24 February 2015 07:14 Go to previous message
VIP2013
Messages: 91
Registered: June 2013
Member
Thank You so much.
Previous Topic: datapump option for DB upgrade from solaris to Linux
Next Topic: decimal value in control file
Goto Forum:
  


Current Time: Thu Mar 28 05:17:18 CDT 2024