Forum Stats

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

Discussions

Categories

Where does UTL_MAIL queue emails?

User_XOTKG
User_XOTKG Member Posts: 2 Blue Ribbon
in SQL & PL/SQL

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:
· Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond
    https://community.oracle.com/tech/developers/discussion/comment/16837520#Comment_16837520

    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.

    · Share on TwitterShare on Facebook