This discussion is archived
2 Replies Latest reply: Oct 26, 2012 11:45 AM by bounethai RSS

How To Error If Date Is Not Expected Format In CSV

bounethai Newbie
Currently Being Moderated
Hi Experts,
I have a situation where I'm expecting the DATE format as 'DD-MON-YYYY', but there are issues when the CSV file is created with DATE as 'DD-MON-YY' (example '15-11-12'). The date is inserted into the DB as '15-11-0012', so I'm looking for a way to not load those records and have those records with incorrect dates added to .bad and .log files. Here's my SQL:
DROP TABLE PER_ALL_ASSIGNMENTS_M_XTERN;

create table PER_ALL_ASSIGNMENTS_M_XTERN(
PERSON_NUMBER     VARCHAR2(30 CHAR),
ASSIGNMENT_NUMBER VARCHAR2(30 CHAR),
EFFECTIVE_START_DATE DATE,
EFFECTIVE_END_DATE DATE,
EFFECTIVE_SEQUENCE NUMBER(4),
***ATTRIBUTECATEGORY VARCHAR2(30 CHAR),
***_ATTRIBUTE1 VARCHAR2(150 CHAR),
...
***ATTRIBUTENUMBER20 NUMBER,
***ATTRIBUTEDATE1 DATE,
***ATTRIBUTEDATE2 DATE,
***ATTRIBUTEDATE3 DATE,
***ATTRIBUTEDATE4 DATE,
***ATTRIBUTEDATE5 DATE,
***ATTRIBUTEDATE6 DATE,
***ATTRIBUTEDATE7 DATE,
***ATTRIBUTEDATE8 DATE,
***ATTRIBUTEDATE9 DATE,
***ATTRIBUTEDATE10 DATE,
***ATTRIBUTEDATE11 DATE,
***ATTRIBUTEDATE12 DATE,
***ATTRIBUTEDATE13 DATE,
***ATTRIBUTEDATE14 DATE,
***ATTRIBUTEDATE15 DATE,
ASG_INFORMATION_CATEGORY VARCHAR2(30 CHAR),
ASG_INFORMATION1 VARCHAR2(150 CHAR),
...
ASG_INFORMATION_NUMBER20 NUMBER,
ASG_INFORMATION_DATE1 DATE,
ASG_INFORMATION_DATE2 DATE,
ASG_INFORMATION_DATE3 DATE,
ASG_INFORMATION_DATE4 DATE,
ASG_INFORMATION_DATE5 DATE,
ASG_INFORMATION_DATE6 DATE,
ASG_INFORMATION_DATE7 DATE,
ASG_INFORMATION_DATE8 DATE,
ASG_INFORMATION_DATE9 DATE,
ASG_INFORMATION_DATE10 DATE,
ASG_INFORMATION_DATE11 DATE,
ASG_INFORMATION_DATE12 DATE,
ASG_INFORMATION_DATE13 DATE,
ASG_INFORMATION_DATE14 DATE,
ASG_INFORMATION_DATE15 DATE
)
organization external
( default directory APPLCP_FILE_DIR
access parameters
( records delimited by newline skip 1
badfile APPLCP_FILE_DIR:'PER_ALL_ASSIGNMENTS_M_XTERN.bad'
logfile APPLCP_FILE_DIR:'PER_ALL_ASSIGNMENTS_M_XTERN.log'
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
(PERSON_NUMBER,     
ASSIGNMENT_NUMBER,
EFFECTIVE_START_DATE CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
EFFECTIVE_END_DATE CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
EFFECTIVE_SEQUENCE,
***_ATTRIBUTE_CATEGORY,
***_ATTRIBUTE1,
...
***_ATTRIBUTE_NUMBER20,
***_ATTRIBUTE_DATE1 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE2 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE3 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE4 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE5 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE6 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE7 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE8 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE9 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE10 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE11 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE12 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE13 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE14 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
***_ATTRIBUTE_DATE15 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_CATEGORY,
ASG_INFORMATION1,
...
ASG_INFORMATION_NUMBER20,
ASG_INFORMATION_DATE1 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE2 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE3 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE4 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE5 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE6 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE7 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE8 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE9 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE10 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE11 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE12 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE13 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE14 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
ASG_INFORMATION_DATE15 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY"
)
)
location ('PER_ALL_ASSIGNMENTS_M.csv')
)
REJECT LIMIT UNLIMITED;

...example CSV record:

E040101,EE040101,1-Aug-00,31-Dec-12,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040101,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,31113,31113,31113,31113,31113,31113,,,1-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Thanks,
Thai

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points