This discussion is archived
13 Replies Latest reply: Oct 26, 2012 10:35 AM by bounethai RSS

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

bounethai Newbie
Currently Being Moderated
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
  • 1. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions , and the output of this query
    SQL> select parameter, value from nls_database_parameters where parameter = ‘NLS_DATE_FORMAT’;
    Can you reproduce the issue by using "01-Aug-2000" instead of "1-Aug-2000" ?

    HTH
    Srini
  • 2. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    Hi Srini,
    The sql output is as follows:

    PARAMETER
    ------------------------------
    VALUE
    --------------------------------------------------------------------------------
    NLS_DATE_FORMAT
    DD-MON-RR

    This is happening on a customer site and was not able to try reproducing with your suggestion. I will try tomorrow. We noticed that the new external table is now loading the date with incorrect century digits. For ex 2003 year field is loading as 0003 into Date fields. Do you have any idea what may be causing this?

    Thanks,
    Thai
  • 3. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    User286067 Journeyer
    Currently Being Moderated
    Do the first 8 records get loaded correctly? The line you show us is it from source datafile? care to show us the data from badfile ?
  • 4. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    Here is a snippet of the bad file data:

    E040110,EE040110,01-Aug-00,31-Dec-12,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040110,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,27667.2,27667.2,27667.2,27667.2,27667.2,27667.2,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    E040100,EE040100,01-May-00,31-Dec-12,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040100,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,31113,31113,31113,31113,31113,31113,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    E040101,EE040101,01-Aug-00,31-Dec-12,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040101,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,31113,31113,31113,31113,31113,31113,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    E000916,EE000916,01-Oct-00,31-Dec-12,1,,NDVC,YES,NL,SFC-Commercial,E,SIP Plan - Control Technologies,,,,,,,E000916,21000000,555000,99000,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,10,,,,,,,,,,,,34905.65,34905.65,34905.65,34905.65,34905.65,34905.65,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    E000807,EE000807,03-Jan-00,31-Dec-12,1,,NDVC,YES,FR,SFC-Commercial,E,STIP Plan - Cross BU,,,,,,,E000807,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,3,,,,,,,,,,,,35448.56,35448.56,35448.56,35448.56,35448.56,35448.56,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    E000851,EE000851,25-Apr-00,31-Dec-12,1,,NDVC,YES,FR,SFC-Commercial,E,SIP Plan - Control Technologies,,,,,,,E000851,21000000,555000,99000,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,13,,,,,,,,,,,,43283.76,43283.76,43283.76,43283.76,43283.76,43283.76,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,


    So, the successfully loaded data is being loaded where year=2003 in the CSV as year=0003. Even though the data is loaded fine the data is not correct. For some reason the external table is set to have the year truncated.
  • 5. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    User286067 Journeyer
    Currently Being Moderated
    bounethai wrote:
    Here is a snippet of the bad file data:

    E040110,EE040110,01-Aug-*00*,31-Dec-*12*,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040110,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,27667.2,27667.2,27667.2,27667.2,27667.2,27667.2,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    There you go ... you have bad data, year should be 4 digit not 2, remember you have specified MASK as DD-MON-YYYY.

    HTHs
  • 6. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    That is from the bad file, but the CSV has records which contain the 4 digit like:

    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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
  • 7. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    User286067 Journeyer
    Currently Being Moderated
    bounethai wrote:
    That is from the bad file, but the CSV has records which contain the 4 digit like:

    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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    Ahem, those are two entirely different rows

    Raj
  • 8. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    Hi Raj,
    That's one record and the forum is word wrapping into two lines.

    Thanks,
    Thai
  • 9. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    Just had a call and access to customer environment. "They" were using a different CSV file than the one they provided and their CSV file had only two digits in the year '12' instead of four digit year '2012'. Thanks for all the replies.
  • 10. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    User286067 Journeyer
    Currently Being Moderated
    Originally posted, 
    
    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,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    
    from bad file 
    E040110,EE040110,01-Aug-00,31-Dec-12,1,,NDVC,YES,DE,SFC,N,STIP Plan - Pressure,,,,,,,E040110,,,,2080,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5,,,,,,,,,,,,27667.2,27667.2,27667.2,27667.2,27667.2,27667.2,,,01-Jan-12,31-Dec-12,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    Wrapped or not, these are two different records, one will succeed and another one will fail, as you found out.

    Raj
  • 11. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    Learning lesson...never believe everything and assume nothing.

    Hi Raj,
    Why doesn't the loader error on the date format? Is there something I need to add in order to have it error if the CSV file has a different DATE format?

    Thanks,
    Thai
  • 12. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    User286067 Journeyer
    Currently Being Moderated
    bounethai wrote:
    Learning lesson...never believe everything and assume nothing.

    Hi Raj,
    Why doesn't the loader error on the date format? Is there something I need to add in order to have it error if the CSV file has a different DATE format?

    Thanks,
    Thai
    I am not sure about sqlloader, but if it causes problems, then I would probably do this via external table since it will give more flexibility. It may be possible with loader to check if you have 2 or 4 digits for year and do accordingly, but I am not well versed with sqlloader. Perhaps someone else can chime in here and provide more clues for us.

    Raj
  • 13. Re: Error ORA-01841: (full) year must be between -4713 and +9999, and not be 0
    bounethai Newbie
    Currently Being Moderated
    Hi Raj,
    I have added my follow up question in another thread How To Error If Date Is Not Expected Format In CSV

    Thanks,
    Thai

Legend

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