Drop an Oracle-Loaded XSD in Python
1 min read

Drop an Oracle-Loaded XSD in Python

Drop an Oracle-Loaded XSD in Python

A loaded schema can be dropped if:

  • No other schema depends on it
  • No data using the schema is loaded

I think you may force unloading schemas even if there is data loaded but I haven't tried that.

The code to drop a schema is:

dbms_xmlschema.deleteSchema( schemaURL => 'schema_url');

You can use it if e.g. schema is updated or we don't need it anymore.

The full code in Python is:

DELETE_STR = """
begin
    dbms_xmlschema.deleteSchema( schemaURL => '%s');
end;
"""

SCHEMA_ORDER = [
    'xocs-ani512-xml.xsd',
    'xocs-ani512-ani-ce.xsd',
    'xocs-ani512-ce.xsd',
    'xocs-ani512-cto.xsd',
    'xocs-ani512-ait.xsd',
    'xocs-ani512.xsd',
]

# ...

# Try to delete the XSD from the DB if necessary
#
for xsd_file in reversed(SCHEMA_ORDER):
    xsd_path = os.path.join('/my/dir', xsd_file)

    try:
        print "Remove [%s] schema" % xsd_file
        cursor.execute(DELETE_STR % xsd_file)
    except Exception as e:
        print "Fail to remove [%s]" % xsd_path, e.message

Several notes:

  • The order of removal is, of course, the reversed order of insertion. This way, complex XSDs are removed firse leaving basic dependencies at the end.
  • I'm using PL/SQL because it's easier if all scripts are written in the same way. I could probably use simple SQL
  • I'm over-engineering the code a bit because I don't want it to fail hard if a dependency was not loaded (it's development code so e.g. the list of schemas can fail to load in the first place)

Note: Please refer to the previous article to see e.g. connection setup via cx_oracle

HTH,