oracle sqlplus 把查询结果生成CVS文件，并邮件发送

点击(此处)折叠或打开

#!/bin/bash
#create by linengjiang
#
export LANG=zh_CN.UTF-8
#export LANG=zh_CN.GBK
cd /home/oracle/script/
vfilename=xxx.csv.txt
rm -rf /home/oracle/script/$vfilename
source /home/oracle/.bash_profile
sqlplus -s sys/password@DBNAME AS SYSDBA <<eof< eof</eof<>
set linesize 200
set pagesize 0
set echo off
set feedback off
set trimspool on
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool $vfilename
select 'id,"用户名","性别"' from dual
union all
select '"'||
ID ||'","'||
username ||'","'||
sex ||
'"'
from table_name t;
spool off
EOF
# 删除空行
sed -i '/^ *$/d' $vfilename
echo -e "mail context.\n 请另存为为cvs文件，然后用excel打开，否则会显示乱码.\n 如有问题，请联系xxx.\n this mail comes from host"|mailx -s "mail subject" -a $vfilename -c xxx@162.com yyy@163.com

如果发送邮件，可以参考我的另一篇文章

ITPub博客

oracle sqlplus 把查询结果生成CVS文件，并邮件发送