DBA Hub

📋Steps in this guide1/8

APEX_MAIL : Send Emails from PL/SQL

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

oracle miscconfigurationintermediate
by OracleDba
45 views
1

Introduction

There are several ways to send email from PL/SQL. - : Write all the raw interactions for yourself using the basic TCP protocol. - : 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 . - : An Oracle built-in package that provides a basic email interface. It has limited support for attachments, so people often prefer to use the approach. You can see an example of using the package here . - : 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 package. APEX must be installed to use the 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 package. - It is a lot simpler than writing your own code using the 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 package works fine for standalone PL/SQL code.
2

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.
3

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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;
/
4

Set Security Group (Optional)

This is not necessary if the PL/SQL is called from APEX. If we want to use the package from PL/SQL without APEX itself, we need to manually set the security group to a valid workspace for our session. Before APEX 5.1 we had to take a longer route to achieve the same goal. Thanks to Patrick Wolf for pointing out the simplified method.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
begin
  apex_util.set_workspace(p_workspace => 'DEV_WS');
end;
/

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;
/
5

Plain Text Emails

The procedures and functions provide overloads to send various types of emails. The functions perform the same actions as the procedures, but return the from the table. For plain text emails we could do something as simple as this. We can also reference the , and parameters.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
begin
  apex_mail.send(
    p_to   => '[email protected]',
    p_from => '[email protected]',
    p_body => 'Plain text message body.' || utl_tcp.crlf,
    p_subj => 'Plain text message subject.');
    
  commit;
end;
/
6

HTML Emails

The parameter allows us to send emails in HTML format.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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        => '[email protected]',
    p_from      => '[email protected]',
    p_body      => l_body,
    p_body_html => l_body_html,
    p_subj      => 'HTML message subject.');
    
  commit;
end;
/
7

Attachments

The procedures allow us to add and attachments. We need to use the function to get the of the message, and include the and calls in the same transaction. The following example sends an email with a and attachment. The BLOB attachment is junk, but it could be any BLOB data including media files. Remember to set the mime type to the correct setting for your attachment. You can see the available mime types here .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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        => '[email protected]',
    p_from      => '[email protected]',
    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;
/
8

The APEX Mail Queue

All emails are inserted into a mail queue table called , with attachments in the table. They are subsequently pushed out using a database job called . This job calls the procedure every 5 minutes. We can check if there are emails in the queue using the following query. During testing you may with to force the push of the mail queue. This can be done with the following procedure call. The procedure issues an implicit commit. When diagnosing email issues you might want to check the table. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
select count(*) from apex_mail_queue;

  COUNT(*)
----------
         0

SQL>

begin
  apex_mail.push_queue;
end;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!