问题描述
我们计划安装UTL_MAIL软件包，目前正在开发环境中测试安装步骤.成功安装UTL_MAIL程序包脚本并创建足够的PUBLIC同义词和授权后，我们在运行下面的测试匿名块时收到错误ORA-29278:
We are planning to install the UTL_MAIL Package and we're currently testing the installation steps in our Development Environment.After sucessfully installing the UTL_MAIL Package Scripts and creating the sufficient PUBLIC Synonyms and Grants,we are getting the error ORA-29278 when running the test Anonymous Block below:
BEGIN
UTL_MAIL.SEND(sender => '[email protected]'
, recipients => '[email protected]'
, subject => 'Testmail'
, message => 'Hello');
END;
错误消息的完整详细信息:
Full Details of the error Message:
ORA-29278: SMTP transient error: 421 4.3.2 Service not available
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 3
29278. 00000 - "SMTP transient error: %s"
*Cause: A SMTP transient error occurred.
*Action: Correct the error and retry the SMTP operation.
根据相关链接的研究(使用PLSQL发送电子邮件)，我可能需要设置适当的访问控制列表(ACL)才能起作用.但是，执行以下脚本后，我仍然遇到相同的错误.
As per research from related links (Send Email Using PLSQL),i may need to setup the proper access control list (ACL) for this to work. However, upon executing the script below, i'm still getting the same error.
DECLARE
-- ACL name to be used for email access reuse the same value for all
-- future calls
l_acl VARCHAR2 (30) := 'utl_smtp.xml';
-- Oracle user to be given permission to send email
l_principal VARCHAR2 (30) := 'APPS';
-- Name of email server
g_mailhost VARCHAR2 (60) := 'smtprelay.xxxxx.com';
l_cnt INTEGER;
PROCEDURE validate_smtp_server
AS
l_value v$parameter.VALUE%TYPE;
l_parameter v$parameter.name%TYPE := 'smtp_out_server';
BEGIN
SELECT VALUE
INTO l_value
FROM v$parameter
WHERE name = l_parameter;
IF l_value IS NULL
THEN
raise_application_error (
-20001
, 'Oracle parameter '
|| l_parameter
|| ' has not been set'
|| UTL_TCP.crlf
|| 'it s/b smtprelay.alorica.com'
);
END IF;
DBMS_OUTPUT.put_line ('parameter ' || l_parameter || ' value is ' || l_value);
END validate_smtp_server;
PROCEDURE create_if_needed (p_acl IN VARCHAR2)
AS
l_cnt INTEGER;
BEGIN
SELECT COUNT (*) c
INTO l_cnt
FROM dba_network_acls a
WHERE SUBSTR (acl, INSTR (acl, '/', -1) + 1) = p_acl;
IF l_cnt = 0
THEN
DBMS_OUTPUT.put_line ('creating acl ' || p_acl);
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => p_acl
, description => 'Allow use of utl_smtp'
, principal => l_principal
, is_grant => TRUE
, privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.assign_acl (acl => p_acl, HOST => g_mailhost);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (p_acl || ' acl already exists');
END IF;
END create_if_needed;
PROCEDURE add_if_needed (
p_principal IN VARCHAR2
, p_acl IN VARCHAR2
)
AS
l_cnt INTEGER;
BEGIN
SELECT COUNT (*) c
INTO l_cnt
FROM dba_network_acl_privileges
WHERE SUBSTR (acl, INSTR (acl, '/', -1) + 1) = p_acl
AND principal = p_principal;
IF l_cnt = 0
THEN
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'utl_smtp.xml'
, principal => p_principal
, is_grant => TRUE
, privilege => 'connect'
);
COMMIT;
DBMS_OUTPUT.put_line ('access to ' || p_acl || ' added for ' || p_principal);
ELSE
DBMS_OUTPUT.put_line (p_principal || ' already has access to ' || p_acl);
END IF;
END add_if_needed;
BEGIN
EXECUTE IMMEDIATE 'grant execute on utl_mail to ' || l_principal;
create_if_needed (p_acl => l_acl);
add_if_needed (p_principal => l_principal, p_acl => l_acl);
DBMS_OUTPUT.put_line ('Verification SQL:');
DBMS_OUTPUT.put_line (' SELECT * FROM dba_network_acls;');
DBMS_OUTPUT.put_line (' SELECT * FROM dba_network_acl_privileges;');
COMMIT;
validate_smtp_server;
END;
为此，我还可以采取什么其他步骤或需要向DBA提供什么其他说明?
What other steps can i take or what other instructions do i need to provide to the DBAs for this?
Oracle数据库版本:
Oracle Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
非常感谢您.
推荐答案
我可以通过联系我们的系统管理员并询问邮件服务器的详细信息来解决此问题.事实证明，如果只在内部发送电子邮件，建议您使用其他服务器mail.xxx.xxx.xxxx，因为它不会被防火墙阻止.另一方面，如果我们要从外部发送电子邮件，则涉及到另一台服务器smtprelay.xxxxx.com并且需要执行额外的步骤，将要发送到的外部服务器列入白名单.
I was able to resolve this by contacting our System Administrator and asking for the details of the Mail Server.Turns out, if we'll only be sending the email internally, we are advised to use a different server mail.xxx.xxx.xxxx since its not going to be blocked by the firewall.On the other hand, if we'll be sending email externally, another server is involved smtprelay.xxxxx.comand this involves an extra step of Whitelisting the External Servers to be sent to.
当我在V$PARAMETER中签到时，我们正在使用smtprelay.xxxxx.com服务器，并决定尝试其他服务器mail.xxx.xxx.xxxx.
As i checked in V$PARAMETER, we were using the smtprelay.xxxxx.com server and decided to try the other server mail.xxx.xxx.xxxx.
我发出了如下的Alter命令:
I issued the Alter command as below:
alter system set smtp_out_server = 'mail.xxx.xxx.xxxx';
并运行匿名阻止，并能够成功接收电子邮件.
and ran the anonymous block and was able to recieve the email successfully.
BEGIN
UTL_MAIL.SEND(sender => '[email protected]'
, recipients => '[email protected]'
, subject => 'Testmail'
, message => 'Hello');
END;
这篇关于ORA-29278:SMTP暂时错误:运行UTL_MAIL时服务不可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！