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

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 the UTL_SMTP approach. You can see an example of using the UTL_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 the UTL_SMTP package. APEX must be installed to use the APEX_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:

Hope this helps. Regards Tim...

Back to the Top.

(追記) (追記ここまで)