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: [email protected]
prompt From: [email protected]
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 Good morning,
prompt Here is the department report per &_DATE
prompt Kind Regards,
prompt Your IT Operations

prompt

List of departments with the total salaries of their employees

select dname "Department", sum(sal) "Salary" from emp join dept using (deptno) group by rollup(dname);
spool off
host /usr/sbin/sendmail -t

Then simply call it from sqlplus (you may want to configure the sendmail part)

SQL> @rep

check your mail :

To: [email protected]
From: [email protected]
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...

6 thoughts on “Send html report per email from sqlplus

  1. Laurent Schneider Post author

    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. Pingback: Laurent Schneider » Send sqlplus reports in Excel format

  3. Mohzan

    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.

  4. Pingback: State of Data -#47 « Dr Data's Blog

  5. Anonymous

    Script is simple and easy, IDK why this other guy wants to use perl. Not a fan of perl either, I think the whole language is a hack! WHy not Java or C++ or python or whatever. I think he missed the whole point here.

Comments are closed.