During O & M, sometimes you need to regularly send emails to the SQL query data result set in the form of html tables. Therefore, you need to splice the SQL query result set into html code. There are usually two ways to do this. First, cronjob is directly used for regular round-robin and completed with OS-level email programs. The query result set can be directly in S
During O & M, sometimes you need to regularly send emails to the SQL query data result set in the form of html tables. Therefore, you need to splice the SQL query result set into html code. There are usually two ways to do this. First, the cron job is directly used for regular round-robin and completed with OS-level email programs. The query result set can be directly in S
During O & M, sometimes you need to regularly send emails to the SQL query data html> result set in the form of html tables. Therefore, you need to splice the result set obtained by SQL query into html code. There are usually two ways to do this. First, the cron job is directly used for regular round-robin and completed with OS-level email programs. The query result set can be set in SQL * Plus to automatically implement html table format by setting html tags. One way is to use scheduler job in Oracle for regular round robin. In this way, we need to manually splice html code. This article describes the second case.
For details about how to send emails in PL/SQL, refer to the mail sending program in PL/SQL.
1. Code Description
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
-- The following code snippet is mainly used to send an error message when synchronizing part of database A data to database B.
-- Table syn_data_err_log_tbl records error logs, that is, as long as a new record or old record appears in the table and the mailed column is marked as N, it indicates that an email needs to be sent.
-- The following describes the code segment information one by one. This code segment can be encapsulated into a package.
PROCEDURE email_on_syn_data_err_log (err_num out number,
Err_msg OUT VARCHAR2)
AS
V_msg_txt VARCHAR2 (32767 );
V_sub VARCHAR2 (100 );
V_html_header VARCHAR (4000 );
V_html_content VARCHAR (32767 );
V_count NUMBER;
V_log_seq NUMBER (12 );
V_loop_count NUMBER: = 0;
CURSOR cur_errlog -- Use cursor to generate the table title Section
IS
SELECT'
'
| TO_CHAR (sd. log_seq)
|'
'
| Sd. process
|''
|''
| Sd. rec_id
|''
|''
| REPLACE (sd. err_msg, '<', ';'), '> ',';')
|''
|''
| TO_CHAR (sd. log_time, 'yyyy-mm-dd hh24: mi: ss ')
|'
',
Sd. log_seq
FROM syn_data_err_log_tbl sd
WHERE sd. mailed = 'n'
Order by sd. log_seq;
BEGIN
Err_num: = common_pkg.c_suc_general;
Select count (*)
INTO v_count --> count the total number of records to be sent
FROM syn_data_err_log_tbl sd
WHERE sd. mailed = 'n ';
IF v_count> 0 --> indicates that an email needs to be sent IF a record exists.
THEN
SELECT 'job process failed on' | instance_name | 'HTTP: // www.3lian.com/'| host_name
INTO v_sub --> Generate the subject of the email
FROM v $ instance;
V_html_header: = --> define the header information of a table.
'