5. ORA-01841 (full) year must be between -4713 and +9999, and not be 0
Question: I have a table with a column having date as 20150324 i.e YYYYMMDD. When I try to load this data via sqlldr , it gives me the ORA-01841 error
ORA : 01841. ORA-01841: (full) year must be between -4713 and +9999, and not be 0 My SQL*Loader control file looks like this:
admidate date 'YYYYMMDD' NULL IF admidate = BLANKS;
Answer: The oerr utility notes this on the ora-01841 error:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Cause: Illegal year entered
Action: Input year in the specified range
On Oracle MOSC, a case is present concerning ORA-01841. The solution is described as follows and includes some information about ORA-01841 and a few associated bugs. The bBug numbers are 944232 and 935135.
Are you trying to load NULL values in records?
If so, can you do a “change all” to make the NULL values NOT NULL
There is lots of good advice on this error on Google.
Can you view the records to load and visually inspect the date formats?
Try this date format:
admidate date "YYYYMMDD" nullif (admidate=BLANKS)
If you are loading a column with a DATE datatype, then you need to use the to_date function:
admidate TO_DATE("admidate",'yyyymmdd') nullif (admidate=BLANKS)
from Oracle Error Tips by Donald Burleson(S. Karam) —————————————————
or,
check if you put in the space between quotation.
Ex) ' ' --> ''