1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
| #! /bin/bash
# 注意字符集一致 否则读取Oracle中文数据会有乱码
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
. /home/PCAOSCV/aml_home/common/getConfig.sh
#-------------------------------------------------------------------------------
# define the parameters
#-------------------------------------------------------------------------------
# 以下为数据库连接，可根据自己的数据库环境配置
v_user=$(getKey 'Oracle.bi_aml_usr')
v_password=$(getDecryptKey 'Oracle.bi_aml_pw')
v_orcl_service_name=$(getKey 'Oracle.orcl_sn')
v_pkg_owner=$(getKey 'Oracle.bi_aml_usr')
#-------------------------------------------------------------------------------
# 定义附件路径
attachment=/home/PCAOSCV/aml_home/tmp/check_result.csv
# 链接Oracle读取数据
str_batchno=`sqlplus -S $v_user/$v_password@$v_orcl_service_name <<EOF
set pagesize 0;
select t.para_value
from $v_pkg_owner.aml_data_pkg_parameter t;
exit;
EOF`
# 创建CSV文件表头
echo 'TABLE_NAME,CHECK_TYPE,CHECK_RULE,CHECK_RESULT,CHECK_RESULT_DESC,CHECK_COUNT,BATCH_NUM,CHECK_TIME,'>$attachment
# 连接Oracle数据库读取数据并写入CSV文件
str_result=`sqlplus -S $v_user/$v_password@$v_orcl_service_name >>$attachment <<EOF
set pagesize 0
set feedback off
set heading off
set linesize 1000;
select t.table_name || ',' || t.check_type || ',' || t.check_rule || ',' || t.check_result || ',' || t.check_result_desc || ',' || t.check_count || ',' || to_char(t.batch_num) || ',' || t.check_time
from aml_data_check_result t
where t.batch_num = $str_batchno
order by t.check_count
;
exit;
EOF`
# CSV文件中文乱码解决
iconv -f utf8 -t gbk $attachment -o $attachment
mail_subject="Test"
mail_reveiver_list="huangcheng@163.com"
mail_cc_list="huangcheng@126.com"
mail_message="Dear ,
Test
Thanks!
"
echo "$mail_message" | mailx \
-a ${attachment} \ # 添加附件
-s "$mail_subject" -c $mail_cc_list $mail_reveiver_list # 添加抄送和收件人
|