Context

APEX_Mail is a versatile Oracle APEX function for sending e-mail from APEX applications using PL/SQL. APEX Mail’s many parameters and options make it easy to call from anywhere without needing additional functions.

But is that what you want? Or should there be a single organization/application email manager package calling email related functions and procedures such as “Send_Mail” that wrap APEX_Mail avoiding APEX_Mail peppered throughout your code?

Objective

Identify key factors and design implications to consider when defining coding standards related to the use of APEX_Mail.

Key Factors

• Standard Mail: Should emails sent from the organization or application meet specific standards?

  • For example:

    • Standard email types with defined formats, sender, reply-to, content, etc.

    • Content rules, such as no HTML, only standard attachments, blank attached forms, and guidelines on allowed images

    • Use of email templates

    • Enforce branding requirements

• Flexibility

  • Are you ready for the new VP of Customer Service to request being bcc’d on all emails responding to customer complaints? And to have those emails logged in the issue tracking system?

  • Are you ready for the VP of Marketing to announce that a new domain should be used for all emails from the logistics division? Or that the branding at the top of the email needs to change?

  • Would be helpful to have the formatting and “packaging” of e-mails in one place?

  • What is your organization's communication strategy? Email is just one tool. How does email fit into this strategy, and are there ways to better integrate it or adapt as the strategy evolves? What else can you do to enhance this strategy? Should certain e-mails trigger system events?

  • Creating a layer of abstraction between the application and APEX_Mail is beneficial. Even if none of the other factors apply, routing all calls to APEX_Mail through a single package keeps the door open to address these factors if they arise later. This method also offers some protection against any future changes Oracle might make, like introducing a new, more efficient, or flexible function that could be preferable to use. Instead of passing the parameters through APEX_Mail, you can switch them to APEX_Super_Mail…

  • Want the ability now, or in the future to insert an AI “reviewer” before e-mail goes out the door?

• Security and Compliance

  • Should you only allow emails to be sent to, cc'd, bcc'd, from, and reply-to addresses that are on file in the system, with controls to manage changes to these addresses?

  • Should you check the email content to ensure it doesn't violate organization policies or compliance requirements?

  • Is the user authorized to send emails?

  • If a problem occurs, do you want the option to disable email across the system or just for specific email types?

  • Is there a final check to ensure the email aligns with customer communication preferences?

Tracking

• Do you want to gather extra information about the email being sent that APEX email logging doesn't capture?

  • Collect data that links the email to the calling application or procedure.

  • Gather customer, employee, or other IDs related to the email (from, to, cc, bcc, in the body content) instead of relying on email addresses, which might change or differ across data sources. (That never happens, right?) Then have the ability to easily list all e-mails related to a particular person, or sent by an employee, or of a particular type, or to all contacts at a particular customer, or….

  • Add custom attributes to the email.

Design Implications

• Email Business Rules: Which business rules should be located near the specific business area code, and which should be near the final email processing? Is the business code a standard solution where customization is expensive? Are the requirements specific to the business or more organization-wide? How does location of the business rules affect control over the requirements? What is the most efficient and logical approach?

• Collect more information than APEX_MAIL: Many key factors involve data points beyond what APEX_MAIL parameters and logging cover. Extra attributes can vary based on requirements, potentially for each email and over time. By requiring a single JSON-formatted parameter in addition to APEX_MAIL parameters, you can adjust the standard data elements collected with each email, depending on its nature. This setup allows for changes in additional element requirements in the future without needing to alter the parameter list in the function or procedure that wraps APEX_MAIL.

• What should you do with the additional information? APEX_MAIL creates useful email logs that the application can easily access.

  • Two issues with the APEX_Mail logs:

    • Retention: APEX_Mail logs are cleared based on the retention settings in APEX. If you need information about the e-mails beyond the maximum retention period, you will need to create and maintain tables that mirror those logs, probably using automation.

    • Data set: APEX_Mail logs do not save data elements other than the APEX_Mail parameters. The extra attributes should be saved in related tables, probably using Mail_ID as the foreign key, following good design practices.

• Modular Programming: This approach involves dividing a program into smaller, self-contained units (modules, functions, or procedures) that each perform a specific task, making the code more organized, reusable, and easier to maintain. Wrapping APEX_Mail does not mean one procedure with all the functionality to “rule them all”… The key factors discussed above consider many different function and data possibilities, many of which would be best served by their own function or procedure, including re-using ones that already exist. For example, a mail package for mail checking, formatting, sending, etc., a logging package for tracking, a marketing package with a function for checking communication preferences, etc. There are many variations depending on requirements, the application(s), and the organization.

• Meta Data: Many factors can be effectively managed using templates and metadata that users can modify through an application interface, eliminating the need to alter the code. The Meta Data assists with documenting parameters and puts more control in the hands of authorized users. Meta Data usage applies to email constraints, type definitions, filling templates for specific email types, communication preferences, and more.

Conclusion

That wraps up the identification of key factors and design implications for APEX_Mail. Hopefully, this discussion offers valuable insights as you think about email functionality in your organization and inspires additional ideas. If you found it helpful, please like the post.