Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Send HTML Report Via Email

Expert Oracle Tips by Laurent Schneider

April 18, 2011


By Laurent Schneider

Oracle guru and Rampant TechPress author, Laurent Schneider, has provided us with a way to send a quick HTML report via email.  This example uses SQL*Plus:

Let's assume your business partner wants to receive some daily mail with an SQL query output in it.

It does not need to be ultra-fancy, but some colors and titles would not hurt.

Here is the report in SQL:

  dname, sum(sal)
  dept using (deptno)
group by

Ok, using SQL*Plus, the report can be done like this:


set echo off numf 999G999G999G999 lin 32000 trims on pages 50000 head on feed off markup html off
alter session set nls_numeric_characters='.''' nls_date_format='Day DD. Month, YYYY';
spool /tmp/rep.html
prompt To:
prompt From:
prompt Subject: Daily department report
prompt Content-type: text/html prompt MIME-Version: 1.0
set markup html on entmap off table 'BORDER="2" BGCOLOR="pink"'

prompt <i>Good morning, </i>
prompt <i>Here is the department report per &_DATE</i>
prompt <i>Kind Regards, </i>
prompt <i>Your IT Operations</i>

prompt <br/><h3>List of departments with the total salaries of their employees</h3>

 dname "Department",
 sum(sal) "Salary"
join dept using (deptno)
group by

spool off
host /usr/sbin/sendmail -t </tmp/rep.html

Then simply call it from SQL*Plus.  You may want to configure the sendmail part.

SQL> @rep

Then, check your mail :

It is easier to maintain than APEX, but the capabilities are not as rich.

In fact, there are definitely more elegant ways of achieving the same results; however, the point being illustrated is the ability to format a query quickly and easily as HTML using set markup html on.  This particular example happens to use the HTML capabilities of SQL*Plus.

Get the Complete
Oracle Tuning Details 

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,150 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA