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.