Forum Stats

  • 3,853,655 Users
  • 2,264,249 Discussions
  • 7,905,426 Comments

Discussions

Where does UTL_MAIL queue emails?

User_XOTKG
User_XOTKG Member Posts: 2 Blue Ribbon

So I'm working on a Dev DB and I need to send emails out with UTL_MAIL. Check the parameters, and I need to set smtp_out_server parameter in the spfile, as it is currently NULL. I set it to my SMTP server, and WHAM: thousands of emails come pouring out to my mail server. So I rush to NULL the parameter out again. But clearly some other developers have been testing UTL_MAIL execution without actually sending anything out.

So my question is, where does Oracle queue mails that are created by UTL_MAIL but cannot be sent because smtp_out_server is incorrectly configured? Because I need to clear that location out before I re-enable the parameter.

Thanks!

Tagged:

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓

    There's no queueing involved internally on Oracle side.

    UTL_MAIL is just (poor) wrapper code for UTL_SMTP which uses TCP protocol to communicate with the SMTP server.

    However, you might have some background processes (jobs) running that started to work when you set the parameter. For example, it could be a user-defined queue staging messages before being picked up by your mailing facility, a just a regular table that gets polled by a scheduler job etc.

    We don't know your database, so it's up to you to check that out.

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    Answer ✓

    There's no queueing involved internally on Oracle side.

    UTL_MAIL is just (poor) wrapper code for UTL_SMTP which uses TCP protocol to communicate with the SMTP server.

    However, you might have some background processes (jobs) running that started to work when you set the parameter. For example, it could be a user-defined queue staging messages before being picked up by your mailing facility, a just a regular table that gets polled by a scheduler job etc.

    We don't know your database, so it's up to you to check that out.

  • User_XOTKG
    User_XOTKG Member Posts: 2 Blue Ribbon

    You are correct. Dug into it and found that the application connected to this DB has a mail queue table. Thanks for the help.

  • Hpaiss
    Hpaiss Member Posts: 45 Bronze Badge

    As long user of utl_mail/utl_smtp, I would like to suggest that you'll try apex_mail

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond

    Apex_Mail is just another wrapper for sending emails. Better than UTL_MAIL, but still not at a low enough level to cover all requirements. UTL_SMTP provides the low level mail server communication level functionality on top of TCP that allows for all communication requirements with an SMTP server to be used. I would class UTL_SMTP to be the best functionality to use, and then you can write your own wrapper package to meet your company's requirements with that, if APEX_MAIL doesn't already provide the required functionality. Certainly UTL_MAIL is, to quote Odie... a "poor" wrapper as it has very limited functionality.