Forum OpenACS Q&A: Send mail from PL/SQL

Collapse
1: Send mail from PL/SQL
Posted by Angel Francisco Marcos Alonso on 03/13/02 07:36 PM
How can I send email from a PL/SQL subprogram?
Collapse
2: Response to Send mail from PL/SQL (response to 1)
Posted by John Sequeira on 03/13/02 11:17 PM
I expect dropping into Java or running something like sendmail via a shell command are the Oracle-esque ways to go.

Something more cross-platform and conceptually straightforward would be to write the email to be sent to a queue table which included the from/to/body info,  and have a scheduled AOLServer job process the queue every few minutes.  There's not a lot of code in the queue processor function,  because OACS has a pretty decent library for sending email.

Collapse
3: Response to Send mail from PL/SQL (response to 1)
Posted by Michael Yoon on 03/13/02 11:23 PM
Alternatively, you can try the UTL_SMTP PL/SQL package that's built in to Oracle, which allows you send email via SMTP from PL/SQL.
Collapse
4: Response to Send mail from PL/SQL (response to 1)
Posted by Roberto Mello on 03/14/02 12:18 AM
PostgreSQL has pgMail, which lets you do that via PL/TclU: Link from http://www.brasileiro.net/postgres/cookbook/
Collapse
5: Response to Send mail from PL/SQL (response to 1)
Posted by Daryl Biberdorf on 03/14/02 04:25 AM

Tom Kyte's site has a nice entry on sending mail from PL/SQL using the UTL_SMTP package.

I've got more experience than I'd like to have sending email from PL/SQL. With that in mind, there are some pros and cons of UTL_SMTP:
Pros

  • using it requires nothing but PL/SQL
  • it's simple and straightforward
Cons
  • you can't send attachments with it, unless you want to write all the stuff to build a multi-part message and base64 encoding and all that yourself
  • it's a bit limiting at times

I originally wrote a thing that sent automated email using UTL_SMTP. Later on, I wrote another thing that needed to send automated email (on a different basis) with attachments. The latter forced me into using the JavaMail API. When I get the chance, I'll be altering the former code to use the latter set of routines.

Kyte's book has some helpful samples for interfacing PL/SQL to JavaMail.

Neither JavaMail nor UTL_SMTP deal all that well with transient MTA errors. I recommend having a very available SMTP server nearby for that reason.