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
---------------------
DB Feature Usage Report
...
...
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 DB Feat DB FEA USAGE rep
ort tle>
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…
Hi Marco,
As you’ve found out, those techniques have been floating around for quite some time now.
On recent releases, I’d probably replace the whole thing with a single XMLCAST call :
You are right 😉 (tried it just now on 12.1)
Works perfectly as well AND it gets rid of the old propriety extract stuff AND wrong encoding.
Thanks Marc!
PS.
As you hinted a bit (“on recent releases”), I would be careful with XMLAGG/XMLCAST in 11.1