Introduction
I continue to be impressed with what may seem like minor features released by the APEX development team that turn out to be a big deal because they end up saving hundreds of lines of code. One such feature is APEX Email Templates introduced in APEX 18.1. In this post I will describe how we used them for much more than just Email Templates when building a notification framework for one of our customers.
Background
The goal of the project was to build a notification framework that could be used to handle just about any kind of notification that you can imagine. This includes email (via APEX_MAIL and Mailgun), SMS via Twillio and push notifications to iOS Apps using Firebase. We ended up building a generic notification queue which we processed on a schedule to send out the actual notifications. You may ask, how do APEX Email templates apply to all of these communication methods? The answer lies in the APEX_MAIL PL/SQL API apex_mail.prepare_template. Read on to find out why.
Templating
The goal of templating in the context of notifications is to separate the content of the notification from the format and layout of the notification. This allows you to alter the template without having to change any code (most of the time). All of the notification methods I mentioned above can benefit from templating.
Let’s take email as an example. Let’s say we have an email we want to send to a customer when they place an order. In the email you want to provide information related to the product they purchased, how it will be shipped, and when they can expect it to arrive. You could build a PL/SQL function that generates the complete HTML document and then use that for the content of your email. This would mean, however, that when the marketing department needs to change the layout of the email, you have to change that function, regression test and deploy code. Being able to just change a template has obvious advantages.
As we move forward, I will use the example of the New Order notification and create an APEX email template which can be used for SMS, APEX_MAIL and Mailgun.
Let’s take email as an example. Let’s say we have an email we want to send to a customer when they place an order. In the email you want to provide information related to the product they purchased, how it will be shipped, and when they can expect it to arrive. You could build a PL/SQL function that generates the complete HTML document and then use that for the content of your email. This would mean, however, that when the marketing department needs to change the layout of the email, you have to change that function, regression test and deploy code. Being able to just change a template has obvious advantages.
As we move forward, I will use the example of the New Order notification and create an APEX email template which can be used for SMS, APEX_MAIL and Mailgun.
Creating a Template
Since APEX 18.1, under Shared Components, you will see an option ‘Email Templates’. Click that option then click ‘Create Email Template >’ to create a new template.
On the right-hand side, you have the option to start with a ‘Sample Template’. Clicking one of the samples will pre-populate the template with the sample. This is a great way to see how templates work.
Placeholder Variables
The template works using boilerplate text (and HTML) and placeholder variables. These must be enclosed in a #, must be uppercase and must have no spaces. In the email subject above, you can see an example #ORDER_NUMBER#. Placeholders are replaced at run time by apex_mail.send and apex_mail.prepare_template (more on that later).
HTML Header, Body and Footer
Placeholder Variables
The template works using boilerplate text (and HTML) and placeholder variables. These must be enclosed in a #, must be uppercase and must have no spaces. In the email subject above, you can see an example #ORDER_NUMBER#. Placeholders are replaced at run time by apex_mail.send and apex_mail.prepare_template (more on that later).
HTML Header, Body and Footer
In this section you provide the boilerplate text, HTML and placeholder variables that you need to format the data elements in your notification. Although the HTML you enter here impacts the format of your notification (e.g. bold text, links, tables etc.), it does not drive the overall structure of the email. This is driven by the ‘HTML Template’ field in the ‘Advanced’ section.
You can load a default Template by clicking the ‘Load Default HTML’ button. The APEX team has provided a pretty nice-looking template that works well with browsers and email clients (see below for a screen shot). Of course, you can change this and even copy and paste templates from open source providers like Zurb.
Plain Text
Finally, there is a section for Plain Text. There are still some email clients out there that only support plain text, so it is good practice to provide a Plain Text as well as an HTML formatted email body. This section also comes in handy for SMS templates.
Plain Text
Finally, there is a section for Plain Text. There are still some email clients out there that only support plain text, so it is good practice to provide a Plain Text as well as an HTML formatted email body. This section also comes in handy for SMS templates.
Previewing the Template with Real Data
I have a template now but how do I know exactly how it’s going to look? This introduces the first use case for apex_mail.prepare_template. Create a simple PL/SQL block like the following and run it.
Note: p_application_id refers to the APEX Application that the template was created in.
Note: The placeholders in the JSON passed in p_placeholder are also substituted in the subject and the Text Body.
The API will substitute all of the place holders in the template ‘NEW_ORDER_NOTIFICATION’ with values in the placeholder JSON. Save the results from the dbms_output into a file with a .html extension and open it using your browser. You can now iterate until you have the template looking just how you want it. Of course, it wouldn’t take much additional effort to create an APEX page to show a preview of the template.
Note: The placeholders in the JSON passed in p_placeholder are also substituted in the subject and the Text Body.
The API will substitute all of the place holders in the template ‘NEW_ORDER_NOTIFICATION’ with values in the placeholder JSON. Save the results from the dbms_output into a file with a .html extension and open it using your browser. You can now iterate until you have the template looking just how you want it. Of course, it wouldn’t take much additional effort to create an APEX page to show a preview of the template.
Sending the Email
As well as prepare_template, APEX 18.1 brings a new ‘send’ function which handles the call to prepare_template as well as sending the actual email.
Other Notification Methods
How is this useful for other notification methods like Mailgun and Twillio? Well, just because it says APEX Email Templates on the box does not mean that is all it is good for.
Mailgun
Just like APEX_MAIL.SEND, the Mailgun API looks for three main parameters: subject, text (body) and html (body). We can get all of these values from our APEX Email Template using the apex_mail.prepare_template. We then use the results from this API call to call the Mailgun API to send the actual email.
Mailgun
Just like APEX_MAIL.SEND, the Mailgun API looks for three main parameters: subject, text (body) and html (body). We can get all of these values from our APEX Email Template using the apex_mail.prepare_template. We then use the results from this API call to call the Mailgun API to send the actual email.
Twillio
The Twillio Messages API also expects a message body to send an SMS, but as you may expect we would not want to send HTML in the body. For SMS templates we need to focus on the Plain Text Format portion of the template. You also do need to provide values for subject and HTML body, but it can just be dummy text.
The Twillio Messages API also expects a message body to send an SMS, but as you may expect we would not want to send HTML in the body. For SMS templates we need to focus on the Plain Text Format portion of the template. You also do need to provide values for subject and HTML body, but it can just be dummy text.
The call to the prepare_template API is identical to the one above; we just need to pass different placeholders and we only want to consume p_text from the response.
All you need to do then is send the content of p_text using the Twillio API, and Voila!
Language
Multi-language is also covered. If you have multi-language enabled for your app, when you seed an publish your APEX application, a language translated version of the template is also generated. Once you provide translations for the text, the template can be generated in the users session language.
Feature Requests
APEX Email Templates are a great feature, I think there is still room for improvement though and here are my suggestions for the APEX Dev Team:
- Provide capabilities to store and retrieve templates by language.
- Create an API to create and update templates. That way, developers can create a template building page for end users to manage the templates.
- Create an ‘apex_’ view on the templates table WWV_FLOW_EMAIL_TEMPLATES.
Conclusion
APEX Email Templates, like many other APEX features have been well thought out and provide real value to developers (and their end users). They can also be used for much more than just email templates so don’t let my examples limit you.
Jon Dixon
Co-Founder JMJ Cloud.
RSS Feed