# 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:

1. Why you should **wrap the APEX\_MAIL PL/SQL procedure** with your own API.
    
2. 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](https://blog.cloudnueva.com/apex-email-templates-advanced-formatting) allow you to include placeholder variables that are substituted when you send an email using the [APEX\_MAIL](https://docs.oracle.com/en/database/oracle/apex/24.2/aeapi/SEND-Procedure-Signature-2.html) 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:

```sql
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.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">It’s OK if we have additional fields in the JSON that are not referenced in all the templates. APEX will only substitute the placeholders that appear in the currently referenced template.</div>
</div>

## 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:

```sql
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;
```

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text">By leveraging <code>JSON_OBJECT_T</code>, we gain tremendous flexibility. You can pass additional data between procedures <strong>without changing the parameter lists</strong>, which reduces code maintenance overhead.</div>
</div>

<div data-node-type="callout">
<div data-node-type="callout-emoji">⚠</div>
<div data-node-type="callout-text">Of course, with great power comes great responsibility. I am not advocating that we start creating all procedures and functions with just a single <code>JSON_OBJECT_T</code> parameter.</div>
</div>

# Why Wrap APEX\_MAIL?

Instead of calling `APEX_MAIL` directly, I recommend using a wrapper procedure. Here’s a basic example:

```sql
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](https://sendgrid.com/) 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.