Wednesday, July 02, 2008

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.

5 comments:

Marco Gralike said...

Sometime Oracle syntax is fun - isn't it

;-)

Byte64 said...

Hello Marco,
oh yes, it is, but i developed a bullet-proof technique over the years:
i never try to remember the syntax of a command, there are just too few neurons left to be wasted in this fashion...

;-)

Martin F said...

just found this site after battling with beloved Oracle. Thanks for the solution on kup 04038

El Dani I said...

I think that Oracle should append this blog url to the error codes :P.

Fabio said...

good article!
Thanks

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles