I didn’t change anything… yet I get “Unsaved changes”?

I had an APEX page based on a Form region that I’d built by hand (rather than using the wizard). I was wondering why the user always got an unexpected warning “Changes that you have made may not be saved.” – even though they hadn’t changed anything on the page.

I found Martin D’Souza’s article How to Find Which Item has Been Changed in APEX and ran the code in the browser console. This pointed me to a single item and I had a closer look at it.

I noticed that the item had a List of Values, and it had the Display Null Value setting set to No; however, the value in the underlying column was NULL. What was happening was that the item could not handle a null value, so it was changing to the first value in the LOV; this in turn marked the item as “changed” which caused the “unsaved changes” warning to show when the user tries to navigate away from the page.

When I set Display Null Value to Yes, the problem was resolved. Alternatively, I could have ensured that the underlying column would always have a value (e.g. by putting a NOT NULL constraint on it), which would also have resolved this problem.


APEX Map Region vs. Google Map Plugin

Within the APEX development team it has been known for some time that maps are frequently required and this has been on the roadmap to be built in to the core product.

Since 2016 I have built and maintained plugins that make it easy to integrate Google Maps in your APEX applications. The most popular of these is the ReportMap Plugin which has received a wealth of features and enhancements, and is being used widely.

Introducing: Map regions

In the latest pre-production release APEX 21.1 which you can try today for yourself at apex.oracle.com, the APEX development team has introduced the new Map region. This component allows the developer to show a world map on the page, and add one or more “Layers”, each based on a table or SQL query, to show features on the map.

This is a welcome move because it means maps will be easy for all APEX developers to use, including those who avoid plugins (e.g. due to the lack of direct support from Oracle). It also means that maps added to applications based on the core feature will be fully supported in future releases of APEX without needing to rely on a 3rd party.

Note: this post is based solely on my first impressions of the new feature and is not intended to answer all questions about it. There will be more official blog posts coming directly from the APEX development team about this and other new features being introduced in APEX 21.1, so keep a look out for those!

The APEX Map region renders the map using the MapBox API, with a number of tile backgrounds included based on OpenStreetMap and HERE map data.

Implementing a Map Region

Let’s try the new Map region, and compare it to the jk64 ReportMap plugin. To add a map to a page, I added a region of type “Map” and nominated a data source for the Layer to show on it.

First thing you might notice is what I didn’t need:

  • no 3rd party plugin to install
  • no account on a 3rd party cloud service
  • no API key, and no APIs need to be enabled
  • no credit card details

The Map region is free to use within your applications, just like any other built-in feature of APEX.

If you already have a map based on the ReportMap plugin, you can if you wish convert it to a map by simply changing the region Type to Map – the SQL data source will be preserved and you can use the same query for the new Map region type. However, the danger with this approach is that you may lose some attributes and customisations that you had on the plugin region. Instead, I recommend using the Duplicate feature to create a copy of your map region, change the attributes on the copy, using the original as a reference as you go. Once you’re happy with the new map you can then delete the original plugin region.

You can choose to set the data source directly on each Layer (e.g. if you want to get data from multiple data sources), or you can set the data source on the map Region. If you set it on the Region, each Layers may refer to the Region data source by setting the layer’s Location attribute to Region Source.

If you have a single data source that you want to split up into multiple Layers, you can set the Row Assignment column which will be used to differentiate the data for each layer. This would be more efficient than adding separate SQL queries for each layer if the queries are based on the same table or view.

The first thing I noticed when converting my demo application was that since my data source was based on an APEX collection, the Lat and Lng columns are strings, not numbers; to use this as a data source for the Map region I had to convert the strings to numbers first using TO_NUMBER.

Once that was done, I could map the LAT and LNG columns to the layer by setting the Geometry Column Data Type to Longitude/Latitude:

Notice that the layer may alternatively be based on a SDO_GEOMETRY column, or a GeoJSON geometry object.
Layer attributes

This is the result:

I wanted to style it as close as possible to my plugin demo so I tweaked a few attributes on the Region:

  • Appearance / Template Options / Remove Body Padding = on
  • Region Attributes:
    • Background = Custom
    • Standard (tile background) = Oracle World Map
    • Height = 350
    • Controls / Options / Mousewheel Zoom = on
    • Legend / Show = off
  • Layer Attributes:
    • Point Objects / Shape Scale = 1.5
    • Appearance / Fill Color = #e21818 (reddish)
    • Tooltip / Column = NAME
    • Info Window / Title Column = NAME
    • Info Window / Body Column = INFO

Much nicer colours, in my humble opinion 🙂

Map Dynamic Actions

The next thing I need to do is handle click events on the pins. To do this, I added a Dynamic Action to the map region, on the event Map Object Clicked.

Notice that the Map region can trigger the following events:

  • Map Initialized – the map has loaded, including all the data
  • Map Changed – the user pans the map or zooms in or out
  • Map Clicked – the user clicks in the map area (but not on a pin)
  • Map Object Clicked – the user clicks on a pin

Whenever I’m experimenting with dynamic actions one of the first things I’ll add is a Action to run this JavaScript:

At runtime in debug mode, the console log will then show the content of this.data which shows the structure it takes.

Based on this, I can see that we get the id, infoWindow, lat, lng, and tooltip attributes – similar to the structure provided by the ReportMap plugin events. If I want to display the Latitude and Longitude values on the page, I create a display-only item (e.g. P1_POSITION), and add a “Set Value” action to the dynamic action as follows:

When a pin is clicked, we now see this data on the page:

Of course, we could make this item hidden if we want, and use its value in other ways.

The Map Clicked event returns this.data.lat and this.data.lng to indicate where the user clicked.

The Map Changed event returns:

  • this.data.changeType – e.g. ‘map-resized’, ‘map-drag’, ‘map-zoom’
  • this.data.bbox – bounding box, an array of numbers of longitudes and latitudes [west, south, east, north]
  • this.data.layers – an array with attributes for each layer (id, name, visible)
  • this.data.bearing
  • this.data.pitch
  • this.data.zoom

New Features

The following is a short list of features or enhancements that the new Map region has, which are not (currently) supported by the ReportMap plugin:

  • Multiple data layers, each with their own distinct data source; you can also use one SQL query for multiple layers if you want
  • REST data sources
  • Declarative styles – options for customing how each data layer is rendered, including pin and feature icon, colour and size
  • Min/Max Zoom – each layer can be shown or hidden automatically depending on the zoom level (e.g. show a more detailed layer as the user zooms in)
  • Legend – each data layer (optionally) can be shown in a legend, allowing the user to selectively show/hide each layer
  • Extruded polygons – show a 3D polygon rising from the ground with the height determined by a column from your data source
  • Initial Position and Zoom is remembered between page requests
  • Circle tool – allows the user to draw a circle on the map; you can then respond to this by filtering data by points that fall within the circle
  • Distance tool – allows the user to calculate the distance between two points

Conclusion

That’s page 1 of my side-by-side demo done. Not too shabby!

Only 34 other pages to go over… eventually I plan to release my demo application for you to play with. Some of the demo pages will not be so easy or even possible to replicate, however, until we get the ability to use the Google Maps JavaScript API in the native Map region – so they’ll need to wait for some potential future release.

More posts to come…

Q: Should we switch now, or wait?

If you have a simple map on your page showing some pins, with no custom JavaScript code that provide specific custom behaviour, I think you should consider switching now. Of course, you should make sure to test the new region thoroughly – as with any major new feature, there will be a few bugs here and there. Overall however the Map region looks solid so far and is based on a well-known open source API with a long history.

One of the things to remember if you’re planning to switch from the ReportMap plugin to the new Map region is that not every feature is implemented the same way, and some features are not available at all (at least, not yet, if ever). This is partly because they are based on different JavaScript APIs (Google Maps vs MapBox). In addition, some UI behaviour is different and will likely always be different, although a lot of it will be customisable.

Q: Is this the end for the JK64 ReportMap plugin?

No, the ReportMap plugin is not going away any time soon. I plan to enhance and support it for years to come. My intent is to continue supporting the plugin at least until the APEX Map component supports integration of the Google Maps JavaScript API, and provides declarative support for most (ideally, all) of the declarative features of the ReportMap plugin. I expect that after this, most developers will naturally choose the native Map region over the plugin for new projects, and I’ll only need to provide bug fix support for the plugin while existing users of the plugin migrate gradually to the new component.

I consider this one of the strengths of the plugin system: the APEX development team will never have the capacity to support every last feature needed for every last application built for any customer in the world, so developers and companies should be empowered to create and use plugins to fill those gaps.

These plugins often serve to prove there is demand and need for a particular feature, and this often prompts the APEX development team to consider building these features into the core product, making it better with each release. Exciting times are ahead of us.


Pretty Print JSON in PL/SQL

I have been working with some code that uses JSON, sometimes fairly large documents of the stuff, and it’s often necessary to send this to the debug log (e.g. DBMS_OUTPUT) for debugging; however, the builtin functions that convert a JSON object to a string (or clob) return the JSON document in one big long line, like this:

{"surname":"Jones","name":"Bob","age":42,"alive":true,"children":[{"name":"Sally","age":10},{"name":"Terrance","age":8},{"name":"Ulyses","age":6}]}

To show this formatted, I added the following function using JSON_SERIALIZE with the “PRETTY” option, to my utility package:

function format_json (p_clob in clob) return clob is
    l_blob blob;
    l_clob clob;

    function clob_to_blob(p_clob clob) return blob is
        l_blob blob;
        o1 integer := 1;
        o2 integer := 1;
        c integer := 0;
        w integer := 0;
    begin
        sys.dbms_lob.createtemporary(l_blob, true);
        sys.dbms_lob.converttoblob(l_blob, p_clob, length(p_clob), o1, o2, 0, c, w);
        return l_blob;
    end clob_to_blob;

begin
    l_blob := clob_to_blob(p_clob);
    select JSON_SERIALIZE(l_blob returning clob PRETTY) into l_clob from dual;
    return l_clob;
end format_json;

Note that my function takes a CLOB, not a JSON object, because sometimes I receive the data already as a CLOB and I don’t want to require conversion to JSON before passing it to my formatting function.

Now, when I call this function:

declare
    l_json     json_object_t := json_object_t();
    l_children json_array_t := json_array_t();
    l_clob     clob;
begin
    l_json.put('surname','Jones');
    l_json.put('name','Bob');
    l_json.put('age',42);
    l_json.put('alive',true);
    l_children.append(json_object_t('{"name":"Sally","age":10}'));
    l_children.append(json_object_t('{"name":"Terrance","age":8}'));
    l_children.append(json_object_t('{"name":"Ulyses","age":6}'));
    l_json.put('children',l_children);
    l_clob := l_json.to_clob;
    l_clob := utility_pkg.format_json(l_clob);
end;

I get the following result:

{
  "surname" : "Jones",
  "name" : "Bob",
  "age" : 42,
  "alive" : true,
  "children" :
  [
    {
      "name" : "Sally",
      "age" : 10
    },
    {
      "name" : "Terrance",
      "age" : 8
    },
    {
      "name" : "Ulyses",
      "age" : 6
    }
  ]
}

Comparing Timestamps with Time Zone

If you break out into a sweat reading the title, it probably means that like me, you have had too little exposure to working with timestamps in Oracle.

(meme pretending that Gordon Ramsay says "I hate timezones ... in all timezones")

Until recently I never really had much to do with time zones because I lived in the (now even moreso, due to covid) insular state of Western Australia. In WA most places pretend that there is no such thing as time zones – so our exposure to Oracle data types is limited to simple DATEs and TIMESTAMPs, with nary a time zone in sight. We just set the server time zone to AUSTRALIA/Perth and forget it.

Now I’ve helped build a system that needs to concurrently serve the needs of customers in any time zone – whether in the US, in Africa, or here in Australia. We therefore set the server time zone to UTC and use data types that support time zones, namely:

  • TIMESTAMP WITH TIME ZONE – for dates and times that need to include the relevant time zone;
    and
  • TIMESTAMP WITH LOCAL TIME ZONE – for dates and times of system events (e.g. record audit data) that we want to always be shown as of the session time zone (i.e. UTC), and we don’t care what time zone they were originally created in.

A colleague came to me with the following issue: a business rule needed to check an appointment date/time with the current date; if the appointment was for the prior day, an error message should be shown saying that they were too late for their appointment. A test case was failing and they couldn’t see why.

Here is the code (somewhat obfuscated):

if appointment_time < trunc(current_time) then
    :p1_msg := 'This appointment was for the previous day and has expired.';
end if;

We had used TRUNC here because we want to check if the appointment time was prior to midnight of the current date, from the perspective of the relevant time zone. The values of appointment_time and current_time seemed to indicate it shouldn’t fail:

appointment_time = 05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth
current_time     = 05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth

We can see that the appointment time and current time are in the same time zone, and the same day – so the tester expected no error message would be shown. (Note that the “current time” here is computed using localtimestamp at the time zone of the record being compared)

After checking that our assumptions were correct (yes, both appointment_time and current_time are TIMESTAMP WITH TIME ZONEs; and yes, they had the values shown above) we ran a query on the database to start testing our assumptions about the logic being run here.

select
    to_timestamp_tz('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') as appt_time,
    to_timestamp_tz('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') as current_time
from dual

APPT_TIME    = '05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH'
CURRENT_TIME = '05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH'

So far so good. What does an ordinary comparison show for these values?

with q as (
    select
        to_timestamp_tz('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') as appt_time,
        to_timestamp_tz('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') as current_time
    from dual)
select
    q.appt_time,
    q.current_time,
    case when q.appt_time < q.current_time then 'FAIL' else 'SUCCESS' end test
from q;

APPT_TIME    = '05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH'
CURRENT_TIME = '05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH'
TEST         = 'SUCCESS'

That’s what we expected; the appointment time is not before the current time, so the test is successful. Now, let’s test the expression actually used in our failing code, where the TRUNC has been added:

with q as (
    select
        to_timestamp_tz('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') as appt_time,
        to_timestamp_tz('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') as current_time
    from dual)
select
    q.appt_time,
    q.current_time,
    trunc(q.current_time),
    case when q.appt_time < trunc(q.current_time) then 'FAIL' else 'SUCCESS' end test
from q;

APPT_TIME           = '05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH'
CURRENT_TIME        = '05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH'
TRUNC(CURRENT_TIME) = '03/05/2021'
TEST                = 'FAIL'

Good: we have reproduced the problem. Now we can try to work out why it is failing. My initial suspicion was that an implicit conversion was causing the issue – perhaps the appointment date was being converted to a DATE prior to the comparison, and was somehow being converted to the UTC time zone, which was the database time zone?

with q as (
    select
        to_timestamp_tz('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') as appt_time,
        to_timestamp_tz('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') as current_time
    from dual)
select
    q.appt_time,
    q.current_time,
    cast(q.appt_time as date),
    cast(q.current_time as date)
from q;

APPT_TIME                  = '05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH'
CURRENT_TIME               = '05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH'
CAST(APPT_TIME AS DATE)    = '03/05/2021 07:00:00 AM'
CAST(CURRENT_TIME AS DATE) = '03/05/2021 06:45:00 AM'

Nope. When cast to a DATE, both timestamps still fall on the same date. Then I thought, maybe when a DATE is compared with a TIMESTAMP, Oracle first converts the DATE to a TIMESTAMP?

with q as (
    select
        to_timestamp_tz('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') as appt_time,
        to_timestamp_tz('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') as current_time
    from dual)
select
    q.appt_time,
    q.current_time,
    cast(trunc(q.current_time) as timestamp with time zone),
    case when q.appt_time < trunc(q.current_time) then 'FAIL' else 'SUCCESS' end test
from q;

APPT_TIME                              = '05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH'
CURRENT_TIME                           = '05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH'
CAST(TRUNC(CURRENT_TIME) AS TIMESTAMP) = '05-MAR-2021 12.00.00.000000 AM +00:00'
TEST                                   = 'FAIL'

Ah! Now we can see the cause of our problem. After TRUNCating a timestamp, we have converted it to a DATE (with no timezone information); since Oracle needs to implicitly convert this back to a TIMESTAMP WITH TIME ZONE, it simply slaps the UTC time zone on it. Now, when it is compared with the appointment time, it fails the test because the time is 12am (midnight) versus 7am.

Our original requirement was only to compare the dates involved, not the time of day; if the appointment was on the previous day (in the time zone relevant to the record), the error message should appear. We therefore need to ensure that Oracle performs no implicit conversion, by first converting the appointment time to a DATE:

with q as (
    select
        to_timestamp_tz('05-MAR-2021 07.00.00.000000 AM AUSTRALIA/Perth') as appt_time,
        to_timestamp_tz('05-MAR-2021 06.45.00.000000 AM AUSTRALIA/Perth') as current_time
    from dual)
select
    q.appt_time,
    q.current_time,
    case when cast(q.appt_time as date) < trunc(q.current_time) then 'FAIL' else 'SUCCESS' end test
from q;

APPT_TIME                              = '05-MAR-2021 07.00.00.000000000 AM AUSTRALIA/PERTH'
CURRENT_TIME                           = '05-MAR-2021 06.45.00.000000000 AM AUSTRALIA/PERTH'
TEST                                   = 'SUCCESS'

Our logic therefore should be:

if cast(appointment_time as date) < trunc(current_time) then
    :p1_msg := 'This appointment was for the previous day and has expired.';
end if;

It should be noted that if the tester had done this just an hour later in the day, they would not have noticed this problem – because Perth is +08:00, and the timestamps for the test data were prior to 8am in the morning.

Lesson #1: in any system that deals with timestamps and time zones it’s quite easy for subtle bugs to survive quite a bit of testing.

Lesson #2: when writing any comparison code involving timestamps and time zones, make sure that the data types are identical – and if they aren’t, add code to explicitly convert them first.


Some collection methods I’d like to see added to PL/SQL

In building a code generator I found the need to write a number of helper methods for doing basic modifications of arrays that are indexed by integer – such as appending one array onto another, inserting, and shifting. These arrays represent an ordered sequence of strings (e.g. lines of source code).

I think these would be a useful addition to the language if they were made native – e.g. (new commands in UPPERCASE):

declare
    type str_array_type is table of varchar2(32767)
        index by binary_integer;
    l_lines str_array_type;
    l_new str_array_type;
    l_idx binary_integer;
begin
    .. (some code to fill the arrays here) ..

    -- get a subset of lines
    l_new := l_lines.SLICE(50, 59);

    -- extend l_lines with l_new at the end:
    l_lines.APPEND(l_new);

    -- shift l_lines forwards and insert l_new
    -- at the beginning:
    l_lines.PREPEND(l_new);

    -- insert l_new into l_lines at the given index;
    -- shift any existing lines at that location
    -- forward:
    l_lines.INSERT(l_new, at_idx => 21);

    -- remove the given range of indices from
    -- l_lines, replace with whatever is in l_new:
    l_lines.UPDATE(l_new,
        from_idx => 120,
        to_idx   => 149);

    -- apply the given substitution on each line
    l_lines.REPLACE_ALL(
        old_val => 'foo',
        new_val => 'bar');

    -- shift the given range of lines by the given
    -- offset (raise exception if existing data
    -- would get overwritten):
    l_lines.SHIFT(
        from_idx => 20,
        to_idx   => 29,
        offset   => 1000);

    -- shift and renumber all indices in the array
    -- with the given starting index and increment:
    l_lines.RENUMBER(start_idx => 10, increment => 10);

    -- make the array contiguous (i.e. remove gaps):
    l_lines.RENUMBER;

    -- loop over every line in the array that contains
    -- the given string:
    l_idx := l_lines.FIND_NEXT(contains => 'hello');
    loop
        exit when l_idx is null;
        .. do something with l_lines(l_idx) ..
        l_idx := l_lines.FIND_NEXT(contains => 'hello',
            from_idx => l_idx);
    end loop;
end;

I’ve illustrated these with a little sample package that may be viewed here:

https://github.com/jeffreykemp/sample/tree/master/str_array_pkg

What do you think?


ReportMap Release 1.5

The latest version of the ReportMap APEX plugin is now v1.5.

The plugin now supports Overlays, which allows you to add almost any arbitrary HTML or image content to particular points on the map. For example, instead of showing the default red pin, you might want to show a pretty Information card at a location. The cards are clickable as well, and you can use a dynamic action to make your app respond however you wish.

You can even show an image as a map overlay, which will be scaled automatically as the user zooms and pans the map.

In the previous release I added a companion Dynamic Action “Action” plugin that allows you to declaratively perform actions on the map. This release fixes a few bugs in that plugin, and also adds a new, second companion Dynamic Action plugin: Show Directions. This plugin allows you to invoke the Directions API on the map between one location and a destination without needing to write JavaScript. The locations may be specified with static values (lat,lng coordinates or addresses), from items on your page, JavaScript expressions, or jQuery selectors. The travel mode (e.g. driving, bicycling, etc.) can also be specified the same way.

You can review the full list of changes and download the plugin from here: https://github.com/jeffreykemp/jk64-plugin-reportmap/releases/tag/v1.5


Speaking at AUSOUG 2021

The Australian Oracle User Group is hosting a series of webinars this year and on 17 February I’ll be speaking about how to add an interactive Google Map to your APEX application. If you’re using APEX and want to see how easy it can be to integrate Google Map capabilities into your application, this is for you.

My session is at 9am in Perth time (1am UTC) – which is:

  • 6:30am in India
  • 9:45am in Eucla
  • 10:30am in Darwin
  • 11am in Brisbane
  • 11:30am in Adelaide
  • 12pm in Sydney, Melbourne, Canberra and Tasmania
  • 2pm in New Zealand

For more information about the AUSOUG National Webinar Series, go to: https://ausoug.org.au/home/2021-webinar-series/national-webinar-series-2021/

To register for this session, go to: https://register.gotowebinar.com/register/6201790250674031374


Short circuit evaluation and the great Unknown

One of the nice things about PL/SQL is that it implements short circuit evaluation, a performance enhancement which takes advantage of the fact that an expression using logical AND or OR does not necessarily to evaluate both operands to determine the overall result.

For an expression using AND, if the first operand is not TRUE, the overall expression cannot be TRUE; for one using OR, if the first operand is TRUE, the overall expression must be TRUE. In the case of AND, what if the first operand is Unknown? It seems to depend on how the expression is used.

In my examples below, I have an expression that looks up an array. When an array is accessed with a key that is null, it will raise an exception. If short circuit evaluation is applied, the array is never accessed so the exception is not raised.

In the first example below, the PL/SQL engine never evaluates the second expression since the first expression ('abc' = p_param) is not TRUE:

declare
    p_param varchar2(10);
    l_result boolean := false;
    l_arr apex_t_varchar2;
begin
    if 'abc' = p_param and l_arr(p_param) = 'bla' then
        l_result := true;
    end if;
end;

This does not apply if the expression is being assigned to a variable. In the second example below, the exception ORA-06502: PL/SQL: numeric or value error: NULL index table key value is raised:

declare
    p_param varchar2(10);
    l_result boolean;
    l_arr apex_t_varchar2;
begin
    l_result := 'abc' = p_param and l_arr(p_param) = 'bla';
end;

If the first expression were to result in FALSE, it runs without error. If the first expression is Unknown (NULL), the second operand must be evaluated to determine whether to assign FALSE or NULL to the result.

A workaround is to use an IF statement to make the evaluation order explicit:

declare
    p_param varchar2(10);
    l_result boolean := false;
    l_arr apex_t_varchar2;
begin
    if 'abc' = p_param then
        l_result := l_arr(p_param) = 'bla';
    end if;
end;

Thanks to Connor for clearing up my understanding for this one.

8/10/2020 updated with better example code – instead of comparing to a literal null (which is never right), we compare to a variable which may or may not be null at runtime.


DBMS_MVIEW.explain_mview with no MV_CAPABILITIES_TABLE

Need to run DBMS_MVIEW.explain_mview in APEX SQL Workshop, but don’t have the MV_CAPABILITIES_TABLE? You’ll get this error:

ORA-30377: table ORDS_PUBLIC_USER.MV_CAPABILITIES_TABLE not found

You don’t need to create this table. You could create this table by running admin/utlxmv.sql (if you have it). Instead, you can get the output in an array and do whatever you want with its contents, e.g.:

declare
    a sys.ExplainMVArrayType;
begin
    dbms_mview.explain_mview('MY_MV',a);
    dbms_output.put_line('Explain MV '
        || a(1).mvowner || '.' || a(1).mvname);
    for i in 1..a.count loop
        dbms_output.put_line(
            rpad(a(i).capability_name, 30)
            || ' [' || case a(i).possible
                       when 'T' then 'TRUE'
                       when 'F' then 'FALSE'
                       else a(i).possible
                       end || ']'
            || case when a(i).related_num != 0 then
                   ' ' || a(i).related_text
                   || ' (' || a(i).related_num || ')'
               end
            || case when a(i).msgno != 0 then
                   ' ' || a(i).msgtxt
                   || ' (' || a(i).msgno || ')'
               end
        );
    end loop;
end;

The result will be something like this:

Now, the challenge is merely how to resolve some of those “FALSEs” …


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
  • 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.