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.

ORA-31086: insufficient privileges to register schema

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

Attempting to register a global XML schema as a non-privileged user, you may get the following error:

BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'http://www.acme.com/XMLSchemaURL.xsd',
SCHEMADOC => bfilename('XMLDIR','someXMLSchema.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => FALSE);
END;

ORA-31086: insufficient privileges to register schema "http://www.acme.com/XMLSchemaURL.xsd"

when you specify the boolean value FALSE for LOCAL, you are attempting to register a global XML schema, but only privileged users like the DBA can perform this operation.

See message translations for ORA-31086 and search additional resources

ORA-19007 and XML Schema

ORA-19007: Schema - does not match expected string

After a couple of hours of unsuccessful attempts, frustration had begone to creep in.
It was at that time that I had a sort of epiphany: "The problem is not with the XML Schema document, it is in the XML Schema instance declaration!"

My XML Schema document was registered in the repository as a local resource, whose schema URL is :

http://www.fake.com/OutboundParty.xsd

however my instance document was referring to the following XML Schema document:

http://www.fake.com/OutboundPartyData.xsd

And I completely overlooked it for more than two hours, because it was surronded by other strings.

Now, as you can see, error ORA-19007 is not telling you that there is not such XML Schema in the repository, but that the instance document doesn't match the expected schema.

If the error is in the value ranges, you'll get different errors, fortunately, but if an element is typed wrong, you will still get ORA-19007.

---

ORA-19007: Mancata corrispondenza tra schema ed elemento
ORA-19007: El esquema y el elemento no coinciden
ORA-19007: Esquema i element no coincidents
ORA-19007: Schéma et élément discordants
ORA-19007: Schema und Element stimmen nicht überein
ORA-19007: Το σχήμα και το στοιχείο δεν συμφωνούν
ORA-19007: Skema og element matcher ikke
ORA-19007: Schemat och elementet matchar inte varandra
ORA-19007: Skjema og element samsvarer ikke
ORA-19007: Kaava ja elementti eivät vastaa toisiaan
ORA-19007: A séma és az elem nem egyeik
ORA-19007: Schema şi elementele nu se potrivesc
ORA-19007: Schema en element komen niet met elkaar overeen.
ORA-19007: O esquema e o elemento não correspondem
ORA-19007: O schema e o elemento não correspondem
ORA-19007: Схема и элемент не согласуются
ORA-19007: Schéma a element se nerovnají
ORA-19007: Schéma a prvok sa nezhodujú
ORA-19007: Schemat i element nie są zgodne
ORA-19007: Şema ve öğe eşleşmiyor