1、shell脚本:
export NLS_LANG="Simplified Chinese_china".AL32UTF8
#export NLS_LANG="Simplified Chinese_china".zhs16gbk
export LANG=zh_CN.UTF-8
export ORACLE_SID=orcl
export ORACLE_BASE=/home/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export DATE=`date '+%Y%m%d'`
NOW=$(date +%Y%m%d)
###前一天的取法:yestday=$(date -d -1day +%Y%m%d)
sqlplus test/test << EOF
@/home/oracle/baobiao/spool_report_data.sql;
EOF
wait;
zip -r /home/oracle/baobiao/$NOW报表.zip /home/oracle/baobiao/xlsdir
wait;
echo "$NOW报表" | mutt 123456789@qq.com,987654321@qq.com -s "$NOW报表" -a /home/oracle/baobiao/$NOW报表.zip -F /home/oracle/.muttrc_test;
rm -rf /home/oracle/baobiao/xlsdir/*.xls
exit
2、spool_report_data.sql内容:
set linesize 200
set term off verify off feedback off pagesize 9999
set markup html on entmap ON spool on preformat off
spool ./xlsdir/test.xls
select * from test;--------------test表中所有字段建议都为字符类型,否则在execl中打开时,列头易产生乱码
3、muttrc_test文件中,系统管理员已配置好,我仅需配置 set realname和set from 这两个参数。
export NLS_LANG="Simplified Chinese_china".AL32UTF8
#export NLS_LANG="Simplified Chinese_china".zhs16gbk
export LANG=zh_CN.UTF-8
export ORACLE_SID=orcl
export ORACLE_BASE=/home/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin
export DATE=`date '+%Y%m%d'`
NOW=$(date +%Y%m%d)
###前一天的取法:yestday=$(date -d -1day +%Y%m%d)
sqlplus test/test << EOF
@/home/oracle/baobiao/spool_report_data.sql;
EOF
wait;
zip -r /home/oracle/baobiao/$NOW报表.zip /home/oracle/baobiao/xlsdir
wait;
echo "$NOW报表" | mutt 123456789@qq.com,987654321@qq.com -s "$NOW报表" -a /home/oracle/baobiao/$NOW报表.zip -F /home/oracle/.muttrc_test;
rm -rf /home/oracle/baobiao/xlsdir/*.xls
exit
2、spool_report_data.sql内容:
set linesize 200
set term off verify off feedback off pagesize 9999
set markup html on entmap ON spool on preformat off
spool ./xlsdir/test.xls
select * from test;--------------test表中所有字段建议都为字符类型,否则在execl中打开时,列头易产生乱码
3、muttrc_test文件中,系统管理员已配置好,我仅需配置 set realname和set from 这两个参数。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10569970/viewspace-1800784/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10569970/viewspace-1800784/