String Aggregation of “Huge” Strings via XML

My goal was initially to concatenate all rows generated via the package DBMS_FEATURE_USAGE_REPORT in one big HTML (XML?) document instead of the by default generated multiple row output… So how can you achieve this (in Oracle 11.1 and onwards) without getting into varchar2() or other buffer limitations…

SQL> SELECT output FROM TABLE(dbms_feature_usage_report.display_html);

OUTPUT
---------------------

...
...


349 rows selected.



Package DBMS_FEATURE_OUTPUT_REPORT has been defined via


CREATE OR REPLACE PACKAGE dbms_feature_usage_report AS

  /********************************************************************
   * FUNCTIONS
   *   display_text, display_html
   *
   * DESCRIPTION
   *   Pipelined functions that displays the DB Feature Report in
   *   either Text or HTML format for the inputted DBID and Version.
   *
   *   For example, to generate a report on the DB Feature Usage
   *   data for the local database ID and Version, the following
   *   statements can be used:
   *
   *     -- display in Text format
   *     select output from table(dbms_feature_usage_report.display_text);  
   *     -- display in HTML format
   *     select output from table(dbms_feature_usage_report.display_html);
   *
   * PARAMETERS
   *   l_dbid    - Database ID to display the DB Feature Usage for.
   *               If NULL, then default to the local dbid.
   *   l_version - Version to display the DB Feature Usage for.
   *               If NULL, then default to the current version.
   *   l_options - Report options, currently no options are supported
   ********************************************************************/

  /* Displays the DB Feature Report in Text format */
  FUNCTION display_text(l_dbid    IN NUMBER   DEFAULT NULL,
                        l_version IN VARCHAR2 DEFAULT NULL,
                        l_options IN NUMBER   DEFAULT 0
                       )
  RETURN awrrpt_text_type_table PIPELINED;

  /* Displays the DB Feature Report in HTML format */
  FUNCTION display_html(l_dbid    IN NUMBER   DEFAULT NULL,
                        l_version IN VARCHAR2 DEFAULT NULL,
                        l_options IN NUMBER   DEFAULT 0
                       )
  RETURN awrrpt_html_type_table PIPELINED;

END dbms_feature_usage_report;
/

create or replace type AWRRPT_HTML_TYPE
  as object (output varchar2(1500 CHAR))

create or replace type AWRRPT_HTML_TYPE_TABLE
  as table of AWRRPT_HTML_TYPE

So in short, row output are limited within the 1500 characters defined AWRRPT_HTML_TYPE and I wanted those somehow concatenated in one string, so it would reflect one HTML document. Searching the internet, you will find links from amongst others Tim, Adrian and William, explaining different techniques how to concatenate/aggregate strings:

• Tim Hall – String Aggregation Techniques
• William Robertson – Creating a comma-separated list in SQL
• Adrian Billington – The collect function in 10g

I wanted a simple, if possible SQL(/XML) solution, without writing a lot of PL/SQL code to solve this aggregation issues. As mentioned by William in his post, in the end I “solved” it via the “dark magic of XML”, if not only those operators and functions are used to handle big (CLOB etc) pieced of data and will/would probably be future proof.

Row output of the mentioned package will always start or end with a valid element tag, so I used the following statement to aggregate all the row content knowing it would not end up in wellformed XML and therefore using the “CONTENT” value in XMLSERIALIZE (version 11.1 and onward).

select xmlserialize(CONTENT 
                    extract(xmlagg(xmlelement(e, output)),'//text()') 
                      as CLOB NO INDENT)
from table( dbms_feature_usage_report.display_html );

The XMLELEMENT operator will create “E” element tags with values/content generated by the DBMS_FEATURE_USAGE_REPORT.DISPLAY_HTML package. To get rid of all the begin and “E” end tags, the //text() XPath expression is used. Then afterwards everything is aggregated via XMLAGG and serialized in one big (HTML/CLOB) document via the XMLSERIALIZE operator. The XMLSERIALIZE “NO INDENT” hint, will avoid creating any unneeded pretty print output and use of CONTENT will, as explained by the documentation – “If you specify CONTENT, then the value_expr need not be a singly rooted XML document. However it must be valid XML content”.

After everything is aggregated, it will have a single root element (begin and end HTML tag).

One annoying bit still was that the encoding/decoding was initially wrong. This can be corrected via package DBMS_XMLGEN.CONVERT, so while applying this knowledge a view can be created via:

create or replace view DB_FEATURE_USAGE_HTML_DOC
(output)
as
select xmltype(dbms_xmlgen.convert(t.cnt,1)) output
from  (
      select xmlserialize(CONTENT 
                          extract(xmlagg(xmlelement(e, output)),'//text()') 
                               as CLOB NO INDENT) cnt
      from table( dbms_feature_usage_report.display_html )
      ) t;

Now I had a XMLTYPE view I can use for future processing and/or if you don’t want XMLTYPE use XMLSERIALIZE or extract.xmltype.getclobval() to make the output CLOB content.

But based on the view created by the statement above, you could use XMLTRANSFORM, to generate PDF or other needed content, via XSLT or do start getting some bits and pieces via XML operators and functions like…

create or replace view DB_FEATURE_HTML_DOC_EXAMPLE
( row_num
, title
, head
, body
, features
)
as
select xt.num01
     , xt.title              as title
     , xt.head.getclobval()  as head
     , xt.body.getclobval()  as body
--     , xf.num02
     , xf.feat               as features
  from DB_FEATURE_USAGE_HTML_DOC fux
     , xmltable('/html'
               PASSING fux.output
               COLUMNS
                 num01 for ordinality
               , title varchar2(50) path 'head/title'
               , head  xmltype      path 'head'
               , body  xmltype      path 'body'
               ) xt
     , xmltable('body'
               PASSING xt.body
               COLUMNS
                  num02 for ordinality
               ,  feat  varchar2(30) path 'h1'
               ) xf;

SQL> describe DB_FEATURE_HTML_DOC_EXAMPLE
 Naam                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_NUM                                            NUMBER
 TITLE                                              VARCHAR2(50 CHAR)
 HEAD                                               CLOB
 BODY                                               CLOB
 FEATURES                                           VARCHAR2(30 CHAR)

SQL> set lines 80
SQL> col title for a10
SQL> col head for a20
SQL> col body for a20
SQL> col features for a10

SQL> select * from DB_FEATURE_HTML_DOC_EXAMPLE;

   ROW_NUM TITLE      HEAD                 BODY                 FEATURES
---------- ---------- -------------------- -------------------- ----------
         1 DB Feature 
                      

Be aware that I didn’t fully test the construct while handling string bigger than, for example 32K, but XMLSERIALIZE should be able to handle big strings. That said XMLAGG had issues over the years so…