Home » RDBMS Server » Server Utilities » SQL Loader Issue (Oracle 10g)
SQL Loader Issue [message #337068] Tue, 29 July 2008 18:33 Go to next message
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 Go to previous message
Barbara Boehmer
Messages: 8635
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",
Previous Topic: Max Primary Key Value for Oracle 10g
Next Topic: Control Files in Oracle
Goto Forum:
  


Current Time: Thu Dec 08 12:09:47 CST 2016

Total time taken to generate the page: 0.06712 seconds