8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

APEX_MAIL : Send Emails from PL/SQL

The APEX_MAIL package provides an API for sending emails from PL/SQL.

Related articles.

Introduction

There are several ways to send email from PL/SQL.

So why use the APEX_MAIL package?

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.

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.

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.