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

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

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:

select
  dname, sum(sal)
from
  emp
join
  dept using (deptno)
group by
  rollup(dname);

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

rep.sql

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: laurentschneider@example.com
prompt From: laurentschneider@example.com
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>

select
 dname "Department",
 sum(sal) "Salary"
from
 emp
join dept using (deptno)
group by
 rollup(dname);

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

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:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 

 
   

 Copyright © 1996 -2016 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