APEX_MAIL
The APEX_MAIL package is a more robust version of what you’ll find with UTL_MAIL, but it is only available when APEX is installed. It also requires pre-configuration of the smtp server, port, authentication, wallets, and encryption. These features require APEX-administrative privileges to set. Since APEX might not be installed and the configuration is both restricted and global in nature. I again find myself often using UTL_SMTP instead. However, if you have APEX_MAIL available to you, and it’s configured for everything you need then I definitely recommend it over UTL_MAIL, especially in databases 11gR2 and above which support most authentication natively as well as encrypted authentication through TLS.
Setting up the APEX instance can be done through the APEX INTERNAL workspace or via PL/SQL. In the code block below, setting up a sample SMTP server it might look something like this…
BEGIN
apex_instance_admin.set_parameter('SMTP_HOST_ADDRESS', 'smtp.example.com');
apex_instance_admin.set_parameter('SMTP_HOST_PORT', '25');
apex_instance_admin.set_parameter('SMTP_USERNAME', 'email_user@example.com');
apex_instance_admin.set_parameter('SMTP_PASSWORD', 'email_user_password');
apex_instance_admin.set_parameter('SMTP_TLS_MODE', 'STARTTLS');
apex_instance_admin.set_parameter('WALLET_PATH', 'file:/home/sds/wallets');
-- if the wallet is auto-login no need to set password
-- if not auto-login then set the password with WALLET_PWD parameter
--apex_instance_admin.set_parameter('WALLET_PWD', 'my_wallet_password');
COMMIT;
apex_instance_admin.validate_email_config;
END;
Security Note– all of the APEX instance parameters are plain text. So it is not recommended to use personal authentication credentials to configure your APEX instance.
Once the SMTP and wallet parameters have been set, you can send email simply by calling the SEND procedure or function. The email creation is transactional. That is, it won’t be queued for sending until you COMMIT the transaction. If you want to send attachments you simply call the ADD_ATTACHMENT procedure for each file. Unlike UTL_MAIL you can attach multiple files and they can be of CLOB and BLOB types instead of the smaller VARCHAR2 and RAW types.
Another nice feature of the APEX_MAIL package is it supports sending dual-format email bodies. If the receiving client supports HTML formatted bodies they can be used or you can just send plain text. If you do want to send HTML content, the APEX_MAIL package doesn’t construct it for you. You will need to create both the HTML and plain text versions yourself and send them in the corresponding parameters.
DECLARE
v_id NUMBER;
v_text CLOB := EMPTY_CLOB();
v_image BLOB;
BEGIN
-- Read the source code of one of my functions into a variable
FOR s IN ( SELECT text
FROM user_source
WHERE name = 'SPLIT_CLOB' AND TYPE = 'FUNCTION'
ORDER BY line)
LOOP
v_text := v_text || s.text;
END LOOP;
-- Read a jpg image into a BLOB
SELECT blobimage
INTO v_image
FROM my_images
WHERE id = 1;
v_id :=
apex_mail.send(p_to => 'jane.doe@gmail.com',
p_from => 'sean.stuber@gmail.com',
p_subj => 'Test APEX email with 2 attachments',
p_body => 'Check out the attachments.',
p_body_html => '<h2>Check out the attachments</h2>');
apex_mail.add_attachment(p_mail_id => v_id,
p_attachment => v_text,
p_filename => 'data.txt',
p_mime_type => 'text/plain');
apex_mail.add_attachment(p_mail_id => v_id,
p_attachment => v_image,
p_filename => 'sample.jpg',
p_mime_type => 'image/jpg');
COMMIT;
END;
/
Also note, when mail is sent via APEX, it is queued and then periodically a job processes the queue. If you want to immediately force send you must use the APEX_MAIL.PUSH_QUEUE procedure, specifying the host and port. You can’t specify a particular queued message, the entire queue for that host/port is processed when pushed.
Some of the most powerful parts of APEX_MAIL are, as one might expect, designed to work with other APEX features. In particular, it supports APEX email templates (form letters) with value substitution for place holders. The templates are used for both plain text and html content. Note, if you use the template-based SEND, the generated email will always include both html and text bodies; even if the template has an empty_clob and nulls for the html portions.
In the example below I’ve created a simple template with similar structure for both html and text versions. The HTML content looks like this:
Hello #FIRST_NAME# #LAST_NAME#,<br> <br> We are reaching out to let you know you have #EXPIRED_COUNT# expired order(s).<br> If you would like to renew, please contact us at our <a href=https://renew.example.com>renewal site.</a><br> <br> Thank you
And the plain text version like this:
Hello #FIRST_NAME# #LAST_NAME#, We are reaching out to let you know you have #EXPIRED_COUNT# expired order(s). If you would like to renew, please contact us at https://renew.example.com. Thank you
Note the use of the break and the linked anchor tags in the HTML form. Also note the place holders enclosed in hash characters (#.) At run time these values are replaced with their respective values by passing in a JSON object. For each name/value pair in the object, the name is a place holder (without the # characters), and the value is what will be inserted into that place holder. For the template above, the JSON might look like this:
{
"FIRST_NAME":"Jane"
,"LAST_NAME":"Doe"
,"EXPIRED_COUNT":4
}
When the email is sent using the template and the place holder JSON values, the resulting email text will look like this (the html version will be similar but with a link anchor):
Hello Jane Doe, We are reaching out to let you know you have 4 expired order(s). If you would like to renew, please contact us at https://renew.example.com. Thank you
To create a template you should use the APEX application builder. There is an undocumented api wwv_flow_api.create_email_template that could be used to programmatically create them; but it still requires the IDs of an APEX workspace and application.
To send the mail the structure is similar to that of the previous SEND examples but a little smaller because the template defines subject and bodies. The JSON is just a simple text CLOB value and be constructed by any means you find convenient. Here I’m using the APEX_JSON package. For something as simple my object of 3 values it could be constructed with simple concatenation.
DECLARE
v_json_values CLOB := EMPTY_CLOB();
BEGIN
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write('FIRST_NAME', 'Jane');
apex_json.write('LAST_NAME', 'Doe');
apex_json.write('EXPIRED_COUNT', 4);
apex_json.close_object;
v_json_values := apex_json.get_clob_output;
apex_json.free_output;
apex_mail.send(p_template_static_id => 'EXPIRED_ORDERS',
p_placeholders => v_json_values,
p_to => 'jane.doe@gmail.com',
p_from => 'sean.stuber@gmail.com',
p_application_id => 100);
COMMIT;
END;
/
Here, as with the creation of the template, sending email with the template also requires an APEX application id. I created my sample template for my application with id 100.
That might seem a little convoluted for a single email, but where the power of templates shines is when you need to send a lot of them. Here I’m going to loop through all customers with expired orders and send each of them. I’ll use the JSON_OBJECT function to easily combine all the needed values into their place holder syntax and generate an email for each customer. Note the email address is not included in the place holder json, it is a distinct column in the result set.
BEGIN
FOR x
IN ( SELECT json_object(
KEY 'FIRST_NAME' VALUE c.cust_first_name,
KEY 'LAST_NAME' VALUE c.cust_last_name,
KEY 'EXPIRED_COUNT' VALUE COUNT(*)
) json_place_holders,
c.email_address
FROM customers c JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'EXPIRED'
GROUP BY c.cust_first_name, c.cust_last_name)
LOOP
apex_mail.send(p_template_static_id => 'EXPIRED_ORDERS',
p_placeholders => x.json_place_holders,
p_to => x.email_address,
p_from => 'sean.stuber@gmail.com',
p_application_id => 100);
END LOOP;
COMMIT;
END;
/
Another option, if you only want to send one body type, or if you want to adjust the content after the template has been populated with its final values is to use the PREPARE_TEMPLATE procedure. You use it essentially the same way you would with the SEND routines, but instead of directing the output to the email queue, the template returns a subject, html body, and text body which can then be processed further or used in the SEND routine as shown earlier.
In the example below I’m returning to the first template example, but instead of constructing an email with html and text bodies I’m going to just send the plain text content. I create my JSON as shown earlier, and then prepare the template to get my desired values; but I don’t use the html clob output in the SEND call.
DECLARE
v_json_values CLOB := EMPTY_CLOB();
v_subject VARCHAR2(1000);
v_html_body CLOB;
v_text_body CLOB;
BEGIN
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write('FIRST_NAME', 'Jane');
apex_json.write('LAST_NAME', 'Doe');
apex_json.write('EXPIRED_COUNT', 4);
apex_json.close_object;
v_json_values := apex_json.get_clob_output;
apex_json.free_output;
apex_mail.prepare_template(p_static_id => 'EXPIRED_ORDERS',
p_placeholders => v_json_values,
p_application_id => 100,
p_subject => v_subject,
p_html => v_html_body,
p_text => v_text_body);
apex_mail.send(p_to => 'jane.doe@gmail.com',
p_from => 'sean.stuber@gmail.com',
p_subj => v_subject,
p_body => v_text_body);
COMMIT;
END;
/
For more information on creating and managing email templates within the APEX framework I recommend the APEX App Builder User’s Guide. Email Templates are a shared component of an APEX application. So the section on templates can be found in the Managing Shared Components chapters of the User’s Guide.