oracle 向 UTL_MAIL.SEND 提供凭据以绕过 ORA-29278
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26186179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Give credentials to UTL_MAIL.SEND to bypass ORA-29278
提问by user2755525
I am trying to send email from a PL/SQL program using UTL_MAIL.Send packaged procedure. But I am getting the following error:
我正在尝试使用 UTL_MAIL.Send 打包过程从 PL/SQL 程序发送电子邮件。但我收到以下错误：
ORA-29278: SMTP transient error: 421 Service not available
ORA-29278: SMTP 瞬时错误: 421 服务不可用
I searched on web, and found that this error occurs because there is no SMTP service on the host running my Oracle database.
我在网上查了一下，发现这个错误是因为运行我的Oracle数据库的主机上没有SMTP服务。
Now if I want to use another SMTP server, for example , hotmail's "smtp.live.com", i need my user name and password.
现在如果我想使用另一个 SMTP 服务器，例如，hotmail 的“smtp.live.com”，我需要我的用户名和密码。
How can I give my password into the UTL_MAIL.Sendprocedure call?
我怎样才能在UTL_MAIL.Send过程调用中提供我的密码？
(according to my understanding, in order to use any other SMTP server, I have to provide my username/password). I know that to use UTL_MAILpackage, we set the SMTP server with an initialization parameter, and we can give username in "Sender" parameter, but the question is where should we give the password?
（根据我的理解，为了使用任何其他 SMTP 服务器，我必须提供我的用户名/密码）。我知道要使用UTL_MAILpackage，我们用初始化参数设置SMTP服务器，我们可以在“Sender”参数中提供用户名，但问题是我们应该在哪里提供密码？
回答by Sylvain Leroux
Basically you have to use the "lower level" UTL_SMTPpackage in order to send the various SMTP messages required by the distant SMTP server.
基本上，您必须使用“低级”UTL_SMTP包才能发送远程 SMTP 服务器所需的各种 SMTP 消息。
Authentication
验证
From Stefano Ghio's blog:
-- prepare base64 encoded username and password
l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
-- Open connection and send EHLO and AUTH messages
l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
UTL_SMTP.command(l_conn, l_encoded_username);
UTL_SMTP.command(l_conn, l_encoded_password);
The main issue here is that you need be able to send the AUTHmessage using the "right" authentication scheme for your server. I can't say for "smtp.live.com"specifically, but depending the server's configuration, they might be different authentication scheme, like PLAINand LOGIN, DIGEST_MD5, ... Usually (always ?) the parameters (username, password) are base64 encoded.
这里的主要问题是您需要能够AUTH使用服务器的“正确”身份验证方案发送消息。我不能说“smtp.live.com”明确，但根据服务器的配置，它们可能是不同的身份验证方案，如PLAIN和LOGIN，DIGEST_MD5......通常（总是？）的参数（username，password）的base64编码。
Sending mail
发送邮件
But the bad newsis, since you are now using a low-level library, you have to implement the client part of the SMTP protocolyourself. From the same source as above (edited by myself to only keep the absolutely minimum necessary stuff):
但坏消息是，由于您现在使用的是低级库，您必须自己实现SMTP 协议的客户端部分。来自与上述相同的来源（由我自己编辑以只保留绝对最少的必要内容）：
UTL_SMTP.mail(l_conn, mailFrom);
UTL_SMTP.rcpt(l_conn, rcptTo);
[...]
--start multi line message
UTL_SMTP.open_data(l_conn);
--prepare mail header
UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || crlf);
UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || crlf);
UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || crlf);
--include the message body
UTL_SMTP.write_data(l_conn, messageBody || crlf || crlf);
--send the email and close connection
UTL_SMTP.close_data(l_conn);
UTL_SMTP.quit(l_conn);
Using SSL/TLS
使用 SSL/TLS
And now, for the very bad news: some server required a secure connection. Claiming something like 530 Must issue a STARTTLS command first. Unfortunately, UTL_SMTP.STARTTLSis only supported starting from Oracle Database 11g release 2 (11.2.0.2).
现在，非常坏的消息是：某些服务器需要安全连接。声称之类的东西530 Must issue a STARTTLS command first。遗憾的是，UTL_SMTP.STARTTLS仅从 Oracle Database 11g 第 2 版 (11.2.0.2) 开始支持。
If you are lucky enougth to use a recent version of Oracle, you should write something like that to open a secure connection with your server:
如果您足够幸运使用最新版本的 Oracle，您应该编写类似的内容来打开与您的服务器的安全连接：
l_conn := UTL_SMTP.open_connection(l_conn, smtpHost,
wallet_path => 'file:/oracle/wallets/smtp_wallet',
wallet_password => 'password',
secure_connection_before_smtp => FALSE);
UTL_SMTP.starttls(l_conn);
To quote Oracle's documentation:
引用 Oracle 的文档：
SSL/TLS requires an Oracle wallet which must be specified when the connection was opened by the OPEN_CONNECTION Functions.
SSL/TLS 需要一个 Oracle 钱包，必须在 OPEN_CONNECTION 函数打开连接时指定该钱包。
Please see the corresponding documentation to see how to create and manage wallet
请查看相应文档了解如何创建和管理钱包
Few more readings:
更多读物：
- Oracle's documentation about sending e-mail from PL/SQL has some nice examples too depicting you to properly send your mail message using
UTL_SMTP. - See the SMTP Authentification Wikipedia pagefor a transcription of a typical SMTP session.
- Oracle 的关于从 PL/SQL 发送电子邮件的文档也有一些很好的例子，描述了如何使用
UTL_SMTP. - 有关典型 SMTP 会话的转录，请参阅SMTP 身份验证维基百科页面。
回答by myte
found this online. seems like either of these should do the trick:
在网上找到了这个。似乎其中任何一个都可以解决问题：
AUTH PLAIN
纯正版
v_plain_string :=
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('my_user_name'||chr(0)||'my_user_name'||chr(0)||'my_secret_password'))
);
v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com'); -- Must use EHLO vs HELO
UTL_SMTP.command(v_connection, 'AUTH', 'PLAIN ' || v_plain_string);
AUTH LOGIN
授权登录
v_username_b64 :=
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_username))
);
v_password_b64 :=
UTL_RAW.cast_to_varchar2(
UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_password))
);
v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com'); -- Must use EHLO vs HELO
UTL_SMTP.command(v_connection, 'AUTH', 'LOGIN'); -- should receive a 334 response, prompting for username
UTL_SMTP.command(v_connection, v_username_b64); -- should receive a 334 response, prompting for password
UTL_SMTP.command(v_connection, v_password_b64); -- should receive a 235 response, you are authenticated
also i think when you talk about the "Sender" parameter you might be referring to the "From" header for the email itself.
我还认为，当您谈论“发件人”参数时，您可能指的是电子邮件本身的“发件人”标头。
