oracle send mail,数据库包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.查看邮件配置的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

fromdba_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.