Send html report per email from sqlplus

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, let’s do the report within sqlplus.

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 sqlplus (you may want to configure the sendmail part)

SQL> @rep

check your mail :

To: laurentschneider@example.com
From: laurentschneider@example.com
Subject: Daily department report
Good morning,

Here is the department report per Friday 15. April , 2011

Kind Regards,

Your IT Operations



List of departments with the total salaries of their employees

Department Salary
ACCOUNTING 8’750
RESEARCH 10’875
SALES 9’400
  29’025

It is pretty easier to maintain than APEX, but the capabilities are not that rich…

5 thoughts on “Send html report per email from sqlplus”

  1. No worries !

    The point is not wether you use “host within sqlplus”, “sqlplus from shell”, “perl”, “powershell” or whatever mail program. You missed the (you may want to configure the sendmail part) point.

    If you have a query and want to quickly format it as html, you can use the set markup html on. I gave my readers an example on how to use the HTML capabilities of sqlplus.

    But I am not a perl fanatic. I especially hate the DBI module, because it is not a core module and because of some traumatic experience of migrating from perl4 to perl5.

    Of course, sqlplus HTML formatting is not state of the art, it is not even XHTML conform (the <P> tags are not closed)

  2. hi laurent,
    i found your sqlplus tips in this blog is very encouraging. wish i’ve found it much earlier :)

    i would like to know, how do we go specifying a condition in sqlplus? for instance i do a select count from a table, if no record found, i will not spool the result to a file. if there is a record, then i would spool it. tq so much.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>