7 Replies Latest reply on Jul 11, 2007 2:18 PM by Edwin van Meerendonk

    Bug? htmldb_util.reset_pw gives ORA-29278: SMTP transient error 451

    Edwin van Meerendonk
      After changing mailprovider we had some troubles mailing from the database.
      After mailing to his SMTP-server we got the error message:
      ORA-29278: SMTP transient error: 451

      We were able to correct our custom code by replacing all the end-of-line LF with calls to
      UTL_TCP.CRLF.

      However, our Apex-application is still broken. By calling HTMLDB_UTIL.RESET_PW
      the system should mail the new pwd to our user.
      Should
      But it doesn't

      In the Mail Log/queue we again get the ORA-29278,
      so my deduction is that in the message that RESET_PW assembles a single LF is
      used and not the CRLF.

      Question: is there a way to overcome this bug?

      BTW, the SMTP-server is present. Writing a HTMLDB_MAIL.SEND by hand gives no trouble at all.
        • 1. small change
          Edwin van Meerendonk
          small correction: our custom code uses UTL_SMTP for mailing, not HTMLDB_MAIL
          • 2. Re: small change
            60437
            I don't fully understand what causes the problem. If it helps, here is the call that is used in reset_pw:
                wwv_flow_mail.send (p_to        => l_email_address,
                                    p_from      => l_email_address,
                                    p_subj      => wwv_flow_lang.system_message('PASSWORD_RESET_NOTIFICATION'),
                                    p_body      => p_msg ||
                                                   wwv_flow_lang.system_message('ACCESS_CONTROL_USERNAME')||': '||upper(p_user)||chr(10)||chr(10)||
                                                   wwv_flow_lang.system_message('PASSWORD')||': '||l_pw||chr(10)
                                    );
            Scott
            • 3. Re: Bug? htmldb_util.reset_pw gives ORA-29278: SMTP transient error 451
              Edwin van Meerendonk
              That's what i was afraid of,

              the chr(10) should be replaced whit a call to UTL_TCP.CRLF.

              The linefeeds are not enough for some smtp-program's.
              Now you are missing the carriage return chr(13).
              • 4. Re: Bug? htmldb_util.reset_pw gives ORA-29278: SMTP transient error 451
                joelkallman-Oracle
                CRLF characters are required to separate each SMTP command, per RFC 821 (http://www.ietf.org/rfc/rfc0821.txt), and that is what the APEX_MAIL package does.

                But the portion that "you are afraid of" which contains CHR(10) is the message[b] body of the reset password functionality of APEX - there can be any combination of characters in there. It is not a requirement that the body of the message uses CRLF for line endings. So, unfortunately, I don't think the deduction applies here.

                Let's start with some basic things. If you change the "SMTP Host Address" in the internal Administration Services of APEX to a known good SMTP host, does the problem go away?

                451 means "Requested action aborted: error in processing". That could be anything. It could even be an issue with your provider, your connection to the provider, etc. Does your provider have a log of the incoming "conversation" and a log of the abort? That may be helpful.

                Joel

                P.S. By the way, which version of Application Express/HTML DB are you using?
                • 5. Re: Bug? htmldb_util.reset_pw gives ORA-29278: SMTP transient error 451
                  Edwin van Meerendonk
                  We're working on 3.0.0.00.20

                  In the mail-log we were refered to http://pobox.com/~djb/docs/smtplf.html

                  Hence my -perhaps what quick- "deduction".

                  I'm not that fluent in my SMTP so any help on this part would be highly appreciated.
                  The site refers to
                  822bis section 2.3, <quote> which specifically prohibits all bare LF's </quote>
                  • 6. Re: Bug? htmldb_util.reset_pw gives ORA-29278: SMTP transient error 451
                    joelkallman-Oracle
                    "emoracle",

                    822bis is an old-school name for RFC 2822. They are correct in that section 2.3 of this document prohibits bare linefeed characters.

                    But RFC 822 is the recognized standard for email transfer. And it does not prohibit the use of bare linefeed characters in the bodies of e-mail messages. Additionally, RFC 821 (the standard for SMTP) states "The receiver should not close the transmission channel until it receives and replies to a QUIT command (even if there was an error)." So it sounds like your provider's SMTP server is prematurely closing the transmission channel, even though it is encountering what it perceives as an error.

                    With all of this said, what are you to do? I'll bet your provider is using qmail. Either they can modify qmail (unlikely), or you can route the APEX-generated e-mails to a different SMTP relay.

                    Unfortunately, I cannot conjure any other solution. Maybe others can help.

                    Joel
                    • 7. Re: Bug? htmldb_util.reset_pw gives ORA-29278: SMTP transient error 451
                      Edwin van Meerendonk
                      Thanks, You've been of great help.

                      Now i will start the fight with our provider.