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
Here is the report in SQL:
dept using (deptno)
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';
prompt To: email@example.com
prompt Subject: Daily department report
text/html prompt MIME-Version: 1.0
set markup html on entmap off table
<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>
join dept using (deptno)
host /usr/sbin/sendmail -t </tmp/rep.html
Then simply call it from SQL*Plus. You may want to configure
the sendmail part.
It is easier to maintain than APEX, but the capabilities are not as
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.