8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
APEX_MAIL : Send Emails from PL/SQL
The APEX_MAIL
package provides an API for sending emails from PL/SQL.
- Introduction
- APEX Mail Server Setup
- Network ACL
- Set Security Group (Optional)
- Plain Text Emails
- HTML Emails
- Attachments
- The APEX Mail Queue
Related articles.
- APEX_MAIL : Send Emails from PL/SQL
- Email From Oracle PL/SQL (UTL_SMTP)
- UTL_MAIL : Email from PL/SQL in the Oracle Database
- Fine-Grained Access to Network Services Enhancements in Oracle Database 12c Release 1
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
Introduction
There are several ways to send email from PL/SQL.
UTL_TCP
: Write all the raw interactions for yourself using the basic TCP protocol.UTL_SMTP
: Write your own mail procedures using the SMTP protocol. This approach has proved very popular as it is much simpler than coding TCP operations directly, there are plenty of examples on the internet, and it allows you to perform any operation available to email. You can see examples of that here.UTL_MAIL
: An Oracle built-in package that provides a basic email interface. It has limited support for attachments, so people often prefer to use theUTL_SMTP
approach. You can see an example of using theUTL_MAIL
package here.APEX_MAIL
: An API for the APEX mail system. All emails are queued in tables, to be sent at a later date by a database job. Under the hood APEX sends the messages using theUTL_SMTP
package. APEX must be installed to use theAPEX_MAIL
package, but you can use it directly from PL/SQL, even if you are not using APEX itself.
So why use the APEX_MAIL package?
- It is much more flexible than
UTL_MAIL
package. - It is a lot simpler than writing your own code using the
UTL_SMTP
package. Let Oracle maintain the code. - It is transactional. Emails are only sent once the database session commits the changes to the queue tables. This means email requests can be rolled back if they are part of a larger transaction that fails.
- It is asynchronous. The emails are queued and sent later. This means code is not delayed waiting for the email to be sent. Also, if the destination mail server is temporarily not working, the emails remain in the queue until they are sent.
- It allows you to work with mail templates, but those won't be covered in this article.
Although it was designed to be used in PL/SQL code called by an APEX application, the APEX_MAIL
package works fine for standalone PL/SQL code.
APEX Mail Server Setup
The APEX mail system sends email to a mail server or mail relay. The details of the service need to be set at in the APEX instance. You can find that location here.
- Log into APEX using the INTERNAL workspace.
- Navigate to "Manage Instance > Instance Settings > Email (tab)".
- Set your mail server details, then click the "Apply Changes" button.
If you are using a local mail relay on the database server, the default settings of "localhost" and port 25 should work fine.
Remember, if you are accessing an external mail server using TLS, you will need to include the root certificate in the wallet referenced by the APEX instance.
- Log into APEX using the INTERNAL workspace.
- Navigate to "Manage Instance > Instance Settings > Wallet (tab)".
- Enter the path to the wallet, prefixed with "file:". The wallet password is not needed if the wallet is set to auto-login.
- Click the "Apply Changes" button.
Network ACL
We need to make sure the the database can make a callout to the mail server. This requires a network ACL for the specific host and port. In the following example we are using "localhost:25", a local relay on the database server. The principal of the ACL must the the "APEX_XXXXXX" user.
declare l_username varchar2(30) := 'APEX_210100'; begin dbms_network_acl_admin.append_host_ace( host => 'localhost', lower_port => 25, ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => l_username, principal_type => xs_acl.ptype_db)); commit; end; /
Set Security Group (Optional)
This is not necessary if the PL/SQL is called from APEX.
If we want to use the APEX_MAIL
package from PL/SQL without APEX itself, we need to manually set the security group to a valid workspace for our session.
begin apex_util.set_workspace(p_workspace => 'DEV_WS'); end; /
Before APEX 5.1 we had to take a longer route to achieve the same goal.
declare l_workspace_id apex_workspaces.workspace_id%type; begin select workspace_id into l_workspace_id from apex_workspaces where workspace = 'DEV_WS'; apex_util.set_security_group_id(p_security_group_id => l_workspace_id); end; /
Thanks to Patrick Wolf for pointing out the simplified method.
Plain Text Emails
The SEND
procedures and functions provide overloads to send various types of emails. The functions perform the same actions as the procedures, but return the MAIL_ID
from the APEX_MAIL_QUEUE
table.
For plain text emails we could do something as simple as this.
begin apex_mail.send( p_to => 'person@exmaple.com', p_from => 'me@example.com', p_body => 'Plain text message body.' || utl_tcp.crlf, p_subj => 'Plain text message subject.'); commit; end; /
We can also reference the P_CC
, P_BCC
and P_REPLYTO
parameters.
HTML Emails
The P_BODY_HTML
parameter allows us to send emails in HTML format.
declare l_body clob; l_body_html clob; begin l_body := 'Please use a HTML mail client.'; l_body_html := '<html><head></head><body>' || '<p>HTML message body.</p>' || '</body></html>'; apex_mail.send( p_to => 'person@exmaple.com', p_from => 'me@example.com', p_body => l_body, p_body_html => l_body_html, p_subj => 'HTML message subject.'); commit; end; /
Attachments
The ADD_ATTACHMENT
procedures allow us to add CLOB
and BLOB
attachments. We need to use the SEND
function to get the MAIL_ID
of the message, and include the SEND
and ADD_ATTACHMENT
calls in the same transaction.
The following example sends an email with a CLOB
and BLOB
attachment. The BLOB attachment is junk, but it could be any BLOB data including media files.
declare l_mail_id NUMBER; l_blob blob; l_clob clob; begin l_blob := UTL_RAW.cast_to_raw('This could be a document or image.'); l_clob := 'This could be any CLOB information'; l_mail_id := apex_mail.send( p_to => 'person@exmaple.com', p_from => 'me@example.com', p_body => 'Attachment message body.', p_subj => 'Attachment message subject.'); apex_mail.add_attachment( p_mail_id => l_mail_id, p_attachment => l_blob, p_filename => 'blob_attachment.binary', p_mime_type => 'application/octet-stream'); apex_mail.add_attachment( p_mail_id => l_mail_id, p_attachment => l_clob, p_filename => 'clob_attachment.txt', p_mime_type => 'text/plain'); commit; end; /
Remember to set the mime type to the correct setting for your attachment. You can see the available mime types here.
The APEX Mail Queue
All APEX_MAIL
emails are inserted into a mail queue table called APEX_MAIL_QUEUE
, with attachments in the APEX_MAIL_ATTACHMENTS
table. They are subsequently pushed out using a database job called ORACLE_APEX_MAIL_QUEUE
. This job calls the WWV_FLOW_MAIL.PUSH_QUEUE_IMMEDIATE
procedure every 5 minutes.
We can check if there are emails in the queue using the following query.
select count(*) from apex_mail_queue; COUNT(*) ---------- 0 SQL>
During testing you may with to force the push of the mail queue. This can be done with the following procedure call.
begin apex_mail.push_queue; end; /
The PUSH_QUEUE
procedure issues an implicit commit.
When diagnosing email issues you might want to check the APEX_MAIL_LOG
table.
For more information see:
- APEX_MAIL
- APEX_MAIL : Send Emails from PL/SQL
- Email From Oracle PL/SQL (UTL_SMTP)
- UTL_MAIL : Email from PL/SQL in the Oracle Database
- Fine-Grained Access to Network Services Enhancements in Oracle Database 12c Release 1
- Fine-Grained Access to Network Services in Oracle Database 11g Release 1
Hope this helps. Regards Tim...