Forum Stats

  • 3,726,956 Users
  • 2,245,297 Discussions
  • 7,852,506 Comments

Discussions

APEX_MAIL and opening and closing SMTP connections

CaribbeanUser
CaribbeanUser Member Posts: 80 Blue Ribbon

Hi,

When using UTL_SMTP to send an email, one has to open a connection to the SMTP server and after having sent the email, issue a QUIT. APEX_MAIL.send, on the other hand, queues the email in an APEX queue. A separate job in APEX dequeues the messages and actually sends the queued emails. My question is the following, suppose I have number of emails in the queue, does the aforementioned job open a connection to the SMTP server once, sends all the queued emails, and then quits, or will the job do an open/send/quit for every email in the queue?

Any help would be greatly appreciated,

Regards,

Abraham

Tagged:

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,529 Black Diamond
    edited April 9 Accepted Answer

    Please update your forum profile with a recognisable username instead of "user617110".

    Thank you for your reply. As I indicated in my original post, we were using UTL_SMTP to send individual messages by doing an open connection, send, quit cycle. It so happens that with an increasing number of messages to be sent, there might be more concurrently open smtp connections than the server can handle.

    We'd hoped that APEX mail would alleviate this issue.

    apex_mail is simply an enhanced wrapper for utl_smtp with methods providing higher-level abstractions for HTML mail, attachments etc. At the protocol level—including connections—it's all utl_smtp under the covers.

    However, as Andy said:

    Isn't this what APEX mail does? Stores emails in a queue and periodically delivers those emails one-by-one and where a send fails returning the email to the queue?

    As far as I can see, APEX will only be making one concurrent server connection at a time so it seems unlikely that is the cause of your transient SMPT error (unless you have multiple APEX instances on the same server?).

    When used properly with just the ORACLE_APEX_MAIL_QUEUE scheduled job interacting with the mail server, an APEX instance should only be opening a single SMTP connection at a time—irrespective of whether it is doing an open/send/quit for each message, or a bulk open/send/send/.../quit with multiple messages. An all too common problem is that developers do not rely on the ORACLE_APEX_MAIL_QUEUE job to manage things, and invariably invoke apex_mail.push_queue after every apex_mail.send call. This can result in multiple sessions trying to simultaneously open connections to the SMTP server.

Answers

  • jariola
    jariola Member Posts: 10,191 Bronze Crown

    Why you ask? What issue you facing how APEX sends emails?

  • AndyH
    AndyH Member Posts: 619 Gold Badge

    Each email is sent as a separate utl_smtp.mail to utl_smtp.quit process.

    jariola
  • CaribbeanUser
    CaribbeanUser Member Posts: 80 Blue Ribbon

    Hi,

    I don't have any issues with APEX emails, it's such that I'm looking for a solution to the following: we have an Oracle APEX applications where as a result of certain events emails are to be sent. We are experiencing the following error:

    ORA-29278: SMTP transient error: 421 4.3.2 The maximum number of concurrent server connections has exceeded a per-source limit, closing transmission channel

    as a result of trying to open several connections concurrently.

    The idea for a possible solution is to queue the messages and then at regular intervals opening a connection to the smtp server, send the queued email messages, and then quitting the connection. We thought apex_mail could help us, but apparently this is not the case.

    Thank you for your replies.

    Regards,

  • AndyH
    AndyH Member Posts: 619 Gold Badge

    The idea for a possible solution is to queue the messages and then at regular intervals opening a connection to the smtp server, send the queued email messages, and then quitting the connection. We thought apex_mail could help us, but apparently this is not the case.

    Isn't this what APEX mail does? Stores emails in a queue and periodically delivers those emails one-by-one and where a send fails returning the email to the queue?

    As far as I can see, APEX will only be making one concurrent server connection at a time so it seems unlikely that is the cause of your transient SMPT error (unless you have multiple APEX instances on the same server?).

  • CaribbeanUser
    CaribbeanUser Member Posts: 80 Blue Ribbon
    edited April 8

    Hi AndyH,

    Thank you for your reply. As I indicated in my original post, we were using UTL_SMTP to send individual messages by doing an open connection, send, quit cycle. It so happens that with an increasing number of messages to be sent, there might be more concurrently open smtp connections than the server can handle.

    We'd hoped that APEX mail would alleviate this issue.

    Regards,

  • AndyH
    AndyH Member Posts: 619 Gold Badge

    Perhaps we have a different definition of 'concurrent'? You seem be describing a sequence of actions that would result in one call to the SMTP server at a time. There may be many calls in a short period of time, but only one at any time?

  • fac586
    fac586 Senior Technical Architect Member Posts: 19,529 Black Diamond
    edited April 9 Accepted Answer

    Please update your forum profile with a recognisable username instead of "user617110".

    Thank you for your reply. As I indicated in my original post, we were using UTL_SMTP to send individual messages by doing an open connection, send, quit cycle. It so happens that with an increasing number of messages to be sent, there might be more concurrently open smtp connections than the server can handle.

    We'd hoped that APEX mail would alleviate this issue.

    apex_mail is simply an enhanced wrapper for utl_smtp with methods providing higher-level abstractions for HTML mail, attachments etc. At the protocol level—including connections—it's all utl_smtp under the covers.

    However, as Andy said:

    Isn't this what APEX mail does? Stores emails in a queue and periodically delivers those emails one-by-one and where a send fails returning the email to the queue?

    As far as I can see, APEX will only be making one concurrent server connection at a time so it seems unlikely that is the cause of your transient SMPT error (unless you have multiple APEX instances on the same server?).

    When used properly with just the ORACLE_APEX_MAIL_QUEUE scheduled job interacting with the mail server, an APEX instance should only be opening a single SMTP connection at a time—irrespective of whether it is doing an open/send/quit for each message, or a bulk open/send/send/.../quit with multiple messages. An all too common problem is that developers do not rely on the ORACLE_APEX_MAIL_QUEUE job to manage things, and invariably invoke apex_mail.push_queue after every apex_mail.send call. This can result in multiple sessions trying to simultaneously open connections to the SMTP server.

  • CaribbeanUser
    CaribbeanUser Member Posts: 80 Blue Ribbon

    Thank you all for providing valuable comments.

    Regards

Sign In or Register to comment.