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

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 2

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.