SQL Loader Issue [message #337068] |
Tue, 29 July 2008 18:33 |
prashanthgs
Messages: 89 Registered: May 2005 Location: chennai
|
Member |
|
|
Hi,
I am trying to load a set of data using sqlloader in Oracle database.
I am having the column CREATE DATE, PROCESS DATE in table which is TIMESTAMP type.
Pls find my control file:
Load Data
INFILE *
Replace
INTO TABLE ABC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
CREATE_DATE "MM-DD-YYYY",
ETL_PROCESS_DATE "MM-DD-YYYY"
)
BEGINDATA
"100","10-12-2008","11-12-2008",
"101","10-15-2007","25-06-2007",
Result:
Record 2: Rejected - Error on table "owner"."abc", column ETL_PROCESS_DATE.
ORA-00984: column not allowed here
I am not able to sort it out whats the issue. Anything i need to change in the definition as "MM-DD-YYYY HH:MM:SS" etc..
Pls advice.
Prashanth.g.s.
|
|
|
Re: SQL Loader Issue [message #337070 is a reply to message #337068] |
Tue, 29 July 2008 19:10 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You need to add the word DATE or TIMESTAMP before your format and make sure that the format matches your data. It looks like your timestamp has the day before the month.
Load Data
INFILE *
Replace
INTO TABLE ABC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID,
CREATE_DATE DATE "MM-DD-YYYY",
ETL_PROCESS_DATE TIMESTAMP "DD-MM-YYYY"
)
BEGINDATA
"100","10-12-2008","11-12-2008",
"101","10-15-2007","25-06-2007",
|
|
|