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.

ORA-22929: invalid or missing directory name and ORA-06564: object Xyz does not exist

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

You can see this error when executing a command like:

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY 'IMPORT_DIR';

ORA-22929: invalid or missing directory name

The problem is in the single quotes surrounding the directory object name.
The same error is also returned when using a CREATE TABLE statement.

The correct syntax requires either double quotes for case sensitive names or no quotes at all for case insensitive names.

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY "Import_Dir";

or

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY import_dir;

The last statement is equivalent to :

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY "IMPORT_DIR";

Note that when using case sensitive names, if you mistype the name you'll get:

ORA-06564: object Import_Dir does not exist

See message translations for ORA-22929, ORA-06564 and search additional resources.

ORA-29400: data cartridge error

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

The pair ORA-29913/ORA-29400 is a sort of catch-all exception embedding KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.
The type of errors encountered spans from syntax errors to missing files or privileges.

For instance, yesterday i got this one when i forgot to specify a keyword in the external table definition.

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);

select * from IMP_BAD_BOXES;

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "logfile": expecting one of: "column, ..."
KUP-01007: at line 1 column 1

I marked in red color the position in the statement that caused the run-time error.
Note indeed that when you create the table, no issues are reported, you won't know if it works until you go live.

So, in the end, this verbose error message was to report that i forgot to specify the keyword RECORDS before LOGFILE.
If you look at the syntax diagram of the ACCESS PARAMETERS clause (ver. 10R1), you'll notice that there are four distinct sub-clauses.
LOGFILE belongs to the record format sub-clause. This means that you cannot specify any keyword in this sub-clause if you haven't specified the RECORDS keyword first.
Note also that from a syntax standpoint it is perfectly legitimate to write the RECORDS keyword alone, but if you do not add DELIMITED BY NEWLINE ( or some other specification) then the record terminator will remain undefined, resulting in the following run-time error:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04038: internal error: unknown record type

So, my original statement must be rewritten as:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);

But how do i put the log file in directory other than IMPORT_DIR?
While the official documentation states that one can write a file location as directory:filename, in the reality it turns out that one must enclose the file name in single quotes, otherwise the following syntax error is returned:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_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
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( IMPORT_DIR:BOXES.dat)
);

ORA-00905: missing keyword

On the other hand, if you put the directory specifier inside the quotes too, you'll get the following run-time error:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_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
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
('IMPORT_DIR:BOXES.dat')
);

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file IMPORT_DIR:BOXES.dat in LOG_DIR not found

Finally, here is the correct syntax in blue color:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_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
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
(IMPORT_DIR:'BOXES.dat')
);

It is up to you to decide whether you want to make the DEFAULT DIRECTORY the place where the source file is read from or the folder where the log files are written to.
Depending on the situation, you may need the appropriate READ and WRITE privileges on it.

As a last note, in case you wonder what would happen if you ALTER the table instead of dropping and re-creating it, you may want to know that it would perfectly possible to execute a statement like this:

ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'boxes.log');

however this statement will wipe out the previous definition of ACCESS PARAMETERS, because it doesn't add just a LOGFILE, but replaces the ACCESS PARAMETERS as a whole.
So, don't forget to include the whole sub-clause again if you plan to use ALTER TABLE, as follows:

ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
);

Finally, let me provide a full-fledged example of external table where every log file goes to its own place:

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY IMPORT_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE LOG_DIR:'BOXES.log'
DISCARDFILE DISCARD_DIR:'DISCARDS.log'
BADFILE BAD_DIR:'BAD.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION ('BOXES.dat');

As you can see i specified the DEFAULT DIRECTORY clause without embedding the directory object name in double quotes. You must use double quotes if the directory object name is case sensitive otherwise always use uppercase letters.
Do not use single quotes for the directory object name or you'll get ORA-22929.

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

ORA-30653: reject limit reached

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

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

You may get this error when selecting from an external table, type ORACLE_LOADER, and you have set the REJECT LIMIT to some numerical value instead of the literal UNLIMITED and, for some reason to be investigated, oracle reached the limit while loading the rows from the file.

First of all you should open the log file associated with the external table, if any.
If the log file is not present because the NOLOGFILE option was specified, it is advisable to redefine the table and specify a valid log file destination.

It's worth spending some more words on the significant differences that you may get with what is to be considered as a rejected record (collected in the .bad file) or as a discarded record (collected into the .dsc file), depending on whether certain keywords are specified or not in the external table definition.
In the end, what matters is that rejected records are those that affect the REJECT LIMIT counter, whilst discarded records (LOAD WHEN) do not affect it.

For instance, a classical problem of row rejection is represented by an empty line of text.

If REJECT LIMIT 0 is specified, then you must handle empty lines in some way, because they will cause rejections causing the failure of the operation. Let's look at the following example:

CREATE TABLE test_ext (
a  VARCHAR2(10),
b  VARCHAR2(10),
c  VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY file_location
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
LOAD WHEN ((1:1) != "#")
FIELDS TERMINATED BY ":"
(
a    CHAR(10),
b    CHAR(10),
c    CHAR(10)
)
)
LOCATION ('list.dat')
)
REJECT LIMIT 0
/

Then suppose the content of file list.dat is the text in green color:

#

test:external:table:

Line 2 contains just a newline character, so it will be considered an empty line.
When we attempt to access the table, we get:

select * from test_ext;

ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached

In the log file generated (in the folder pointed to by directory FILE_LOCATION), the last lines are:

KUP-04102: record 1 discarded from file ...list.dat
KUP-04021: field formatting error for field A
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file ...list.dat

The first message refers to the line beginning with the pound sign (#), that we deliberately discarded by means of the LOAD WHEN clause. The next three lines refer to the empty line. In the end what really matters is that the record was rejected, thereby causing the entire operation to abort because of the reached reject limit.

Now, let's rebuild the table adding a special field definition clause (in green color):

CREATE TABLE test_ext (
a  VARCHAR2(10),
b  VARCHAR2(10),
c  VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY file_location
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
LOAD WHEN ((1:1) != "#")
FIELDS TERMINATED BY ":"
MISSING FIELD VALUES ARE NULL
(
a    CHAR(10),
b    CHAR(10),
c    CHAR(10)
)
)
LOCATION ('list.dat')
)
REJECT LIMIT 0
/

select * from test_ext;

A          B          C
---------- ---------- ----------
             
test       external   table

2 rows selected

If you look at the log file, this time there will be only the KUP-04102 message at the bottom.

Now, you might say you don't want null values in your table: piece of cake, just add one more clause:

CREATE TABLE test_ext (
a  VARCHAR2(10),
b  VARCHAR2(10),
c  VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY file_location
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
LOAD WHEN ((1:1) != "#")
FIELDS TERMINATED BY ":"
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
a    CHAR(10),
b    CHAR(10),
c    CHAR(10)
)
)
LOCATION ('list.dat')
)
REJECT LIMIT 0
/

select * from test_ext;

A          B          C
---------- ---------- ----------
test       external   table

1 rows selected

The last three lines in the log file this time contain:

KUP-04102: record 1 discarded from file ...list.dat
KUP-04073: record ignored because all referenced fields are null for a record
KUP-04049: record 2 discarded from file ...list.dat

Finally it's interesting to note a linguistic quirk, which makes me so happy, as you know:
the log file says record 2 discarded, but the clause we applied is REJECT ROWS...
Indeed the discarded record will be found in the .dsc file, not in the .bad file.

I guess how easily one can be fooled by such terminology where discarded records must be explicitly rejected and rejected records are those automatically discarded... good grief!
:-D

On a subtler level, finally note that in the log file the record discarded owing to REJECT ROWS clause is marked by code KUP-04049, whereas the record discarded because it doesn't match the LOAD WHEN condition is marked by code KUP-04102.

See message translations for ORA-30653 and ORA-29913 and search additional resources.