Within a PL/SQL procedure, wrap a query or refcursor in HTML table It seems really easy if you are using SQL*Plus to use 

SQL&gt; set markup html on;


and get some lovely results to the SQL*Plus window.  we have an oracle job that runs overnight and sends an email of results to a number of people.  I would like to wrap a sql statement in an HTML table to be in that message.  What is the best way of doing that?
It is possible to generate xml with dbms_xmlgen, eg:

select dbms_xmlgen.getxml('select * from dual') as xml from dual;

XML
---
&lt;?xml version="1.0"?&gt;
&lt;ROWSET&gt;
 &lt;ROW&gt;
  &lt;DUMMY&gt;X&lt;/DUMMY&gt;
 &lt;/ROW&gt;
&lt;/ROWSET&gt;


Using getxmltype instead, it is then possible to apply an xslt transform, perhaps like this:

select dbms_xmlgen.getxmltype('select * from dual').transform(XMLType(
'&lt;?xml version="1.0" encoding="ISO-8859-1"?&gt;
&lt;xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
&lt;xsl:output method="html"/&gt;
&lt;xsl:template match="/"&gt;
&lt;html&gt;
  &lt;body&gt;
   &lt;table border="1"&gt;
     &lt;tr bgcolor="cyan"&gt;
      &lt;xsl:for-each select="/ROWSET/ROW[1]/*"&gt;
       &lt;th&gt;&lt;xsl:value-of select="name()"/&gt;&lt;/th&gt;
      &lt;/xsl:for-each&gt;
     &lt;/tr&gt;
     &lt;xsl:for-each select="/ROWSET/*"&gt;
      &lt;tr&gt;
       &lt;xsl:for-each select="./*"&gt;
        &lt;td&gt;&lt;xsl:value-of select="text()"/&gt; &lt;/td&gt;
       &lt;/xsl:for-each&gt;
      &lt;/tr&gt;
     &lt;/xsl:for-each&gt;
   &lt;/table&gt;
   &lt;/body&gt;
&lt;/html&gt;
  &lt;/xsl:template&gt;
&lt;/xsl:stylesheet&gt;')).getclobval() as html from dual;

HTML
----
&lt;html&gt;
&lt;body&gt;
&lt;table border="1"&gt;
&lt;tr bgcolor="cyan"&gt;
&lt;th&gt;DUMMY&lt;/th&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;X&lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;
&lt;/body&gt;
&lt;/html&gt;


nb credit to Tom Kyte for the xslt from his blog. See also this other post for a slightly more advanced version.