Introduction

Background
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.
Creating a Template
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
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
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
Other Notification Methods
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.
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.
Language
Feature Requests
- 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
Jon Dixon
Co-Founder JMJ Cloud.