Use Oracle’s RegisterSchema with Large Schemas (from Python)

Posted by in Software

An important note when loading XSDs in Oracle is that DBMS_XMLSCHEMA.registerSchema() requires the actual content of the xsd is loaded in a string. A side effect is that strings in Oracle have limits and schemas can exceed those limits. Therefore, such strings must be fragmented. In this case, we need to employ PL/SQL. A processed fragment could look something like this:

Now, all you need to do is populate the fragments (where I’ve placed 'long-string-fragment') with the actual content. A rule of thumb I’ve got is to have a fragment to be roughly 3000 characters.

For my project, I’ve used a bit of python code to generate my string. A first part is the PL/SQL code (wrapped in python):

Here, the '%(name)s' is the schema’s name and the '%(segments)s' will be replaced with the sequence of vSCHEMA := CONCAT(vSCHEMA, '...');.

The meat is:

Few notes:

  • Code can be a bit simplified to avoid CONCAT() on short strings.
  • The 3000 limit is a rule of thumb. The larger the string, the less concatenation operations need to be performed, the quicker the code (ish).

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

HTH,


A little experiment: If you find this post and ad below useful, please check the ad out :-)