Send HTML Report Via Email

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