Oracle發郵件，權限問題
- 創建 ACL
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'email_server_permissions.xml',
description => 'Enables network permissions for the e-mail server',
principal => 'LJZ',
is_grant => TRUE,
privilege => 'connect');
END;
-- 與郵件服務關聯
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'email_server_permissions.xml',
host => 'smtp.163.com',
lower_port => 25,
upper_port => NULL);
END;
-- 這樣 email_user 用戶帳戶創建的存儲過程便可以向此郵件服務器發送郵件
--刪除
BEGIN
DBMS_NETWORK_ACL_ADMIN.drop_acl(acl => 'email_server_permissions.xml');
COMMIT;
END;
--查詢
SELECT host, lower_port, upper_port, acl FROM dba_network_acls;
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM dba_network_acl_privileges;
存儲過程
SQL Code
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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
create or replace procedure send_mail_sp(p_from in varchar2,
p_to in varchar2,
p_subject in varchar2,
p_text in clob default null,
p_html in clob default null,
p_attachment_path varchar2,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2 default 25,
p_need_valid varchar2 default 'Y',
p_user_name varchar2,
p_user_pwd varchar2) IS
/*
作用：用oracle發送郵件
主要功能：1、支持多收件人。
2、支持中文
3、支持抄送人
4、支持大於32K的附件
5、支持多行正文
6、支持多附件
7、支持HTML郵件
作者：jinzhao
參數說明：
p_from 發件人
p_to 收件人，多收件人用逗號或分號分隔
p_subject 主題
p_text 文本內容
p_html HTML內容
p_attachment_path 附件地址（絕對路徑）,多附件用逗號或分號分隔
p_smtp_hostname 郵件服務器地址 例：smtp.163.com
p_smtp_portnum 端口號，默認25
p_need_valid 是否需要用戶名密碼驗證，默認需要（'Y'）
p_user_name 用戶名
p_user_pwd 密碼
*/
l_crlf varchar2(2) := utl_tcp.crlf;
l_write_encode varchar2(20) := 'zhs16gbk';
l_attachment_encode varchar2(10) := 'base64';
l_attachment_mime_type varchar2(50) := 'application/octet-stream';
l_attachment_disposition varchar2(10) := 'attachment';
l_boundary_mail varchar2(255) default '#---hhiuuihh-mail---#';
l_boundary_content varchar2(255) default '#---hhiuuihh-content---#';
l_first_boundary constant varchar2(256) := '--' || l_boundary_mail ||
l_crlf;
l_last_boundary constant varchar2(256) := '--' || l_boundary_mail || '--' ||
l_crlf;
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --HTML郵件內容
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
l_file_handle utl_file.file_type;
l_line varchar2(1000);
l_mesg varchar2(32767);
l_filepos pls_integer := 1;
l_fil bfile;
l_file_len number;
l_modulo number;
l_pieces number;
l_amt number := 8580;
l_chunks number;
l_buf raw(32767);
l_data raw(32767);
l_max_line_width number := 54;
l_directory_base_name varchar2(100) := 'DIR_FOR_SEND_MAIL';
l_receivers varchar2(32767);
type address_list is table of varchar2(100) index by binary_integer;
my_address_list address_list;
type acct_list is table of varchar2(100) index by binary_integer;
my_acct_list acct_list;
--分割郵件地址或者附件地址
procedure sub_splite_str(p_str varchar2, p_splite_flag int default 1) is
l_addr varchar2(254) := '';
l_len int;
l_str varchar2(4000);
j int := 0; --表示郵件地址或者附件的個數
begin
/*處理接收郵件地址列表，包括去空格、將;轉換為,等*/
l_str := trim(rtrim(replace(replace(p_str, ';', ','), ' ', ''), ','));
l_len := length(l_str);
for i in 1 .. l_len loop
if substr(l_str, i, 1) <> ',' then
l_addr := l_addr || substr(l_str, i, 1);
else
j := j + 1;
if p_splite_flag = 1 then
--表示處理郵件地址
--前後需要加上'<>'，否則很多郵箱將不能發送郵件
l_addr := '<' || l_addr || '>';
--調用郵件發送過程
my_address_list(j) := l_addr;
elsif p_splite_flag = 2 then
--表示處理附件名稱
my_acct_list(j) := l_addr;
end if;
l_addr := '';
end if;
if i = l_len then
j := j + 1;
if p_splite_flag = 1 then
--調用郵件發送過程
l_addr := '<' || l_addr || '>';
my_address_list(j) := l_addr;
elsif p_splite_flag = 2 then
my_acct_list(j) := l_addr;
end if;
end if;
end loop;
end;
--刪除directory
procedure sub_drop_directory(p_directory_name varchar2) is
begin
execute immediate 'drop directory ' || p_directory_name;
exception
when others then
null;
end;
--創建directory
procedure sub_create_directory(p_directory_name varchar2, p_dir varchar2) is
begin
execute immediate 'create directory ' || p_directory_name || ' as ''' ||
p_dir || '''';
execute immediate 'grant read,write on directory ' || p_directory_name ||
' to public';
exception
when others then
raise;
end;
--返回附件源文件所在目錄或者名稱
function sub_get_file(p_file varchar2, p_get int) return varchar2 is
--p_get=1 表示返回目錄
--p_get=2 表示返回文件名
l_file varchar2(1000);
begin
if instr(p_file, '\') > 0 then
if p_get = 1 then
l_file := substr(p_file, 1, instr(p_file, '\', -1) - 1) || '\';
elsif p_get = 2 then
l_file := substr(p_file,
- (length(p_file) - instr(p_file, '\', -1)));
end if;
elsif instr(p_file, '/') > 0 then
if p_get = 1 then
l_file := substr(p_file, 1, instr(p_file, '/', -1) - 1);
elsif p_get = 2 then
l_file := substr(p_file,
- (length(p_file) - instr(p_file, '/', -1)));
end if;
end if;
return l_file;
end;
--發送附件
procedure sub_attachment(conn in out nocopy utl_smtp.connection,
filename in varchar2,
dt_name in varchar2) is
l_filename varchar2(1000);
l_amount number;
begin
sub_drop_directory(dt_name);
--創建directory
sub_create_directory(dt_name, sub_get_file(filename, 1));
--得到附件文件名稱
l_filename := sub_get_file(filename, 2);
l_temp := l_temp || l_crlf || '--' || l_boundary_mail || l_crlf;
l_temp := l_temp || 'Content-Type: ' || l_attachment_mime_type || ';
name="' || l_filename || '"
Content-Transfer-Encoding: ' || l_attachment_encode || '
Content-Disposition: ' || l_attachment_disposition || ';
filename="' || l_filename || '"' || l_crlf || l_crlf;
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(l_temp,
l_write_encode)));
--begin
--begin
--把附件分成多份，這樣可以發送超過32k的附件
l_filepos := 1; --重置offset，在發送多個附件時，必須重置
l_fil := bfilename(dt_name, l_filename);
l_file_len := dbms_lob.getlength(l_fil);
l_modulo := mod(l_file_len, l_amt);
l_pieces := trunc(l_file_len / l_amt);
if (l_modulo <> 0) then
l_pieces := l_pieces + 1;
end if;
dbms_lob.fileopen(l_fil, dbms_lob.file_readonly);
l_data := null;
l_amount := l_amt;
for i in 1 .. l_pieces loop
dbms_lob.read(l_fil, l_amount, l_filepos, l_buf);
l_filepos := i * l_amount + 1;
l_file_len := l_file_len - l_amount;
utl_smtp.write_raw_data(conn, utl_encode.base64_encode(l_buf));
if i = l_pieces then
l_amount := l_file_len;
end if;
end loop;
dbms_lob.fileclose(l_fil);
/*exception
when others then
dbms_lob.fileclose(l_fil);
sub_end_boundary(conn);
raise;
end; --結束處理二進制附件*/
sub_drop_directory(dt_name);
end; --結束過程attachment
procedure sub_send_mail is
l_from varchar2(1000) := '<' || p_from || '>';
begin
l_connection := utl_smtp.open_connection(p_smtp_hostname,
p_smtp_portnum);
utl_smtp.helo(l_connection, p_smtp_hostname);
/* smtp服務器登錄校驗 */
IF p_need_valid = 'Y' THEN
utl_smtp.command(l_connection, 'AUTH LOGIN', '');
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user_name))));
utl_smtp.command(l_connection,
utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_user_pwd))));
END IF;
utl_smtp.mail(l_connection, l_from);
sub_splite_str(p_to); --處理郵件地址
for k in 1 .. my_address_list.count loop
l_receivers := l_receivers || my_address_list(k) || ';';
utl_smtp.rcpt(l_connection, my_address_list(k));
end loop;
l_temp := l_temp || 'From: ' || l_from || l_crlf;
l_temp := l_temp || 'To: ' || l_receivers || l_crlf;
--l_temp := l_temp || 'Cc: ' || l_receivers || l_crlf;--抄送
--l_temp := l_temp || 'Bcc: ' || l_receivers || l_crlf;--密送
l_temp := l_temp || 'Subject: ' || p_subject || l_crlf;
--l_temp := l_temp || 'X-Mailer: Foxmail 7, 1, 3, 48[cn]' || l_crlf;--發送客戶端
l_temp := l_temp || 'Content-Type: multipart/mixed; boundary="' ||
l_boundary_mail || '"' || l_crlf;
l_temp := l_temp || 'MIME-Version: 1.0' || l_crlf || l_crlf;
l_temp := l_temp || '--' || l_boundary_mail || l_crlf;
if nvl(p_attachment_path, ' ') <> ' ' then
l_temp := l_temp || 'content-type: multipart/alternative; boundary="' ||
l_boundary_content || '"' || l_crlf || l_crlf || l_crlf;
l_temp := l_temp || '--' || l_boundary_content || l_crlf;
end if;
--開始
dbms_lob.createtemporary(l_body_html, false, 10);
dbms_lob.write(l_body_html, length(l_temp), 1, l_temp);
--文本內容
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := 'content-type: text/plain; charset="GB2312"; Content-Transfer-Encoding: base64' ||
l_crlf || l_crlf;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
dbms_lob.append(l_body_html, p_text);
--html內容
if nvl(p_attachment_path, ' ') <> ' ' then
l_temp := l_crlf || l_crlf || '--' || l_boundary_content || l_crlf;
else
l_temp := l_crlf || l_crlf || '--' || l_boundary_mail || l_crlf;
end if;
l_temp := l_temp ||
'content-type: text/html;charset="GB2312";Content-Transfer-Encoding: quoted-printable' ||
l_crlf || l_crlf;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
dbms_lob.append(l_body_html, p_html);
--content結束
if nvl(p_attachment_path, ' ') <> ' ' then
l_temp := l_crlf || l_crlf || '--' || l_boundary_content || '--' ||
l_crlf || l_crlf;
else
l_temp := l_crlf || '--' || l_boundary_mail || '--' || l_crlf ||
l_crlf;
end if;
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
--寫入郵件
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(dbms_lob.substr(l_body_html,
l_ammount,
l_offset),
l_write_encode)));
l_offset := l_offset + l_ammount;
l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
commit;
----------------------------------------------------
l_temp := null;
--附件
--如果文件名稱不為空，則發送附件
if (p_attachment_path is not null) then
--根據逗號或者分號拆分附件地址
sub_splite_str(p_attachment_path, 2);
--循環發送附件(在同一個郵件中)
for k in 1 .. my_acct_list.count loop
sub_attachment(conn => l_connection,
filename => my_acct_list(k),
dt_name => l_directory_base_name || to_char(k));
l_temp := l_crlf;
end loop;
end if;
l_temp := l_crlf || l_crlf || '--' || l_boundary_mail || '--' || l_crlf ||
l_crlf;
utl_smtp.write_raw_data(l_connection,
utl_raw.cast_to_raw(convert(l_temp,
l_write_encode)));
commit;
utl_smtp.close_data(l_connection);
utl_smtp.quit(l_connection);
--utl_smtp.close_connection(l_connection);
dbms_lob.freetemporary(l_body_html);
end;
begin
sub_send_mail();
/*exception
when others then
null;*/
end;
