ReportMap Release 1.4

Release 1.4 of the JK64 Report Google Map R1 has been released with a number of bug fixes and enhancements. You can review the changes here: https://github.com/jeffreykemp/jk64-plugin-reportmap/releases/tag/v1.4. The most important enhancement is a new Dynamic Action plugin that is included as a recommended companion to the map plugin.

The Dynamic Action plugin, called JK64 Report Google Map R1 Action, allows you to implement any of a range of custom behaviours on your map region. If installed, you can make the map respond to user behaviour or other events without needing to resort to writing your own custom JavaScript.

You can add a dynamic action to modify a variety of options and attributes of the map, execute searches, pan and zoom, load features via geoJson, and more – and these can be based on the value of items on your page, or via JavaScript expressions that you specify.

For example, on the demo Plugin Options, the user can change the map type:

This is implemented as a radio item with a dynamic action on the Change event:

The dynamic action has the following attributes:

Note that in this case, it sets an Option – Map Type, based on the triggering element (the P28_MAP_TYPE item). The Affected Elements is required, and must refer to the map region that we want to change.

Notice anything missing? That’s right – No Code needed!

The plugin makes it easy to customise which of the default Google Map controls (buttons, etc.) are shown to the user:

  • Full Screen control
  • Map Type control
  • Rotate control
  • Scale control
  • Street View Pegman control
  • Zoom control

Other options that can be set include:

  • Clickable Icons
  • Control size
  • Disable default UI
  • Gesture Handling
  • Heading
  • Keyboard shortcuts
  • Map Type
  • Maximum Zoom level
  • Minimum Zoom level
  • Restrict search to Country
  • Styles
  • Tilt
  • Zoom level

In addition, the plugin allows you to restrict the map to a set of bounds, via the Restrict to Bounds or Restrict to Bounds Strict Mode actions.

You can browse all the possible actions in the WIKI: https://github.com/jeffreykemp/jk64-plugin-reportmap/wiki/DA-Plugin-Attributes-Reference


Another enhancement included in this release is explicit support for the Table / View data source. This is simple to use, although not quite as flexible as the SQL Query option. Your table or view must include columns with the correct column names expected by the selected Visualisation – for example, if your Visualisation is Pins, the table or view must have columns named lat, lng, name and id. Click the Help tab on Table Name for more details, or review the WIKI (https://github.com/jeffreykemp/jk64-plugin-reportmap/wiki/SQL-Query-Examples).


NOTE: the plugin supports APEX 18.2 and later. It is no longer planned to include backports for older versions of APEX.


A big thanks to many APEX developers around the world who have installed and used the map plugin over the years. Your suggestions, questions and bug reports have contributed a great deal to improving the plugin.


Saved Interactive Reports, Lost and Found

Sometimes we can too quickly say “No” when we should take a moment to think about whether we can actually say “Yes”. This was one of those times for me.

We had just gone live with a fairly big system move + upgrade – a suite of APEX applications shifted from one database instance running APEX 5 to another instance running version 19.1. The migration went smoothly. After the new instance was released to the business to do some final shakedown testing, they noticed one problem:

“All our saved reports are gone.”

Oops. We had built the new instance by simply importing each application from source control; since these scripts don’t include the Saved Report formats that users had customised in many of the Interactive Reports in prod, they didn’t get migrated to the new database.

When they asked if we can transfer the saved reports across, I initially replied, “Sorry, we can’t” – thinking that redoing the migration from scratch with a full export from the old database, followed by re-doing all the app changes, was going to take too much time and effort.

A minute later, I sent another email. “Hold that thought – there is a way.” I’d forgotten that my script for exporting the applications uses an APEX API with some parameters that until now I’d rarely used, but which would solve our problem. My script included this:

wwv_flow_utilities.export_application_to_clob (
    p_application_id => 100,
    p_export_ir_public_reports => 'N',
    p_export_ir_private_reports => 'N',
    p_export_ir_notifications => 'N');

NOTE: on more recent versions this API became supported as apex_export.get_application.

In order to restore all the user saved reports, I created a new version of my export script, but changed all the “N”s into “Y”s:

wwv_flow_utilities.export_application_to_clob (
    p_application_id => 100,
    p_export_ir_public_reports => 'Y',
    p_export_ir_private_reports => 'Y',
    p_export_ir_notifications => 'Y');

I call this the “Yes” script. Using this “Yes” script, we performed the following steps:

  1. Log into the schema on the old database
    (we hadn’t shut it down yet, thankfully – we’d just locked the schemas and set all the applications to “Unavailable”)
  2. Run the “Yes” script.
    Rename the resulting export files, e.g. f100_ir.sql, f110_ir.sql, etc.
  3. Log into the new database.
  4. Run the IR scripts (f100_ir.sql, etc.).
    This reverted all the applications to their old versions, but they included all the user’s saved reports.
  5. Re-import the new versions of the applications from source control.
    This upgraded all the applications, leaving the user’s saved reports intact.

Problem solved.


ReportMap Release 1.2

Version 1.2 of the ReportMap Google Map plugin has been released today. While the rest of you have been idling away under Covid-19 restrictions, I’ve been happy as a clam working on some exciting enhancements to the plugin.

If you just want to get in and have a play, check out the demo on apex.oracle.com.

New Features in this Release

Included in this release are the following new features:

  • New visualisation: Spidifier
  • Show turn-by-turn Directions
  • Customise each Marker with your own JavaScript function
  • Load large data sets in batches
  • Show spinner while data is loading
  • Localisation options

A bug when the new Friendly URLs feature of Oracle APEX 20.1 is used with the Clustering visualisation has also been fixed in this release.

The full list of enhancements and bugfixes, with links to the issues register, may be viewed here.

If you use the Directions or Route Map options, you can now show the turn-by-turn directions on your page, anywhere you want.

The documentation has been updated. The plugin now has four new plugin attributes, as well as a number of other attributes that can be set via JavaScript (the officially supported ones are documented on the plugin attributes page). Three new plugin events have also been added to support the new features.

Spiderfier

If you have a map that needs to show a lot of pins, especially ones that are close together, the plugin previously had the option of Clustering them at high zoom levels. The user could click on a cluster to zoom in enough to show the individual pins. One weakness of this approach is that if one or more pins are almost (or exactly) overlapping, the cluster never “unclusters” – the user cannot zoom in far enough to get the pins to show individually.

This release provides another Visualisation option, Spiderfier. This uses the OverlappingMarkerSpiderfier to control how pins react when clicked. When the user clicks a pin that is close or overlapping with other pins, it shifts the pins in that area into a ring, or a spiral (depending on how many pins are there) with lines pointing back to their original location. It also colours them blue to indicate they’ve been “spiderfied”. The user can then hover and click each marker separately.

If the user zooms in, the Spiderfier automatically returns all the pins to their original location.

I think the defaults I’ve set work reasonably well. If needed, you can customise the Spiderfier by setting its options via the JavaScript Initialisation Code (refer to “spiderfier options” here for details). You can also provide your own formatting function to change how the markers look when they are “spiderfied”.

Marker Icons

The WIKI has been augmented with a handy guide to Map Icons. The plugin has long supported the ability to specify custom images for the marker icons. This release gives a whole lot more control over the markers to the developer:

If all the icons in the query are being loaded from the same location, you can now set the iconBasePath option once and just have a relative icon file name in the query. When there is a lot of data to show in the map, this can significantly reduce the volume of data loaded to the client, which can lead to a significant performance improvement.

The developer can now supply a custom JavaScript function (via the markerFormatFn option) to format each marker using whatever logic they need.

For example, if the marker icon needs to be different according to some data value, you can send the data via one of the flex fields, and then write your custom function to set the marker icon depending on the value of the flex field.

You could also modify other characteristics of the marker, such as the title (hover text), info text (popup window), icon anchor point, opacity, and even position (although usually I’d expect your query would provide the correct lat/lng coordinates).

If you have a large number of custom icons you wish to use, along with a large data set of pins to render on the map, you could even compile the icons into a single sprite map to reduce network overhead. This means the image file is loaded once to the client, and then the map “cuts out” bits of the sprite map to render the marker icons. This can be done by setting just a few attributes of the marker’s Icon object. I haven’t tried it myself yet, but this tool looks like it would be useful for this purpose.

Loading Large Datasets

This release adds the Show Spinner and the Rows Per Batch attributes. These attributes are independent of each other, and they help to improve the quality of the experience for your end users when you are rendering a large number of pins on the map.

By default, new maps will have Show Spinner set on. For any existing maps, after upgrading you can turn this option on by setting it in the plugin attributes. This option causes the map to show the default APEX spinner while the data is loaded. The spinner is then removed when the last marker has been rendered. The effect is to give the user an indication that the map is “working”, and gives them immediate feedback when the data has finished loading and they may now interact with the map.

If the spinner seems to stay forever, it may indicate an issue with connectivity to the server (or perhaps that the server is under severe load or has stopped responding to requests).

When the APEX page has been rendered on the client, the Google Map is shown but the data is not immediately loaded; instead, a separate AJAX request is sent to the server to run your query and download all the data to render the pins on the map. By default, this is all done in one single AJAX call, which is the fastest way to get from start to finish; the downside is that the user will not see any pins on the map until all the data has been downloaded. You can change this behaviour by setting Rows Per Batch to some number (e.g. 1000). With this attribute set, the plugin will send a series of AJAX calls to the database (one at a time) and get a batch of records at a time. After loading a batch, the plugin will render the pins on the map (and if necessary, it will pan / zoom the map to show them all) and then send another AJAX request to get the next batch. When it has finished receiving all the batches, it adds any finishing touches needed (e.g. for a visualisation) and returns control to the user.

The advantage of this approach is that the user can see the pins being shown gradually, and they will know that “something is working”. This may help to give them a nicer user experience.

The downside of this approach is that it may cause a bigger load on the server (because each AJAX request requires running a new query, with an offset) and will usually take longer from start to finish. Generally, if your data comprises only a few hundred records at most, you will probably want to leave the Rows Per Batch setting blank.

The Future

There are still a few little enhancements on my “todo” list, but I’m keen to hear how you are using (or perhaps planning to use) this plugin, and if there are any new features or improvements that you need or want. If so, please raise them on the GitHub Issues page.

Quite a few people have raised questions or ideas in the past and sometimes I’ve incorporated them straight away, and other times it’s taken a little longer but I get there eventually. If you’re keen to contribute, feel free to have a poke around in the code and perhaps even do a pull request on the GitHub source to suggest a change. It would be great to collaborate with you because everyone has something unique to offer.

Long-term, I’m watching with interest the future direction of Oracle APEX. I remember at one point they were talking about incorporating some sort of new map region into the product, although the mention of this seems to have been dropped from the Statement of Direction (or maybe my memory is misleading me). I guess time will tell.


Font Awesome v5 alongside Font APEX

Font APEX is preferred most of the time but sometimes there are icons I really want to use which are not (yet) included. For these cases I want to load the latest Font Awesome library.

It is possible to load Font Awesome instead of Font APEX by opening Shared Components -> Themes -> Universal Theme, and setting Custom Library File URLs to the location of the library (wherever you have loaded it). However, this replaces the Font APEX font completely so you can’t use both at the same time using this method.

These regions are shown in the same page; the first region uses a Font APEX icon, the second uses a Font Awesome 5 Free icon.

In order to use both at the same time, I’ve downloaded the latest free version of Font Awesome 5 from here (fontawesome.com), taken a copy of the file css/all.css and edited it to replace all occurrences of “.fa” with “.fa5” (if you use CSS precompiler you can do this by editing the appropriate variables file, e.g. _variables.less). This is necessary because the “fa” class prefix would conflict with Font APEX. I named my custom file “fa5.css” and created a minified version as well.

On my web server I created the folder /fa5 under my public html folder, and copied the following files / folders into it:

  • /fa5/css/fa5.css
  • /fa5/css/fa5.min.css
  • /fa5/webfonts/* (all contents)

In my APEX application, in the Universal Theme properties I set:

  • Custom Library File URLs = /fa5/css/fa5#MIN#.css
  • Custom Prefix Class = fa5
  • (optional) Custom Classes = (comma-delimited list of your favourite icons)
Theme attributes to load a custom library.

Alternatively, you could upload the library into your Static Application Files and load them from there.

Files loaded into Static Application Files.
Theme settings to load the custom library from Static Application Files.
If you set the Custom Classes attribute on the theme, you get them listed for convenience in the Pick Icon / Custom list. It doesn’t show previews of the icons, however, since I don’t know how to load a custom library into the APEX builder environment itself.

There’s not enough room in the Custom Classes attribute to list every single icon, unfortunately. You might choose to include just the ones you use frequently. I’ve selected a number of them and listed them here if you want my list: https://github.com/jeffreykemp/sample/blob/master/fontawesome/fa5_selection_custom_icons.txt

If I want my page to use an icon from Font APEX, I use the fa- icons as usual, e.g. fa-apex. Where I need an icon from Font Awesome, I have to include both the fa5 class as well as the icon class, e.g. fa5-restroom. For brand icons, of which Font Awesome has a large selection, the class is fa5b, e.g. fa5b fa5-amazon-pay. Font Awesome also includes a range of modifiers including sizes, spin, pulse, rotating, mirroring, and stacking.

The spin and pulse effects are not visible in the screenshot above. A live demo can be viewed and examined here: https://jk64.com/apex/f?p=TEST:FA5:0.

You are, of course, asking, can I stack two icons AND spin just one of them? The answer, of course, is yes:

Issue #1: Featured Hero Region Icon

When I tried to use a Font Awesome icon in a Hero region with the “Featured” style, the font failed to load. This is because the “Featured” style overrides the font-family causing it to fail to use the Font Awesome font. To fix this, on the page I added the following CSS:

.apex-icons-fontapex .t-HeroRegion--featured .t-HeroRegion-icon.fa5:after,
.apex-icons-fontapex .t-HeroRegion--featured .t-HeroRegion-icon.fa5:before
  { font-family:'Font Awesome 5 Free'!important; } 

Issue #2: Navigation Menu Icon

In a navigation menu, APEX includes the “fa” class which controls the positioning of the icons in the menu, but it also overrides the font library and fails to load the icon from Font Awesome. To fix this, I further edited my fa5.css file (as well as the minified version) to add the following:

.fa.fa5:before, .fa.fa5:after {<br>
    font-family: 'Font Awesome 5 Free' !important;<br>
}

Bonus: Using an Icon Stack for a Region Icon

The Icon attribute on a region can only be used to provide a class (or list of classes) to serve as the icon for the region. To use a Stacked icon in this case is impossible as the stack must be specified using a span with nested nodes for each icon in the stack. A workaround for this is to use some jQuery to modify the html at runtime, as follows:

  1. Set the region’s Static ID, e.g. stacked
  2. Set the region’s Icon attribute to one of the icons in the stack (just so that there is something shown if the javascript is delayed), eg. fa5-camera
  3. Add this to the page’s Execute When Page Loads (this example is for a Hero region:
var stackCameraBan = '
<span class="t-HeroRegion-icon t-Icon fa5 fa5-stack">
  <i class="fa5 fa5-camera fa5-stack-1x"></i>
  <i class="fa5 fa5-ban fa5-stack-2x" style="color:Tomato"></i>
</span>';

$("#stacked span.t-HeroRegion-icon.fa5").replaceWith(stackCameraBan);

It’s a messy kludge, and you’ll have to adapt it if you want to use it in other region templates (check what the span class is), but if this provides significant business benefit then it might be worthwhile.

Comparing Font Awesome 5 Free with Font APEX

I’ve loaded lists of all the icons in the Font Awesome 5 Free and the Font APEX libraries into a table and created a little application that allows me to compare them.

You can browse the list here: https://apex.oracle.com/pls/apex/jk64/r/fa5/home

  • 348 icons appear in both libraries
  • 722 are unique to Font APEX
  • 1,089 are unique to Font Awesome 5 Free
  • 2,159 total

Note: these stats are not perfect because some of the icon names are slightly different between the libraries – for example, all of the “hand” icons have slightly different names between the two libraries.

You can download all the source files (including the APEX icon comparison list application) from here: https://github.com/jeffreykemp/sample/tree/master/fontawesome

References


Friendly URL structure

Oracle has updated apex.oracle.com to APEX 20.1 which includes among other features the new “Friendly URL” option. The legacy URL structure concatenated a string of parameters into a single “p” parameter, which works fine; but it can make it difficult to configure web server rules to match and rewrite URLs. Apart from the application ID or alias (the first part), all the parameters are optional; if all were specified the URL will be something like this (line breaks added for clarity):

f?p=SAMPLE_DB_APP:HOME:16801234560918:myrequest:mydebug
  :myclearcache:myitems:myvalues:myprinterfriendly
  &p_trace=mytrace
  &cs=19A56DBFDXXXE4DF293C96D786
  &c=jk64

In this example, the application alias is used (SAMPLE_DB_APP) and page alias (HOME) followed by the session ID etc. I’ve also specified the workspace (jk64) using the “c” query parameter.

In APEX 20.1 if you edit your Application Properties, you will see the new “Friendly URLs” setting.

With this setting turned on, URLs generated throughout the application will take this form (this is not exhaustive, there are other query parameters supported as well):

/pls/apex/jk64/r/sample_db_app/home
   ?myitems=myvalues
   &request=myrequest
   &clear=myclearcache
   &debug=mydebug
   &printerFriendly=myprinterfriendly
   &session=16801234560918
   &p_trace=mytrace
   &cs=19A56DBFDXXXE4DF293C96D786

When the documentation says the URLs should be “easier to read” I’m pretty sure they mean “by developers”, not end users. I suppose this means the URLs make more sense to developers of other web-based applications, because they conform better to REST URL conventions.

In its simplest form, the URL generated by APEX will include just the workspace path prefix, application, page, and session ID:

 /pls/apex/jk64/r/sample_db_app/home?session=16801234560918 

Calling APEX_PAGE.GET_URL generates the URLs correctly for the application according to whether the Friendly URL setting is on or not.

I noticed the following features of this new format:

  • The URL generated by APEX_PAGE.GET_URL now includes a full path (excluding the domain).
  • Instead of the “c” workspace query parameter, the application’s Path Prefix (which could be set to something other than the workspace name) is used (jk64 in my example). This is not the workspace name, although in most cases it usually will happen to be the same because it’s defaulted that way. This attribute is set at the workspace level, under Administration / Manage Service / Set Workspace Preferences / SQL Workshop.
  • If not specified, the application and page alias will be used rather than the application or page ID, which is nice.
  • Even if you specify the application or page alias in uppercase, APEX_PAGE.GET_URL returns them in all lowercase.
  • The more important attributes relevant to a user navigating the application are now further towards the start of the URL, such as page and item values, so they will be more likely to be noticed by the end user.
  • The “/r/” bit in the URL is just that. It’s just “r” and can’t be anything else, don’t ask me what it means. EDIT: apparently it stands for “router”

If your users have bookmarked your application using the legacy URL format, you can still safely upgrade your application to use Friendly URLs because both are still supported. This also means that if you have some old code that generates links programmatically they should still work the same (although it is best practice to call APEX_PAGE.GET_URL for this purpose).

In case you’re wondering, it is not possible to change the URL format when calling APEX_PAGE.GET_URL, it will follow your application’s Friendly URL setting. If you call APEX_PAGE.GET_URL for another application, it will return the correct format of URL for the target application. If you call APEX_PAGE.GET_URL for an application that does not exist, it will return the URL in the legacy format.

Existing applications after upgrading, or ones you import from an older version of APEX, will still use the legacy URL syntax. New applications will use the new Friendly URLs by default – but you can revert them to the legacy URLs if you wish.

On a side note, in earlier versions when you create a new application the application alias was set to the application ID by default. In APEX 20.1, a new application will have an application alias generated from the initial application name; when I tried it, it added a number as well for some reason.

For obvious reasons, existing code that parses the URL (e.g. in javascript on the client) will probably break. This is a fairly rare thing but does happen (such as in a plugin of mine which I’ll need to fix).

At this very early stage, the legacy URL format is still fully supported – I imagine it will eventually be deprecated, but not yet.

Finally, I’d like to point out some entries on the APEX Feature Requests site:

Our patience has been rewarded 🙂


Next/Previous Record: how APEX does it

It’s not a common requirement in my experience, but it does come up every now and then: a customer is happy with a simple Report + Form but when they open a record, they want to be able to make their changes and go to the “next” record in one button click, instead of having to go back to the report and select the other record.

In the Sample Database Application, page 29 (Order Details) implements “Next” and “Previous” buttons which allow the user to save and open another record in one action.

These buttons are implemented using a legacy Form Pagination process. This process sets some hidden items (P29_ORDER_ID_NEXT, P29_ORDER_ID_PREV, P29_ORDER_ID_COUNT) based on a query on a specified table (DEMO_ORDERS) with a specified unique identifier (ORDER_ID) associated with a primary key item (P29_ORDER_ID). The process requires a navigation order, specified by one or two columns (ORDER_ID, in this instance) in order to know what would the “next” and “previous” records be. If there is no next or previous record is found, the buttons are hidden.

When the page is submitted, the Next and Previous buttons submit a request (GET_NEXT_ORDER_ID or GET_PREVIOUS_ORDER_ID, respectively). After the ApplyMRU process has run, one of the relevant Branches will redirect the client back to the same page (p29) and set P29_ORDER_ID to either &P29_ORDER_ID_NEXT. or &P29_ORDER_ID_PREV. which causes the page to load the relevant record.

Some things to note with this approach:

  1. The legacy Form Pagination process is limited to a maximum 2 columns for the uniqueness constraint, and 2 columns for the navigation order.
  2. The navigation order of records will not match any custom sort order or filtering the user might have used on the report; so after the user opens the “first record” in the report, the form will not necessarily navigate to the “next record” that they might expect.
  3. The Next / Previous record IDs are queried when the page is initially loaded, so if anything has changed prior to the user clicking “Next” or “Previous”, it’s possible the user will inadvertently be directed to a record that is not actually “next” or “previous” to the record as it is now.
    In the worst case, if someone had deleted a record, the page would show “record not found”.
    In a perhaps less problematic case, if someone else has just inserted a new record with a unique identifier that happens to fall between the user’s previous record and the record they are navigating to, the user will effectively “skip over” the newly inserted record and might be led to believe it doesn’t exist.
  4. For large datasets there may be a performance penalty whenever each user loads the form since the page must issue additional queries to find the IDs for the “previous smaller” and “next larger” record, as well as to get the total number of records, and the position in the overall dataset of the current record (so it can set the “count” item to something like “8 of 10”). This involves the execution of a second query (in addition to the original query which gathered the data for the record being viewed).

These are not necessarily insurmountable or showstopping issues but should be kept in mind for forms using this approach.


With the new APEX Form feature, the above approach can still be used in much the same way – the attribute settings are a little different.

The page has a process before header of type Form – Initialization. This process has the following optional settings: Next Primary Key Item(s), Previous Primary Key Item(s), and Current Row/Total Item. To use this feature you must first create items (usually hidden) and then set these attributes to the item names.

Note that the Next / Previous Primary Key Item(s) attributes accept a comma-delimited list of items which allows them to support a compound key; I haven’t tested it but I expect this means it can support more than 2 columns.

The form will automatically populate these hidden items with the Order ID of the next and previous record, and will set the Current Row/Total Item to something like “8 of 10”. You can then use these items how you wish, e.g. as per the legacy pagination scheme, add the “Next” and “Previous” buttons, and create the navigation Branches to open the form with the relevant records.

To control the navigation order, you must set the Order By attribute on the form region. If this is not set, the navigation order is essentially unpredictable (APEX uses null as the sort order).

Same as the legacy pagination process, the form executes two queries: one to load the data for the form, and a second to gather the Next, Previous, Position and Count for the record, using SQL like this (I’ve simplified it a bit):

select i.*
from (
    select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ...
           lead(ORDER_ID,1) over (order by ORDER_ID) as APX$NEXT_1,
           lag(ORDER_ID,1) over (order by ORDER_ID) as APX$PREV_1,
           row_number() over (order by ORDER_ID) as APX$ROWN,
           count(*) over () as APX$TOTAL
    from (
        (select /*+ qb_name(apex$inner) */
                d.ORDER_ID, d.CUSTOMER_ID, d.ORDER_TOTAL, ...
         from (select x.* from DEMO_ORDERS x) d
    )) i 
) i where 1=1 
and ORDER_ID=:apex$f1

It should be noted that the comments above about how concurrent record inserts and deletes by other users, and about report filters and sorting, also apply to the new form process.


In the past I built a system where it was important that the “Next” / “Previous” buttons should allow the user to navigate up and down the records exactly as shown in the report, respecting user-entered filters and sort order. The approach I took was to gather the IDs into a collection and pass this to the form when the user opened a record. I described the implementation and limitations of this approach in an older blog post which I expect still works today: Next/Previous buttons from Interactive Report results


Loading large GeoJSON objects

A good question – how to load fairly largish GeoJSON documents into a Google Map in APEX?

To investigate this I started by downloading a source of GeoJSON data for test purposes – one containing the borders of countries around the world: https://datahub.io/core/geo-countries. This file is 23.5MB in size and contains a JSON array of features, like this:

(the data does not appear to be very accurate for a lot of countries, but it will do just fine for my purposes)

Uploading the file to the database

To load this file into my database I copied the file to the server and ran this to load the data into a temporary table:

create table import_lob_tmp (the_clob clob);

create directory dataload as '/home/oracle/dataload';

declare
   b bfile := bfilename('DATALOAD','countries.geojson');
   c clob;
   l_dest_offset integer := 1;
   l_src_offset integer := 1;
   l_bfile_csid number := 0;
   l_lang_context integer := 0;
   l_warning integer := 0;
 begin
   dbms_lob.open(b);
   dbms_output.put_line(dbms_lob.getlength(b));
   dbms_lob.createtemporary(c,true);
   dbms_lob.loadclobfromfile (
     dest_lob      => c,
     src_bfile     => b,
     amount        => dbms_lob.lobmaxsize,
     dest_offset   => l_dest_offset,
     src_offset    => l_src_offset,
     bfile_csid    => l_bfile_csid ,
     lang_context  => l_lang_context,
     warning       => l_warning);
   insert into import_lob_tmp values (c);
   commit;
   dbms_lob.fileclose(b);
   dbms_lob.freetemporary(c);
 end;
 /

Alternatively, I could also have created a temporary APEX application with a File Browse item to upload the file and insert it into the import_lob_tmp table.

Parsing the JSON

I wanted to get the array of features as a table with one row per country; to get this I used json_table; after a fair bit of muddling around this is what I ended up with:

create table country_borders as
select j.*
from import_lob_tmp,
     json_table(the_clob, '$.features[*]'
         columns (
             country varchar2(255) path '$.properties.ADMIN',
             iso_a3 varchar2(255) path '$.properties.ISO_A3',
             geometry clob format json
         )) j;

alter table country_borders modify country not null;  
alter table country_borders modify iso_a3 not null;
alter table country_borders modify geometry not null;  
alter table country_borders add
    constraint country_border_name_uk unique (country);
alter table country_borders add
    constraint geometry_is_json check (geometry is json);

The first JSON path expression allowed me to drill down from the document root ($) to the features node; this is an array so I added [*] to get one row for each entry.

The COLUMNS list then breaks down each entry into the columns I’m interested in; each entry consists of a type attribute (which I don’t need), followed by a more interesting properties node with some attributes which are extracted using some relative JSON path expressions; followed by the geometry node with the GeoJSON fragment that represents the country borders that I wish to store “as is” in a clob column.

Now if I query this table it’s interesting to see which countries are likely to have the most complex coastlines (at least, as far as the data quality provided in this file will provide):

select country,
       iso_a3,
       dbms_lob.getlength(geometry) geometry_size,
       geometry
from country_borders
order by 3 desc;

It should be noted that since I’ve extracted the geometry node from each feature, the resulting data in the geometry column do not actually represent valid GeoJSON documents. However, it’s easy to construct a valid GeoJSON document by surrounding it with a suitable JSON wrapper, e.g.:

'{"type":"Feature","geometry":' + geometry + '}'

Showing the GeoJSON on a map

The next step is to load this border data onto a map for display. I recently released version 1.1 of my Report Map Google Map plugin which adds support for loading and manipulating geoJSON strings, so I started by importing region_type_plugin_com_jk64_report_google_map_r1 into my APEX application.

I created a page with a region using this plugin. I set the map region Static ID to testmap. On the same page I added a text item, P1_GEOJSON, to hold the GeoJSON data; and a Select List item P1_COUNTRY with the following query as its source:

select country
       || ' ('
       || ceil(dbms_lob.getlength(geometry)/1024)
       || 'KB)' as d
      ,country
from country_borders
order by country

I added a dynamic action to the Select List item on the Change event to load the geometry from the table into the map. Initially, I added the following actions:

  1. A Set Value action that sets P1_GEOJSON to the result of the query:
    select geometry from country_borders
    where country = :P1_COUNTRY
  2. An Execute JavaScript action that loads the GeoJSON into the map (after first clearing any previously loaded features):
$("#map_testmap").reportmap("deleteAllFeatures");
$("#map_testmap").reportmap("loadGeoJsonString", 
    '{"type":"Feature","geometry":' + $v("P1_GEOJSON") + '}');

This technique works ok, but only for smallish countries where the GeoJSON of their borders is less than 4K in size. For countries with more border detail than can fit within that limit, the Set Value action query only loads part of the JSON data, resulting in an invalid JSON string – and so the map failed to load it. The Set Value action was therefore unsuitable for my purpose.

To load the entire CLOB data I used another plugin. There are a few CLOB load plugins available for APEX – search the Plugins list at apex.world for “clob”. I chose APEX CLOB Load 2 by Ronny Weiß.

I imported the plugin dynamic_action_plugin_apex_clob_load_2 into my application, then replaced the Set Value action with the action APEX CLOB Load 2 [Plug-In]. I set SQL Source to:

select /* Element type dom - for jQuery selector e.g. body or #region-id,
          item - for item name e.g. P1_MY_ITEM */
       'item' as element_type,
       /* jQuery selector or item name */
       'P1_GEOJSON' as element_selector,
       geometry as clob_value
from country_borders
where country = :P1_COUNTRY

I set Items to Submit = P1_COUNTRY and Sanitise HTML = No. I also set Selection Type = Region and select the map region so that the spinner is shown while the data is loaded.

Result

The plugin works well. The border for any country can be loaded (for some countries, it takes a few extra seconds to load) and drawn on the map:

If you would like to see this in action, you may play with it here: https://jk64.dev/apex/f?p=JK64_REPORT_MAP:GEOJSON


Getting Started with APEX Plugins

There is a great deal you can build in APEX without even thinking about using a plugin. The development platform has an excellent range of built-in functionality and components so that almost all user requirements can be covered using standard APEX features. Restricting yourself to only the built-in, declarative features (i.e. “low code”) of the tool will result in applications that are easy to maintain and resistant to regression issues when upgrading. However, there will come a time when some custom code is required, whether some special data processing (using PL/SQL), some special user interface styling (using CSS), or some special user interface behaviour (using JavaScript or jQuery).

After you have built an application with (a minimum of) custom code like this, it sometimes happens that the same user requirement crops up again elsewhere; whether on another page, another application, or another workspace. The first time you encounter this you will probably just do a “copy and paste” to replicate the custom code. When you encounter the requirement a third time, you should be asking yourself, “how can I make this code more re-usable” – because chances are one day you’ll need to fix a bug or make an enhancement to that code, and you’ll want it to be easy to deploy the change wherever it has been used. This is where an APEX plugin could be a suitable solution.

This post is mostly based on a talk given in October 2019 at the Australian Oracle User Group conference in Perth, Western Australia.

What is an APEX Plugin?

An APEX Plugin is a Shared Component that can be used in any number of pages in the application. When used, it generates a Region, an Item, a Process, or a Dynamic Action; or, it may implement an Authentication or Authorization Scheme. It is a self-contained set of attributes, code, and (optionally) file attachments. It may be copied to another application, or exported as a SQL script for importing into an application in another workspace or another APEX instance.

Where can I get started?

In my opinion, a great way to get started learning about plugins is to examine a lot of plugins that others have created. The two sources I would recommend you look at are:

  1. Install the Sample Database Application – go into its Shared Components -> Plugins, and look at each of the plugins therein. Look through each attribute and examine the code; read the help for each attribute as well. Look at how the plugins are used in the application.
  2. Browse the plugins at apex.world, and install a few that seem interesting to you.

When looking at these plugins, be aware that many of them have been created by people just like you and me – at differing levels of experience and skill; so they may or may not perfectly reflect “best practice”. This especially goes for the plugins published by yours truly :). In addition, depending on how long ago a plugin was written, it might be more complex than it needs to be, as it might not take advantage of recent enhancements to the APEX plugin API.

Plugin Concepts

Plugin Types

Plugin Type – a plugin may be for a page Region, a page Item, a page Process or a Dynamic Action; or it may implement an Authentication Scheme or an Authorization Scheme. For example, if you create or install an Item Plugin, it will appear in the list of item types available for adding to a page.

PL/SQL and Callbacks – if needed, you can embed PL/SQL code that APEX will call when rendering the plugin component or for other events such as handling an AJAX callback or validating an item.

The Callbacks attributes allow you to specify the name of the function for APEX to call; these might be functions that you define within the PL/SQL Code section, or they can call functions in a database package you have defined.

The functions you define must have the exact parameters and return types as documented in the help for each Callback attribute.

Standard & Custom Attributes – some plugin types have Standard Attributes (defined by APEX), and can have additional Custom Attributes (defined by you). For example, a Region plugin has standard attributes that define whether it supports a Data Source or supports the CSS Classes attribute.

If needed you can define up to 15 custom Application attributes. These would be set by the developer when they first install the plugin into their application, and their values apply to all usages of the plugin across their application.

You can also define up to 25 custom Component attributes, which are set on each usage of the plugin separately. The developer will find these in the Attributes node.

Each Custom attribute can be any of a long list of different types, including Text, TextArea, Yes/No, Number, Checkboxes, Select list, Item name, Icon, PL/SQL Code, JavaScript Code, and many others. You can provide a default value for each attribute if needed.

Files, JS and CSS – you can attach any number of resources including JavaScript, CSS scripts, images, or anything else that are required by your plugin. To load JavaScript or CSS files on pages where your plugin is used, you can simply enter them in the File URLs to Load, or call the APEX API routines in your render PL/SQL code (e.g. APEX_JAVASCRIPT.add_library or APEX_CSS.add_file).

When you export the plugin, all the attached resources will be included in the SQL script.

Plugin Events – if your plugin needs to allow your developers to add Dynamic Actions that respond to events raised by your plugin (e.g. to respond to user actions) you can define any number of custom Events here.

Walkthroughs

I’ve written these instructions to get you started creating plugins. They assume you are already reasonably familiar with building simple applications in APEX, and are comfortable editing PL/SQL.

Resources


User-editable Application Setting

A nice addition to APEX release 18.1 is the Application Settings feature. This allows the developer to define one or more configuration values that are relevant to a particular application. In a recent project this feature came in useful.

I had built a simple questionnaire/calculator application for a client and they wanted a small “FAQ” box on the left-hand side of the page:

I could have built this as an ordinary HTML region, but the Admin users needed to be able to modify the content later, so the content needed to be stored somewhere. I didn’t feel the users’ requirement was mature enough to design another table to store the boilerplate (not yet, at least), so I thought I’d give the Application Settings feature a go.

An Application Setting is a single value that can be set in Component Settings, and retrieved and modified at runtime via the supplied PL/SQL API (APEX_APP_SETTINGS). The feature is most useful for “configuration”-type data relevant to the application’s user interface. In the past I would have created a special table to store this sort of thing – and in some cases I think I still would – but in some cases using Application Settings may result in a simpler design for your applications.

I went to Shared Components, Application Settings and created a new Setting called “FAQ_BOILERPLATE“. Each Application Setting can have the following attributes configured:

  • Name – although this can be almost anything, I suggest using a naming standard similar to how you name tables and columns, to reduce ambiguity if you need to refer to the setting in your PL/SQL.
  • Value – at first, you would set this to the initial value; if it is changed, it is updated here. Note that the setting can only have one value at any time, and the value is global for all sessions. The value is limited to 4,000 bytes.
  • Value Required – if needed you can make the setting mandatory. In my case, I left this set to “No”.
  • Valid Values – if needed you can specify a comma-delimited list of valid values that APEX will validate against. In my case, I left this blank.
  • On Upgrade Keep Value – if you deploy the application from Dev to Prod, set this to Yes so that if a user has changed the setting your deployment won’t clobber their changes. On the other hand, set this to No if you want the value reset to the default when the application is deployed. In my case, I set this to Yes.
  • Build Option – if needed you can associate the setting with a particular build option. If the build option is disabled, an exception will be raised at runtime if the application setting is accessed.

On the page where I wanted to show the content, I added the following:

  1. A Static Content region titled “FAQ”.
  2. A hidden item in the region named “P10_FAQ_BOILERPLATE“.
  3. A Before Header PL/SQL process.

The Text content for the static content region is:

<div class="boilerplate">
&P10_FAQ_BOILERPLATE!RAW.
</div>

Note that the raw value from the application setting is trusted as it may include some embedded HTML; you would need to ensure that only “safe” HTML is stored in the setting.

The Before Header PL/SQL process has this code:

:P10_FAQ_BOILERPLATE := apex_app_setting.get_value('FAQ_BOILERPLATE');

Side note: a simpler, alternative design (that I started with initially) was just a PL/SQL region titled “FAQ”, with the following code:

htp.p(apex_app_setting.get_value('FAQ_BOILERPLATE'));

I later rejected this design because I wanted to hide the region if the FAQ_BOILERPLATE setting was blank.

I put a Server-side Condition on the FAQ region when “Item is NOT NULL” referring to the “P10_FAQ_BOILERPLATE” item.

Editing an Application Setting

The Edit button is assigned the Authorization Scheme “Admin” so that admin users can edit the FAQ. It redirects to another very simple page with the following components:

  1. A Rich Text Editor item P50_FAQ_BOILERPLATE, along with Cancel and Save buttons.
  2. An After Header PL/SQL process “get value” (code below).
  3. An On Processing PL/SQL process “save value” when the Save button is clicked (code below).

After Header PL/SQL process “get value”:

:P50_FAQ_BOILERPLATE := apex_app_setting.get_value('FAQ_BOILERPLATE');

On Processing PL/SQL process “save value”:

apex_app_setting.set_value('FAQ_BOILERPLATE',:P50_FAQ_BOILERPLATE);

The more APEX-savvy of you may have noticed that this design means that if an Admin user clears out the setting (setting it to NULL), since it has the Server-side Condition on it, the FAQ region will disappear from the page (by design). This also includes the Edit button which would no longer be accessible. In the event this happens, I added another button labelled “Edit FAQ” to the Admin page so they can set it again later if they want.

This was a very simple feature that took less than an hour to build, and was suitable for the purpose. Later, if they find it becomes a bit unwieldy (e.g. if they add many more questions and answers, and need to standardise the layout and formatting) I might replace it with a more complex design – but for now this will do just fine.

Related


Show null for switch items

An application I maintain needed a checklist feature added. I wanted to show a “Yes / No” switch for a list of checklist items. Initially, when the record is created, the checklist is populated with the questions along with a NULL for the response.

I generated the switches in an ordinary Classic report using code like this:

select r.name as risk_category
      ,apex_item.switch
         (p_idx        => 10
         ,p_value      => i.response
         ,p_on_value   => 'Yes'
         ,p_on_label   => 'Yes'
         ,p_off_value  => 'No'
         ,p_off_label  => 'No'
         ,p_item_id    => 'RESPONSE_' || rownum
         ,p_item_label => i.risk_category_code || '-' || i.rci_fk
         ,p_attributes => 'data-risk="' || i.risk_category_code || '"'
         )
       ||apex_item.hidden(p_idx => 11, p_value => i.rci_fk)
       as response
      ,i.question_text
from supplier_risk_checklist_items i
join risk_categories r on r.code = i.risk_category_code
where i.sri_fk = :P10_ID
order by r.sort_order nulls last, i.sort_order nulls last, i.rci_fk

I’ve used p_idx values of 10 and 11 in order to avoid conflicting with another tabular report on this particular page. The “response” column in the report has CSS Classes set to responseSwitch (this becomes useful later when we want to write javascript targeting just these items and nothing else on the page) and its Escape special characters attribute is set to No. The report when run looks like this:

Some of the responses are “Yes”, some “No”, and some are NULL (unanswered).

The problem is that all the NULL responses are indistinguishable from the “No” responses. If the user clicks “Yes” or “No”, the response is saved correctly – but the user cannot tell which ones haven’t explicitly been answered yet.

To find a solution for this issue I started by examining the HTML being generated for each question. I noticed that the input option for the “No” value was marked as “checked”, while the hidden input item had no “value” on it. These were the ones that needed fixing.

Example 1. Notice that the displayed radio button RESPONSE_10_N is “checked”, but the associated hidden input RESPONSE_10 has no value attribute.
Example 2. In this example, the displayed radio button RESPONSE_5_N is “checked”, but that’s ok because the hidden input RESPONSE_5 has the value “No” – so we don’t want to change this one.

In the page’s Execute When Page Loads, I search for all instances of responseSwitch where the hidden input item does not have a value attribute; in each case, I find the associated input item that shows “No” and unset the “checked” property:

// workaround for generated switch items showing "No" when value is null
// search for the hidden input items without a value (i.e. null on the database)
$(".responseSwitch input[name='f10']:not([value])").each(function(i){
    var id = $(this).attr("id");
    // these will have "checked" on the "No" option; remove it
    $(".responseSwitch input#"+id+"_N").prop("checked",null);
});

This makes it clear to the user which checklist items have been answered so far, and which ones haven’t.

Note: the user is given no way to unset an answer once it has been saved; if this were a problem I would change this to use an ordinary Select list item instead of a Switch item.