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

如果傳送郵件，可以參考我的另一篇文章

oracle sqlplus 把查詢結果生成CVS檔案，並郵件傳送

相關文章