2 Replies Latest reply on Jul 9, 2009 9:59 PM by 437624

    HTML eMail - ORA-29278: SMTP transient error: 421 Service not available

    437624
      Hi,
      I have used the code given in the following Ask Tom link http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1739411218448 to send HTML emails.

      When i execute the procedure, it hangs at the line utl_smtp.close_data(l_connection); and does not respond for almost 10 minutes and returns with the "ORA-29278: SMTP transient error: 421 Service not available" error.

      Finding 1:
      But, when i tried the executing the below normal emailing code with the same input of SMTP Server & Port Number , the email was sent without any error.

      mail_conn := UTL_SMTP.open_connection (P_SMTP_HOSTNAME, 25);
      UTL_SMTP.helo (mail_conn, P_SMTP_HOSTNAME);
      -- this performs the initial handshake with the server
      UTL_SMTP.mail (mail_conn, P_FROM);
      UTL_SMTP.rcpt (mail_conn, P_TO);
      UTL_SMTP.DATA (mail_conn, P_HTML);
      UTL_SMTP.quit (mail_conn);

      Finding 2:
      When i tried running the Ask Tom's HTML email code on a different Oracle database (ver 10g) in the same network, the HTML email was sent without any errors.

      Is this error because of any issues specific to the Oracle Database version ?

      The following is my Oracle version where the AskTom HTML email code failed

      Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
      PL/SQL Release 9.2.0.8.0 - Production
      CORE     9.2.0.8.0     Production
      TNS for Solaris: Version 9.2.0.8.0 - Production
      NLSRTL Version 9.2.0.8.0 - Production

      Thanks,
      Senthil
        • 1. Re: HTML eMail - ORA-29278: SMTP transient error: 421 Service not available
          sb92075
          http://www.qksoft.com/qk-smtp-server/smtp-error-421.html

          Is the MTA on P_SMTP_HOSTNAME configured to relay message from the DB server?
          Does any email message ever get successfully delivered?
          • 2. Re: HTML eMail - ORA-29278: SMTP transient error: 421 Service not available
            437624
            Yes, when i tried the executing the below normal emailing code with the same input of SMTP Server & Port Number in my 9i Oracle server , the email was sent without any error.

            mail_conn := UTL_SMTP.open_connection (P_SMTP_HOSTNAME, 25);
            UTL_SMTP.helo (mail_conn, P_SMTP_HOSTNAME);
            -- this performs the initial handshake with the server
            UTL_SMTP.mail (mail_conn, P_FROM);
            UTL_SMTP.rcpt (mail_conn, P_TO);
            UTL_SMTP.DATA (mail_conn, P_HTML);
            UTL_SMTP.quit (mail_conn);

            The problem is coming only when i use that html_email utility, i also made few more verifications and noticed that the smtp parameter is not there in my 9i database, but my 10g database had a value setup.

            Values from my 9i Database_
            SQL> select name,open_mode from v$database;

            NAME OPEN_MODE
            --------- ----------
            EITD1 READ WRITE

            SQL> show parameter smtp
            SQL>
            SQL> select NAME,VALUE from v$parameter where NAME like 'smtp%';

            no rows selected

            Values from my 10gi Database_
            SQL> select name,open_mode from v$database;

            NAME OPEN_MODE
            --------- ----------
            XXXX READ WRITE

            SQL> show parameter smtp

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            smtp_out_server string