Oracle Cloud Infrastructure (OCI) offers various database management products such as Autonomous Data Warehouse (ADW), Autonomous Transaction Processing (ATP) and Database Cloud Service (DBCS). As we know, an Oracle database can be used to send outbound emails using utilities such as UTL_SMTP and UTL_MAIL. Additionally, APEX also offers it own utility APEX_MAIL (built on top of UTL_SMTP) which can be used to easily send emails from an APEX application. When dealing with OCI products though, it is important to note that enabling outbound email functionality involves separate set of steps depending on the product that you are using. I recently had to enable email functionality using APEX_MAIL on a Autonomous Data Warehouse (ADW) and a Database Cloud Service (DBCS) instance and the steps involved for these two OCI products are listed here. Please note that in both cases, one needs to have a subscription for Oracle Cloud Infrastructure Email Delivery service since that is the only email provider that is currently supported by ADW, ATP and DBCS. Please refer to the documentation for setting up OCI Email Delivery. At the very least, you need to create the SMTP credentials and setup the Approved Senders. Also, since the APEX_MAIL utility comes with APEX hence you need to have Apex installed as a prerequisite.
On Autonomous Data Warehouse (ADW) or Autonomous Transaction Processing (ATP)
The steps for configuring APEX_MAIL for ADW on Shared Exadata Infrastructure are relatively straightforward and are listed in this documentation. The steps for ATP instances on Shared Exadata Infrastructure are also the same. Once APEX_MAIL is configured, you can send email from an APEX application or from the APEX SQL workshop.
On Database Cloud Service (DBCS)
The corresponding steps for DBCS are slightly more complicated but the Note 2428364.1 – Database Cloud Service (DBCS / DBaaS) on OCI: How to Set Up APEX_MAIL provides all required details and some additional troubleshooting steps in case you run into issues. Maybe it is me but the instructions get a little fuzzy at the point where you need to modify the APEX Mail Server settings. Below is a screenshot from the note showing the steps to modify the APEX settings.
Here are some details on the values that need to be set for the Apex Mail Sever.
- SMTP Host address: <Get this from your OCI Email Delivery Service instance>
- SMTP Host Port: <Get this from your OCI Email Delivery Service instance>
- SMTP Authentication Username: <OCID of the SMTP user that you are using>
- SMTP Authentication Password: <Password of the SMTP user that you are using>
- Use SSL/TLS: After connection is established
- Default Email From Address: <You would have set this up in your OCI Email Delivery Service instance>
In DBCS, once you setup APEX_MAIL, you can then send mails using both APEX_MAIL as well as UTL_SMTP. This means that you can send outbound email not just from an APEX application but also from a custom PLSQL procedure. The latter option is quite useful since you can write a custom PLSQL procedure to send email notifications and you can call the procedure from Java Cloud Service (which does not have an in-built email functionality), Oracle Integration Cloud or any other Middleware.
Notes:
Once you have APEX_MAIL is configured, here are a couple of ways to send mails from outside the APEX application.
The first is to logon to the database from the schema associated with the APEX workspace and running the script provided below:
declare
l_queue_id VARCHAR2 (50) := '-1';
l_workspace VARCHAR2 (50) := 'MYWORKSPACE';
l_security_group_id NUMBER;
BEGIN
--initialize the session with the security attributes of the APEX workspace
--bascially creating an APEX session in SQL
l_workspace := SYS_CONTEXT ('userenv', 'current_schema' );
l_security_group_id := APEX_UTIL.FIND_SECURITY_GROUP_ID(P_WORKSPACE => l_workspace);
APEX_UTIL.SET_SECURITY_GROUP_ID(P_SECURITY_GROUP_ID => l_security_group_id);
l_queue_id:= APEX_MAIL.SEND(p_from => 'example_from@testing.com',
p_to => 'example_to@testing.com',
p_subj => 'Test Subject',
p_body => 'Sent using APEX_MAIL');
APEX_MAIL.PUSH_QUEUE;
END;
/
The second is to use UTL_SMTP. You can use the PLSQL block listed in the Note 2428364.1 and pass your SMTP server/port details to send emails using UTL_SMTP. In this case also, you have to logon to the database from the schema associated with the APEX workspace. Additionally, you also need to add an entry in the Access Control List (ACL) so that the schema user can connect to the SMTP server. You can check the ACL entry in the USER_NETWORK_ACL_PRIVILEGES view.
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
host => '',
lower_port => null,
upper_port => null,
ace => xs$ace_type(privilege_list => xs$name_list('connect','resolve'),
principal_name => '',
principal_type => XS_ACL.PTYPE_DB));
END;
/
Notes on Oracle Applications 