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

And here the function get_html_report  that I have copied from

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;


ctxh:= dbms_xmlgen.newcontext(p_query);


— XSLT Transformation to HTML

xslt_tranfsorm := NEW XMLTYPE(‘

<xsl:stylesheet xmlns:xsl=”; version=”1.0″>

<xsl:template match=”/ROWSET”>



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

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






<xsl:template match=”ROW”>



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

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




dbms_xmlgen.setnullhandling(ctxh, dbms_xmlgen.empty_tag);


dbms_xmlgen.setxslt(ctxh, xslt_tranfsorm);


l_mail_body := dbms_xmlgen.getxml(ctxh);




RETURN l_mail_body;

END get_html_report;


Leave a Reply

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

You are commenting using your 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 )

Connecting to %s