使用 Oracle 的 utl_smtp 进行调试

Debugging with Oracle's utl_smtp

提问by Dan Maharry

A client of mine uses Oracle 9i's utl_smtp to send mails out notifications to managers when their employees have made travel requests and they woul like quite a few changes made to the mailouts done.

我的一个客户使用 Oracle 9i 的 utl_smtp 在他们的员工提出旅行请求时向管理人员发送邮件通知，他们希望对已完成的邮件进行一些更改。

We're having a lot of problems getting utl_smtp to talk to any smtp server on our network. We've even tried installing free smtp server on the oracle box but it will not spot the mail server running on port 25. The error code is ORA-29278.

我们在让 utl_smtp 与我们网络上的任何 smtp 服务器通信时遇到了很多问题。我们甚至尝试在 oracle 机器上安装免费的 smtp 服务器，但它不会发现运行在端口 25 上的邮件服务器。错误代码是 ORA-29278。

So two questions really.

所以真的有两个问题。

1. Does anyone have any experience setting up email using Oracle's utl_smtp utility and have any suggestions as to where we might be going wrong.

2. Does anyone know if it is possible to get utl_smtp to dump text emails to a directory much as you can do if you're using system.net.mail's specifiedpickupdirectory config setting. This would be by far the preferable option.

1. 有没有人有使用 Oracle 的 utl_smtp 实用程序设置电子邮件的经验，并对我们可能出错的地方有任何建议。

2. 有谁知道是否有可能让 utl_smtp 将文本电子邮件转储到一个目录，就像您使用 system.net.mail 的指定pickupdirectory 配置设置一样。这将是迄今为止最好的选择。

Thanks, Dan

谢谢，丹

采纳答案by cagcowboy

Looks like the HELO is the problem. Please can we check with a simple testcase...

看起来 HELO 是问题所在。请我们用一个简单的测试用例来检查...

set serveroutput on

declare
      lConnection UTL_SMTP.CONNECTION;
begin
      lConnection := UTL_SMTP.OPEN_CONNECTION(your_smtp_server);
      DBMS_OUTPUT.PUT_LINE('Opened ok');

      UTL_SMTP.HELO(lConnection, your_client_machine_name);
      DBMS_OUTPUT.PUT_LINE('HELO ok');

      UTL_SMTP.MAIL(lConnection, your_email_address);
      UTL_SMTP.RCPT(lConnection, your_email_address);
      DBMS_OUTPUT.PUT_LINE('Addressing ok');
end;
/

回答by Dan Maharry

Looks like we've resolved this. To answer the two questions.

看起来我们已经解决了这个问题。来回答这两个问题。

1. Double check that the schema calling utl_smtphas execute permissions on sys.utl_smtp, sys.utl_tcpand sys.dbms_lob. Also check that at no time the message being sent is > 32Kb.

2. No there is no way to get utl_smtp to dump emails to a directory a la system.net.mail.

1. 仔细检查架构调用utl_smtp是否对sys.utl_smtp、sys.utl_tcp和具有执行权限sys.dbms_lob。还要检查发送的消息是否大于 32Kb。

2. 不，没有办法让 utl_smtp 将电子邮件转储到 system.net.mail 目录中。

Thanks to cagcowboy for the help.

感谢 cagcowboy 的帮助。

回答by Dan Maharry

Yes, we can telnet to the server.

是的，我们可以 telnet 到服务器。

-- ****** Object: Stored Procedure TRAVELADMIN_DEV.HTML_EMAIL Script Date: 22/08/2008 12:41:02 ******
CREATE PROCEDURE "HTML_EMAIL"  (
    p_to            in varchar2,
    p_cc            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2 default null,
    p_html          in varchar2 default null
    )
is
    l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection    utl_smtp.connection;
    l_body_html     clob := empty_clob;  --This LOB will be the email message
    l_offset        number;
    l_ammount       number;
    l_temp          varchar2(32767) default null;
    p_smtp_hostname varchar2(30):= 'rockies';
    p_smtp_portnum  varchar2(2) := '25';
begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );
    l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    IF ((p_cc <> NULL) OR (LENGTH(p_cc) > 0)) THEN
      l_temp := l_temp || 'Cc: ' || p_cc || chr(13) || chr(10);
      utl_smtp.rcpt( l_connection, p_cc );
    END IF;
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
                         chr(34) || l_boundary ||  chr(34) || chr(13) ||
                         chr(10);
    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);
    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13)||chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);
    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
                    chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' ||
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   := chr(13) || chr(10) || '--' ||  l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
        utl_smtp.write_data(l_connection,
                            dbms_lob.substr(l_body_html,l_ammount,l_offset));
        l_offset  := l_offset + l_ammount ;
        l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
end;

回答by cagcowboy

• The OPEN_CONNECTION parameter should be the FQDN or IP address of the server you're connecting to.
• The HELO parameter should be the FQDN of the machine you're connecting from.

• OPEN_CONNECTION 参数应该是您要连接的服务器的 FQDN 或 IP 地址。
• HELO 参数应该是您正在连接的机器的 FQDN。

If this doesn't work, do you know which line it errors on?

如果这不起作用，您知道它在哪一行出错吗？