1. 先建立一個SEND_EMAIL儲存過程
create or replace PROCEDURE SEND_EMAIL (p_From IN VARCHAR2 :='mdssysmail@mic.com.tw'
,p_To IN VARCHAR2
,p_Subject IN VARCHAR2
,p_Body IN VARCHAR2
,p_Cc IN VARCHAR2 := NULL
,p_Bcc IN VARCHAR2 := NULL
,p_ContentType IN VARCHAR2 := 'text/plain;charset=gb2312'
,p_MailIp IN VARCHAR2 := '118.244.235.30'
,p_Port IN NUMBER := 25) IS
v_Connection utl_smtp.connection;
v_Data RAW(32767);
v_email varchar2(100);
v_count number;
v_start number;
v_end number;
v_date varchar2(50);
v_user_name varchar2(50) := 'mdssysmail@mic.com.tw';
v_password varchar2(50) := 'Password_1';
BEGIN
v_date:=to_char(sysdate-1/3,'YYYY-mm-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN');
v_Connection := UTL_smtp.open_connection(p_MailIp,p_Port);
UTL_smtp.ehlo(v_Connection, p_MailIp);
UTL_smtp.command(v_Connection, 'AUTH LOGIN');
UTL_smtp.command(v_Connection,
demo_base64.encode(utl_raw.cast_to_raw(v_user_name)));
UTL_smtp.command(v_Connection,
demo_base64.encode(utl_raw.cast_to_raw(v_password)));
UTL_smtp.mail(v_Connection, p_From);
IF (p_to IS NOT NULL) THEN
v_count := 1;
v_start := 0;
v_end :=1;
for c in 1..length(p_to) loop
if c>v_start then
v_end := instr(p_to,';',1,v_count);
if v_end>0 then
UTL_smtp.rcpt(v_Connection,substr(p_to,v_start+1,v_end-v_start-1));
else
UTL_smtp.rcpt(v_Connection,substr(p_to,v_start+1,length(p_to)-v_start));
exit;
end if;
v_start := v_end;
v_count := v_count +1;
end if;
end loop;
END IF;
IF (p_Cc IS NOT NULL) THEN
v_count := 1;
v_start := 0;
v_end :=1;
for c in 1..length(p_Cc) loop
if c>v_start then
v_end := instr(p_Cc,';',1,v_count);
if v_end>0 then
UTL_smtp.rcpt(v_Connection,substr(p_Cc,v_start+1,v_end-v_start-1));
else
UTL_smtp.rcpt(v_Connection,substr(p_Cc,v_start+1,length(p_Cc)-v_start));
exit;
end if;
v_start := v_end;
v_count := v_count +1;
end if;
end loop;
END IF;
IF (p_Bcc IS NOT NULL) THEN
v_count := 1;
v_start := 0;
v_end :=1;
for c in 1..length(p_Bcc) loop
if c>v_start then
v_end := instr(p_Bcc,';',1,v_count);
if v_end>0 then
UTL_smtp.rcpt(v_Connection,substr(p_Bcc,v_start+1,v_end-v_start-1));
else
UTL_smtp.rcpt(v_Connection,substr(p_Bcc,v_start+1,length(p_Bcc)-v_start));
exit;
end if;
v_start := v_end;
v_count := v_count +1;
end if;
end loop;
END IF;
UTL_smtp.open_data(v_Connection);

--select to_char(sysdate,'YYYY-MON-DD','NLS_DATE_LANGUAGE=AMERICAN') from dual
/* ** Sending the header information */
--UTL_smtp.write_data(v_Connection,'Date: ' || to_char(sysdate-1/3,'YYYY-MON-DD hh24:mi:ss','NLS_DATE_LANGUAGE=AMERICAN') ||UTL_tcp.CRLF);
UTL_smtp.write_data(v_Connection,'Date: ' || TO_CHAR(SYSTIMESTAMP,'DD Mon YYYY HH24:MI:SS TZHTZM','NLS_DATE_LANGUAGE=AMERICAN') ||UTL_tcp.CRLF);
--UTL_smtp.write_data(v_Connection,'Date: ' || v_date ||UTL_tcp.CRLF);
--UTL_smtp.write_data(v_Connection,'Date: ' || 'NOW' || UTL_tcp.CRLF);
IF (p_From IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'From: ' ||p_From|| UTL_tcp.CRLF);
END IF;
IF (p_to IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'To: ' || p_to|| UTL_tcp.CRLF);
END IF;
IF (p_Cc IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'Cc: ' || p_cc || UTL_tcp.CRLF);
END IF;
IF (p_Bcc IS NOT NULL) THEN
UTL_smtp.write_data(v_Connection, 'Bcc: ' || p_bcc || UTL_tcp.CRLF);
END IF;
--UTL_smtp.write_data(v_Connection, 'Subject: ' || p_Subject || UTL_tcp.CRLF); //以下可以顯示中文subject
UTL_smtp.write_raw_data(v_Connection,utl_raw.cast_to_raw(convert('Subject: ' ||p_Subject||utl_tcp.crlf,'ZHS16GBK')));
UTL_smtp.write_data(v_Connection, 'MIME-Version: ' || '1.0' || UTL_tcp.CRLF);
UTL_smtp.write_data(v_Connection, 'Content-Type: ' ||p_ContentType|| UTL_tcp.CRLF);
UTL_smtp.write_data(v_Connection, 'Content-Transfer-Encoding: ' || '8bit' || UTL_tcp.CRLF);
/* ** End of header information */
UTL_smtp.write_data(v_Connection, UTL_tcp.CRLF);
/* ** Actual body is sent here */
v_Data := utl_raw.cast_to_raw(convert(p_body,'ZHS16GBK'));
UTL_smtp.write_raw_data(v_Connection, v_Data);
/* ** Connection is closed here */
UTL_smtp.close_data(v_Connection);
UTL_smtp.quit(v_Connection);

EXCEPTION
WHEN UTL_smtp.transient_error OR UTL_smtp.permanent_error THEN
UTL_smtp.quit(v_Connection);
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
UTL_smtp.quit(v_Connection);
dbms_output.put_line(sqlerrm);

END SEND_EMAIL;

2. 配置ACL和privilege
已經建立了ACL和privilege的情況下
1.檢視郵件配置的ACL和ip
select * from dba_network_acls

2.檢視郵件配置的privilege是什麼
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;

3.在郵件配置中新增該使用者
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl=> '第一步查到的ACL',
principal => '使用者名稱大寫',
is_grant => TRUE,
privilege => '第二步查到的privilege');
end;
commit;

4.測試，進入使用者下進行測試，如果在sys使用者下測試，則send_email前面要加使用者名稱
如果在putty上發，中文可能亂碼，可以在plsql上發就不會亂碼了
如果第一步查到的acl對應10.98.0.211則用如下測試
begin
SEND_EMAIL ('mdssysmail@mic.com.tw'
,'luxus.liao@mic.com.tw'
,'KANGDA OK'
,'KANGDA OK'
,'luxus.liao@mic.com.tw'
,'luxus.liao@mic.com.tw'
, 'text/plain;charset=gb2312'
, '10.98.0.211'
, 25);
end;

如果第一步查到的acl對應10.98.10.120，則用如下測試
begin
SEND_EMAIL ('mdssysmail@mdsets.cn'
,'luxus.liao@mic.com.tw'
,'KANGDA OK'
,'KANGDA OK'
,'luxus.liao@mic.com.tw'
,'luxus.liao@mic.com.tw'
, 'text/plain;charset=gb2312'
, '10.98.10.120'
, 25);
end;

沒有建立了ACL和privilege的情況下
1.建立 ACL
begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl=>'mail_acl.xml', description=>'使用者名稱大寫', principal=>'使用者名稱大寫大寫', is_grant=>TRUE, privilege=> 'connect');
END;
/
commit;

2.與郵件服務關聯
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'mail_acl.xml',
host => '客戶郵件伺服器IP',
lower_port => 25);
END;
/
commit;

3.郵件配置關聯使用者
begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl=> 'mail_acl.xml',
principal => '使用者名稱大寫',
is_grant => TRUE,
privilege => 'connect');
end;
/
commit;

4.郵件測試
begin
SEND_EMAIL ('客戶那邊的一個發件人'
,'luxus.liao@mic.com.tw'
,'KANGDA OK'
,'KANGDA OK'
,'luxus.liao@mic.com.tw'
,'luxus.liao@mic.com.tw'
, 'text/plain;charset=gb2312'
, '客戶郵件伺服器IP'
, 25);
end;

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL中privilege的解釋
Network privilege to be granted or denied - 'connect |
resolve' (case sensitive). A database user needs the connect
privilege to an external network host computer if he or she is
connecting using the UTL_TCP, UTL_HTTP, UTL_SMTP, and UTL_
MAIL utility packages. To resolve a host name that was given a
host IP address, or the IP address that was given a host name,
with the UTL_INADDR package, grant the database user the
resolve privilege.

資料庫包send_mail發郵件的配置含ACL和privilege

相關文章