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
|
It is pretty easier to maintain than APEX, but the capabilities are not that rich...
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.
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)
Pingback: Laurent Schneider » Send sqlplus reports in Excel format
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.
Pingback: State of Data -#47 « Dr Data's Blog
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.