13 Replies Latest reply on Oct 26, 2012 5:35 PM by Bounethai-Oracle

    Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    Bounethai-Oracle
      Hi Experts,
      I seem to be getting the error "Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0" when my dates are in the year 2000. 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),
      ASS_ATTRIBUTE_CATEGORY           VARCHAR2(30 CHAR),
      ASS_ATTRIBUTE1 VARCHAR2(150 CHAR),
      ...
      ASS_ATTRIBUTE_NUMBER20 NUMBER,
      ASS_ATTRIBUTE_DATE1 DATE,
      ASS_ATTRIBUTE_DATE2 DATE,
      ASS_ATTRIBUTE_DATE3 DATE,
      ASS_ATTRIBUTE_DATE4 DATE,
      ASS_ATTRIBUTE_DATE5 DATE,
      ASS_ATTRIBUTE_DATE6 DATE,
      ASS_ATTRIBUTE_DATE7 DATE,
      ASS_ATTRIBUTE_DATE8 DATE,
      ASS_ATTRIBUTE_DATE9 DATE,
      ASS_ATTRIBUTE_DATE10 DATE,
      ASS_ATTRIBUTE_DATE11 DATE,
      ASS_ATTRIBUTE_DATE12 DATE,
      ASS_ATTRIBUTE_DATE13 DATE,
      ASS_ATTRIBUTE_DATE14 DATE,
      ASS_ATTRIBUTE_DATE15 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,
      ASS_ATTRIBUTE_CATEGORY,
      ASS_ATTRIBUTE1,
      ...
      ASS_ATTRIBUTE_NUMBER20,
      ASS_ATTRIBUTE_DATE1 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE2 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE3 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE4 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE5 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE6 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE7 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE8 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE9 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE10 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE11 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE12 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE13 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_ATTRIBUTE_DATE14 CHAR(20) DATE_FORMAT DATE MASK "DD-MON-YYYY",
      ASS_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;

      ...and getting errors when data looks like the following:

      E040101,EE040101,*1-Aug-2000*,31-Dec-4712,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040101,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,31113,31113,31113,31113,31113,31113,,,1-Jan-2012,31-Dec-2012,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

      ...error message:

      error processing column EFFECTIVE_START_DATE in row 19 for datafile /u05/dbadir/jmf/incident_logs/PER_ALL_ASSIGNMENTS_M.csv
      ORA-01841: (full) year must be between -4713 and +9999, and not be 0


      Thanks,
      Thai