Checking If a Region’s Data Source Returned Any Data

Oracle APEX lets application developers easily use any data they need. A region’s data can come from a local table or view, the rows returned by a SQL query, a REST service’s response payload, or rows from a table, view or query sent to a remote, REST-enabled SQL database. APEX also supports the conditional visibility of regions and page items using a declaratively-configured “server-side condition”. Let’s consider a use case that combines these features to show or hide a region based on whether it contains any data. In other words, if the region’s data source returns rows, then show it, otherwise hide it.

At first, the solution sounds simple. One of the Server-side Condition types is “Rows Returned”. This configures the region to be processed and displayed when at least one row is returned from the SQL Query that you type into the box below. This works great for regions based on local data that the user cannot filter further, but if the end-user can filter the data using interactive report/grid filtering, a faceted search, or smart filters, how can this “Rows returned” query correctly reflect the end-user’s active filters applied? What’s more, if the region uses a REST service or REST-enabled SQL data source, then what SQL Query do we type in here? The APEX engine executes the Server-side condition’s “Rows returned” query against the local database, so it won’t be useful to determine if the remote data source returns data. So we need another strategy when a region employs remote data or might be actively filtered by the end-user.

Server-side Condition “Rows returned” query is executed against the local database

I didn’t find any ready-made APEX API to inquire whether a region’s data source returned data, so I set out to write my own. Luckily, I had recently read my colleague Carsten’s great article Add a Chart to your Faceted Search Page. There he explains a technique to produce a chart of exactly the same filtered results as another report region on the page that has been filtered using a faceted search region. The crux of his clever plan involves using the open_query_context() function in the apex_region package to obtain a logical cursor over that region’s data, which can be used to process its data one row at a time. The cursor-like “query context” returned by this API hides the underlying details of how the data is retrieved, so it works the same way no matter how the region is configured to source its rows. It also conveniently encapsulates the current state of the user’s applied filters, if any.

In Carsten’s article, the chart needs all of the other region’s rows to visualize them graphically. However, for our purposes we can use a simplified version of his approach that just attempts to retrieve a single row to see whether or not the region has data or not. The has_data() function in the region_utils package below implements the idea. When someone calls has_data('CHART'), for example, passing in the static id of a region on the current page, the function…

  1. looks up the region id using the region’s static id and the id of the current page
  2. opens a query context “cursor” on the region’s data source indicating that it wants to retrieve at most one row (p_max_rows=>1),
  3. tests whether the cursor has at least one result row by calling next_row(),
  4. closes the context, and
  5. returns the result of whether or not a first row was returned.
create or replace package region_utils is
  function has_data(p_region_static_id varchar2) return boolean;
end;
/
create or replace package body region_utils is
    function boolean_to_string(p_value boolean) return varchar2 is
    begin
        return case when p_value then 'TRUE' else 'FALSE' end;
    end;
    --
    function has_data(p_region_static_id varchar2) return boolean
    is
        l_context     apex_exec.t_context;
        l_region_id   apex_application_page_regions.region_id%type;
        l_return      boolean;
        l_page_id     number := v('APP_PAGE_ID');
        l_app_id      number := v('APP_ID');
    begin
        -- 1. Lookup region id using static id & page id
        select region_id
        into l_region_id
        from apex_application_page_regions
        where application_id = l_app_id
          and page_id        = l_page_id
          and static_id      = p_region_static_id;
        -- 2. Open "cursor" on region's data source (max 1 row!)
        l_context := apex_region.open_query_context(
                       p_page_id   => l_page_id,
                       p_region_id => l_region_id,
                       p_max_rows  => 1);
        -- 3. Test if there is a next (i.e. first) row.
        l_return  := apex_exec.next_row( p_context => l_context );
        -- 4. Close the context "cursor"
        apex_exec.close( l_context );
        apex_debug.info('--- region_utils.has_data(''%s'') = %s',
                        p_region_static_id,
                        boolean_to_string(l_return));  
        -- 5. Return whether we got a row or not
        return l_return;
    exception when others then
        apex_exec.close( l_context );
        raise;
    end;
end;
/

With this package in place, we can configure a chart region having static id of CHART to have a Server-side Condition using the “Expression” type as shown below to include the single line of PL/SQL code that uses our new has_data() function:

Using new has_data() function in a PL/SQL Expression for Server-side Condition

This approach worked great for a page that did not need the chart region’s data to be refreshed based on actions the end-user takes while using the page. When I tried to employ it on a page with interactivity, there was a learning opportunity awaiting me.

Consider the example below where the user can choose a department from a select list and immediately see a pie chart of all of the employees salaries in that department. Here both the shared component LOV for department names and the employees chart region below are based on REST-enabled SQL using a remote database.

Refreshing the chart region when Department changes

What I discovered from trying the Server-side Condition mentioned above with this more dynamic example was that when the condition evaluates to false, the chart component was left out of the page completely (as expected, perhaps!) so in some cases it was not present in the browser HTML page for my dynamic action on the select list to interact with.

To get the more dynamic use case working how I wanted it, I abandoned the idea of using the Server-side Condition to hide the chart, and resorted instead to using dynamic actions to conditionally hide and show the chart region based on the result of calling region_utils.has_data('CHART'). I introduced a hidden page item P1_CHART_HAS_DATA and added an additional function to my region_utils package has_data_as_string() to return the result of the has_data() function as either the string TRUE or FALSE for easy storage in this new, hidden page item. Then I used a Client-side Condition on several dynamic action steps to test the value of this P1_CHART_HAS_DATA page item using the JavaScript expression:

$v('P1_CHART_HAS_DATA') === 'TRUE'

The figure below shows the four-step dynamic action event handler for the Change event on the P1_DEPTNO select list page item. The first step uses has_data_as_string('CHART') to set the value of the hidden P1_CHART_HAS_DATA page item, submitting the P1_DEPTNO page item value since the region’s query depends on it. Then the subsequent three steps use a Client-side Condition JavaScript expression to conditionally Refresh the chart region if it had any data to graph, conditionally Show the chart region if it had data, and conditionally Hide the chart region if it did not have data. In this last situation, the JavaScript expression tests if $v('P1_CHART_HAS_DATA') === 'FALSE' while the previous two steps tested for 'TRUE'.

Dynamic action event handler on the Change event of the P1_DEPTNO select list to set value of P1_CHART_HAS_DATA and conditionally refresh, show, or hide the chart

After making the Hide dynamic action step Fire on Initialization to initially hide the chart region when the P1_DEPTNO select list has its default null value, this combination achieved the result I was looking for.

You can download the example app, and during import you will need to configure its REST-enabled SQL endpoint named slc12kos to point to your own REST-enabled SQL endpoint that has the EMP and DEPT tables available.

One tip my colleague Carsten mentioned when I was asking him questions about this approach was that if the chart region’s data were coming from a REST service instead of a REST-enabled SQL source, that would give you some additional options to use APEX’s periodic REST synchronization feature or REST source caching to reduce the number of HTTP requests between the APEX engine and the remote data endpoint when rendering the chart region in this example.

The final source for the region_utils package looks like the code below. For more information on enabling REST-Enabled SQL in your ORDS listener, check out Tim Hall’s video on Setting up REST-Enabled SQL over on his Oracle-Base blog.

create or replace package region_utils is
  function has_data(p_region_static_id varchar2) return boolean;
  function has_data_as_string(p_region_static_id varchar2) return varchar2;
end;
/
create or replace package body region_utils is
    function boolean_to_string(p_value boolean) return varchar2 is
    begin
        return case when p_value then 'TRUE' else 'FALSE' end;
    end;
    --
    function has_data(p_region_static_id varchar2) return boolean
    is
        l_context     apex_exec.t_context;
        l_region_id   apex_application_page_regions.region_id%type;
        l_return      boolean;
        l_page_id     number := v('APP_PAGE_ID');
        l_app_id      number := v('APP_ID');
    begin
        -- 1. Lookup region id using static id & page id
        select region_id
        into l_region_id
        from apex_application_page_regions
        where application_id = l_app_id
          and page_id        = l_page_id
          and static_id      = p_region_static_id;
        -- 2. Open "cursor" on region's data source (max 1 row!)
        l_context := apex_region.open_query_context(
                       p_page_id   => l_page_id,
                       p_region_id => l_region_id,
                       p_max_rows  => 1);
        -- 3. Test if there is a next (i.e. first) row.
        l_return  := apex_exec.next_row( p_context => l_context );
        -- 4. Close the context "cursor"
        apex_exec.close( l_context );
        apex_debug.info('--- region_utils.has_data(''%s'') = %s',
                        p_region_static_id,
                        boolean_to_string(l_return));  
        -- 5. Return whether we got a row or not
        return l_return;
    exception when others then
        apex_exec.close( l_context );
        raise;
    end;
    function has_data_as_string(p_region_static_id varchar2) return varchar2 is 
    begin
        return boolean_to_string(has_data(p_region_static_id));
    end;
end;
/

Adding Tags Using Multi-Value PopupLOVs and Smart Filters

Overview

Google’s Gmail was the first app I remember that popularized users’ inventing their own descriptive labels and applying them as “tags” to data. Instead of moving an email from the “Inbox” folder into one named “Charitable Donations 2019 USA”, for example, it encouraged me to tag the message with multiple, distinct labels like “2019”, “taxes”, “charitable donations”, and “USA”. While not initially as intuitive as folders, this approach quickly proved its value. It let me quickly locate mails related to a particular year, to charitable donations, to taxes in general, or to the US, or any combination of those criteria.

For the same reasons, a flexible tagging facility comes in handy for many kinds of data we work with everyday. Whether it’s pictures in a photo library, products in an online store, or books in your collection, tags that help users quickly find what they are looking for are a boon. When combined with Oracle APEX’s powerful faceted search and Smart Filters capabilities, it packs a powerful productivity punch.

In this article, I explain the technique I used over the holidays to add a flexible tagging facility to my art tracker application using a no-code approach that takes advantage of APEX’s multiple value support in popup LOV page items and Smart Filters. Here, we’ll apply the approach to a simple application that tracks books, publishers and authors. You can find the link to download the example application at the end of the article.

Book Finder page featuring Cards region showing title, authors, publisher, and tags

Multi-Value PopupLOV for Tags

The BOOK_TAGS table defines an ID and NAME for the descriptive tags you can apply to books. The sample data includes pre-defined tags like “Trains” (2), “Travel Narrative” (1), “Children” (5), “Fiction” (6), and others.

The BOOK_TITLES table contains a book’s TITLE, the PUBLISHER_ID, and a TAGS column, whose value is a colon-separated list of tag ids. For example, a book that is a fictional travel narrative about riding on trains might have the value “6:1:2” representing the id values of the three tags “Fiction”, “Travel Narrative”, and “Trains”. Since the order of the the tags is not significant to our use case, it also might have the value “1:2:6” depending on the order in which the end-user added the tags to the list.

The figure below shows how I configured the P3_TAGS page item in page 3’s form region to support visualizing and editing the possibly-multiple tags applied to a book. Notice that the page item type is Popup LOV, the Multiple Values property is enabled, the literal colon character (:) is indicated as the value Separator, and the Search as You Type property is on. I’ve configured a shared component TAGS_LOV List of Values to provide the alphabetized list of available tags.

Configuring multi-value popup LOV page item in an edit form page

Pay special attention to the Manual Entry setting above. We’ve consciously disabled that property since by design at the moment APEX shows the end user the underlying values (e.g. 6 , 1 , 2 ) for a manual-entry popup LOV instead of showing their corresponding display text values (e.g. Fiction , Travel Narrative, Trains ). That may be appropriate for some multi-value use cases where the LOV entry’s display and return values are the same, but here I preferred that my end-users would see the tag display values.

The result is the easy-to-use book editing page shown below. Notice that the P3_AUTHOR_IDS page item is configured in the same way as P3_TAGS to allow entering the colon-separated list of one or more author ids for the book.

Multi-value popup LOV page items editing book details at runtime

Multi-Value Smart Filter for Tags

APEX makes it simple to easily filter on one or more tags applied to records in both faceted search as well as the new Smart Filters region. The figure below shows how the P5_TAGS smart filters search facet in the Book Finder cards region page (5) is configured to enable this behavior. Notice the search facet Type is set to Checkbox Group, the Multiple ValuesType is set to Delimited List with the Separator configured to be a literal colon character (:), and we’ve enabled the Trim Whitespace option.

Configuring multi-value P5_TAGS smart filter facet to handle colon-delimited values

This is the only configuration necessary to get the tags field working for searching. The result produces a smart filter search field (above our cards region) on page 5 in the example app that looks like the figure below. Ticking one or more tags in the list narrows the search results to show only books having that/those tags applied (in any order).

Checkbox Group smart filters facet to search for books by one or more tags applied.

As above, the P5_AUTHOR_IDS search facet has been configured identically to the P5_TAGS one to allow narrowing down the search results by any combination of authors and/or tags as shown below where we’ve found books authored by Brian Spendolini about APEX.

Applying two multi-value smart filters: one for authors, one for tags

Displaying Multi-Value Fields in Report Regions

When working with multi-value columns like TAGS and AUTHOR_IDS in our BOOK_TITLES example table, it’s useful in report pages or card regions to show the list of display values corresponding to the one-or-more ids stored in the colon-separated column value. For this task, I employed the handy LISTAGG() function to aggregate the set of related tag display values and author names into an ordered, comma-separated list. I combined it with the useful split_numbers() function in the apex_string utility package. When wrapped by a table() operator, this helpful routine lets us select the numbers in the colon-separated list as a table row source right in the query. To make it easier to use this information from any report or card regions where I needed it in my application, I created the BOOK_TITLES_V database view with the following SELECT statement.

select 
    b.id,
    b.title,
    b.tags,
    b.rating,
    b.publisher_id,
    b.author_ids,
    (select listagg(name,', ')
            within group (order by name collate binary_ai)
       from book_tags 
      where id in (
        select column_value 
          from table(apex_string.split_numbers(b.tags,':'))
        )
    ) display_tags,
    (select listagg(name,', ')
            within group (order by name collate binary_ai)
      from book_authors 
     where id in (
       select column_value 
         from table(apex_string.split_numbers(b.author_ids,':')))
    ) display_authors,
    p.name as publisher
from book_titles b
left outer join book_publisher p 
             on p.id = b.publisher_id

Notice that I’m passing the literal colon character as the separator to the apex_string.split_numbers() function in two places, and passing the separator string consisting of a comma followed by a space to the listagg() function. The collate binary_ai keywords in the order by part of the listagg function’s within group clause ensures that display values sort in an accent-insensitive (and case-insensitive) way.

Enabling Custom PopupLOV Behavior via CSS Class

I had achieved my goal without writing any code and was happy with how simple it was to implement… when I noticed an interesting extra-credit opportunity that piqued my interest.

While interacting with the PopupLOV component, I observed that its Search as You Type filter remained “sticky” across multiple interactions with the dropdown list of choices. For example, consider the screenshot below where I was editing the tags for a Bill Bryson book. If I typed tr into the PopupLOV search field, as expected the list narrowed down to only show relevant tags “Trains” and “Travel Narrative”. However after choosing “Trains”, if I clicked again into the multi-value field to drop-down the list to choose another tag to apply, the list remained filtered as before to only those tags containing tr in their names. However, often the next tag I wanted to apply required me to clear the previous search field text to start fresh with the complete list of tags to choose from or search through.

Search as You Type filter in use in a PopupLOV for tags applied to a book.

After not finding any declarative PopupLOV setting to control the “stickiness” of the search field value, I began by experimenting with various ideas using dynamic actions to force the PopupLOV search field to clear. Not satisfied with the results, I reached out to my colleague John for some expert advice.

He suggested I implement generic JavaScript code in an app.js static application file that would automatically enable a custom behavior whenever a PopupLOV page item had a particular custom CSS class applied to it. This way, the code was reusable and enabled declaratively by simply adding a CSS class name like popup-lov-reset when the non-default search-field-resetting behavior was desired. The figure below shows the modal Edit Book page (12) called by the full card action on the card region of the Book Finder page (5). Notice the custom CSS class name popup-lov-reset in the AdvancedCSS Classes section. This is the signal to our generic code in app.js that this particular PopupLOV prefers the reset-search-field behavior each time the user engages the page item’s dropdown list.

Opting-in to custom application behavior by adding a custom CSS class to a page item

If you’re curious, you can study the full details of the custom JavaScript code John helped me with in the downloaded example app. However, most important were the higher-level principles he taught me along the way about how he recommends structuring application-level JavaScript code. Since I’m not a JavaScript expert, these were the even more interesting bits of precious knowledge that I felt fortunate to learn from him.

The high-level structure of the code in the example application’s app.js file appears below. It exposes a single app namespace inside of which can appear private functions specific to its implementation. This app namespace exports only the members it wants to be the public API. In this application, only the single function handlePopupLOVsWithSearchResetClass() is exported.

// Export just a single global symbol "app" to keep code clean
const app = (function($) {

    // Function private to the impl of the
    // exported 'app' namespace

    function makeResetPopupLov(itemName) {
      // Code removed for clarity here registers an event
      // handler on body of the page to react to the popupopen
      // event of PopupLOV page item in question.
      // See example app for full details.
    }

    // app namespace members
    return {
        //----------------------------------------------------
        // Turn any PopupLOV page items on page into ones that
        // reset their search field when dropdown pops open. 
        //----------------------------------------------------
        handlePopupLOVsWithSearchResetClass: function() {
            $(".popup-lov-reset.apex-item-popup-lov").each(
                function() {
                  makeResetPopupLov(this.id);
                }
             );
        }
    };
    // Ensure $ in app namespace resolves to correct jQuery
})(apex.jQuery);

If we include the “document ready” event handler code below inside the app.js file, then all pages in the application magically inherit the ability to have any PopupLOV page item on the page opt-in to reset-search-field behavior just by adding the CSS class name popup-lov-reset in the page item’s AdvancedCSS Classes section. In contrast, if you only want the functionality to be available on selected pages, then include this event handler code just on the specific pages where you want the behavior to be available.

// Inside app.js, runs for every page after document is ready
// Alternatively, you can just add to the pages where you want
// the behavior to be available.
//
// "Document Ready" event handler code
$(function() {
    app.handlePopupLOVsWithSearchResetClass();
});

If you want to try out the example for yourself, then download the APEX 21.2 example application here.

Capturing Lat/Long Address Info Without a Map

The new APEX 21.2 Geocoded Address page item makes it easy to translate user-entered address information into a point you (or your user) can see on a map. After the user-entered address information has been geocoded and the user has confirmed which matching address is correct, the value of the Geocoded Address page item is the text of a GeoJSON document representing the point on the map for that address.

Your application can decide whether the end-user will provide their address input in one of two ways:

  • A single “unstructured” page item containing street, city, and postal code info, or
  • Separate “structured” page items for street, city, and postal code

In either case, the country can be fixed by your application, or can be a value from a Pn_COUNTRY page item into which your user enters (or chooses) a country.

For example, entering the address in a single field might look like the screenshot below this for your end-users, separating the street, postal code, and city by a comma as they enter the address information. Of course, in your application the latitude and longitude fields will likely be configured to be hidden and be related to corresponding columns in the underlying table. Here in the sample app I’ve built, they are just transient page items to show off the geocoding result.

By default, submitting the form automatically triggers the geocoding, but you can also configure the geocoded address page item to be triggered by a Dynamic Action. After configuring this page item property, to complete the functionality you then need to add a dynamic action event handler — for example, a Click event handler for a button — that includes an action step of type Trigger Geocoding.

The geocoded address page item includes a map display of the point that the user has confirmed. If your underlying table has a column of type SDO_GEOMETRY then the GeoJSON value of the geocoded address will effortlessly be saved by the default APEX DML process for the form region. So, these defaults provide a no-code experience that caters to the most common situation.

However, what happens if you are working with a data model that requires storing the longitude and latitude of the point in separate LONGITUDE and LATITUDE columns? And what if you don’t want to immediately display the point on the map, but only capture the numerical coordinates. Then read on to discover how to accomplish these two tweaks to the default behavior.

We can accomplish both tasks in a completely declarative way, but we’ll take the second part first since it’s the simpler of the two. To hide the map, there are two steps:

  1. Set the application-level component settings for the Geocoded Address page item to disable the Map Preview
  2. Set the AppearanceCSS Classes property on the item so that even the read-only display field is hidden.

The Geocoded Address component exposes some application-level configuration properties that you can set in the Shared ComponentsComponent SettingsGeocoded Address section of the APEX builder. In the screenshot below, you can see I’ve unchecked the Item checkbox in the Map Preview section. This ensures that not only will the map be visually hidden, but also that browser will avoid retrieving any map tile images. Also notice I’ve configured the Geocoder Match Mode to the value Relax All to give the most chance of matching the address on all pieces of information the user supplies. By clicking on the help icon next to this field, you can see the description of the different behavior the various values imply.

For the second step, just set the AppearanceCSS Classes property for the Geocoded Address page item to:

u-VisuallyHidden

See Universal Theme utility classes for more information on classes like this one and others.

Next let’s implement the assignment of the longitude and latitude values from the geocoded address. The value of the geocoded address page item will be the GeoJSON point information about the geocoded address. It will look something like this (with indenting added by me to improve readability):

{
  "type": "Point", 
  "coordinates": [
    -122.1173, 
      37.8923
  ]
}

This is the information that will be saved into the underlying SDO_GEOMETRY column if your table has that. However, here we’re considering the situation when the table has separate LONGITUDE and LATITUDE columns instead of an SDO_GEOMETRY column.

Luckily we won’t need to process this JSON document ourselves because APEX provides the dynamic action event Result Selection [Geocoded Address] that fires when the user confirms which of possibly-multiple matching addresses is the one they want to use. We can handle this event with a dynamic action event handler, whose action steps can assign the longitude and latitude without writing code.

When the event fires, its event object contains a data property that contains all of the information about the geocoded address. This information includes the latitude and longitude values that we need, along with a number of other interesting pieces of information like the following (discovered using the browser’s developer tools console):

data: {
  "country"         : "IT"
  "edgeId"          : 55331351
  "houseNumber"     : "123"
  "language"        : "ITA"
  "latitude"        : 45.40329
  "longitude"       : 11.87561
  "matchCode"       : 1
  "matchVector"     : "??010101010??000?"
  "matchVectorScore": 100
  "municipality"    : "Padova"
  "name"            : ""
  "percent"         : 0.17
  "postalCode"      : "35122"
  "region"          : "PADOVA"
  "sequence"        : 0
  "settlement"      : "Padova"
  "side"            : "R"
  "street"          : "Via Roma"
}

So, assigning the P1_LONGITUDE and P1_LATITUDE page items can be done by creating a dynamic action event handler for the Result Selection [Geocoded Address] event using two separate Set Value action steps configured as follows:

  • “True” Action Step 1
    • Action: Set Value
    • Settings > Set Type: JavaScript Expression
    • Settings > JavaScript Expression: this.data.longitude
    • Affected Elements > Selection Type: Items(s)
    • Affected Elements > Items(s): P1_LONGITUDE
    • Execution Options > Fire on Initialization: False
  • “True” Action Step 2
    • Action: Set Value
    • Settings > Set Type: JavaScript Expression
    • Settings > JavaScript Expression: this.data.latitude
    • Affected Elements > Selection Type: Items(s)
    • Affected Elements > Items(s): P1_LATITUDE
    • Execution Options > Fire on Initialization: False

The figure below shows the APEX Page Designer showing the properties of the second declarative dynamic action step for the structured Geocoded Address page item in the example app that is assigning the value of the latitude to the P1_LATITUDE page item.

Action step for structured Geocoded Address assigns latitude to P1_LATITUDE

Thanks to members of the APEX community on Twitter like @daust_de and @andremlde for suggesting several improvements that made this article better since my original published version.

You can download this sample application from here.

Enhancing Existing APEX App with PWA, Nav Bar Items & Smart Filters

I was excited to try three powerful new no-code-required APEX 21.2 features in my art tracker application, and I finally had an hour to spare after work this week to give it a go. This article documents my successful attempt to evolve the showcase page from my existing app to leverage the space-saving Smart Filters search region, leaving more room for the cards that visualize the main application content.

In the process I regained even more screen real estate by positioning items in the navigation bar using the new layout positions available in 21.2’s Universal Theme. And last but not least, I enabled users of my app to install it like a native desktop or mobile application for quick access from the home screen or dock and faster startup time. Read on if any (or all!) of these features sound like they might be a useful upgrade in your own APEX application.

The screenshot below shows the main page of my application before the enhancements. It employs a faceted search region and two select-list page items in a Left Column layout position to let users find the artworks they are looking for. They can also switch dynamically among several useful orderings and interactively change the display size of the images. These complement a Cards region showing the works of art they are tracking.

Main page of an APEX 21.1 art collection app using faceted search and cards region

Setting the Stage

While none of these three new features required writing any code, the simplest of all to enable was the installable Progressive Web App capability. I first needed to ensure my app was using Friendly URLs, as shown below in the application definition’s Properties tab. While I was here, I also changed the Compatibility Mode to 21.2, which is a prerequisite to refreshing its Universal Theme version to 21.2. As we’ll see shortly, this theme refresh allowed me to access the new, flexible layout positions in Page Designer.

Ensuring friendly URLs are used and setting compatibility mode to 21.2

Installable Native App in Two Clicks

With these two settings configured appropriately, I proceeded to enhance my application to allow installation on desktop or mobile devices. As shown below, on the Progressive Web App tab of the application definition, I just needed to switch on the Enable Progressive Web App toggle, as well as the Installable toggle. The former enables the device to cache key application resources for faster startup, while the latter lets users add the app to their home screen or dock to launch it like every other app on their computer, phone, or tablet.

Turning your app with friendly URLs into an installable native app in two clicks

Adding Existing Items to the Nav Bar

The new page item positions are made available by the 21.2 version of the Universal Theme. Since my existing application is using the theme’s 21.1 version, the new positions won’t show in Page Designer until I’ve refreshed the theme to version 21.2. Above, we set the application’s Compatibility Mode to 21.2. This is a required step to be able to refresh our theme to the latest 21.2 version that supports the new page item positions.

Refreshing the Theme

From my application’s Shared Components page, I clicked on Themes. My application only uses a single theme “Universal Theme” (number 42), so I clicked on its name to edit it. On the Edit Theme page, as shown below, clicking the (Refresh Theme) button gets the job done. If by chance I had forgotten to update the application compatibility mode to 21.2, I would have seen a helpful error message telling me to go do that first before attempting again to refresh the theme.

Refreshing Universal Theme to latest 21.2 version to see new component positions

Seeing the New Page Positions in Page Designer

After updating the Universal Theme, editing the page in Page Designer shows the new positions. The one I was specifically interested in is Before Navigation Bar. This will allow me to position my sort control, size control, and new smart filter search field into the previously unused space in that bar. If you don’t see the Before Navigation Bar position (or any of the other new ones) you might have hidden the empty positions. If so, then as shown below, right-click on the layout panel and ensure that Hide Empty Positions is unchecked.

Existing art collection page in Page Designer before starting the changes

Moving Existing Items/Regions into New Positions

I had previously grouped the two select lists that control the dynamic sorting and card image size into a static content region called Sort. I could have simply set the Layout Position property of this Sort region to Before Navigation Bar, but I wanted to see how individual items could be placed into the new positions without belonging to a region, so instead I did the following for both the P18_ORDER_BY and P18_ZOOM page items:

  • Set the Layout Parent Region property to No Parent.
  • Set the Layout Position property to Before Navigation Bar

Changing Faceted Search to Smart Filters

My existing page was using the Left Side Column template, and the existing faceted search region (named Filter) resided in the template’s Left Column position. So I started by selecting the Filter faceted search region and changing its Layout Position property to Before Navigation Bar.

After doing this, I no longer needed the Left Side Column page template, so I set the page template to Theme Default to free up the space currently taken up by the left column, and deleted the now-empty Sort static content region. After, right-clicking on the layout pane to select Hide Empty Positions, the in-progress page looked like what you see below.

In-progress work after moving items and Filter region to Before Navigation Bar position

Next, I needed to change the region type of the Filter region from Faceted Search to Smart Filters. However, before doing this, for each facet I made a note of the following three elements of its definition:

  • Facet name (e.g. P18_ARTWORK_TYPE)
  • List of Values name (e.g. TYPE_LOV)
  • Icon name (e.g. fa-shapes)

This step simplified my recreating the same filters under the Smart Filter region. At the moment APEX does not automatically convert the existing facets into filters when the region type changes from Faceted Search to Smart Filters. However, with the information noted down above regarding the facet names, LOV names, and icon names, setting up the new filters was just a few additional clicks. So, in practice this missing automatic conversion was not a big deal.

After changing the Filter region’s type from Faceted Search to Smart Filters, an initial filter named P18_SEARCH got created for me. This is the mandatory search filter that will search on row text by default. Next I proceeded to recreate the five additional filters using the same names, corresponding List of Values, and icon names that the facets were using previously. I kept the default Checkbox Group type for each filter I created, and finally (based on a tip from my colleague Vincent) I set the CSS class on the Filter smart filter region to w100p so that it would stretch to fill up the space in the navigation bar. Then, I ran the page to test out the upgraded functionality.

As shown below, by default the Smart Filters region shows a suggestion “chip” for each filter category below the search bar with the most popular value and a record count for each filter type. [Note: the record counts have been replaced in the screenshot by (..)]

By default, the Smart Filter search region shows suggestion chips for each filter with counts

These suggestion chips simultaneously educate the end-user about the available filters as well as provide useful information about the most popular value for each category. Clicking on the label of a particular filter chip, the end-user can select specific value(s) they want to search for in that category. In contrast, clicking on the suggestion chip value, applies that most-popular value to the user’s search.

For my particular application, I preferred to further streamline the navigation bar by disabling these suggestions. To suppress the suggestion chips, I set the Maximum Suggestion Chips to the value zero (0) on the Attributes tab of the Filter region as shown below.

Disabling the Smart Filter suggestion chips for a region if desired.

Installing the App on Windows and iPad

After saving this Page Designer change and re-running the page, I saw the result below without the suggestion chips. Notice the new (Install App) button in the navigation bar. When I enabled the Progressive Web App feature above, the APEX builder added a new item into the Navigation Bar list which advertises the ability for the user to install the application on their device.

Enabling PWA with the Installable option adds a new (Install App) navigation bar button

Clicking on the (Install App) button in Chrome or Edge desktop browser let me install the application in one click on my desktop. I tried the same thing on my iPad and there the app can also be installed using the native iOS gesture of clicking on the share icon and choosing “Add to Home Screen” as shown below.

Adding your installable APEX application to the iPad’s home screen

This allowed me to long-press on the home screen icon for the application to enter “wiggle mode” so I could drag it into to my iPad’s dock like this:

Native app icon for an APEX application in the iPad dock

Clicking this new iPad dock icon launched the app into a full screen experience indistinguishable from other native apps on my iPad as shown below. Also notice that the (Install App) navigation bar button no longer displays, so APEX has configured the new navigation bar list entry to be conditionally displayed only when it makes sense. A nice touch!

Full screen iPad app user experience looks like every native app

Trying the Smart Filters User Experience

Back on my Windows desktop machine, I repeated this same experience of launching the application from the dock and noticed it now displayed in its own window with no browser address bar as shown below. Clicking into the Smart Filters search field, the user sees which filter categories are available and can click one of them to choose specific values to search for in that category…

Filter categories suggest the smart ways the user can narrow their search

Alternatively, the user can just start typing some text to search for. As shown below, after typing the two characters gr into the search field, the Smart Filters region shows matches in any appropriate categories. It’s showing some artists (e.g. Gregory Mason, Rich Pellegrino), an artwork type (Photography), and some sources (i.e. stores) like Ross Art Group and others, all which contain a “gr” in their name. If desired, clicking on one of these filter category matches will apply that filter to the search for the respective category.

Filter search matches show in the drop down as the user types

The user can ignore the filter category suggestions and just keep typing, for example, to enter the search term grand and upon pressing [Enter] they’ll see the artworks that contain grand in their row text (title plus other card text) as shown below.

Resulting show all types of artwork with “grand” in their title

Clicking again into the search field, suppose the user clicked on the Type filter category and selected Painting. That would result in the situation shown below where the results have been narrowed to only show paintings with “grand” in their title.

Results filtered to show only paintings with “grand” in the title

Clicking back on the “Type: Painting” filter chip in the search bar, the user can select other types of artwork to also include as shown below. The set of choices available is automatically filtered to reflect the narrowed search results so other types of artwork like “Animation Cel” are not in the list since no animation cel in the collection contains the word “grand” in the title or descriptive text.

Adding additional types of artwork to the search

After further narrowing the results by Decade and Source, the result looks like what you see below. Clicking back into the search field only shows those filter categories that are not already involved in the filter.

After applying multiple filters, only unused filter types show in the dropdown

I was very satisfied with the functionality and additional screen space I gained in my application by uptaking these new features. In short order, without having to write any code, I evolved my application to give users more room to see the primary subject of the application (the artwork cards!) while gaining a compact searching interface every bit as powerful and smart as the faceted search I was using before.

What’s more, enabling the installable Progressive Web App (PWA) feature let my users have a more native desktop and mobile experience that looks like all the other apps on their machine when they’re using my app.

Of course, in many use cases users may appreciate a more “AirBnB”- or “Amazon”-style search experience with the faceted search items always visible, but the new Smart Filters region gives developers a new option to consider when users may appreciate the powerful search capability to be more compact so other page content can be the star of the “show”.

I encourage you all to give these three new features a try!

P.S. In the process of doing the enhancements above, I also simplified the P18_ORDER_BY and P18_ZOOM select list items to have no visible label and fiddled with CSS styling to get these two items to be positioned and sized exactly how I wanted them in the navigation bar. But as I’m not a CSS expert, I didn’t want to offer any advice on what I did to achieve the final result since I’m not certain yet that it’s the best way. Once I have a chance to check with colleagues with more CSS expertise, I’ll either write a new blog article about that or update this article with more specific advice.

Finding & Fixing Unindexed Foreign Keys

A colleague Martin showed me a cool feature of Oracle APEX this week to find missing foreign key indexes. Under SQL WorkshopUtilitiesObject Reports, a number of helpful reports offer insights about your application’s database objects. As shown by the arrow in the figure below, one of these is the Unindexed Foreign Keys exception report.

SQL Workshop ⟶ Utilities ⟶ Object Reports Page

Clicking on this report type shows a list of any foreign keys missing an index.

SQL Workshop ⟶ Utilities ⟶ Object Reports ⟶ Unindexed Foreign Keys Report

This useful result highlighted a number of opportunities to potentially improve the performance of my application. However, as I continue to find, Oracle APEX has an ace up its sleeve. Notice the (Create Script) button in the report toolbar. Clicking on that button created the SQL script shown below to create all the missing indexes.

Automatically created SQL script to create missing foreign key indexes

One additional click on the (Run) button and all my missing indexes were a thing of the past!

As with so many things I’m discovering about Oracle APEX, getting things done ’tis but the work of a moment as Rowan Atkinson’s store clerk character said in a family favorite film Love Actually

Create an ORDS REST Service with Custom JSON In and Out

As I’ve been getting up to speed with ORDS REST services over the last couple weeks, Tim Hall’s Oracle-Base tutorial about REST services basics proved very helpful. I started by experimenting with ORDS’ support for automatic handling of different built-in datatypes to and from JSON. In many cases, this lets you focus on your core PL/SQL logic without having to work directly with JSON.

However, to make sure I was understanding all the moving parts, I set myself the task of creating a service that could process an arbitrary JSON document as the request and return back an arbitrary JSON document in response (possibly created programmatically or by doing a JSON SQL query or some combination of the two). After reading another of Tim’s articles RESTful Web Services Handling Complex JSON Payloads, I set out to try my own example. The Oracle reference docs on PL/SQL JSON datatypes also came in handy while doing this.

In my head, what made the most sense to represent a PL/SQL procedure that accepted a JSON request and returned a JSON response was a procedure specification like this:

procedure generic_service(p_request in json_object_t,
                          p_response out json_object_t)

The simplest version of such a service would be an “echo” service that just returned the JSON document passed in as the response:

procedure generic_service(p_request in json_object_t,
                          p_response out json_object_t) is
begin
    -- Start by just echoing the request as the response
    p_response := p_request;
end;

I knew that once I got this “echo” service working I could modify the internals of the generic_service() procedure to do whatever I needed to do to process the incoming JSON and return a custom output JSON.

Let’s assume the JSON payload we need to process looks like this:

{
  "customer_id" : 1234,
  "items" : [
    {
      "product_number":"978-0262542456",
      "quantity":1
    },
    {
      "product_number":"978-0358446286",
      "quantity":1
    }
  ]
}

In an EXAMPLES schema I’d previously enabled for ORDS access, I ran the following PL/SQL block to define the module, template, and two handlers (one for the POST verb and another for the PATCH verb). Notice that I’m passing the BLOB-valued :body bind variable to the json_object_t() constructor in order to pass the resulting JSON object as the value of the p_request parameter in the call to generic_service(). This results in parsing the incoming JSON payload into a form that’s easy to work with in PL/SQL.

begin
  ords.define_module(
    p_module_name    => 'rest-jsonecho',
    p_base_path      => 'rest/json/',
    p_items_per_page => 0);
  
  ords.define_template(
   p_module_name    => 'rest-jsonecho',
   p_pattern        => 'echo/');

  ords.define_handler(
    p_module_name    => 'rest-jsonecho',
    p_pattern        => 'echo/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => q'[
    declare
        l_response json_object_t;
    begin
        generic_service(p_request  => json_object_t(:body),
                        p_response => l_response);
        owa_util.mime_header ('application/json', true); 
        -- Show example of modifying the response from the invoked
        -- json-in-json-out method before returning.
        l_response.put('method-used-was','post');
        htp.p(l_response.stringify);
    END;       
    ]',
    p_items_per_page => 0);

  ords.define_handler(
    p_module_name    => 'rest-jsonecho',
    p_pattern        => 'echo/',
    p_method         => 'PATCH',
    p_source_type    => ords.source_type_plsql,
    p_source         => q'[
    declare
        l_response json_object_t;
    begin
        generic_service(p_request  => json_object_t(:body),
                        p_response => l_response);
        owa_util.mime_header ('application/json', true); 
        -- Show example of modifying the response from the invoked
        -- json-in-json-out method before returning.
        l_response.put('method-used-was','patch');
        htp.p(l_response.stringify);
    END;        
    ]',
    p_items_per_page => 0);    
  commit;
end;

When the procedure call to generic_service() returns, the l_response variable contains the JSON object returned in the p_response out parameter. The handler’s PL/SQL code can then perform any post-processing if necessary. In the two example handlers above, notice that I’m simply setting one additional JSON property named method-used-was so you can see a difference between sending the sample payload using the POST or PATCH verb.

In order to write out the JSON response, we start by using owa_util.mime_header() to add the appropriate HTTP response header that signals to the caller that the response is a JSON document. Then we use the venerable htp.p() procedure to write the “stringified” version of the JSON object out to the response. The json_object_t object’s stringify() function prints out the contents of the JSON data structure using the right combination of curly braces, colons, quotes, and brackets as needed to produce the expected text representation of the data.

To test out the result, I sent a POST and a PATCH request to my local ORDS server with the URL http://localhost:8181/ords/examples/rest/json/echo/

After my first attempts were failing to work, I learned from colleagues that ORDS is very exacting when matching the URL for the defined template handlers. My PL/SQL that defined the template handlers used "rest/json/" for the module base path and used "echo/" for the template pattern, so sending my request to the URL http://localhost:8181/ords/examples/rest/json/echo(without the trailing slash) was not matching any defined template handler. Good lesson learned!

Here’s what my Postman window looked like when testing the POST request:

With this basic skeleton in place, I aim to create a number of custom JSON-in, JSON-out services for a sample app I’m working on that will follow this pattern. If you notice something I could be doing even better, please let me know on Twitter. I’m always happy to learn ways to improve my code, and of course still consider myself an ORDS newbie, so I’m sure there is plenty more to learn.

Mixing Positional & Named Params Can Lead to Trouble

Mixing positional and named parameters can lead to trouble.

A user of the APEX application I built for a local non-profit sent me a screenshot over WhatsApp today saying that she was getting an error trying to save a change to a student record in the system.

I could reproduce the problem, and quickly enabled APEX debug trace to level 9 which gave me a rich amount of information on what was happening.

The trigger on my STUDENTS table contained lines that were conditionally logging audit information for particular column values like the example below and the call stack for the ORA-6502 visible in the APEX debug trace was pointing to the line handling the audit for change in value of the YEAR_OF_BIRTH column.

if (updating) then
    -- etc.
    bambini.handle_audit('STUDENTS','FIRST_NAME',:old.id,
                         p_old_text_value => :old.first_name,
                         p_new_text_value => :new.first_name);
    bambini.handle_audit('STUDENTS','CLASS_YEAR',:old.id,
                         p_old_number_value => :old.class_year,
                         p_new_number_value => :new.class_year);
    bambini.handle_audit('STUDENTS','YEAR_OF_BIRTH',lv_user,:old.id,
                         p_old_number_value => :old.year_of_birth,
                         p_new_number_value => :new.year_of_birth);
    bambini.handle_audit('STUDENTS','DATE_OF_BIRTH',:old.id,
                         p_old_date_value => :old.date_of_birth,
                         p_new_date_value => :new.date_of_birth);
    -- etc.
end if;

Nestled in there among many similar lines of code, my eye wasn’t seeing what could be wrong, but luckily my colleague Patrick spotted an odd disturbance in the repetitive code. What was that extra lv_user variable doing in the argument list? None of the other lines had it! The declaration of the handle_audit() procedure being invoked looked like this:

    PROCEDURE handle_audit(
       p_table_name       VARCHAR2,
       p_column_name      VARCHAR2,
       p_id               NUMBER,
       p_old_text_value   VARCHAR2 := null,
       p_new_text_value   VARCHAR2 := null,
       p_old_date_value   DATE     := null,
       p_new_date_value   DATE     := null,
       p_old_number_value NUMBER   := null,
       p_new_number_value NUMBER   := null    
    );

An accidental copy/paste inserted lv_user at a position in the text that, due to my mixing positional arguments with named arguments, did not cause a compilation error. It resulted in passing the value of lv_user to the numeric p_id parameter and causing PL/SQL to convert the value of the numeric :old.id field to a string as the value of the next p_old_text_value parameter. Since lv_user contained a string with the currently logged-in user name, it was getting an ORA-6502 trying to convert that to a number to pass into p_id.

As a result of this exercise, I learned never to mix positional and named parameter notation when invoking PL/SQL program units, and it reminded me how useful it would be if PL/SQL supported an ANYRECORD datatype so I could have written a single, generic handle_audit() procedure that could accept the :new and :old records from the trigger without stamping out line after line of similar code in all of my table’s triggers where I want value-change auditing. So I also filed an enhancement request in our internal bug database requesting that PL/SQL someday add an ANYRECORD type to make writing generic record-processing code like this less tedious. If that were a thing, my trigger could have been a one-liner:

-- NOTE: Dream code, not currently supported!
if (updating) then
    bambini.handle_audit('STUDENTS',:new,:old);
end if;

Thanks, Patrick, for the eagle-eye code spotting and the lesson learned on PL/SQL best practice.

Interactive, User-Configurable Card Width #JoelKallmanDay

Create a cards region with interactive card width selector, saving user’s preference across logins.

We miss you, Joel.

Everyone in the Oracle APEX community

Oracle APEX card regions let your users browse and act on a grid of tiles, each representing a row of data. The card region directly taps into your end user’s intuition of browsing their mobile phone’s photo library, especially when the cards feature an image, so it’s a compelling way to present data to users.

The card region’s grid style resizes automatically to the screen space available, but by default end users can’t influence the size of each tile in the grid. Read on to learn how to let your users adjust the card width interactively and remember their choice as a preference across logins. At the end, you’ll find a step-by-step video tutorial and downloadable sample application, but we’ll explore the key ideas behind the technique first.

Overview of Strategy

To implement the feature, you’ll add the following to your page with the card region:

  • A select-list page item showing list of available sizes (e.g. Small, Medium, Large)
    • Having corresponding values of the pixel widths 180px, 220px, 300px
    • Defaulted to the static value for the Medium size (220px)
    • Configured with Maintain Session State setting of Per User (Disk).
  • A dynamic action “trigger” for the select list’s Change event with actions:
    1. Execute JavaScript to update the CSS variable controlling the card size
    2. Execute Server-side Code to save the updated value to the APEX session state

What’s a CSS Variable?

A CSS variable is a custom property whose name is prefixed by a double-hyphen (e.g. --preferred-button-width). It can be associated with any element in a page, either explicitly or implicitly by being associated with a class applied to that element. Any CSS expression can reference the value of a variable by using the syntax var(--variable-name) . The usage can also provide a default value to use in case the variable reference has no value of its own by including a second argument like var(--variable-name, defaultValue) . So, a CSS class named myButton could set the width property to the value of the --preferred-button-width variable (providing a default of 80 pixels) like this:

.myButton {
  width : var(--preferred-button-width,80px);
}

If the same variable exists on multiple elements in the page, the value of the most specific occurrence is used. To provide a global default value for a variable, you can set a value for it on the special :root pseudo-class. If no more specific element in the page provides a value, then the one from the root is used.

As we’ll see below, the Universal Theme style class that defines the card region’s grid layout uses a CSS variable to control the size of the cards in the grid. So setting the right variable to a user-chosen value on the appropriate scope for your needs is the crux of the solution. So let’s explore which variable to set and consider on what context makes sense to set it.

Which Variable Do We Need to Set?

While a page containing a grid region is displayed, using the Chrome developer tools to inspect one of the cards (and clicking to enable the CSS-grid related style properties) we can observe that the card items grid layout is setup by this CSS class:

.a-CardView-items--grid {
    grid-template-columns: 
         repeat(auto-fill,minmax(var(--a-cv-item-width,320px),1fr));
}

At first glance, it’s admittedly cryptic, but let’s unpack what it says. This style property defines the grid-template-columns layout to be a repeating set of columns that auto-fill the horizontal space available with uniform-sized grid cells. The browser computes the width of each grid cell automatically so it falls in the range between the values passed to the minmax() function. The first argument, that is the minimum width value, is given by the value of the CSS variable named --a-cv-item-width (or a default to 320 pixels if the variable is not defined). The second argument providing the maximum card width is one fractional unit (1fr), which represents the width of one column in the layout taking into account a spacing between grid cells, too. In short, if we assign the user-preferred card width value to the CSS variable --a-cv-item-width then the grid will instantly react to layout the grid with cards having that width (or slightly bigger to make each grid cell uniformly sized).

On What Context Do We Set the CSS Variable?

We have at least two sensible choices for the context on which to set the card size CSS variable:

  1. On the card region itself, after assigning it a static id in the App Builder, or
  2. On the “global” root context

Choice 1 imposes the user-preferred card width on just the region on which it’s assigned, whereas choice 2 sets the user-preferred card width so that all card regions in the application will abide by it.

The code examples that follow assume you’ve created a page item named P3_CARD_SIZE on the page with the card region, and that the values for the P3_CARD_SIZE select list page item are one of 180px for Small, 220px for Medium, and 300px for Large.

JavaScript to Set the CSS Variable on a Region

After configuring a static id on your card region (e.g. BooksCardRegion), your dynamic action on the P3_CARD_SIZE page item’s Change event can use the following line of JavaScript to change the CSS variable --a-cv-item-width to the new value of the P3_CARD_SIZE page item:

$('#BooksCardRegion').css('--a-cv-item-width',$v('P3_CARD_SIZE'));
JavaScript to Set the CSS Variable Globally

Your dynamic action on the P3_CARD_SIZE page item’s Change event can use the following line of JavaScript to change the CSS variable --a-cv-item-width on the global “root” context to the new value of the P3_CARD_SIZE page item:

$(':root').css('--a-cv-item-width',$v('P3_CARD_SIZE'));

Pushing the Interactive Card Size Change Immediately to the Server

The change to the CSS variable is made in the browser when the dynamic action reacts to the user’s change of the select list page item, and immediately takes visual effect for the end user in their browser. However, to immediately force the value change to be saved in the APEX session, add an additional dynamic action step to Execute server-side Code and which specifies the P3_CARD_SIZE as one of the page items to send to the server. Since it doesn’t need to perform any other server-side logic beyond pushing the values, you can simply use the “do nothing” PL/SQL instruction NULL; to enter into the required PL/SQL script property. If the page item’s Maintain Session State setting is configured to “Per Session (Disk)” then the user’s preference will persist for the duration of the session. If instead it’s set to “Per User (Disk)“, the setting will survive across subsequent user logins.

Step by Step Tutorial Video

NOTE: The video illustrates setting the CSS variable on the :root context, while the downloadable example app illustrates setting the variable on the card region. See above for a consideration on which is appropriate for your use case .

Sample Application

You can download an Oracle APEX 21.1 example application (containing a supporting objects installation script for two sample tables PNL_THEROUX_BOOKS and PNL_PUBLISHERS) from here.

Old Dog, New Tricks

Over lockdown I fell in love with Oracle APEX, while using it to build a donation tracking application for Progetto Dogon, a local non-profit here in Italy. My end users immediately noticed the lightning speed, attractive UI, and many handy new features I was able to deliver in the new system. What they didn’t know, is that APEX did most of the work, while I got to take the credit. It was the ace up my sleeve.

Friends and colleagues know that when something inspires me, I go all in, so I kept learning more about APEX over the past year with the goal of finding a way to join the development team in time. The Harvard CS50x online course I was taking to refresh my knowledge of C and Python let us use any technology to implement our final project, so I used APEX to build an art collection tracking application that I hope to continue enhancing and make available as open source on GitHub eventually in case others might find it useful. If you’re curious to check out what I built, here’s a short demo I had to submit along with the source code for my final grade in the course.

Many colleagues helped me on my APEX learning journey by answering my newbie questions about the tool, but I gradually began to feel confident enough to start answering a few questions about APEX myself on our internal discussion forums. It was eye-opening to witness how many Oracle employees and teams are using APEX on a daily basis to build themselves applications they and their teams use to get their jobs done.

By listening to all the back episodes of The Oracle APEX Talkshow podcast on my daily morning walks, I got to “meet” many of the APEX dev team members including Joel, Mike, Carsten, Patrick, Marc, and others, and became aware of the scale, reach, and passion of the APEX community by listening to numerous other guests on the show. It was a group of like-minded folks across the globe looking out for each other, helping each other succeed. I felt compelled to join them.

Long story short, I started a new chapter in my Oracle career on the APEX dev team on October 1st, and look forward to using this blog to chronicle the new tricks that this old dog learns along the road. We’ve come a long way together since EXEMACRO EXETRG in Forms 2.3 in 1990, but a consistent thread through my Oracle career has been helping the community make the best use of Oracle’s tools and helping make our tools the easiest way to build database-backed business applications. That journey continues in my new role…