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.


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:
prompt From:
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


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 :

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
SALES 9'400

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

Categorized as sqlplus

By Laurent Schneider

Oracle Certified Master


  1. With all due respect, this is an ugly hack. For the tricks with Oracle and email, there is Perl. Doing that from SQL*Plus is an ugly hack.

  2. 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)

  3. 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. 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.

Leave a comment

Your email address will not be published.