PROCEDURE send_email_m (STR_TO in varchar2, STR_SUBJECT in varchar2, STR_BODY in varchar2) is
l_mail_conn UTL_SMTP.connection;
arrRecipients string_fnc.t_array;
begin
l_mail_conn := UTL_SMTP.open_connection('10.50.50.50', 25); -- IP Email server
UTL_SMTP.helo(l_mail_conn, '10.50.50.50');
utl_smtp.command( l_mail_conn, 'AUTH LOGIN');
utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'Email_Warning' ))) ); -- Email username
utl_smtp.command( l_mail_conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( '12345678' ))) ); -- Password của email
UTL_SMTP.mail(l_mail_conn, 'Email_Warning@bossdata..vn'); -- Email
arrRecipients := string_fnc.split(STR_TO,',');
for i in 1..arrRecipients.count LOOP
UTL_SMTP.rcpt(l_mail_conn, arrRecipients(i));
end loop;
UTL_SMTP.open_data(l_mail_conn);
--UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || STR_TO || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || 'Email_Warning@bossdata..vn' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || STR_SUBJECT || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || 'Email_Warning@bossdata..vn' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, STR_BODY || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END send_email_m;
2. Chạy
begin
-- Các email cách nhau bằng dấu ,
send_email_m('tranbinh48ca@gmail.com,binhtv10@gmail.com','Test Subject','Test Body');
end;
3. Nếu gặp lỗi:
ORA-24247:
network access denied by access control list (ACL)
ORA-06512:
at "SYS.UTL_TCP", line 17
ORA-06512:
at "SYS.UTL_TCP", line 267
ORA-06512:
at "SYS.UTL_SMTP", line 161
ORA-06512:
at "SYS.UTL_SMTP", line 197
ORA-06512:
at "BINHTV.SEND_EMAIL_M", line 5
ORA-06512:
at line 2
Khắc phục như sau:
1
|
Gán quyền
grant select any dictionary to binhtv;
grant select any table to binhtv;
grant dba to binhtv;
grant execute on utl_http to binhtv;
|
2
|
SELECT * FROM dba_network_acls;
begin
dbms_network_acl_admin.create_acl
(
acl => 'utl_mail_new.xml',
description => 'HTTP Access',
principal => 'BINHTV',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
commit;
|
3
|
SELECT * FROM
dba_network_acl_privileges;
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl
=> 'utl_mail_new.xml',
principal => 'BINHTV',
is_grant => true,
privilege => 'connect');
end;
/
commit;
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl
=> 'utl_mail_new.xml',
principal => 'BINHTV',
is_grant => true,
privilege => 'resolve');
end;
/
commit;
|
4
|
BEGIN
dbms_network_acl_admin.assign_acl
(
acl => 'utl_mail_new.xml',
host => '10.50.50.50',
lower_port => 25,
upper_port => 25
);
END;
revoke select any dictionary from binhtv;
revoke select any table from binhtv;
revoke dba from binhtv;
|
