SQL Select or refCursor to HTML #54
Description
Not sure if anyone else sees the benefit, but I'd be interested in a function added to oos_util_web that accepts either a refCursor or a select statement, and returns an HTML table.
I found this example on Tom Kyte's blog, but it's missing the line to close the context
http://tkyte.blogspot.com/2006/01/i-like-online-communities.html
Here's my slightly modified version that closes the context and allows an additional param for full html document or table-only
CREATE OR REPLACE FUNCTION fncRefCursor2HTML (
rf SYS_REFCURSOR,
full_html BOOLEAN DEFAULT FALSE)
RETURN CLOB
IS
lRetVal CLOB;
lHTMLOutput XMLTYPE;
lXSL CLOB;
lXMLData XMLTYPE;
lContext DBMS_XMLGEN.CTXHANDLE;
BEGIN
-- get a handle on the ref cursor --
lContext := DBMS_XMLGEN.NEWCONTEXT (rf);
-- setNullHandling to 1 (or 2) to allow null columns to be displayed --
DBMS_XMLGEN.setNullHandling (lContext, 2);
-- create XML from ref cursor --
lXMLData := DBMS_XMLGEN.GETXMLTYPE (lContext, DBMS_XMLGEN.NONE);
-- this is a generic XSL for Oracle's default XML row and rowset tags --
-- " " is a non-breaking space --
lXSL := lXSL || q'[<?xml version="1.0" encoding="ISO-8859-1"?>]';
lXSL :=
lXSL
|| q'[<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">]';
lXSL := lXSL || q'[ <xsl:output method="html"/>]';
lXSL := lXSL || q'[ <xsl:template match="/">]';
IF full_html = TRUE
THEN
lXSL := lXSL || q'[ <html>]';
lXSL := lXSL || q'[ <body>]';
END IF;
lXSL := lXSL || q'[ <table border="1">]';
lXSL := lXSL || q'[ <tr bgcolor="cyan">]';
lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/ROW[1]/*">]';
lXSL := lXSL || q'[ <th><xsl:value-of select="name()"/></th>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </tr>]';
lXSL := lXSL || q'[ <xsl:for-each select="/ROWSET/*">]';
lXSL := lXSL || q'[ <tr>]';
lXSL := lXSL || q'[ <xsl:for-each select="./*">]';
lXSL := lXSL || q'[ <td><xsl:value-of select="text()"/> </td>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </tr>]';
lXSL := lXSL || q'[ </xsl:for-each>]';
lXSL := lXSL || q'[ </table>]';
IF full_html = TRUE
THEN
lXSL := lXSL || q'[ </body>]';
lXSL := lXSL || q'[ </html>]';
END IF;
lXSL := lXSL || q'[ </xsl:template>]';
lXSL := lXSL || q'[</xsl:stylesheet>]';
-- XSL transformation to convert XML to HTML --
lHTMLOutput := lXMLData.transform (XMLType (lXSL));
-- convert XMLType to Clob --
lRetVal := lHTMLOutput.getClobVal ();
DBMS_XMLGEN.CLOSECONTEXT (lContext);
RETURN lRetVal;
END fncRefCursor2HTML;