OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL Loader Error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader Error [message #632795]

Thu, 05 February 2015 13:52

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

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.

Report message to a moderator

Re: SQL Loader Error [message #632797 is a reply to message #632795]

Thu, 05 February 2015 15:24

Michel Cadot
Messages: 68418
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.

Report message to a moderator