DBMS_XMLGEN limited workaround for converting LONG columns into CLOBs

If you landed here is because you hit the problem of dealing with LONG columns.

There are some good articles elsewhere about how to tackle this old problem, my contribution in this case consists in advising about some limitations that apparently have been overlooked when using the same technique explained below.

It's a mystery to me why after so many years we can't rid of this annoyance once for good, why not "simply" adding a CLOB column equivalent at least in the case of data dictionary columns?
Come on!

I needed to extract the content of the TEXT column from DBA_VIEWS and DBA_MVIEWS, possibly without having to pass through an INSERT into a table (using function TO_LOB), which is the best workaround in case you deal with static data, for one-off operations.

I stumbled upon an old video of Connor McDonald showing how to extract the content of a LONG column exploiting the XML API DBMS_XMLGEN.GETXMLTYPE. This trick seemed to save the day after some adaptation for my case, and actually I was almost ready to celebrate when I started hitting some errors while doing further tests.

To cut a long story short, eventually I encountered the following problems:

1. API documentation for version 19c of DBMS_XMLGEN.SETCONVERTSPECIALCHARS is incorrect as it mentions a parameter "conv" but the real parameter name is "replace". This typo is still present in the latest version of the documentation of 23ai.
   
   
2. DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML won't perform special characters escaping via DBMS_XMLGEN.SETCONVERTSPECIALCHARS if the column type is LONG.
   I was getting parsing errors when using Connor's EXTRACTVALUE technique because the XML document contained < or > as spare characters in the source (as in WHERE conditions inside the query source).
   
   
3.  DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML will truncate the content to the first 32K for LONG columns.
   

Problem #1 was easily solved, problem #2 was solved extracting the data using REGEXP_SUBSTR instead of EXTRACTVALUE, but this was possible because I was working on a XML document containing a single ROW tag at a time. For multiple rows this solution will not work.

  FUNCTION long2clob
     ( p_qry in clob,  -- must return a single row!
       p_col in varchar2)
  RETURN CLOB
  IS
    c        CLOB;
  BEGIN
    c := regexp_substr(
           dbms_xmlgen.getxml(p_qry),
           '(<ROW>.*<' || p_col || '>(.*)</' || p_col || '>.*</ROW>)',
           1,
           1,
           'cn'
           ,2 
         );
    return c;

  END long2clob;

Problem #3 remains, unless LONG columns are less than 32K.
Unfortunately we do have some views exceeding 32K of source, but considering the usage of this function I'll probably live with this limitation for the moment.
By the way, SQLDeveloper won't allow you to edit a view larger than 32K, and to me this sounds like an invitation to avoid such situations.

Finally, I also tried to see what happens when you supply a LONG column to function JSON_OBJECT, unfortunately it returns the exception:

ORA-40654: Input to JSON generation function has unsupported data type.

That's all folks!
(quote)

Interesting change in the XML export format of Oracle SQLDeveloper

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

In the last days I've been playing with XML files using SQL Developer and other tools and i noticed the following interesting change in the behavior of SQL Developer when it comes to exporting the data from a table in XML format:

until version 1.5.0 build 5338, the elements in the XML file when exporting table were (in XPath notation format):
results/row/COLUMN NAME (note the mixture of lower and upper case, where COLUMN NAME was the relevant column name.

From version 1.5.1 build 5440, the element names have been changed and are now more consistent:
RESULTS/ROW/COLUMN@NAME="COLUMN NAME" (where COLUMN is now a fixed element whose NAME attribute contains the actual column name.

I've been lazily checking the release notes but could not find anything on this interesting change, but it could be that i didn't notice it.

Note that this new format is not the DBMS_XMLGEN canonical format (as it wasn't the previous one either).

DBMS_XMLGEN canonical format is:
ROWSET/ROW/COLUMN NAME

You can download and use the following XSL stylesheet (SQLDEV2APEX.XSL) to convert from this new format to the DBMS_XMLGEN canonical format, in case you wish to use the exported file to load a table using DBMS_XMLSTORE or through Oracle Application Express (Apex) Data Load page.

Converting XML leaf nodes into relational data

If you, like me, tend to forget how to build certain non-trivial SQL statements involving XML functions, you won't regret if i post here a sort of memorandum on how to extract data from the leaf nodes of an XML document.

SELECT extractvalue(value(d),'/TABLE_NAME') as TABLE_NAME
FROM dual e,
   table(
    xmlsequence(
     extract(
      xmltype.createXML(
       dbms_xmlgen.getxml('select * from user_tables')),
      '/ROWSET/ROW/TABLE_NAME')
     )
   ) d;

The query above extracts the text node under the TABLE_NAME element in an XML file containing the canonical representation of a table, as it would exported from Apex invoking the export table utility.

In order to simulate the output of Apex, i used packaged function DBMS_XMLGEN.GETXML.

The key components of the query are highlighted in bold typeface.
Function extract returns a set of nodes that is, in other words, an XML fragment.
An XPath expression identifies the desired nodes (/ROW/ROWSET/TABLE_NAME).
Function xmlsequence takes the node-set and converts it into a varray of XMLtype.
Once i have joined the source table with the nested array of nodes, i can finally apply function extractvalue that converts nodes into scalar values, that is simple oracle types.

Note also that i had to explicitly convert the document from CLOB type into XMLType.

Updated January 12, 2008.
i forgot to explain why i put DUAL table in my example: here DUAL represents the outer table that will be probably present in the real life. For instance, here is a similar statement i've created today for extracting translations from an XML file (in XLIFF format), that is stored in a staging table, that i called XML_IMPORTS:

select extractvalue(value(d),'/trans-unit/source') as source,
      extractvalue(value(d),'/trans-unit/target') as target
from xml_imports x,
    table(xmlsequence(extract(x.xmlfile, '/xliff/file/body/trans-unit'))) d
where x.id = 31