Debugging with Oracle's utl_smtp

Question

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.

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.

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.

Thanks, Dan

Answer 1

Can you telnet to the SMTP server from the Oracle server on port 25?

If that works pls can you post your PL/SQL?

Answer 2

Yes, we can telnet to the server.

-- ****** 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;

Answer 3

• 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.

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

Answer 4

No joy using FQDN or IP address. The full error is as follows

An error has occurred:
ORA-29278: SMTP transient error: 421 service not available
ORA-06512: at 'SYS.UTL_SMTP', line 17
ORA-06512: at 'SYS.UTL_SMTP', line 96
ORA-06512: at 'SYS.UTL_SMTP', line 138
ORA-06512: at 'TRAVELADMIN_DEV.HTML_EMAIL', line 20
ORA-06512: at 'TRAVELADMIN_DEV.SEND_MAIL', line 421
ORA-06512: at line 1

Answer 5

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

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;
/

Answer 6

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

1. Double check that the schema calling utl_smtp has execute permissions on sys.utl_smtp, sys.utl_tcp and 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.

Thanks to cagcowboy for the help.