I posted earlier about sending html mail.
Here an example on how to send -fake- excel per mail
echo "select * from emp;"|
sqlplus -M "HTML ON" -s scott/tiger|
May this be of some help to my readers !
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';
prompt To: firstname.lastname@example.org
prompt From: email@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);
host /usr/sbin/sendmail -t </tmp/rep.html
Then simply call it from sqlplus (you may want to configure the sendmail part)
check your mail :
Subject: Daily department report
Here is the department report per Friday 15. April , 2011
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…
Advertisment found on http://www.firefoxsupport.net/software-download/advanced-oracle-sql-programming-the-expert-guide-to-writing-complex-queries.html
I wrote about tracking ddl changes with a trigger there : track ddl changes
Another option is to use auditing.
A new and cool alternative is to use enable_ddl_logging (11gR2). This will track all ddl’s in the alert log
ALTER SYSTEM SET enable_ddl_logging=TRUE
Then later you issue
create table t(x number)
and you see in the alertLSC01.log
Tue Apr 05 14:43:32 2011
create table t(x number)
Wait, that’s not really verbose !?
Remember the alert log is just there for backward compatibility, it is time you start looking in the xml file 🙂
<msg time='2011-04-05T14:43:42.210+02:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:3937:4222333111' client_id='' type='NOTIFICATION'
group='schema_ddl' level='16' host_id='srv01'
host_addr='192.168.0.141' module='TOAD Beta 188.8.131.52' pid='2777799'>
<txt>create table t(x number)
There is not really much more there but the module, which indeed reveals someone is using TOAD to access my database !