OraOpenSource
SQL Select or refCursor to HTML
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;
@zhudock very nice example of translating the XML to HTML. A similar problem came up for me the other day and may use it for a solution.
I've tagged this as a Future release item as I don't think it'll go in the initial version. I want to give it some more thought as to a more global approach. One idea that comes to mind is something similar but to csv.
Glad you were able to use it! Bill and Tom did all the work, I just added a couple tiny changes.
I see a lot of benefit to a to-CSV version, or possibly making the xslt a parameter as well so users can generate a variety of output. My free time is at a minimum right now but I'll see what I can come up with.
Feel free to change the function name too.
This is a great pattern but I struggle a bit how this could be a "general purpose" ? I have used the similar kind of pattern too (but making the transformation with XQuery).
@zhudock if you have different implementation ideas can you please create a separate repository for this where we can easily try and share different ideas ? (I have some ideas too but they could be the same than yours ;)
Please have a look to oos_transform in https://github.com/janihur/oos-utils-sandbox
That's my initial generalization of refcursor to xml conversion. As you can see there is no much code when XSLT/XQuery is excluded.
I added two different draft versions of refcursor to csv to oos_transform https://github.com/janihur/oos-utils-sandbox
I like the CSV methods. I haven't had much time to dig into either of them, but how difficult would it be to include the column names on the first line?
how difficult would it be to include the column names on the first line?
Trivial.
I know how to do it in dbms_sql based implementation, but I'm not sure about xquery based implementation (my xquery skills are not that good).
I guess the column names feature is worth of a switch:
function refcur2csv2(
p_rc in out sys_refcursor
,p_column_names in boolean default false
,p_separator in varchar2 default ','
,p_endline in varchar2 default chr(10)||chr(13)
,p_date_fmt in varchar2 default 'YYYY-MM-DD HH24:MI:SS'
) return clob
I agree with making the column names a switch. I knew it was trivial in dbms_sql, as I had a rather quickly and poorly coded solution I used in the past. I'm not terribly familiar with xquery either
This should take care of it for dbms_sql. Still researching xquery
if p_column_names
then
for i in 1 .. v_col_count
loop
v_buf := '"' || v_col_desc(i).col_name || '"';
if i < v_col_count
then
v_buf := v_buf || p_separator;
end if;
dbms_lob.writeappend(v_lob, length(v_buf), v_buf);
end loop;
dbms_lob.writeappend(v_lob, length(p_endline), p_endline);
end if;
I'd also suggest swapping the order of the 2 characters in the default for p_endline to match the CRLF pattern for Windows line endings.
e.g.
function refcur2csv2(
p_rc in out sys_refcursor
,p_column_names in boolean default false
,p_separator in varchar2 default ','
,p_endline in varchar2 default chr(13)||chr(10)
,p_date_fmt in varchar2 default 'YYYY-MM-DD HH24:MI:SS'
)
I'm getting close with XQUERY. It looks like 2 distinct calls to your xquery function will be needed. one for the column headers, one for the column values. I've been experimenting with quoting as well, but so far i can only get it to output """ instead of a real double-quote character. However, the xquery I used is below
v_xquery_headers constant varchar2(32767) := q'[let $nl := codepoints-to-string(10),
$q := codepoints-to-string(34),
$nodes := /ROWSET/ROW
return concat(string-join(distinct-values($nodes/*/concat($q, name(.), $q)), ','), $nl)
]';
Thanks @zhudock - I'll apply your comments soon. Meanwhile please keep working with xquery :)
@janihur - Do you have any objections to me submitting a pull request here that includes your transform package and my additions to it?
@janihur @zhudock FYI I'm off for a few weeks and will look at all PRs when I'm back / have time.