需求：
阅读(2102) | 评论(0) | 转发(0) |
从数据库获取数据并保存为CSV的文件，并把数据文件通过sendEmail以附件的形式发送到指定邮箱。
这里主要利用sendEmail的： -m 邮件内容 -a 邮件附件 这两个参数来完成此工作。
shell 脚本：
#!/bin/sh
#function: get data from database and keep them in csv mode
#then send the log and the csv file to var $TO
#author: yang qilong
#date: 2011 08 01
TODAY=`date +"%Y_%m_%d_%H%M%S"`
DIR='YANGDIR_TMP'
FILENAME=IM_REPORT_ERRINFO_$TODAY.csv
SQLCMD='select * from YANG.ERRINFO WHERE rownum
LOGFILE=/tmp/oracle/get_im_report_errinfo.log
FROM=qilong.yangql@yangql.com
TO=yangqilong@163.com,yangql@163.com --为了发送至多人
sqlplus yang/yang 1>$LOGFILE 2>&1
select sysdate from dual;
exec sys.csv.generate('$DIR','$FILENAME','$SQLCMD');
exit;
EOF!
attach_file_list=/tmp/oracle/$FILENAME
/home/oracle/admin/bin/sendEmail -u "`whoami`@`hostname`:$attach_file_list" \
-m --以执行过程日志为文件的内容
-f $FROM -s 172.18.100.3:25 -t $TO \
-a $attach_file_list
-发送的附件
脚本输出：
oracle@rac1:rac1 /tmp/oracle>./test.sh
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 21:10:49 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
yang@rac1>
SYSDATE
-----------------------------
Aug 01 2011 21:10:49
yang@rac1>
PL/SQL procedure successfully completed.
yang@rac1>Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Reading message body from STDIN because the '-m' option was not used.
If you are manually typing in a message:
- First line must be received within 60 seconds.
- End manual input with a CTRL-D on its own line.
Aug 01 21:10:49 rac1 sendEmail[12684]:
Message input complete.
Aug 01 21:10:49 rac1 sendEmail[12684]:
Email was sent successfully!
oracle@rac1:rac1 /tmp/oracle>
附上此过程遇到的问题：
主要在 -m 的使用上，将执行结果重定向到 -m 的输入内容。但是中间有注释，写法如下：
/home/oracle/admin/bin/sendEmail -u "`whoami`@`hostname`:$attach_file_list" \
-m
"`whoami`@`hostname`:$attach_file_list"
#-m /oracle/sql.log \
-f qilong.yang@yangql.com -s 10.18.100.3:25 -t yangql@163..com \
-a $attach_file_list
输出：
Aug 01 21:10:03 rac1 sendEmail[12628]: ERROR => You must specify a 'from' field! Try --help.
./test.sh: line 22: -f: command not found
1 经过测试，发现sendEmail 语句中不能带#注释。
2 当格式为：
-m "`whoami`@`hostname`:$attach_file_list"
此方式 -m 发送的邮件的内容是：
root@localhost.localdomain:/tmp/IM_REPORT_ERRINFO_2011_08_01_182007.csv 而不是我们想要的从数据库取数据的过程日志。
