Oracle 11g 环境下，利用utl - smtp创建发送邮件的存储过程

Oracle 11g 环境下，利用utl_smtp创建发送邮件的存储过程

create or replace procedure Send_mail(mail_body varchar2) is

smtp_conn utl_smtp.connection;

user_name varchar2(20) := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('username@email.com')));

user_paswd varchar2(20) := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('password')));

lv_mail_header varchar2(200):='From:username@email.com'||utl_tcp.CRLF||

'To:sanoul@email.com'||utl_tcp.CRLF||

'Subject:Oracle数据库'||utl_tcp.CRLF;

lv_mail_content varchar2(2000);

begin

lv_mail_content := utl_tcp.CRLF||mail_body;

smtp_conn := utl_smtp.open_connection('smtp.email.com',25);

utl_smtp.helo(smtp_conn,'smtp.email.com');

utl_smtp.command(smtp_conn,'AUTH LOGIN');

utl_smtp.command(smtp_conn,user_name); --邮件用户名

utl_smtp.command(smtp_conn,user_paswd); --邮件密码

utl_smtp.mail(smtp_conn,''); --发件人邮箱

utl_smtp.rcpt(smtp_conn,''); --收件人邮箱

utl_smtp.open_data(smtp_conn);

utl_smtp.write_data(smtp_conn,utl_raw.cast_to_raw(lv_mail_header));

utl_smtp.write_raw_data(smtp_conn,utl_raw.cast_to_raw(lv_mail_content));

utl_smtp.close_data(smtp_conn);

utl_smtp.quit(smtp_conn);

exception

when others then

utl_smtp.quit(smtp_conn);

end Send_mail;

/

--储存过程已创建

第二步直接测试函数；

begin

send_mail('测试内容');

end;

/

ORA-29278: SMTP 临时性错误: 421 Service not available

ORA-06512: 在 \

ORA-06512: 在 \

ORA-06512: 在 \

ORA-06512: 在 \

ORA-06512: 在 \

ORA-06512: 在 line 2

话说我第一次看到这个错误非常震惊，因为整个邮件发送的存储过程是先用PL/SQL直接测试代码后，再封装到存储过程中的，后来经过搜索才知道为了更细致地控制网络权限,Oracle 11g中针对UTL_TCP， UTL_SMTP， UTL_MAIL， UTL_HTTP和 UTL_INADDR的访问设置了单独的权限访问控制方式（ACL）. OK，第三步，设置ACL；

--ACL第一步，创建

BEGIN

dbms_network_acl_admin.create_acl(acl => 'httprequestpermission.xml', --文件名，可以任意取名

DESCRIPTION => 'Normal Access',

principal => 'CONNECT', --角色

is_grant => TRUE,

PRIVILEGE => 'connect',

start_date => NULL,

end_date => NULL);

END;

/

commit; --必须要提交；

然后检查是否创建了该 ACL控制文件；

SQL> SELECT any_path FROM resource_view WHERE any_path like '/sys/acls/%.xml';

如果列表里出现刚才创建的文件 httprequestpermission.xml ，请继续ACL第二步

--ACL第二步，授权用户（示例用scott作为测试）

begin

dbms_network_acl_admin.add_privilege(acl => 'httprequestpermission.xml',

principal => 'SCOTT', --用户，请按照实际变更

is_grant => TRUE,

privilege => 'connect',

start_date => null,

end_date => null);

end;

/

--ACL第三步，添加主机或域名

begin

dbms_network_acl_admin.assign_acl(acl => 'httprequestpermission.xml',

host => 'www.http://www.wodefanwen.com/', --http网页地址

lower_port => 80, --http端口

upper_port => NULL);

end;

/

commit;

begin

dbms_network_acl_admin.assign_acl(acl => 'httprequestpermission.xml',

host => 'smtp.sina.com.cn', --smtp服务器地址

lower_port => 25, --smtp端口

upper_port => NULL);

end;

/

commit;

最后就是再次测试存储过程

SQL> begin

2 send_mail(mail_body => 'afafagaga');

3 end;

4 /

PL/SQL procedure successfully completed

没有任何错误，邮件正确收到；（本文测试环境：Oracle 11.2.0.0，OS：Windows 2008 Server） （作者测试过程中曾遇到过

ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝;

ORA-29278: SMTP 临时性错误: 421 Service not available； ORA-44416: ACL 无效: 无法解析的主用户 'AGENT' 这三个主要错误，按照上述步骤均能解决这些问题）

2 send_mail(mail_body => 'afafagaga');

3 end;

4 /

PL/SQL procedure successfully completed

没有任何错误，邮件正确收到；（本文测试环境：Oracle 11.2.0.0，OS：Windows 2008 Server） （作者测试过程中曾遇到过

ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝;

ORA-29278: SMTP 临时性错误: 421 Service not available； ORA-44416: ACL 无效: 无法解析的主用户 'AGENT' 这三个主要错误，按照上述步骤均能解决这些问题）

本文来源：https://www.bwwdw.com/article/zkva.html