Report Google Map Plugin v1.0 Released

Over the past couple of weeks I’ve been working on an overhaul of my Google Maps region for Oracle Application Express. This free, open-source plugin allows you to integrate fully-featured Google Maps into your application, with a wide range of built-in declarative features including dynamic actions, as well as more advanced API routines for running custom JavaScript with the plugin.

The plugin has been updated to Oracle APEX 18.2 (as that is the version my current system is using). Unfortunately this means that people still on older versions will miss out, unless someone is willing to give me a few hours on their APEX 5.0 or 5.1 instance so I can backport the plugin.

EDIT: Release 1.0.1 includes some bugfixes and a backport for APEX 5.0, 5.1 and 18.1.

The plugin is easy to install and use. You provide a SQL Query that returns latitude, longitude, and information for the pins, and the plugin does all the work to show them on the map.

The plugin has been rewritten to use the JQuery UI Widgets interface, at the suggestion of Martin D’Souza. This makes for a cleaner integration on any APEX page, and reduces the JavaScript footprint of each instance on the page if you need two or more map regions at the same time. This represented a rather steep learning curve for me personally, but I learned a lot and I’m pleased with the result. Of course, I’m sure I’ve probably missed a few tricks that the average JavaScript coder would say was obvious.

The beta releases of the plugin (0.1 to 0.10) kept adding more and more plugin attributes until it hit the APEX limit of 25 region-level attributes. This was obviously not very scaleable for future enhancements, so in Release 1.0 I ran the scythe through all the attributes and consolidated, replaced, or removed more than half of them – while preserving almost every single feature. This means v1.0 is not backwards compatible with the beta versions; although many attributes are preserved, others (including the SQL Query itself, which is rather important) would be lost in the conversion if the plugin was merely replaced. For this reason I’ve changed the Internal ID of the plugin. This is so that customers who are currently using a beta version can safely install Release 1.0 alongside it, without affecting all the pages where they are using the plugin. They can then follow the instructions to gradually upgrade each page that uses the plugin.

All of the plugin attributes relating to integrating the plugin with page items have been removed. Instead, it is relatively straightforward to use Dynamic Actions to respond to events on the map, and an API of JavaScript functions can be called to change its behaviour. All of this is fully documented and sample code can be found in the wiki.

New features include, but are not limited to:

  • Marker Clustering
  • Geo Heatmap visualisation (this replaces the functionality previous provided in a separate plugin)
  • Draggable pins
  • Lazy Load (data is now loaded in a separate Ajax call after the page is loaded)

The plugin attributes that have been added, changed or removed are listed here.

If you haven’t used this plugin before, I encourage you to give it a go. It’s a lot of fun and the possibilities presented by the Google Maps JavaScript API are extensive. You do need a Google Maps API Key which requires a Google billing account, but it is worth the trouble. It is recommended to put a HTTP Referer restriction on your API Key so that people can’t just copy your public key and use it on their own sites. For more information refer to the Installation Instructions.

If you are already using a beta version of the plugin in your application, please review the Upgrading steps before starting. Don’t panic! It’s not quite as simple as just importing the plugin into your application, but it’s not overly complicated. If you were using any of the Page Item integration attributes, you will need to implement Dynamic Actions to achieve the same behaviour. If you had any JavaScript integrations with the plugin, you will need to update them to use the new JQuery UI Widget API calls. I am keen for everyone to update to Release 1.0 as soon as possible, so I will provide free support (via email) for anyone needing help with this.

I am very keen to hear from everyone who is using the plugin, and how it is being used – please let me know in the comments below. If you notice a bug or have a great idea to enhance the plugin, please raise an issue on GitHub.

Links


Escaped strings and PL/SQL Dynamic Actions

I had a simple form where the user can select a vendor from a select list (using the Select2 plugin), and the form would retrieve the latest purchase details based on a simple string search of that vendor’s name – for most cases. Sometimes, however, it failed to find anything even though I knew the data was there.

There was a dynamic action that fires on Change of the list item that executes a PL/SQL procedure and retrieves the Total Amount and the Memo:

transaction_pkg.get_suggestion
  (vendor_name => :P5_VENDOR_NAME
  ,total_amt   => :P5_AMOUNT /*out*/
  ,memo        => :P5_MEMO); /*out*/

This didn’t work in some cases where a vendor name included special characters, such as &. This is because the item had Escape special characters set to the default (Yes), which is good practice to protect against Cross-Site Scripting (XSS) attacks. Therefore, the value sent by the dynamic action to my PL/SQL procedure had the special html characters escaped, e.g. “Ben & Jerry’s” was escaped to “Ben & Jerry's“. I believe APEX uses the apex_escape.html function to do this.

Usually, I would try to rework my code to send a numeric ID instead of a string; but in this particular case the data model does not have surrogate keys for vendors (it’s just a free-text field in the transactions table) so I want to use the name.

If I was doing this properly, I would fix the data model to make vendors first-class entities, instead of using a free-text field. This would allow using a surrogate key for the vendor list and this escaping behaviour would no longer be a problem.

Another alternative here is to use the latest transaction ID as a surrogate key for each vendor; but then I would need to modify the form to translate this back into a vendor name when inserting or updating the table; and this would add unnecessary complexity to this simple form, in my opinion.

Instead, before sending this string to my procedure, I’ve chosen to unescape the data. To do this, I add a call to utl_i18n.unescape_reference:

transaction_pkg.get_suggestion
  (vendor_name => utl_i18n.unescape_reference(:P5_VENDOR_NAME)
  ,total_amt   => :P5_AMOUNT /*out*/
  ,memo        => :P5_MEMO); /*out*/

This converts the escaped data back into a plain string, and my simple data matching procedure now works as expected.


List all installed plugins

After upgrading APEX I found this query useful to review all the plugins I had installed across multiple workspaces and in multiple applications to find ones that needed to be upgraded.

select name
      ,plugin_type
      ,version_identifier
      ,workspace
      ,application_id
      ,application_name
from APEX_APPL_PLUGINS
where workspace not in ('INTERNAL','COM.ORACLE.CUST.REPOSITORY')
order by name, workspace, application_id;