sqlldr_date

this is my loader ctl file

LOAD DATAINFILE 'trainer.data' BADFILE 'trainer.bad'DISCARDFILE 'trainer.dsc'REPLACEINTO TABLE trainerFIELDS TERMINATED BY ','TRAILING NULLCOLS(ID TERMINATED BY ',' ENCLOSED BY '"',FN TERMINATED BY ',' ENCLOSED BY '"',LN TERMINATED BY ',' ENCLOSED BY '"',GENDER TERMINATED BY ',' ENCLOSED BY '"',PHONE TERMINATED BY ',' ENCLOSED BY '"',MOBILE TERMINATED BY ',' ENCLOSED BY '"',EMAIL TERMINATED BY ',' ENCLOSED BY '"',CERTIFICATE_LEVEL TERMINATED BY ',' ENCLOSED BY '"',IS_PERSONAL_TRAINER TERMINATED BY ',' ENCLOSED BY '"',HIRE_DATE TERMINATED BY ',' ENCLOSED BY '"',IS_ACTIVE ENCLOSED BY '"')

the field 'hire_date' is a datetime field. e.g. '15-JUN-2005 13:30' in the format of 'DD-MON-YYYY HH24:Mi'. please see the first 3 rows of my datafile below:

"1","Cara","Surename","m","06623355","0446002359","lorem.ut.aliquam@dictum.edu","a","y","06-JUN-2006 00:00","y""2","Noble","Surename","m","28435168","0451498500","Mauris.magna.Duis@id.com","a","n","06-JUN-2006 00:00","y""3","Coby","Surename","f","79907443","0460112510","Mauris@accumsan.ca","d","n","06-JUN-2006 00:00","y"


then when i executed the ctl file, i got this error below in the log file

Record 1: Rejected - Error on table TRAINER, column HIRE_DATE.ORA-01830: date format picture ends before converting entire input stringRecord 2: Rejected - Error on table TRAINER, column HIRE_DATE.ORA-01830: date format picture ends before converting entire input stringRecord 3: Rejected - Error on table TRAINER, column HIRE_DATE.ORA-01830: date format picture ends before converting entire input string



solution:
HIRE_DATE DATE "DD-MON-YYYY HH24:Mi" TERMINATED BY ',' ENCLOSED BY '"',

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14855094/viewspace-421460/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14855094/viewspace-421460/