Send query result in HTML format by email

Send query result in HTML format by email

You can create the following function which returns a CLOB with the query result formatted in HTML. Then you can symply send the email calling the UTL_MAIL.send procedure as follows:

sys.UTL_MAIL.send ( sender => v_sender,
recipients => ‘your@email’,
cc => NULL,
bcc => NULL,
subject => ‘your subject’,
message=> get_html_report( ‘select * from employees’ ),
mime_type => ‘text/html’
);

More info at http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_mail.htm

And here the function get_html_report  that I have copied from  http://www.orafaq.com/forum/t/121740/

create or replace FUNCTION get_html_report(p_query IN VARCHAR2) RETURN  CLOB IS

ctxh            dbms_xmlgen.ctxhandle;

xslt_tranfsorm  XMLTYPE;

l_mail_body     CLOB;

BEGIN

ctxh:= dbms_xmlgen.newcontext(p_query);

 

— XSLT Transformation to HTML

xslt_tranfsorm := NEW XMLTYPE(‘

<xsl:stylesheet xmlns:xsl=”http://www.w3.org/1999/XSL/Transform&#8221; version=”1.0″>

<xsl:template match=”/ROWSET”>

<table>

<tr>

<xsl:for-each select=”ROW[1]/*”>

<th> <xsl:value-of select=”name()”/></th>

</xsl:for-each>

<xsl:apply-templates/>

</tr>

</table>

</xsl:template>

<xsl:template match=”ROW”>

<tr><xsl:apply-templates/></tr>

</xsl:template>

<xsl:template match=”ROW/*”>

<td style=”text-align:right;”><xsl:value-of select=”.”/></td>

</xsl:template>

</xsl:stylesheet>’);

 

dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);

 

dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);

 

l_mail_body := dbms_xmlgen.getxml(ctxh);

 

dbms_xmlgen.closecontext(ctxh);

 

RETURN l_mail_body;

END get_html_report;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s