and the winner is...

Believe it or not, it looks like i won one of the available sessions for presenting at Oracle Open World 2008.
The full list of winners can be found at the Oracle Open World blog.

So, it turns out that on Monday, September 22, i'll be conducting a demonstration on how to effectively use Oracle Application Express and DBMS_PROFILER to analyze PL/SQL source code, as anticipated in a previous posting.

In the meanwhile i am collecting some real-life scenarios, especially from manufacturing plants, where, for performance reasons, the usage of DBMS_PROFILER for tuning the applications is not just an option, but it's a true necessity.

I really appreciated the support of friends and acquaintances who made this possible.

Thank you all!
Flavio

ORA-31088: object XYZ depends on the schema

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

This error can be seen when you are trying to delete an XML schema from the repository using the DELETE_RESTRICT or DELETE_CASCADE option in DBMS_XMLSCHEMA.DELETE_SCHEMA , but there are still XMLType tables or XMLType columns referencing it.

A slightly subtler situation involves the recycle bin of Oracle 10G and above.
Supposing the recycle bin is enabled, if you drop a table referencing an XML schema but you don't specify PURGE and you don't flush the recycle bin, then you'll get ORA-31088, which may sound difficult to understand at first.

You can reproduce the problem by executing the scripts i used in a previous posting, then performing the following additional steps:

DROP TABLE purchaseorder_as_column;

BEGIN
DBMS_XMLSCHEMA.deleteSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
delete_option => DBMS_XMLSCHEMA.DELETE_CASCADE);
END;

ORA-31088: object "TEST"."PURCHASEORDER_AS_COLUMN" depends on the schema
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 82
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 102

So, the cleanest workaround is to ensure that no depending objects are still present, including those that may have been moved to the recycle bin and then re-execute deleteSchema.
You may execute the following query, for instance:

select *
 from user_dependencies d, user_xml_schemas x
where d.referenced_type = 'XML SCHEMA'
and d.referenced_name = x.int_objname
and x.schema_url = 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd';

Alternatively you can try to specify either of the two remaining options for the deleteSchema call: DELETE_INVALIDATE or DELETE_CASCADE_FORCE.

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

ORA-22809: nonexistent attribute

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

As recently promised in an OTN forum posting, i am now ready to say something more detailed about ORA-22809 and other issues i encountered during recent experiments with XML db.

For once let's begin from a working script and then modify it to reproduce the problem.
Given the limitations of blogspot when it comes to display inline XML tags, i'll just put links to external XML documents that you can download and open separately.

First of all, let's create a directory object pointing to a folder on our server where we'll place the XML schema to be registered.

CREATE DIRECTORY SCHEMA_DIR = "path to desired folder";

Make sure you have READ privileges on this folder.

Secondly, we need a working sample of XML purchase order schema. Download it and save it in the folder pointed to by directory object SCHEMA_DIR created previously.

Thirdly, we need to register the schema, in this fashion:

BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
SCHEMADOC => bfilename('SCHEMA_DIR','purchaseOrder.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => FALSE);
END;

Then, at last, let's build a table containing an XMLType column with an object-relational storage model:

CREATE TABLE purchaseorder_as_column (
id NUMBER,
xml_document XMLType )
XMLTYPE COLUMN xml_document STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY XML_DOCUMENT.XMLDATA.ACTIONS.ACTION
STORE AS TABLE action_table2
       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
       ORGANIZATION INDEX OVERFLOW)
VARRAY XML_DOCUMENT.XMLDATA.LINEITEMS.LINEITEM
STORE AS TABLE lineitem_table2
       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
       ORGANIZATION INDEX OVERFLOW);

Thanks to Marco Gralike who pointed me to the right place in the XML Db Developer's Guide because at a certain point i was lost.

Now, if everything worked out as expected, you should have been able to carry out the above steps without issues, so let's see how easy is to tamper with the annotations in order to get ORA-22809.

Go to line #19 of purchaseOrder.xsd and remove the following text: xdb:SQLName="ACTIONS"
Save the new version as purchaseOrder22809.xsd.

Now, let's replay everything from the beginning:

DROP TABLE purchaseorder_as_column PURGE;

BEGIN
DBMS_XMLSCHEMA.deleteSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
delete_option => DBMS_XMLSCHEMA.DELETE_CASCADE);
END;
/
BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
SCHEMADOC => bfilename('SCHEMA_DIR','purchaseOrder22809.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => TRUE,
GENTYPES => TRUE,
GENTABLES => FALSE);
END;
/
CREATE TABLE purchaseorder_as_column (
id NUMBER,
xml_document XMLType )
XMLTYPE COLUMN xml_document STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY XML_DOCUMENT.XMLDATA.ACTIONS.ACTION
STORE AS TABLE action_table2
       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
       ORGANIZATION INDEX OVERFLOW)
VARRAY XML_DOCUMENT.XMLDATA.LINEITEMS.LINEITEM
STORE AS TABLE lineitem_table2
       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
       ORGANIZATION INDEX OVERFLOW);

Error at Command Line:25 Column:28
Error report:
SQL Error: ORA-22809: nonexistent attribute
22809. 00000 -  "nonexistent attribute"
*Cause:    An attempt was made to access a non-existent attribute of an
           object type.
*Action:   Check the attribute reference to see if it is valid. Then retry
           the operation.

As i explained in my last comment in the OTN forum, the problem is caused my a mismatching attribute name.
Side note: i used DROP TABLE ... PURGE, because otherwise i'd get an error when unregistering the schema (ORA-31088). Clearly PURGE works on Oracle 10G or above only.
I believe this example might work on 9iR2 too, after removing the PURGE keyword, but i didn't try it.

Annotations like xdb:SQLName="ACTIONS" allow you to rename columns and/or object attributes that would otherwise derive their name from the original XML Schema element/attribute definition.
After removing the annotation, Oracle created object type PURCHASEORDER_T with an attribute called "Actions" (case sensitive) instead of "ACTIONS" (case insensitive), hence the error returned. The good news is that in the error message stack there is a precise indication of the location of the problem: line 25, column 28 that is where the ACTIONS attribute begins.

As a consequence, you ought to modify the CREATE TABLE statement as follows:

CREATE TABLE purchaseorder_as_column (
id NUMBER,
xml_document XMLType )
XMLTYPE COLUMN xml_document STORE AS OBJECT RELATIONAL
XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY XML_DOCUMENT.XMLDATA."Actions".ACTION
STORE AS TABLE action_table2
       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
       ORGANIZATION INDEX OVERFLOW)
VARRAY XML_DOCUMENT.XMLDATA.LINEITEMS.LINEITEM
STORE AS TABLE lineitem_table2
       ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
       ORGANIZATION INDEX OVERFLOW);

whether you prefer to use all uppercase name for SQL entities in contrast with mixed case element names is a matter of taste. Normally i prefer to use either all lowercase or all uppercase element and attribute names in XML just to avoid the inevitable confusion when you mix them up.
There can be situations though where you are forced to use such annotation, like when the XML element or attribute carries an Oracle reserved keyword name.

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

DBMS_XMLSTORE and LPX-00222: error received from SAX callback function

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

You may see the following stack of errors

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "TEST.INSERTXML", line 28
ORA-06512: at line 1

in an attempt of:

1. using DBMS_XMLSTORE.INSERTXML (prior to version 10GR2) for loading a document containing an apostrophe ('). The error is raised regardless of the representation of the apostrophe ( ' or as ' or ') and is filed as a bug in metalink.
   
2. using DBMS_XMLSTORE.INSERTXML for loading a document into a table whose columns have NOT NULL constraints, no default value and the corresponding element(s) in the document present at least one empty field. This holds also for documents containing a subset of the columns of the table and one of the missing columns is mandatory.
3. using DBMS_XMLSTORE.INSERTXML for loading a document into a table with primary, unique or foreign keys and the data being loaded violates one or more constraints.
   
4. using DBMS_XMLSTORE.INSERTXML for loading a document into a table with insufficient column size. For instance, a document containing strings larger than the target table column can hold.
   

Most likely there are more situations involving other procedures in the DBMS_XMLSTORE package that i hadn't the time to investigate yet.
In other words, this LPX-00222 error is a sort of catch-all exception, which means that you'll have to go through the pesky job of identifying the offending element(s) in your source XML file manually.
For large files this can really turn into a nightmare, indeed initially i wasted several hours trying to load an XML containing an apostrophe before realizing that this was a known bug of 10GR1.

See message translations for LPX-00222 and search additional resources.

Interesting change in the XML export format of Oracle SQLDeveloper

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

In the last days I've been playing with XML files using SQL Developer and other tools and i noticed the following interesting change in the behavior of SQL Developer when it comes to exporting the data from a table in XML format:

until version 1.5.0 build 5338, the elements in the XML file when exporting table were (in XPath notation format):
results/row/COLUMN NAME (note the mixture of lower and upper case, where COLUMN NAME was the relevant column name.

From version 1.5.1 build 5440, the element names have been changed and are now more consistent:
RESULTS/ROW/COLUMN@NAME="COLUMN NAME" (where COLUMN is now a fixed element whose NAME attribute contains the actual column name.

I've been lazily checking the release notes but could not find anything on this interesting change, but it could be that i didn't notice it.

Note that this new format is not the DBMS_XMLGEN canonical format (as it wasn't the previous one either).

DBMS_XMLGEN canonical format is:
ROWSET/ROW/COLUMN NAME

You can download and use the following XSL stylesheet (SQLDEV2APEX.XSL) to convert from this new format to the DBMS_XMLGEN canonical format, in case you wish to use the exported file to load a table using DBMS_XMLSTORE or through Oracle Application Express (Apex) Data Load page.

ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB

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

You may see this error when invoking procedure DBMS_LOB.LOADCLOBFROMFILE as follows:

create or replace
FUNCTION LoadTempClobFromFile (
p_Dir      IN VARCHAR2,
p_FileName IN VARCHAR2,
p_csid     IN INTEGER)
RETURN CLOB IS

l_srcFile          BFILE := BFILENAME(p_Dir, p_FileName);
l_tmpClob          CLOB;
l_warning          INTEGER;
l_dest_offset      INTEGER := 1;
l_src_offset       INTEGER := 1;
l_lang             INTEGER := 0;

BEGIN

DBMS_LOB.CREATETEMPORARY(l_tmpClob, TRUE, DBMS_LOB.SESSION);
BEGIN
--    DBMS_LOB.OPEN(l_srcFile);
  DBMS_LOB.LOADCLOBFROMFILE(dest_lob     => l_tmpClob,
                            src_bfile    => l_srcFile,
                            amount       => DBMS_LOB.LOBMAXSIZE,
                            dest_offset  => l_dest_offset,
                            src_offset   => l_src_offset,
                            bfile_csid   => p_csid,
                            lang_context => l_lang,
                            warning      => l_warning);
--    DBMS_LOB.CLOSE(l_srcFile);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_LOB.FREETEMPORARY(l_tmpClob);
    RAISE;
END;

RETURN l_tmpClob;

END;
/

DECLARE
mydoc       CLOB;
BEGIN
mydoc := LoadTempClobFromFile('IMPORT_DIR', 'some_utf8_file.txt', 873);
-- do something with the CLOB
--
-- eventually release the resource
DBMS_LOB.FREETEMPORARY(mydoc);
END;

ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB

Note that indeed i didn't explicitly open the BFILE prior to reading the file (the relevant line is commented out), on the other hand the official documentation for PL/SQL packages and Types version 10GR1 doesn't state that opening the file is mandatory, on the contrary it says:
"It is not mandatory that you wrap the LOB operation inside the Open/Close APIs".
However the Application Developer's Guide for Large Objects (10GR2) does.

This is not the only problem with the instructions.
The dest_lob parameter should be CLOB not BLOB and parameter src_csid doesn't exist, the right name is bfile_csid.
In the documentation for 10GR2 the parameter name has been amended, but the wrong BLOB type remains. Finally, in 11GR1 all typos have been fixed, but i could not verify if one can omit to wrap the call between DBMS_LOB.OPEN and DBMS_LOB.CLOSE, probably not.

See message translations for ORA-22289 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.