oracle utl_smtp 邮件发送协议

最新推荐文章于 2023-02-10 11:19:04 发布

原创

最新推荐文章于 2023-02-10 11:19:04 发布 · 916 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #application #header #c #exception #server

本文介绍了如何在Oracle中利用UTL_SMTP包发送邮件，包括发送邮件的基本步骤、注意事项及解决中文乱码问题的方法，并提供了具体的PL/SQL示例代码。

最近想在Oracle中设置一个触发器，每天执行数据检测脚本之后，如果发现错误数据就自动发送邮件到我邮箱里，于是研究了一下在Oracle中发送邮件的方法。据说10g里可以使用UTL_MAIL包来简单得发送邮件了，但是觉得通用性不高，万一哪天换成9i了就要重写，于是还是决定用UTL_SMTP包来做。

先简单看一下官方文档上的例子。其实很简单：

------------------------------------------
The following example illustrates how UTL_SMTP is used by an application to send e-mail. The application connects to an SMTP server at port 25 and sends a simple text message.

DECLARE
c UTL_SMTP.CONNECTION;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
BEGIN
UTL_SMTP.WRITE_DATA(c, name || ': ' || header || UTL_TCP.CRLF);
END;
BEGIN
c := UTL_SMTP.OPEN_CONNECTION('smtp-server.acme.com');
UTL_SMTP.HELO(c, 'foo.com');
UTL_SMTP.MAIL(c, 'sender@foo.com');
UTL_SMTP.RCPT(c, 'recipient@foo.com');
UTL_SMTP.OPEN_DATA(c);
send_header('From', '"Sender" <sender@foo.com>');
send_header('To', '"Recipient" <recipient@foo.com>');
send_header('Subject', 'Hello');
UTL_SMTP.WRITE_DATA(c, UTL_TCP.CRLF || 'Hello, world!');
UTL_SMTP.CLOSE_DATA(c);
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(c);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL;
-- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
------------------------------------------

需要说明一下的是：必须要按照这个例子里的顺序依次执行包中的各个方法。另外注意用UTL_TCP.CRLF来作为换行符。理由如下：

------------------------------------------
The calls to OPEN_DATA, WRITE_DATA, WRITE_RAW_DATA and CLOSE_DATA must be made in the right order. A program calls OPEN_DATA to send the DATA command to
the SMTP server. After that, it can call WRITE_DATA or WRITE_RAW_DATA repeatedly to send the actual data. The data is terminated by calling CLOSE_DATA. After OPEN_DATA is called, the only subprograms that can be called are WRITE_DATA, WRITE_RAW_DATA, or CLOSE_DATA. A call to other APIs will result in an INVALID_OPERATION exception being raised.

The application must ensure that the contents of the body parameter conform to the MIME(RFC822) specification. The DATA routine will terminate the message with a <CR><LF>.<CR><LF> sequence (a single period at the beginning of a line), as required by RFC821. It will also translate any sequence of &