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|
uuencode emp.xls|
mail laurentschneider@example.com
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.
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
|
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 11.0.0.52' pid='2777799'>
<txt>create table t(x number)
</txt>
</msg>
There is not really much more there but the module, which indeed reveals someone is using TOAD to access my database !