Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Oct 12 '11 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 Oct 12 '11 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 Oct 12 '11 at 18:32
1  
I was looking a the HTP package and agree it would not save much in complexity. –  Lloyd Oct 12 '11 at 18:33
add comment

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer
    
I have to say the "This other post" was the sweetness. Thanks! –  Lloyd Oct 13 '11 at 14:24
    
Let me guess, you preferred the ref cursor? :) –  Jack Douglas Oct 13 '11 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 Oct 13 '11 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 Oct 13 '11 at 14:45
    
something like select cursor(select * from mytable) from dual I think (can't test right now) –  Jack Douglas Oct 13 '11 at 14:46
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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