UTL_SMTP Sending HTML e-mails with multiple BLOB attachments via custom SMTP Server

The last few days I’ve been struggling with Oracle APEX trying to send e-mails via APEX_MAIL from different SMTP servers. But since SMTP configuration is at Instance level, it’s not that easy neither secure to keep changing instance settings and sending e-mails. (you can see the whole information at the oracle forum: https://forums.oracle.com/forums/message.jspa?messageID=10376262#10376262).

Resuming I needed the following settings:

  • Each Mail can be sent from different SMTP servers (Even in the same queue of e-mails each one could be sent from different server)
  • Body can be Plain text or HTML
  • E-mails can have multiple BLOB attachments

So finally I’ve googled around and mixed different solutions using UTL_SMTP to achieve my specifications. It could seem like reinventing the wheel, but I think it’s necessarily since APEX_MAIL (at APEX 4.1.1) uses UTL_MAIL, and UTL_MAIL configures SMTP connection at instance level as well. Therefore, it’s not likely in future versions of APEX to provide feature of sending e-mails from different SMTP servers.

In order to achieve sending as many BLOB attachments as needed, you need to previously create the following types at your schema:

I don’t like that much creating custom types since they are uncomfortable to use, but it is the only way to implement it without creating DB tables. At the end, I’ll show how to properly construct required types.

The e-email sending procedure is the following:

I provide also an example of how to call the function passing blob attachments:

Filling custom type manually:

Filling via SELECT INTO:

Code based on:

http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html

Alternatives (CES.email_files):

http://www.uaex.edu/bknox/Using_CES.email_files.htm

Tags: , , , , , , , ,

No comments yet.

Leave a Reply

UTL_SMTP Sending HTML e-mails with multiple BLOB attachments via custom SMTP Server

The last few days I’ve been struggling with Oracle APEX trying to send e-mails via APEX_MAIL from different SMTP servers. But since SMTP configuration is at Instance level, it’s not that easy neither secure to keep changing instance settings and sending e-mails. (you can see the whole information at the oracle forum: https://forums.oracle.com/forums/message.jspa?messageID=10376262#10376262).

Resuming I needed the following settings:

  • Each Mail can be sent from different SMTP servers (Even in the same queue of e-mails each one could be sent from different server)
  • Body can be Plain text or HTML
  • E-mails can have multiple BLOB attachments

So finally I’ve googled around and mixed different solutions using UTL_SMTP to achieve my specifications. It could seem like reinventing the wheel, but I think it’s necessarily since APEX_MAIL (at APEX 4.1.1) uses UTL_MAIL, and UTL_MAIL configures SMTP connection at instance level as well. Therefore, it’s not likely in future versions of APEX to provide feature of sending e-mails from different SMTP servers.

In order to achieve sending as many BLOB attachments as needed, you need to previously create the following types at your schema:

I don’t like that much creating custom types since they are uncomfortable to use, but it is the only way to implement it without creating DB tables. At the end, I’ll show how to properly construct required types.

The e-email sending procedure is the following:

I provide also an example of how to call the function passing blob attachments:

Filling custom type manually:

Filling via SELECT INTO:

Code based on:

http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html

Alternatives (CES.email_files):

http://www.uaex.edu/bknox/Using_CES.email_files.htm

No comments yet.

Leave a Reply

UTL_SMTP Sending HTML e-mails with multiple BLOB attachments via custom SMTP Server

The last few days I’ve been struggling with Oracle APEX trying to send e-mails via APEX_MAIL from different SMTP servers. But since SMTP configuration is at Instance level, it’s not that easy neither secure to keep changing instance settings and sending e-mails. (you can see the whole information at the oracle forum: https://forums.oracle.com/forums/message.jspa?messageID=10376262#10376262).

Resuming I needed the following settings:

  • Each Mail can be sent from different SMTP servers (Even in the same queue of e-mails each one could be sent from different server)
  • Body can be Plain text or HTML
  • E-mails can have multiple BLOB attachments

So finally I’ve googled around and mixed different solutions using UTL_SMTP to achieve my specifications. It could seem like reinventing the wheel, but I think it’s necessarily since APEX_MAIL (at APEX 4.1.1) uses UTL_MAIL, and UTL_MAIL configures SMTP connection at instance level as well. Therefore, it’s not likely in future versions of APEX to provide feature of sending e-mails from different SMTP servers.

In order to achieve sending as many BLOB attachments as needed, you need to previously create the following types at your schema:

I don’t like that much creating custom types since they are uncomfortable to use, but it is the only way to implement it without creating DB tables. At the end, I’ll show how to properly construct required types.

The e-email sending procedure is the following:

I provide also an example of how to call the function passing blob attachments:

Filling custom type manually:

Filling via SELECT INTO:

Code based on:

http://www.oracle-base.com/articles/misc/email-from-oracle-plsql.php#html

Alternatives (CES.email_files):

http://www.uaex.edu/bknox/Using_CES.email_files.htm

No comments yet.

Leave a Reply