数据库包send_mail发邮件的配置含ACL和privilege

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.查看邮件 配置的pri vilege是什么
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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2139892/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2139892/