Registering XML Schemas in Oracle - how to get rid of leftover objects

Question

I tried to register a schema in Oracle the other day to validate an XML document. The code I used was fairly simple:

dbms_xmlschema.registerSchema(schemaURL => 'http://www.myCompany.com/schema',
                              schemaDoc => :schemaCLOB);

At first, everything seemed to work well, as far as validation goes. I noticed though, that there were dozens of new database objects: tables, triggers and types (a few dowsn tables and triggers, but probably many hundreds of types). I tried to delete the schema like this:

dbms_xmlschema.deleteSchema(schemaURL => 'http://www.myCompany.ca/schema',
                            delete_option => dbms_xmlschema.DELETE_INVALIDATE);

That de-registered the schema, but all of the objects were left behind.

I RTFM'd a little too late and discovered that the extra objects were created by leaving default values in the call to registerSchema, so I realized I would have to manually remove the extra objects.

Now when I try to remove the objects, Oracle tells me they don't exist. I can't select from them and my IDE (PL/SQL Developer) shows them as being invalid (a little red "X" beside them). I also can't find any info on these tables in all_tables. How do I get rid of these?

Answer 1

Hi,

I'm probably missing something obvious but looking at the documentation could you not use the DELETE_CASCADE or DELETE_CASCADE_FORCE options?

Answer 2

Metalink is Oracle's support site (http://support.oracle.com) but you would need a customer support ID(entifier) to enter the protected site.

If on 11g use DBMS_XMLSCHEMA.PURGESCHEMA to get rid of the objects. If on 10gRx use

dbms_xmlschema.deleteSchema(schemaURL => 'http://www.myCompany.ca/schema', delete_option => dbms_xmlschema.DELETE_CASCADE_FORCE);

or

dbms_xmlschema.deleteSchema('http://www.myCompany.ca/schema',4);

If that doesn't help bounce the database after using force delete and try again. If that doesn't help, delete the schema via a delete statement based on user_xml_schemas (last option is not supported).

Answer 3

Solution was: restored from previous day's backup. In the future, I'll be setting gentables and gentypes to FALSE ALWAYS, and delete with DELETE_CASCADE_FORCE. Anything doesn't work with that, I'll probably post a new question.