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

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

There are oracle errors that can be easily explained.
Sometimes one of these trivial error messages pops up in an unexpected situation and it's really tough to get to the bottom of it.
And that is exactly what happened yesterday, but i am still doing some tests today.

Let's see first the simplest form of ORA-01841:

select to_date('0000-03-10','YYYY-MM-DD') as d
from dual;

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

Gregorian calendar starts with year 1 and there is very little to say about it when you get this error in a query: just fix the input value.

When the input value comes from an external source and it contains all zeroes, something that I've seen happening quite often when you receive a file from a third party meaning a NULL date value, then you'll have to deal with it using one of those special init_spec clauses like NULLIF or DEFAULTIF (both in SQL*Loader and external tables using the SQL*Loader driver).

However there are less common situations like that i came across today, where i found this message in the log of the SQL*Loader driver invoked by an external table definition in a program that was running since months.

 LOG file opened at 03/10/09 11:13:10

Field Definitions for table IMP_BAD_BOXES
 Record format DELIMITED BY NEWLINE
 Data in file has same endianness as the platform
 Reject rows with all null fields

 Fields in Data Source:

   CREATED                         CHAR (14)
     Date datatype DATE, date mask YYYYMMDDHH24MISS
     Record position (1, 14)
     Trim whitespace same as SQL Loader
   BOX_ID                          CHAR (8)
     Record position (15, 22)
     Trim whitespace same as SQL Loader
   DEPT                            CHAR (2)
     Record position (23, 24)
     Trim whitespace same as SQL Loader
error processing column CREATED in row 5 for datafile BOXES.dat
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Date Cache Statistics for table IMP_BAD_BOXES
 Max Size:      1000
 Entries :         4
 Hits    :         0
 Misses  :         0

A batch script invoked by a Windows service running as System was failing to load a file whereas the same script run manually as Administrator was running just fine and the only difference between the two log files was in the aforementioned error on the last imported line of the file.

The only known difference with the past is in that the input file is now generated by an export procedure whereas it was previously exported manually from an excel spreadsheet.
Practically speaking the old files did not contain the trailing newline at the bottom.

Given the following external table definition:

CREATE TABLE "IMP_BOXES"
( "BOX_ID" NUMBER(8,0),
 "DEPT" VARCHAR2(2 BYTE),
 "CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
(
CREATED POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
BOX_ID  POSITION(15:22) CHAR,
DEPT    POSITION(23:24) CHAR
)
   )
LOCATION
( "IMPORT_DIR":'BOXES.dat'
)
);

a source file of 4 records containing a newline (0D0A) in the last record position would cause ORA-01841 to be raised on the 5th record when launched automatically from the Windows service. The same source file did not cause any error when executed as a user with administrator rights.
This problem was reproduced on two different 10GR2 instances running on top of Windows 2003 SE, so, at least, the good news is this is a consistent pattern.
Oracle NLS session parameters were looking the same (i spooled NLS_SESSION_PARAMETERS before invoking the script), so i'd rule them out as a possible cause, i wonder if the system user is reading a different parameter set from the registry.
Note that adding any combination of MISSING FIELD VALUES ARE NULL and/or REJECT ROWS WITH ALL NULL FIELDS did not affect the result.

So, in the end, i don't really know yet what's going on.

I failed to find the exact reason (or oracle bug?) but at least i could find a workaround, consisting in changing the external table definition as follows (inserted clause in green color):

CREATE TABLE "IMP_BOXES"
( "BOX_ID" NUMBER(8,0),
 "DEPT" VARCHAR2(2 BYTE),
 "CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
LOAD WHEN((1:1) != NULLS)
FIELDS
(
CREATED POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
BOX_ID  POSITION(15:22) CHAR,
DEPT    POSITION(23:24) CHAR
)
   )
LOCATION
( "IMPORT_DIR":'BOXES.dat'
)
);

The advantage of checking a character range using the syntax (n:m) is in that i can discard the record before the parser fails to read the last record.
While i was looking for a solution i also incidentally found out that all Oracle Utilities manual through version 11G, where they talk about the so-called condition specifier, are wrong when they say that you can use the keyword NULL in the comparison.
The correct keyword is NULLS.

Another interesting finding is in that the .bad file generated by Oracle contained a weird 1A character preceding the rejected newline character, a character that was nowhere to be found in the source file. This file is only generated when the script is launched from the service.

Curious, isn't it?

See message translations for ORA-01841 and search additional resources.