2 Replies Latest reply: Oct 26, 2012 1:45 PM by bounethai RSS

    How To Error If Date Is Not Expected Format In CSV

    bounethai
      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