2

It seems really easy if you are using SQL*Plus to use 

SQL> 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?

Improve this question
4
  • Is the Oracle Job kicked off from the OS? If so you can spool output from SQLPLUS and then email it. If all within the database, you may have add HTML codes to the Pl/SQL code that generates your output. Can you give us an idea of how your current procedure gathers and emails output?
    – David Mann
    Commented Oct 12, 2011 at 16:48
  • NO, it is a scheduled recurring oracle job. Currently we wrap <table><tr><td> tags and concatinate around 32 fields with 4 UNION ALLs. Making it a major pain to update the output. What I would like to be able to do is make a view, that would be easier to modify and then wrap that in the HTML markup.
    – Lloyd
    Commented Oct 12, 2011 at 18:19
  • SET MARKUP is functionality contained in the SQLPLUS binary program, not inside the database. I am not aware of any out-of-the-box way to just blanket apply to a view or table. Closest I know of is HTP package to help with formatting fields as HTTP elements. download.oracle.com/docs/cd/B14099_19/web.1012/b15896/pshtp.htm But you will still have some amount of 'ugliness' to get those implemented, it may not save you any complexity. Maybe roll your own pipelined function to take input and format it? THen your view could gather data, and function could handle formatting.
    – David Mann
    Commented Oct 12, 2011 at 18:32
  • 1
    I was looking a the HTP package and agree it would not save much in complexity.
    – Lloyd
    Commented Oct 12, 2011 at 18:33

1 Answer 1

Reset to default
3

It is possible to generate xml with dbms_xmlgen, eg:

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

XML
---
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DUMMY>X</DUMMY>
 </ROW>
</ROWSET>

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

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

HTML
----
<html>
<body>
<table border="1">
<tr bgcolor="cyan">
<th>DUMMY</th>
</tr>
<tr>
<td>X</td>
</tr>
</table>
</body>
</html>

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

Improve this answer
7
  • I have to say the "This other post" was the sweetness. Thanks!
    – Lloyd
    Commented Oct 13, 2011 at 14:24
  • Let me guess, you preferred the ref cursor? :)
    – Jack Douglas
    Commented Oct 13, 2011 at 14:27
  • Oh most definitively! Though I am most familiar working with it. I will have to noodle with the solution you gave though, because it could also be handy to skip the declaration of a refcursor. Any reason to do one over the other?
    – Lloyd
    Commented Oct 13, 2011 at 14:38
  • Well if you have procedure that handles a ref cursor then you can also call it from SQL: "Basically, he has a routine to take in any ref cursor (any cursor really, I’ll call it from SQL directly) and convert it into an html table"
    – Jack Douglas
    Commented Oct 13, 2011 at 14:45
  • something like select cursor(select * from mytable) from dual I think (can't test right now)
    – Jack Douglas
    Commented Oct 13, 2011 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.