Wrapping APEX_MAIL & Using JSON_OBJECT_T for Placeholders
Introduction
In this post, I’ll share two practical tips to enhance email functionality in Oracle APEX applications:
Why you should wrap the APEX_MAIL PL/SQL procedure with your own API.
How to use JSON_OBJECT_T to manage email placeholders more flexibly.
The second technique is also useful for passing parameters between procedures, without needing to know all the parameters in advance.
Using JSON_OBJECT_T for Placeholders
Background
APEX mail templates allow you to include placeholder variables that are substituted when you send an email using the APEX_MAIL API. All you have to do is pass JSON in the p_placeholders parameter. This is a very flexible approach.
However, in many apps, you’ll encounter multiple templates that share common fields. Without a structured approach, this often leads to redundant, boilerplate code. If we utilize the JSON_OBJECT_T data type, we can build a more efficient and lower-code solution.
Setting Placeholders
First, we define a reusable procedure to populate common placeholder fields related to quotes:
PROCEDURE common_email_placeholders
(p_quote_id IN quotes.quote_id%TYPE,
x_placeholders IN OUT NOCOPY json_object_t) IS
lr_quote_info cr_quote_info%ROWTYPE;
BEGIN
-- Get Details for the Quote.
OPEN cr_quote_info (cp_quote_id => p_quote_id);
FETCH cr_quote_info INTO lr_quote_info;
CLOSE cr_quote_info;
-- Set Quote related values.
x_placeholders.put ('CUSTOMER_NAME', lr_quote_info.account_name);
x_placeholders.put ('QUOTE_TYPE', lr_quote_info.quote_type);
x_placeholders.put ('QUOTE_NAME', lr_quote_info.quote_number);
x_placeholders.put ('QUOTE_AMOUNT', lr_quote_info.quote_amount);
-- Set other common values e.g. links back to our app, instance, name, and language.
x_placeholders.put ('LANGUAGE_CODE', lr_quote_info.language_code);
x_placeholders.put ('INSTANCE_NAME', get_instance_name());
x_placeholders.put ('CUSTOMER_APP_LINK', get_app_url());
x_placeholders.put ('PORTAL_APP_LINK', portal_url());
END common_email_placeholders;
The common procedure can include all possible placeholders.
Procedure to Send the Email
Next, we create a procedure to send a specific type of email, such as a request for approval. It builds on the common placeholders and adds email-specific fields if needed:
PROCEDURE email_request_for_approval
(p_document_id IN evl_documents.document_id%TYPE,
p_rule_name IN VARCHAR2,
p_to_email IN VARCHAR2,
p_comments IN VARCHAR2 DEFAULT NULL) IS
l_placeholders_obj json_object_t := json_object_t();
BEGIN
-- Set common email placeholders.
common_email_placeholders
(p_document_id => p_document_id,
x_placeholders => l_placeholders_obj);
-- Add placeholders specific to this particular email.
l_placeholders_obj.put ('APPROVAL_TYPE', p_rule_name);
-- Call wrapper API to send the email (see below).
send_email
(p_to => p_to_email,
p_template_static_id => 'APPROVAL_REQUIRED',
p_placeholders => l_placeholders_obj);
END email_request_for_approval;
JSON_OBJECT_T, we gain tremendous flexibility. You can pass additional data between procedures without changing the parameter lists, which reduces code maintenance overhead.JSON_OBJECT_T parameter.Why Wrap APEX_MAIL?
Instead of calling APEX_MAIL directly, I recommend using a wrapper procedure. Here’s a basic example:
PROCEDURE send_email
(p_to IN VARCHAR2,
p_cc IN VARCHAR2 DEFAULT NULL,
p_from IN VARCHAR2 DEFAULT NULL,
p_template_static_id IN VARCHAR2,
p_placeholders IN json_object_t) IS
l_to_email VARCHAR2(32000);
l_cc_email VARCHAR2(32000);
l_placeholders CLOB;
BEGIN
-- Convert Placeholders json_object_t object to a CLOB required by APEX_MAIL.
l_placeholders := p_placeholders.to_Clob;
-- Whitelist To email dlist.
-- Useful for testing, avoid sending customers emails from development or test instances.
l_to_email := apply_email_whitelist (p_email_address => p_to);
IF p_cc IS NOT NULL THEN
-- Whitelist CC email dlist.
l_cc_email := apply_email_whitelist (p_email_address => p_cc);
END IF;
-- TBD additional code that checks a setting and does not send the email at all.
-- Send the Email.
apex_mail.send
(p_to => l_to_email,
p_cc => l_cc_email,
p_from => NVL(p_from, 'Quoting <quoting@example.com>'),
p_replyto => 'no-reply@example.com',
p_template_static_id => p_template_static_id,
p_placeholders => l_placeholders);
-- TBD additional code to log the email.
-- insert into email_log (to_email, subject, body, sent_on) values (...);
END send_email;
Key Benefits of Wrapping APEX_MAIL
| Feature | Description |
| Email Whitelisting | In non-production instances, we mustn’t send emails to the intended recipients. Having a wrapper for APEX_MAIL allows us to apply whitelisting logic to all email addresses before sending the email. For example, if an email was intended for (ceo@example.com, cfo@example.com), we can intercept it in DEV and replace it with (dev@example.com, dev2@example.com). This allows us to test approval emails and other messages without risking sending them to actual recipients. |
| Disable Sending Easily | Centralized check to suppress any emails from being sent if needed. |
| Custom Logging | We may also want to log emails to a table, including the email body, which APEX does not store in its email logs. Custom logging can be expanded to have a scheduled process that checks with the email service provider to confirm that emails have been delivered. |
| Switch Email Providers | Having a wrapper API allows us to change the email provider with minimal impact on our code. For example, we may want to send emails using a service like SendGrid instead of APEX_MAIL. |
| Standardized Error Handling | Centralize exception management for all outgoing emails (optional enhancement). |
Conclusion
Wrapping APEX_MAIL and using JSON_OBJECT_T for placeholders are simple but powerful techniques to improve your APEX application’s email functionality.
Wrappers improve control, security, and maintainability.
JSON-based placeholders enable flexible and scalable email generation.
These strategies reduce technical debt today and future-proof your application for tomorrow.
