PL SQL package for MailChimp API
By: Hayden Hudson On: October 12, 2018 In: APEX Developer Solutions Comments: 0

What’s the optimal technical solution for sending do-not-reply / marketing / notification / batch emails? Is it wiser to in-house or outsource the service? What are my options if I’m considering outsourcing?

These questions are difficult and I don’t propose to answer any of them with much thoroughness. They are, however, the questions that I was asking myself as I was considering upgrading the email notification mechanics for my blog 2122.io and ultimately led me to write a PL/SQL package for handling MailChimp’s API.

If, like me, you are (1) wary of maintaining an email server and (2) interested in an email solution that has easy-to-implement attractive templates and in-depth reporting, this blog post may be of interest to you.

Sending Email Is Easy / Ensuring Receipt Is Tricky

Configuring an MTA (Mail Transfer Agent) on your server, like Postfix, is simple enough. You can set yourself up to send mail in a matter of hours (including testing). However, I am persuaded that correctly installing the MTA software is the easy part.

You may now be able to send email but getting the email to reach its destination is a greater challenge. You must now acquire and maintain a  knowledge of:

All of these topics may be relevant to debugging why your emails aren’t reaching their intended recipients. More on these headaches here.

These challenges of course, don’t include those of:

  • Styling your email templates
  • Reporting on your campaign history (open rate, unsubscribes, etc)

All in all, in-housing your do-not-reply / marketing emails can be an ambitious project.

Give Yourself a Break – Outsource Your Email

For a modest monthly fee (or free if you don’t mind 3rd party branding in your email), you can plug in a 3rd party API and simplify your batch emailing process.

Below, I’ve tabulated some of the options I’m aware of (accurate as of the time of this writing). There are many metrics by which to compare these services – for my purposes, I’ve zeroed in on starting price and the quality of the API documentation:

MailChimp API and competitor APIs

All of these platforms would serve my modest intentions (hooking up email notification to a blog). They all have well-documented APIs and reasonable pricing. As a 1st cut, I can narrow in on the 3 services that would be sustainably free for my purposes: MailChimp, SendinBlue and Zoho. Among these 3, MailChimp, in my opinion, stands out for its richness of features and modernity of feel.

With this analysis, I assembled the following GitHub repo with the hope that others may find my work useful: https://github.com/hhudson/mailchimp_plsql_api. I wrote it for APEX 5.1.4 or higher with a 12.2 database.

Description Of My MailChimp API GIT Repo

The MailChimp API is vast  – I did not build an interface for all of it. I did, however, capture what I consider some of its most essential features:

  • Creating and Modifying Email Recipient Lists
  • Creating and Modifying Email Templates
  • Sending Your Email (obviously)
  • Reviewing Your Email History
  • And more

I provide examples and instructions here: https://github.com/hhudson/mailchimp_plsql_api

You’ll need to create a MailChimp account and pass in some global variables (like the API Key and the path to your wallet) to get started.

My experience coding this package

Writing this API was both easier and more difficult than I had anticipated.

On the ‘Easier Than Expected’ front, I want to thank Postman for being an invaluable tool for experimenting with API Calls. Added to that, I want to reiterate my praise for the MailChimp API, which includes many helpful examples.

On the ‘Harder Than Expected’ front, I was challenged by the new ‘https_host’ parameter in the APEX_WEB_SERVICE.MAKE_REQUEST function, introduced in the 12.2 database. It took a bit of head-scratching to figure out what this parameter was asking for. I documented my resolution to this challenge in my git repo: ORA-29024: Certificate of the remote server does not match the target address

Sign off

Do you agree with my analysis? Let me know if you think I’ve missed anything or if you can think of directions you’d like to see my project go. Drop me a comment below 🙂

Have other questions about Oracle APEX? Contact us

Hayden Hudson

Share this:

Leave reply:

Your email address will not be published. Required fields are marked *