APEX Plugin: Password Strength Estimator

I needed a simple password strength prompt for users when they need to create or change their password on my website. After a bit of Googling I found the “Low-Budget Password Strength Estimator” which is supposedly used by Dropbox, so you know it’s good ūüôā

This simple javascript library runs entirely within the client’s browser, and when presented with a candidate password, gives a score from 0 (very poor) to 4 (very good). It can also return extra feedback, including a warning message for poor passwords, as well as suggestions for making a password more secure.


So I’ve created a very simple Dynamic Action plugin (try the demo here) that allows you to add this functionality to any item on your page. You can specify a minimum length for the password, and can override the default messages for each score. You can also select whether or not the feedback warnings or suggestions are shown.


It seems to catch a lot of poor passwords, including ones comprising common words and names, and ones involving a simple sequence or repetition.



Obviously it’s only really useful for password entry fields; but don’t use it on your Login page!

Download from: http://github.com/jeffreykemp/jk64-plugin-passwordstrength

APEX Reports: One Link, Multiple Destinations

Every Interactive Report has an optional set of “Link” attributes that allow you to specify the location where the user should be redirected if they click a link next to a record in the report. You can choose “Link to Custom Target” and use the Link Builder to easily specify the target application, page, item values to pass, and other attributes.


What if the report combines different entities, and you need to direct the user to a different page depending on the type of entity? Or, if you need to direct the user to a different page with different attributes depending on the status of the record?

One method is to generate the URL in the report query using apex_page.get_url (APEX 5+) or apex_util.prepare_url (APEX 4 or earlier), or (God forbid) you could generate the url yourself using string concatenation.

A more declarative solution is to instead use APEX page redirects. This solution involves the following:

  1. Add some hidden items to the page to store the parameters for each target page;
  2. Add a Branch to the page for each target page; and
  3. Add a Request to the link to signal the page that a redirect has been requested.

Here’s an example. My page 550 has an interactive report which combines TI records with TRQ records (both of which have a very similar structure). If the user clicks on a TI record they should be redirected to p510 with the corresponding TI_ID, and if they click on a TRQ record they should be redirected to p305 with the corresponding TRQ_ID.

Here’s the link attributes for this report:


Notice that the page now redirects back to itself with the request set to “GOTO_TARGET”, along with the IDs required. My report query has been constructed so that every record will only have a TI_ID or a TRQ_ID, never both at the same time; so the link will ensure that only one of the P550_GOTO_xxx_ID values will be set.

The page then just needs two Branches: one for each target. Conditions on each branch ensures they only get activated if the request has been set, and the branch is selected based on which “GOTO ID” item has been set:



For a normal navigation to this report (e.g. from another page), the request should be blank (or some other value) so none of the Branches should be triggered.

For a relatively simple scenario like this, I like the declarative approach. Each branch can take advantage of the full range of APEX features such as the link builder, security conditions, build options, etc.

Note: this method works just as well for Classic report links as well.

The thing to be mindful of is that the order of the branches, and the condition on each branch, must be carefully selected so that the correct branch is activated in each situation. I’ve shown a simple example which works because I have ensured that only one of the ID parameters is set at the same time. If a record has both IDs, the condition on the first branch “GOTO_TARGET (TI_ID)” will evaluate to True and it will be used, regardless of what GOTO_TRQ_ID was set to.

If there were numerous potential destination pages, with a large number of parameters to pass, I might choose the apex_page.get_url method instead.


Using LetsEncrypt on Amazon Linux

sslapexFor a number of years now I’ve been using LetsEncrypt to provide free SSL certificates for the Apex applications I provide. These certificates last for 90 days and are renewed automatically by a simple script on my server.

By the way – if you’re not already using https for your public-facing Apex applications, you should, okay –¬†even if your site doesn’t have data entry.

Each LetsEncrypt certificate can cover multiple subdomains. They don’t currently support wildcard domains (e.g. *.example.com) but they are planning to add this next year (2018).

To install LetsEncrypt I ran the following on my Amazon Linux instance (note – this is my web server, not my database server):

cd /opt
git clone http://github.com/letsencrypt/letsencrypt
cd letsencrypt
./letsencrypt-auto -v --debug

(when prompted at the last step I typed “c” to cancel the subsequent steps)

It wasn’t easy at first because I got a number of errors which I’d google (or search the community forum) and eventually find reasonable answers. I’ve had to reinstall a number of times, as the OS is patched regularly and certbot is updated from time to time.

I use Apache to provide about a dozen virtual hosts and therefore the automated installation option didn’t work for me. Instead, I’ve got lines like these in each VirtualHost:

<VirtualHost *:443>
 ServerName subdomain.mydomain.com
 ServerAlias subdomain.mydomain.com
 SSLEngine on
 SSLCertificateFile "/etc/letsencrypt/live/mydomain.com/cert.pem"
 SSLCertificateKeyFile "/etc/letsencrypt/live/mydomain.com/privkey.pem"
 SSLCertificateChainFile "/etc/letsencrypt/live/mydomain.com/chain.pem"

To register a certificate I used the following command as root (all one line):

certonly --webroot -w /var/www/html
-d mydomain.com,www.mydomain.com,sub1.mydomain.com,sub2.mydomain.com

This generates all the keys and certificates and stores them locally. No private keys ever leave the server. This command is using SAN to combine multiple subdomains in one certificate. I run this command again separately for each domain.

To renew all my certificates I run the following command as root:

/opt/letsencrypt/letsencrypt-auto renew -n --no-self-upgrade
service httpd restart

This will automatically skip any certificates that are not yet due to expire. I’ve put the above script in a file which is run by cron on a monthly basis.

0 20 1 * * /path-to-script/renewall.sh

To get usage info on the options:

/opt/letsencrypt/letsencrypt-auto --help

Since it’s free, one cannot expect support from LetsEncrypt directly if there are issues; however, there is an active LetsEncrypt support community which can be helpful at times.

But it’s certainly made a big difference to my bottom line, and provided a bit of peace-of-mind to my users.


Show greyscale icon as red

I have an editable tabular form using APEX’s old greyscale edit link icons:


The users complained that they currently have to click each link to drill down to the detail records to find and fix any errors; they wanted the screen to indicate which detail records were already fine and which ones needed attention.

Since screen real-estate is limited here, I wanted to indicate the problems by showing a red edit link instead of the default greyscale one; since this application is using an old theme I didn’t feel like converting it to use Font Awesome (not yet, at least) and neither did I want to create a whole new image and upload it. Instead, I¬†tried a CSS trick to convert the greyscale image to a red shade.

I used this informative post to work out what I needed: http://css-tricks.com/color-filters-can-turn-your-gray-skies-blue/

WARNING: Unfortunately this trick does NOT work in IE (tested in IE11). Blast.

Firstly, I added a column to the underlying query that determines if the error needs to be indicated or not:

select ...,
       case when {error condition}
       then 'btnerr' end as year1_err
from mytable...

I set the new column type to Hidden Column.

The link column is rendered using a Link-type column, with Link Text set to:

<img src="#IMAGE_PREFIX#e2.gif" alt="">

I changed this to:

<img src="#IMAGE_PREFIX#e2.gif" alt="" class="#YEAR1_ERR#">

What this does is if there is an error for a particular record, the class "btnerr" is added to the img tag. Rows with no error will simply have class="" which does nothing.

Now, to make the greyscale image show up as red, I need to add an SVG filter to the HTML Header in the page:

<svg style="display:none"><defs>
  <filter id="redshader">
    <feColorMatrix type="matrix"
      values="0.7 0.7 0.7 0 0
              0.2 0.2 0.2 0 0
              0.2 0.2 0.2 0 0
              0   0   0   1 0"/>

I made up the values for the R G B lines with some trial and error. The filter is applied to the buttons with the btnerr class with this CSS in the Inline CSS property of the page:

img.btnerr {filter:url(#redshader);}

The result is quite effective:


But, as I noted earlier, this solution does not work in IE, so that’s a big fail.

NOTE: if this application was using the Universal Theme I would simply apply a simple font color style to the icon since it would be using a font instead of an image icon.

Target=_Blank for Cards report template

cardsreport.PNGI wanted to use the “Cards” report template for a small report which lists file attachments. When the user clicks on one of the cards, the file should download and open in a new tab/window. Unfortunately, the Cards report template does not include a placeholder for extra attributes for the anchor tag, so it won’t let me add “target=_blank” like I would normally.

One solution is to edit the Cards template to add the extra placeholder; however, this means breaking the subscription from the universal theme.

As a workaround for this I’ve added a small bit of javascript to add the attribute after page load, and whenever the region is refreshed.

  • Set report static ID, e.g. “mycardsreport”
  • Add Dynamic Action:
    • Event = After Refresh
    • Selection Type = Region
    • Region = (the region)
  • Add True Action: Execute JavaScript Code
    • Code = $("#mycardsreport a.t-Card-wrap").attr("target","_blank"); (replace the report static ID in the selector)
    • Fire On Page Load = Yes

Note: this code affects all cards in the chosen report.

File Upload Improvements in APEX 5.1

Updated 10/10/2017 now that APEX 5.1 has been out for a while.


The standard File Upload item type is getting a nice little upgrade in Apex 5.1. By simply changing attributes on the item, you can allow users to select multiple files (from a single directory) at the same time.

In addition, you can now restrict the type of file they may choose, according to the MIME type of the file, e.g. image/jpg. This file type restriction can use a wildcard, e.g. image/*, and can have multiple patterns separated by commas, e.g. image/png,application/pdf.


Normally, to access the file that was uploaded you would query APEX_APPLICATION_TEMP_FILES with a predicate like name = :P1_FILE_ITEM. If multiple files are allowed, however, the item will be set to a colon-delimited list of names, so the suggested code to get the files is:

  arr apex_global.vc_arr2;
  arr := apex_util.string_to_table(:P1_MULTIPLE_FILES);
  for i in 1..arr.count loop
    select t.whatever
    into   your_variable
    from   apex_application_temp_files t
    where  t.name = arr(i);
  end loop;

You can play with a simple demo here: https://apex.oracle.com/pls/apex/f?p=UPLOAD_DEMO&c=JK64 . (UPDATE 10/10/2017: recreated demo on apex.oracle.com) If you want to install the demo app yourself, you may copy it from here.

If you want to support drag-and-drop, image copy&paste, load¬†large files asynchronously, or restrict the maximum file size that may be uploaded, you will probably want to consider a plugin instead, like Daniel Hochleitner’s¬†DropZone.

Interactive Grids (APEX 5.1 EA) and TAPIs

DISCLAIMER: this article is based on Early Adopter 1.


I’ve finally got back to looking at my reference TAPI APEX application. I’ve greatly simplified it (e.g. removed the dependency on Logger, much as I wanted to keep it) and included one dependency (CSV_UTIL_PKG) to make it much simpler to install and try. The notice about compilation errors still applies: it is provided for information/entertainment purposes only and is not intended to be a fully working system. The online demo for APEX 5.0 has been updated accordingly.

I next turned my attention to APEX 5.1 Early Adopter, in which the most exciting feature is the all-new Interactive Grid which may replace IRs and tabular forms. I have installed my reference TAPI APEX application, everything still works fine without changes.

I wanted my sample application to include both the old Tabular Forms as well as the new Interactive Grid, so I started by making copies of some of my old “Grid Edit” (tabular form) pages. You will find these under the “Venues” and “Event Types” menus in the sample application. I then converted the tabular form regions to Interactive Grids, and after some fiddling have found that I need to make a small change to my APEX API to suit them. The code I wrote for the tabular forms doesn’t work for IGs; in fact, the new code is simpler, e.g.:

PROCEDURE apply_ig (rv IN VENUES$TAPI.rvtype) IS
  r VENUES$TAPI.rowtype;
    r := VENUES$TAPI.ins (rv =&gt; rv);
    sv('VENUE_ID', r.venue_id);
    r := VENUES$TAPI.upd (rv =&gt; rv);
    VENUES$TAPI.del (rv =&gt; rv);
END apply_ig;

You may notice a few things here:

(1) APEX$ROW_STATUS for inserted rows is ‘I’ instead of ‘C’; also, it is set to ‘D’ (unlike under tabular forms, where it isn’t set for deleted rows).

(2) After inserting a new record, the session state for the Primary Key column(s) must be set if the insert might have set them – including if the “Primary Key” in the region is ROWID. Otherwise, Apex 5.1 raises No Data Found when it tries to retrieve the new row.

(3) I did not have to make any changes to my TAPI at all ūüôā

Here’s the example from my Event Types table, which doesn’t have a surrogate key, so we use ROWID instead:

  r EVENT_TYPES$TAPI.rowtype;
    r := EVENT_TYPES$TAPI.ins (rv =&gt; rv);
    sv('ROWID', r.p_rowid);
    r := EVENT_TYPES$TAPI.upd (rv =&gt; rv);
    EVENT_TYPES$TAPI.del (rv =&gt; rv);
END apply_ig;

Converting Tabular Form to Interactive Grid

The steps needed to convert a Tabular Form based on my APEX API / TAPI system are relatively straightforward, and only needed a small change to my APEX API.

  1. Select the Tabular Form region
  2. Change Type from “Tabular Form [Legacy]” to “Interactive Grid”
  3. Delete any Region Buttons that were associated with the Tabular form, such as CANCEL, MULTI_ROW_DELETE, SUBMIT, ADD
  4. Set the Page attribute Advanced > Reload on Submit = “Only for Success”
  5. Under region Attributes, set Edit > Enabled to “Yes”
  6. Set Edit > Lost Update Type = “Row Version Column”
  7. Set Edit > Row Version Column = “VERSION_ID”
  8. Set Edit > Add Row If Empty = “No”
  9. If your query already included ROWID, you will need to remove this (as the IG includes the ROWID automatically).
  10. If the table has a Surrogate Key, set the following attributes on the surrogate key column:
    Identification > Type = “Hidden”
    Source > Primary Key = “Yes”
  11. Also, if the table has a Surrogate Key, delete the generated ROWID column. Otherwise, leave it (it will be treated as the Primary Key by both the Interactive Grid as well as the TAPI).
  12. Set any columns Type = “Hidden” where appropriate (e.g. for Surrogate Key columns and VERSION_ID).
  13. Under Validating, create a Validation:
    Editable Region = (your interactive grid region)
    Type = “PL/SQL Function (returning Error Text)”
    PL/SQL = (copy the suggested code from the generated Apex API package) e.g.

        RETURN VENUES$TAPI.val (rv =&gt;
            (venue_id     =&gt; :VENUE_ID
            ,name         =&gt; :NAME
            ,map_position =&gt; :MAP_POSITION
            ,version_id   =&gt; :VERSION_ID
  14. Under Processing, edit the automatically generated “Save Interactive Grid Data” process:
    Target Type = PL/SQL Code
    PL/SQL = (copy the suggested code from the generated Apex API package) e.g.

        VENUES$APEX.apply_ig (rv =&gt;
            (venue_id     =&gt; :VENUE_ID
            ,name         =&gt; :NAME
            ,map_position =&gt; :MAP_POSITION
            ,version_id   =&gt; :VERSION_ID

I like how the new Interactive Grid provides all the extra knobs and dials needed to interface cleanly with an existing TAPI implementation. For example, you can control whether it will attempt to Lock each Row for editing – and even allows you to supply Custom PL/SQL to implement the locking. Note that the lock is still only taken when the page is submitted (unlike Oracle Forms, which locks the record as soon as the user starts editing it) – which is why we need to prevent lost updates:

Preventing Lost Updates

The Interactive Grid allows the developer to choose the type of Lost Update protection (Row Values or Row Version Column). The help text for this attribute should be required reading for any database developer. In my case, I might choose to turn this off (by setting Prevent Lost Updates = “No” in the Save Interactive Grid Data process) since my TAPI already does this; in my testing, however, it didn’t hurt to include it.

Other little bits and pieces

I found it interesting that the converted Interactive Grid includes some extra columns automatically: APEX$ROW_SELECTOR (Type = Row Selector), APEX$ROW_ACTION (Type = Actions Menu), and ROWID. These give greater control over what gets included, and you can delete these if they are not required.

Another little gem is the new Column attribute Heading > Alternative Label: “Enter the alternative label to use in dialogs and in the Single Row View. Use an alternative label when the heading contains extra formatting, such as HTML tags, which do not display properly.”.


If you’d like to play with a working version of the reference application, it’s here (at least, until the EA is refreshed) (login as demo / demo):


I’ve checked in an export of this application to the bitbucket repository (f9674_ea1.sql).

Checkbox Item check / uncheck all

If you have an ordinary checkbox item based on a list of values, here is a function which will set all the values to checked or unchecked:

function checkboxSetAll (item,checked) {
 $("#"+item+" input[type=checkbox]").attr('checked',checked);

For example:

checkboxSetAll("P1_ITEM", true); //select all
checkboxSetAll("P1_ITEM", false); //select none

It works this way because a checkbox item based on a LOV is generated as a set of checkbox input items within a fieldset.

Note: If it’s a checkbox column in a report, you can use this trick instead: Select All / Unselect All Checkbox in Interactive Report Header

Unique constraint WWV_FLOW_WORKSHEET_RPTS_UK violated

If your APEX application import log shows something like this:

...PAGE 73: Transaction Lines Report
ERROR at line 1:
ORA-00001: unique constraint (APEX_040200.WWV_FLOW_WORKSHEET_RPTS_UK)
ORA-06512: at "APEX_040200.WWV_FLOW_API", line 16271
ORA-06512: at line 6

(this is on an Apex 4.2.4 instance)

This is due to a Saved Report on an Interactive Report that was included in the export, which conflicts with a different Saved Report in the target instance. The log will, conveniently, tell you which page the IR is on.

The solution for this problem is simple – either:

(a) Export the application with Export Public Interactive Reports and Export Private Interactive Reports set to No;
(b) Delete¬†the Saved Report(s) from the instance you’re exporting from.

You can find all Saved Reports in an instance by running a query like this:

select workspace
where application_user not in ('APXWS_DEFAULT'

You can delete Saved Reports from the Application Builder by going to the page with the Interactive Report, right-click on the IR and choose Edit Saved Reports, then select the report(s) and click Delete Checked.

Email made Easier

an e-mail letter that has a @ sign on itSending emails from the Oracle database can be both simply deceptively braindead easy, and confoundingly perplexingly awful at the same time. Easy, because all you have to do is call one of the supplied mail packages to send an email:

  (sender     => 'sender@host.com'
  ,recipients => 'recipient@example.com'
  ,subject    => 'Test Subject'
  ,message    => 'Test Message');
  (p_from => 'sender@host.com'
  ,p_to   => 'recipient@example.com'
  ,p_subj => 'Test Subject'
  ,p_body => 'Test Message');

If you want more control over your emails you can use UTL_SMTP instead; this is what I’ve been using for the past few years because I like feeling in control (doesn’t everyone?). This is the package that APEX_MAIL is built on top of.

If you just don’t trust these high-level abstractions you can use UTL_TCP and interact directly with the mail server. I don’t know, maybe your mail server does some weird stuff that isn’t supported by the standard packages.

If you want to send attachments, you can build this yourself in UTL_SMTP or UTL_TCP, but it’s easier with APEX_MAIL which can send BLOBs. UTL_MAIL can send attachments but only up to 32K in size (half that for binary files which become¬†base64 encoded).

Let’s make up a checklist of features supported out of the box (i.e. without requiring you to write non-trivial code) and see how they stack up:

Attachments Yes Yes (<32K) No* No*
Asynchronous Yes No No No
Rate limited Yes No No No
Anti-Spam No* No* No* No*
SSL/TLS Yes No No* No*
Authentication Yes No No* No*

Features marked “No*”:¬†these are not natively supported by the API, but generic API routines for sending arbitrary data (including RAW) can be used to build these features, if you’re really keen or you can google the code to copy-and-paste.

(Note: of course, you can add the Asynchronous and Rate limiting features to any of the UTL_* packages by writing your own code.)


Calls to the API to send an email do not attempt to connect to the mail server in the same session, but record the email to be sent soon after in a separate session.

This provides two benefits:

  1. It allows emails to be transactional – if the calling transaction is rolled back, the email will not be sent; and
  2. It ensures the client process doesn’t have to wait until the mail server responds, which might be slow in times of peak load.


Sending an email within an organisation is easy; internal mail servers don’t usually filter out internal emails as spam. Sending an email across the internet at large is fraught with difficulties, which can rear their ugly heads months or years after going live. One day your server tries to send 100 emails to the same recipient in error, and all of a sudden your IP is blocked as a spammer and NO emails get sent, with no warning.

For the last two years I’ve been battling this problem, because my site allows my clients to broadcast messages to their customers and partners via email and SMS. The SMS side worked fine, but emails frequently went AWOL and occasionally the whole site would get spam blocked. Most emails to hotmail went into a black hole and I was always having to apologise to anyone complaining about not getting their emails – “You’re not using a hotmail address by any chance? ah, that’s the problem then – sorry about that. Do you have any other email address we can use?”

I added some rate-limiting code to ensure that my server trickled the emails out. My server was sending about 2,000 to 3,000 per month, but sometimes these were sent in short spikes rather than spread out over the month. My rate-limiting meant a broadcast to 200 people could take several hours to complete, which¬†didn’t seem to bother anyone; and this stopped the “too many emails sent within the same hour” errors from the mail server (I was using my ISP’s mail server).

I managed to improve the situation a little by implementing SPF (Sender Policy Framework). But still, lots of emails went missing, or at least directly into people’s spam folders.

I looked into DKIM as well, but after a few hours reading I threw that into the “too hard basket”. I decided that I’d much prefer to outsource all this worry and frustration to someone with more expertise and experience.

Searching for an Email Gateway

I’ve been hosting my site on Amazon EC2 for a long time now with great results and low cost, and I’ve also been using Amazon S3 for¬†hosting large files and user-uploaded content. Amazon also provides an Email Gateway solution called SES¬†which seemed like a logical next step. This service gives 62,000 messages per month for free (when sent from an EC2 instance) and you just get charged small amounts for the data transfer (something like 12c per GB).

I started trying to build a PL/SQL API to Amazon SES but got stuck trying to authenticate using Amazon’s complicated scheme.¬†Just to make life interesting they use a different encryption¬†algorithm for SES than they do for S3 (for which I already had code from the Alexandria PL/SQL library). It was difficult because their examples all assumed you’ve installed the Amazon SDK.

It always rejected anything I sent, and gave no clues as to what I might be doing wrong. In the end I decided that what I was doing wrong was trying to work this low-level stuff out myself instead of reusing a solution that someone else has already worked out. A good developer is a lazy developer, so they say. So I decided to see what other email gateways are out there.

I looked at a few, but their costs were prohibitive for my teeny tiny business¬†as they assumed I am a big¬†marketing company sending 100,000s of emails per month and would be happy to pay $100’s in monthly subscriptions. I wanted a low-cost, pay-per-use transactional email service that would take care of the DKIM mail signing for me.


In the end, I stumbled upon Mailgun, a service provided by Rackspace. Their service takes care of the DKIM signing for me, do automated rate limiting (with dynamic ramp up and ramp down), it includes 10,000 free emails per month, and extra emails are charged at very low amounts per email with no monthly subscription requirement.

Other benefits I noticed was that it allows my server to send emails by two methods: (1) RESTful API and (2) SMTP. The SMTP interface meant that I was very quickly able to use the service simply by¬†pointing my existing APEX mail settings and my custom UTL_SMTP solution directly to the Mailgun SMTP endpoint, and it worked out of the box. Immediately virtually all our emails were getting sent, even to hotmail addresses. I was able to remove my¬†rate limiting code. Other bonuses were that I now had much better visibility of failed emails – the Mailgun online interface provides access to a detailed log including bounces, spam blocks and other problems. So far I’ve been using it for a few weeks, and of 2,410 emails attempted, 98.55% were delivered, and 1.45% dropped. The emails that were dropped were mainly due to incorrect email addresses in my system, deliberately “bad” test emails I’ve tried, or problems on the target mail servers. One email was blocked¬†by someone’s server which was running SpamAssassin. So overall I’ve been blown away by how well this is running.

Once I had my immediate problem solved, I decided to have a closer look at the RESTful API. This provides a¬†few intriguing features not supported by the SMTP interface, such as sending an email to multiple recipients with substitution strings in the message, and each recipient only sees their own name in the “To” field. My previous solution for this involved sending many emails; the API means that I can send the¬†request to Mailgun just once, and Mailgun will send out all the individual emails.

Another little bonus is that Mailgun’s API also includes a souped-up email address validator. This validator doesn’t just check email addresses according to basic email address formatting,¬†it also checks the MX records on the target domain to determine whether it’s likely to accept emails. For some domains (such as gmail.com and yahoo.com) I noticed that it even does some level of checking of the user name portion of the email address. It’s still not absolutely perfect, but it’s better than other email validation routines I’ve seen.

Note: Mailgun supports maximum message size of 25MB.

Email Validation Plugin

Mailgun also provide a jQuery plugin for their email address validator which means you can validate user-entered email addresses on the client before they even hit your server. To take advantage of this in Oracle APEX I created the Mailgun Email Validator Dynamic Plugin that you can use and adapt if you want.


If you follow me on twitter you are probably already aware that I’ve started building a PL/SQL API to make the Mailgun RESTful API accessible to Oracle developers. You can try it out for yourself by downloading from here if you want. The WIKI¬†on Github has detailed installation instructions (it’s a little involved) and an API reference.

The API supports the following Mailgun features:

  • Email validation – does the same thing as the jQuery-based plugin, but on the server
  • Send email
    • Attachments (CLOB or BLOB)
    • Inline images
    • Mailgun tags
    • Custom mail headers
  • Query Mailgun logs – bounces, unsubscribes, spam complaints


  (p_from_email => 'sender@host.com'
  ,p_to_email   => 'recipient@example.com'
  ,p_subject    => 'Test Subject'
  ,p_message    => 'Test Message'

The API Reference has a lot more detailed examples.

My implementation of the Send Email API so far supports the following features:

Attachments Yes
Asynchronous Yes
Rate limited Yes*
Anti-Spam Yes*
SSL/TLS Yes, required
Authentication Yes

Features marked “Yes*”: these are provided by the Mailgun service by default, they are not specific to this PL/SQL API.

I’m planning to add more features to the API as-and-when I have a use for them, or if someone asks me very nicely to build them. I’ll be pleased if you¬†fork the project from Github and I¬†welcome your¬†pull requests to merge improvements¬†in. I recommend reading through the Mailgun API Documentation for feature ideas.

If you use either of these in your project, please let me know as I’d love to hear about your experience with it.

Link: Oracle PL/SQL API for Mailgun

This article was not solicited nor paid for by Mailgun. I just liked the service so I wanted to blog about it.

UPDATE 26/4/2016: release 0.4 now uses Oracle AQ to enable asynchronous calls.

UPDATE 5/11/2016: release 0.6 adds APIs for querying your Mailgun logs. Also, added a note about UTL_MAIL’s attachment size limit.

UPDATE 4/10/2017: release 1.0 fixes some bugs and adds a few small features.