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 = """
dbms_xmlschema.deleteSchema( schemaURL => '%s');
SCHEMA_ORDER = [
# 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)
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
- 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
A little experiment: If you find this post and ad below useful, please check the ad out :-)