Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQLLDR :- Date format not recognized

JCApr 27 2007 — edited Apr 27 2007
Hi
this is related again to data load through sqlldr process...

My data in flat file looks something like this
...
...
SLT433CR|~|N|~|34C|~|10/10/2002 12:26:09 PM|~|U06931
SNSM955TN|~|TK|~|63C|~|12/9/2005 2:21:19 PM|~|U14947
SVS2800KN|~|KB|~|48Y|~|1/14/2003 3:29:18 PM|~|U06931
SAB9KLCT|~|SB|~|47Q|~|9/30/2002 3:37:40 PM|~|U06931
...
...
In control file I have mentioned as
LOAD DATA
INFILE
"/oradata01/oesdb/gpstest/datafeeds/inbound/test1.txt"
REPLACE INTO TABLE BASE_SYSTEM
FIELDS TERMINATED BY '|~|' OPTIONALLY ENCLOSED BY '"'
(
BASE_SYSTEM_CODE CHAR ,
CHASSIS_ID CHAR ,
PROCESSOR_CODE CHAR ,
UPDATE_DATE DATE "TO_DATE(TO_CHAR(TO_DATE(:UPDATE_DATE,'MM/DD/YYYY HH:MI:SS AM or PM'),'DD-MON-YY'))",
,
UPDATE_UID CHAR "REPLACE(:UPDATE_UID,chr(13),'')"
)

But I am getting an error as
ORA-01821: Date format not recognized

I need the date in dd-mon-and yy format only

Any method of formating the date column particularly before pushing to database without any error.

regards
jc
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 25 2007
Added on Apr 27 2007
3 comments
2,270 views