因工作需要采用job定时用oracle发送邮件,在坛子里找到一篇帖子http://www.itpub.net/thread-732719-1-1.html,copy过程,遇到一大堆问题,不过都被我顺利解决,哈哈!
CREATE OR REPLACE PROCEDURE sp_sendmail(P_TXT VARCHAR2,
P_SUB VARCHAR2,
P_SENDOR VARCHAR2,
P_RECEIVER VARCHAR2,
P_SERVER VARCHAR2,
P_PORT NUMBER DEFAULT 25,
P_NEED_SMTP INT DEFAULT 0,
P_USER VARCHAR2 DEFAULT NULL,
P_PASS VARCHAR2 DEFAULT NULL,
P_FILENAME VARCHAR2 DEFAULT NULL,
P_ENCODE VARCHAR2 DEFAULT 'bit 7')
AUTHID CURRENT_USER IS
1.测试过程:最常见的是ora-29279 错误,当使用企业邮箱发送邮件时不需要P_NEED_SMTP 设1,改成0就好啦!
2.建立job:报ORA-12011: execution of 1 jobs failed
ORA-06512: at “SYS.DBMS_IJOB”, line 242
ORA-06512: at “SYS.DBMS_JOB”, line 218
ORA-06512: at line 1
当把执行sp_sendmail过程的代码去掉时,job运行OK,初步判定是权限问题
3.查看alert.log文件,
ORA-12012: 自动执行作业 72 出错
ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝
ORA-06512: 在 "SCOTT.PROCSENDEMAIL", line 359
ORA-06512: 在 "SCOTT.TEST", line 4
关键是ORA-24247: 网络访问被访问控制列表 (ACL) 拒绝
这个错误,是scott用户没有访问ACL的权限,
执行下列包:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'email_server_permissions.xml',
description => 'Enables network permissions for the e-mail server',
principal => 'SCOTT',
is_grant => TRUE,
privilege => 'connect');
END;
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'email_server_permissions.xml',
principal => 'SCOTT',
is_grant => true,
privilege =>'connect');
end;
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'email_server_permissions.xml',
host => 'oa.rondi.com.cn', --SMTP服务器地址
lower_port => 25,
upper_port => NULL);
COMMIT;
END;
OK,全部搞定,哈哈,可以吃饭去咯。