Author: Steve Muench

Dynamic Behavior Buffet #JoelKallmanDay

To commemorate Joel Kallman Day, I dive into four interesting APEX ideas related to dynamic behavior that my dev team colleagues John Snyders and Stefan Dobre taught me recently. We’ll explore how to:

  • Declaratively compute a dynamic default for a cascading child list
  • Conditionally hide or show a page item based on a SQL query lookup
  • Interactively validate user input to show invalid fields before submitting
  • Easily set several features of a grid and refresh it keeping selected rows.

Download the sample to follow along in APEX 24.1, or read first and download later. Let’s get started.

Note: To see any figure in its original size, right-click on it and open it in a new browser tab.

Configuring Cascading Lists

Cascading lists are a pair of LOV-driven page items where the value of a “parent” list influences the choices available in a “child” list. For example, as shown below, when choosing an automobile the end user first picks a car brand (e.g. Ford, Toyota, Tesla) then chooses a car model from the selected brand.

Example of cascading lists to first choose an automobile brand, then a model from that brand

In Oracle APEX, it’s simple to configure cascading lists. Just decide which page item type works best for your use case – like Select List or Popup LOV, among others – and then set the Parent Item name on the child list as shown below.

Setting the Parent Item property of the P5_MODEL_SELECTLIST child cascading list

Your list of values SQL query for the child list can reference the value of the parent list as a bind variable. For example, the figure below shows a simple data model for car makes and models.

Data Model for Cascading Lists to choose brand (or “make”) and model of an automobile

If the parent page item is P5_MAKE_SELECTLIST and the child list is P5_MODEL_SELECTLIST then the child’s LOV SQL Query might look like the following to retrieve the model_name and id for cars whose auto_make_id is the one selected in the parent list:

select model_name, id
from eba_demo_auto_model
where auto_make_id = :P5_MAKE_SELECTLIST
order by model_name

APEX automatically includes the parent item’s current value in the request it sends to the APEX server to refresh the child page item’s list of values. It’s not the case in this simple example, but if your child list depends on multiple parent items’ values, you can provide a comma-separated list of multiple page item names in the Parent Item(s) property and all of their values get sent to the server. In this case, the child list would be refreshed each time any of the parent page item’s values changes.

If necessary, you can add other page item names into the Items to Submit property in the Cascading List of Values section of the property editor. You’d need to do that if your child list’s LOV SQL query also references other page item values as bind variables. The difference in listing them as other Page Items to Submit instead of as Parent Items is that APEX will not proactively refresh the child list when these other items’ value changes. Listing them as Page Items to Submit just ensures their latest value gets sent to the server when APEX does refresh the child list.

Declarative Default for Cascading Child List

By default, when an end user changes the value of a parent list, APEX sets the child list’s value to null. This compels the user to pick a relevant choice from the refreshed child list. However, what if you want to default the child list to a particular value after the list is refreshed?

For example, after the user selects a car brand, you might want to default the car model select list to the lowest priced car. This is easy. Just use the child list’s Default property to configure a query or expression that returns the desired value. It will typically reference the value of the parent field, and possibly other page items as bind variables. To default the child list P5_MODEL_SELECTLIST to the least expensive model for the current brand, you can set the Default > Type to SQL Query and use a select statement like:

select id
  from eba_demo_auto_model
 where auto_make_id = :P5_MAKE_SELECTLIST
   and retail_price = 
          (select min(retail_price)
             from eba_demo_auto_model
            where auto_make_id = :P5_MAKE_SELECTLIST)

Now, whenever you choose a different car brand, the child list gets defaulted to the model with the lowest price. What makes this magic work? The APEX page automatically performs an behind-the-scenes request to the APEX server to retrieve the default value for the new parent list value whenever the parent page item value changes. This request evaluates the SQL query or PL/SQL expression you’ve configured for the child list, in the presence of the latest value for the parent item(s). If your default value query depends on multiple page items, that’s a sign they should all be listed in the comma-separated Parent Item(s) property so APEX knows to refresh the child list when any of their values changes.

This kind of under-the-covers info request that doesn’t re-render the entire web page is known as an “AJAX” request. The acronym reflects initial implementations’ Asynchronous use of JavaScript to exchange XML. These days, the browser typically sends and receives JSON payloads instead.

Only Defaulting a Singleton Child

The original forum question motivating my research asked, “If the child list has only a single valid choice, can I default it automatically?” This developer wanted users normally to choose from the child list. However, if there was only one choice available, she wanted to save the end-user a few clicks. The P5_MAKE_SELECTLIST2 and buddy P5_MODEL_SELECTLIST2 in page 5 of the sample app illustrate how I solved this problem for her. I configured a Default value query involving the parent page item as above, but crafted the query to only return a value when there was a single valid choice (otherwise return no row). The query looks like this, using the group by and having clauses to only return a row if the count of available auto models is one (1).

select max(id)
  from eba_demo_auto_model
 where auto_make_id = :P5_MAKE_SELECTLIST2
group by auto_make_id
having count(*) = 1

You can try it out in the sample app. Among the sample car makes and models, the Rivian brand is the only one with a single model in the EBA_DEMO_AUTO_MODEL table. If the user picks any other car brand, the child list refreshes and the user has to choose from the list. However, if they pick Rivian for the brand, the child list defaults to the R1T model.

Child lists defaults only if the refreshed child list contains a single entry

Lookup-Driven Conditional Display

Consider the use case below from page 12 of the sample app where an Insurance Amount page item in a car‑ordering form should display only if the retail price of the car model selected is over $25,000. As the user selects different car brands and models, the page should react to the change of P12_MODEL_ID by hiding or showing P12_INSURANCE_AMOUNT.

Insurance Amount page item displays only when model price exceeds $25,000

It’s tempting to reach for the Server-side Condition on P12_INSURANCE_AMOUNT to perform this model price lookup. However, recall that the APEX engine evaluates server-side conditions at initial page rendering time. Once the page is rendered, as the end user is changing field values, the server-side condition is not re-evaluated. So we need a different approach for dynamic, lookup-driven display that reacts to user input as it happens.

Performing the Lookup

The technique starts by adding a hidden page item P12_SHOW_INSURANCE_AMOUNT to the page. Then, as shown in the APEX Page Designer below, I created a dynamic action on the P12_MODEL_ID page item’s “Change” event to first perform the model price lookup. Then using the result of this lookup, two subsequent conditional dynamic action steps either Show or Hide the page item.

Using an Execute Server-side Code dynamic action step to perform a lookup

The initial action step of type Execute Server-side Code calls the show_insurance_for_model() procedure, passing in the value of P12_MODEL_ID. This procedure’s two OUT parameters return a Y or N into the P12_SHOW_INSURANCE_AMOUNT hidden page item, and also return a default insurance amount value into P12_INSURANCE_AMOUNT.

Notice two important details. First, the Page Items to Submit and Page Items to Return are configured to mention the names of the page items referenced as bind variables in the PL/SQL block. Second, since the P12_SHOW_INSURANCE_AMOUNT is a hidden field being changed dynamically in the browser, we need to set its Value Protected property to OFF to avoid a checksum validation error when the user submits the page.

The lookup code for the show_insurance_for_model() procedure in the eba_demo_jkday package is below. It retrieves a row in EBA_DEMO_AUTO_MODEL using the p_model_id passed in. In the process, its case statement returns Y or N depending on whether the auto model’s retail_price column value exceeds 25000. It also computes a default value for the insurance as 2% of the retail price. It fetches these values into the p_display and p_default_insurance OUT parameters, respectively.

procedure show_insurance_for_model(
    p_model_id           in number,
    p_display           out varchar2,
    p_default_insurance out number)
is
begin
    -- Default to not displaying insurance
    p_display := 'N';
    if p_model_id is not null then
        -- Return 'Y' if model price over 25000
        select case 
                  when retail_price > 25000
                  then 'Y'
                  else 'N'
               end,
               round(retail_price * 0.02)
        into p_display,
             p_default_insurance
        from eba_demo_auto_model
       where id = p_model_id;
   end if;
exception
    when no_data_found then
        null;
end show_insurance_for_model;

Using Lookup Result to Hide or Show

Once the Execute Server-side Code action step completes, the P12_SHOW_INSURANCE_AMOUNT hidden page item contains either Y if the Insurance Amount field should show, or N if it should be hidden. We can configure an appropriate Client-side Condition that references the value of this hidden page item on each of the two following action steps. On the Show step affecting page item P12_INSURANCE_AMOUNT we configure the client condition of P12_SHOW_INSURANCE_AMOUNT equals Y. On the Hide step we use a client condition of P12_SHOW_INSURANCE_AMOUNT equals N. If an action step’s client condition evaluates to true, then it runs. Otherwise, it’s skipped. The result is that P12_INSURANCE_AMOUNT will be shown or hidden now, based on the selected model’s retail price.

If the page is used for editing existing rows, we must consider that case, too. First, we ensure the Show Insurance for Model? dynamic action step’s Fire on Initialization property is OFF. This avoids its performing an additional AJAX call to the APEX server when the page is initially rendered. We complement this with an Invoke API page process in the Pre-Rendering section of the component tree to call our show_insurance_for_model() procedure during page load as shown below.

Screenshot

When loading an existing row, we don’t want to use the OUT parameter value returned in the p_default_insurance parameter since it would override the existing value of the insurance amount queried from the database. So, as shown below we configure the Invoke API parameter to ignore the return value.

Ignoring the value of an OUT parameter when using an Invoke API page process

In contrast to the Fire on Initialization property of the Execute Server-side Code action step, we set this property to ON for the hide and show steps. This lets them hide or show the Insurance Amount field during page load based on the value retrieved by the pre-rendering Invoke API page process.

With these few configurations in place, the Insurance Amount field displays when the car model’s price is over $25,000 in both create and edit use cases.

Setting Up Validations in a Form Page

You can define validations in a page to ensure the data users enter is correct. The APEX engine evaluates these rules when the page is submitted. Sometimes, it’s useful to highlight data entry errors more proactively. This lets users see and correct problems even before submitting the page. We’ll study next how to use a dynamic action to show interactive validations that complement those enforced on page submit.

To share logic between submit-time validations and the the interactive ones we’ll configure below, we put the validation code in a PL/SQL package. In the sample app, the eba_demo_jkday package has an is_even() function. It returns true if the number passed in is even, and false otherwise. It’s a trivial example, but it represents any kind of validation function that accepts one or more parameters and returns a boolean result: true for valid, and false for invalid.

In page 6 of the sample app, we ask a user to enter two even numbers whose sum we will compute and display when they click the (Submit) button. We first define validations on the P6_EVEN_VALUE1 and P6_EVEN_VALUE2 page items below.

Users need to enter two even numbers to calculate their sum

Using an Expression type validation we simply call the is_even() function that returns a boolean, passing in the value of the page item to validate like this: 

eba_demo_jkday.is_even(:P6_EVEN_VALUE1)

If it returns true the validation passes. If it returns false instead, APEX displays a corresponding error message to the user.

Speaking of error message, as shown below next we define a translatable text message named VALUE_MUST_BE_EVEN under Shared Components > Text Messages…

Creating a translatable text message to use for the validation error message

Finally, for the validation Error Message we use the special text substitution notation to reference the translatable text message by its message name. We enter &APP_TEXT$VALUE_MUST_BE_EVEN. – including the trailing “dot” – and configure the error message to display inline with the field.

After doing this for both page items, as shown below we get the expected validation messages when entering odd numbers into both fields and clicking on the (Submit) button.

User sees inline validation error messages upon clicking (Submit)

Interactive Validation in Form Pages

To alert a user more proactively to incorrect data, a dynamic action on a field’s value change is the key. As you might expect, since we want to call our is_even() package function, the dynamic action will use an Execute Server-side Code action step, but the first two ways I tried didn’t work as I predicted they might. Neither raising an exception, nor calling the apex_error.add_error() from this server-side step gave the result I needed.

My colleague Stefan encouraged me to use a technique like we used above for the lookup-driven display logic. We’ll return the result of the validation into a hidden page item, then reference it for conditional behavior in a client-side condition. My hidden page item here is P6_ERROR_MESSAGE. I return null into this hidden item when the data is valid, or return the error message text to show the user if the data fails validation. My dynamic action step code looks like this:

:P6_ERROR_MESSAGE := 
     case 
       when not eba_demo_jkday.is_even(:P6_EVEN_VALUE1)
       then apex_lang.message('VALUE_MUST_BE_EVEN')
     end;

If the call to is_even() returns false then P6_ERROR_MESSAGE is set to the text of the VALUE_MUST_BE_EVEN message. If is_even() returns true, then the case statement does not match any when condition and results in assigning null to the page item. We remember to configure the Page Items to Submit and Page Items to Return so APEX sends “over” the value of P6_EVEN_VALUE1 and brings “back” the value of P6_ERROR_MESSAGE. As above, since we’re dynamically changing the value of a hidden field, we set the Value Protected property of P6_ERROR_MESSAGE to OFF.

Next, I use an Execute Javascript action step to clear the errors in the client form for the page item being validated with the one-liner:

apex.message.clearErrors('P6_EVEN_VALUE1');

Then finally, a third JavaScript action step shows the inline error on the field:

apex.message.showErrors( [
    {
        type:       "error",
        location:   [ "inline" ],
        pageItem:   "P6_EVEN_VALUE1",
        message:    $v('P6_ERROR_MESSAGE'),
        unsafe:     false
    }
] );

To ensure this gets skipped if P6_ERROR_MESSAGE is null, I added a Client-side Condition to this action step of Type “Item is not null”, using P6_ERROR_MESSAGE as the item name.

After repeating this technique also for the P6_EVEN_VALUE2 field, the user now sees the interactive validation error messages as soon as they leave the field as shown in the short video below.

Grid Configuration & Refresh Techniques

Another forum question I recently worked on asked, “Can I refresh just a single row in an Interactive Grid on return from a modal edit dialog? When I refresh the whole grid the user’s selected rows are lost.” I heard two intertwined questions. The first was how to refresh just a single row in the grid. The second was how to preserve user row selection across a grid refresh. By consulting with my colleague John Snyders, I learned several new things about APEX grids while researching a solution. The sample app shows off all the tips he taught me:

  • Hiding the grid footer when not needed
  • Letting users select multiple rows in the grid
  • Keeping selected rows across page-by-page grid pagination
  • Automatically maintaining primary keys of selected rows in a hidden page item
  • Refreshing just a single edited row in the grid after closing a modal dialog
  • Restoring the selected rows after a grid refresh

John explained the minimal JavaScript code needed to enable these features. Four of them only require setting a property in the grid’s JavaScript Initialization Function:

  • footer: false – to hide the footer
  • selectionStateItem: "P2_SELECTED_EMPNOS" – to track selected primary keys
  • multiple: true – to enable multiple row selection, and
  • persistSelection: true – to keep selected rows across pagination

You can add one or more of these properties to a small initialization function like the one below, adding whichever ones you need separated by commas. You would define this on the Attributes tab of the interactive grid region. For example, to enable a selection state item in a grid with multi-row selection, the function would look like this:

function( config ) {
    config.defaultGridViewOptions = {
        selectionStateItem: "P2_SELECTED_EMPNO",
        multiple: true
    };
    return config;
}

Refreshing a single row in the grid and restoring the selected rows after a refresh required a few more lines of code, but even a JavaScript novice like me could understand what it was doing. I’ll explain some of that code below.

Capturing Code in a Component

I wanted to capture John’s coaching in a component. After initially writing the helper code in a static application JavaScript file, I asked colleague Stefan Dobre to teach me how to turn my code into something easy to reuse. He gave me back a working dynamic action plug-in that showed me how a slightly modified version of my original JavaScript code could reference custom attribute values that the plug-in defined.

Even as a JavaScript newbie, I was able to evolve Stefan’s solid starting point to present exactly the custom attributes I wanted. I also happily included Stefan’s suggested code improvements. The simple plug-in exposes the features John taught me in a way that others can use declaratively.

I ended up with sample app’s Interactive Grid Utils plug-in. It supports the two Action values shown below: Set Options and Refresh. The Set Options action shows four declarative settings to configure the features mentioned above, without having to write the initialization function to enable them. You would typically use it as a step in a Page Load dynamic action.

Declarative properties developers can configure on the Interactive Grid Utils plug-in

The Refresh action presents a JavaScript expression to provide the primary key value of the grid record to refresh. If the record corresponding to the primary key exists in the grid, just that one row of the grid is refreshed. If a refreshed row was deleted, it gets removed from the grid. If the primary key value is null or cannot be found in the grid – as would happen after inserting a new row – then the whole grid is refreshed and the previously selected rows are restored after the refresh.

In either case, when using the plug-in you associate it with a particular Interactive Grid region by setting two properties in the dynamic action’s Affected Elements section in the property editor. Just set Selection Type to Region and Region to the desired Interactive Grid’s region name.

Binding Selected Primary Keys in a Query

The Employees (Multi-Select) page (4) in the sample has a grid of employees and uses the plug-in above in a Page Load dynamic action to configure P4_SELECTED_EMPNOS as the selection state item and to allow multiple selected rows. Using Command-click on Mac or Ctrl‑click on Windows, you can select multiple employees. As the user changes the grid selection, the P4_SELECTED_EMPNOS automatically reflects the colon-delimited primary keys of the selected grid rows. To avoid the automatic selection of the first row in the grid, I set the Select First Row property of the Employees grid in the page to OFF on the Attributes tab.

This automatically-maintained list of primary key values is useful to use as a bind variable in a query. For example, the Selected Grid Primary Keys in Words classic report region below the grid has the following select statement referencing :P4_SELECTED_EMPNOS Notice how it uses the apex_string.split() function in an EMPNO IN subselect to retrieve employees whose EMPNO appears among the colon-separated values.

select empno, 
       ename, 
       to_char(to_date(empno, 'J'), 'JSP') AS empno_in_words
from eba_demo_employees
where empno in (select column_value 
                 from apex_string.split(
                        :P4_SELECTED_EMPNOS,':'))

You can refresh the report based on this query to show information about the selected grid rows by clicking the (Refresh Selected Rows in Words) button. The screenshot below shows the result after selecting employees FORD and MARTIN and clicking that button.

Using grid selection primary keys page item in a query on the same page

Passing Selected Primary Keys as Parameter

The automatically-maintained list of primary key values is also useful to pass in a parameter to another page. Notice the (Process Selected Employees) button in the figure above. I configured it to submit the page so the P4_SELECTED_EMPNOS selection state item value becomes part of the APEX session state. Then I added the conditional branch shown below – with an appropriate When Button Pressed Server-side Condition – to pass the P4_SELECTED_EMPNOS parameter to page 10.

Notice in the Link Builder – Target dialog below that I’ve escaped the substitution string &P4_SELECTED_EMPNO. by surrounding it with backslashes. By using this escaped substitution \&P4_SELECTED_EMPNO.\ we signal the APEX engine to pass the value containing colons verbatim to the target page. Failing to remember this backslash escaping would result in passing just the first primary key value, since the APEX engine normally treats colons as value separators.

Escaping the value of P4_SELECTED_EMPNOS with backslashes since it can contain colons!

By properly backslash-escaping the substitution string in the link builder dialog, we ensure that when multiple rows are selected in the grid the corresponding colon-delimited list of primary keys is passed correctly to the target page as shown below.

Multiple colon-separated empno values arrive intact when backslash-escaping page item value

Surgically Refreshing an Edited Row

The same page in the sample uses the Interactive Grid Utils plug-in as a step in a “Dialog Closed” dynamic action. To handle the event correctly whether the dialog is opened by the (Create) button or by an edit link in the grid, I use the Selection Type of jQuery Selector and the body selector. The HTML body element represents the whole page content, so it naturally contains both the create button and the interactive grid region.

The Close Dialog page process in the modal employee form page (3) configures its Items to Return property as P3_EMPNO to return the primary key of the row that was just inserted, updated, or deleted. Accordingly, I configured the plug‑in to use its Refresh action and provided the JavaScript expression of this.data.P3_EMPNO to get the primary key of the row to refresh.

You can experiment that editing a row in the grid and saving the changes just refreshes the edited row, maintaining the row selection. If you delete an employee, it’s removed from the grid, and if you add a new employee the grid does a full refresh. In all of these cases the row selections are preserved.

Two other pages in the demo use the plug-in similarly, but with slightly different grid options. The Employees (Single Select) page (2) uses a single-select grid and hides the grid footer. The Employees (Page-by-Page, Multi Select) page (9) uses a page-by-page pagination on the grid, and uses the plug-in to keep the selected row across pages and allow multi-row selection.

Peeking at the Plug-in Definition

Taking a peek at the plug-in, we can see in the figure below that it defines custom attributes for the declarative settings developers can configure in the Page Designer property editor. There is an Action property, and then all of the others depend on the value of the Action property. This lets the property editor correctly display properties only when relevant. Notice that each custom property is assigned to an attribute number, from 1 to 6 in this case.

Defining custom attributes of the Interactive Grid Utils dynamic action plug-in

On the Source tab shown below, you can see the plug-in’s PL/SQL render() function. The APEX engine passes it key information at runtime in the p_dynamic_action argument. It returns the text of a JavaScript function that retrieves the values of the six custom attributes and passes them as the properties of a JSON object in the call to the interactiveGridUtils.run() function. The APEX engine includes the text of this function in the rendered page that uses the plug-in so the browser can execute it when appropriate.

The render() call returns text of function passing custom attributes to main JavaScript code

On the Files tab shown below, you can see the script.js file that contains the main JavaScript code for the plug-in. It defines the interactiveGridUtils object whose run() function is invoked in the rendering function above. Note the script.js is accompanied by a Reference value of #PLUGIN_FILES#script#MIN#.js This is the expression I copied and pasted into the JavaScript section on the File URLs to Load tab. This ensures the plug-in includes the code in the script.js file at runtime.

The script.js file containing the plug-in’s main JavaScript code lives in the Files tab

The JavaScript code in the script.js file has the following pseudo-code flow:

  1. Retrieve the static id of the region from the Affected Elements
  2. Lookup the grid region to work with using that static id.
  3. Use region object to get grid’s jQuery view and model containing its data.
  4. Assign custom attribute values passed in the data parameter to local variables
  5. Depending on Action value, either set grid options, or perform a row refresh.

The row refresh logic uses model.getRecord() to get the row in the grid by primary key. If it exists, it calls model.fetchRecords() to refresh the row. Otherwise, if the row doesn’t exist or no primary key was provided, it calls the refreshAndRestoreSelection() function. That function gets the list of selected rows, configures a one-time gridpagechange event handler to restore the selected rows, and finally calls region.refresh() to refresh the region and trigger the gridpagechange event in the process.

Trying Out the Sample

I encourage you to download the sample and try it out in APEX 24.1. If you’re not yet using APEX 24.1, you can still explore the sample app in a free workspace at oracleapex.com It’s always running the latest APEX version to experiment with. That said, the techniques explained here are applicable to any recent version of APEX you might be using. I had fun learning these techniques. I hope at least one of them was new to you, too.

Thanks again to colleagues John Snyders and Stefan Dobre for the continued mentorship and words of wisdom. After three years on the APEX team I’m still learning from a firehose – many, many new things every day – and it’s an exciting, energizing feeling!

Oracle APEX 24.1 Product Tour

Watch this webinar to see the broad set of Oracle APEX features you can use to quickly deliver and maintain beautiful, functional solutions that add immediate business value. Enjoy end-to-end or expand the YouTube description for a detailed topic timeline to view just a segment. See below for a list of what you’ll learn…

Studying an app for a fictional medical clinic, you’ll see & learn about:

Productive Development
  • Using Generative AI help for SQL, PL/SQL, app creation, and more
  • Creating Gen AI chatbots and using AI services to save users time
  • Modeling data visually using the Quick SQL ER Diagram
  • Automating business processes with workflow and approvals
  • Laying out responsive web pages with minimal effort
Easy User Interfaces and Reporting
  • Visualizing business process status for end users in a diagram
  • Exploring data interactively with filters, aggregates, and charts
  • Saving reports and downloading/emailing/scheduling results
  • Generating pixel-perfect PDF reports from Word templates
  • Presenting data with Maps, Cards, Calendars, and Charts
  • Filtering data with Faceted Search and Smart Filters
  • Searching application-wide with unified results display
  • Reacting to end-user interactions with dynamic behavior
  • Incorporating community plug-ins like a Kanban Board
  • Handling parent/child lists, regions, and conditional display
  • Geocoding addresses
  • Creating reusable UI components with only HTML markup skills
  • Reusing repeating layouts consistently with configurable slots
  • Installing Progressive Web Apps that launch like native ones
  • Uploading images from a mobile device’s camera
  • Capturing GPS location of a mobile user
Simple Business Processes, App Logic, and Integration
  • Orchestrating app logic with workflows that react to data changes
  • Sending Emails and push notifications
  • Integrating data from MySQL, OData, Fusion Apps, and REST APIs
  • Synchronizing remote data periodically to a local cache
  • Querying based on spatial distances
  • Predicting outcomes from historical data with machine learning
  • Validating parent/child data using simple SQL checks
  • Using semantic similarity searching with 23ai vector search
  • Improving Gen AI results with Retrieval-Augmented Generation
  • Offloading longer-running processing to the background
  • Loading CSV, Excel, or JSON data in foreground or background
  • Enriching remote data with local joins and computations
  • Exposing application data as REST APIs using JSON Duality Views
  • Producing reusable REST API catalogs from OpenAPI documents
Hassle-free Dev Ops and Application Lifecycle
  • Tracking issues and tackling tickets in teams with working copies
  • Merging changes from a separate working copy back to main
  • Testing apps and deploying to test and prod environments
  • Valuing APEX scalability, extensibility, security, and governance

Intersection Rows as Multivalue Item

End-users find multivalue page items intuitive for quick data entry, and APEX devs have many kinds to choose from. However, using multivalue items in the simplest way means storing data in a single column as a delimited string. While easy to do, this could complicate other aspects of your app processing. For a row in a book table, for example, often the multiple tags applied to the book are stored in an intersection table. Imagine a book_tag_xref table with foreign key values recording which tag ids are paired with which book ids. In this article, you’ll learn how to manage intersection rows using a multivalue page item.

We start with an approach to edit the tags applied to a book using a combobox. Then we generalize the technique to easily configure this support for any data model and any kind of multivalue page item. The sample app contains an intersection_multival helper package you can use in your own apps.

NOTE: The APEX 24.1 downloadable sample app installs a trycombo ORDS REST Service module used later in the article, so before installing the sample please ensure your workspace parsing schema is enabled for ORDS REST Services. You can check that (and enable it if needed) on the SQL Workshop > RESTFul Services page of the APEX Builder.

Sample Data Model

When you import the sample app, its Supporting Objects scripts install the following simple three-table data model and insert initial sample data for two books, six tags, and rows in the intersection table that record three tags applied to each book. For ease of discussion, we’ll refer to the tables in this article without their prefix simply as book, tag, and book_tag_xref, but the code and pages we’ll study use the full names shown below.

Three tables in the sample app’s data model

Books Manual Cards + Smart Filters Page

Running the app opens the home page Books Manual (page 8) displaying books in a cards region as shown below. We’ll study this page first, along with its modal form page Book Manual (page 11).

Books Manual home page (8) with a cards region showing books and applied tag names

The card region’s SQL query includes an appropriate LISTAGG() select list expression to retrieve a colon-delimited string of applied tag names from the tag table based on the corresponding tag_id foreign key values it finds in the intersection book_tag_xref table for the current book:

select b.id,
       b.name,
       (select listagg(t.name,':')
          from eba_demo_trycombo_tag t
         where t.id in (select btx.tag_id
                          from eba_demo_trycombo_book_tag_xref btx
                         where btx.book_id = b.id)) as tags
  from eba_demo_trycombo_book b
order by b.name

On the card region’s Attributes tab in the property editor, the card Body uses Advanced Formatting with an HTML Expression containing the {loop/} template directive to iterate over the colon-separated tag names it finds in the current row’s TAGS column value. This loop formats each tag name using the a-Chip CSS class. This makes the tag names look like little “chips”:

{loop ":" TAGS/}<span class="a-Chip">&APEX$ITEM.</span> {endloop/}

The Full Card action is configured to open page 11 (Book Manual), passing in the value of the book ID column for the P11_ID page item. Clicking on the card for the Building Oracle XML Applications book opens the modal drawer page we explore next.

Book Manual Modal Form Page

When we edit a book, the page opens to show the name of the book and the currently applied tags in a combobox as shown below:

Book Manual page (11) showing a book’s name and currently applied tags in a combobox

The P11_TAGS combobox page item uses the shared component List of Values LOV_TAGS to provide the list of existing tag names to choose from. The Value HTML Expression uses the following HTML markup to format each entry in the combobox dropdown list using the same a‑Chip CSS class we used in the Books Manual cards page:

<span class="a-Chip">&NAME.</span>

Notice we are referencing a NAME column from the List of Values data source using the &NAME. notation in the combobox’s Value HTML Expression. For this to work, the columns must be configured in the List of Values component definition as Additional Display Columns as shown below:

LOV_TAGS List of Values component with Additional Display Columns configured

This produces the following look and feel when the end-user clicks into the combobox field to show the dropdown list:

P11_TAGS combobox with custom styling to make tag names look like “chips”

While it’s not required for this simple sample app, know that the Value HTML Expression can also make use of HTML Template Directives to perform sophisticated conditional output for each item in the list whenever that could prove useful in your own apps.

Unified Config for Multivalue Items

The Combobox page item lets end-users pick multiple options from a flexibly-styled, type-to-autoreduce list of values. Users can also key in new entries manually. This works perfectly for an app like this sample where users can invent their own tags to classify books, or pick from a set of existing tags. APEX 24.1 adds the Select Many item that works similarly, but in that case users can only choose from the listed options. These two new multivalued item types join the ranks of the Select List, Shuttle, List Manager, and Popup LOV that all handle displaying and editing multiple values from a list. In the APEX 24.1 Builder, you configure all of their multivalue behavior in a consistent way in a new Multiple Values section of the property editor.

As shown in the figure below, the P11_TAGS combobox in the Book Manual page (11) has Delimited List for its Multiple Values > Type property with the colon character (:) as the delimiter.

Configure all multivalue page items in a uniform way

Reading Multivalue from Intersection Table

The P11_TAGS combobox item expects a colon-delimited list of tag ids, but in our data model the tag ids applied to the book being edited are saved in the book_tag_xref table. So, in our page’s Pre-Rendering section, in the Before Header subsection we use an Invoke API page process to call the READ_APPLIED_TAG_LIST() function in the EBA_DEMO_TRYCOMBO package. This is declaratively configured to pass in the value of the P11_ID page item with the book’s primary key and to return the function result into the hidden P11_TAGS_BEFORE page item. In the After Header subsection, we use a computation to copy the value of P11_TAGS_BEFORE into the P11_TAGS combobox. We do this so we can later compare the “before” value of the applied tags with the submitted value of the P11_TAGS combobox to notice if the user has effectively made any changes or not. There’s no need to process the list of tag ids if nothing has changed.

The code of the READ_APPLIED_TAG_LIST() function is straightforward. It uses the listagg() function to produce the colon-separated list of tag id’s from the book_tag_xref intersection table corresponding to the p_book_id passed in using a SQL query as shown below:

-- In Package Body EBA_DEMO_TRYCOMBO
function read_applied_tag_list(
    p_book_id in number)
    return       varchar2
is
    l_ret varchar2(4000);
begin
    select listagg(tag_id,':')
        into l_ret
        from eba_demo_trycombo_book_tag_xref
        where book_id = p_book_id
        order by tag_id;
    return l_ret;
exception
    when no_data_found then
        return null;
end read_applied_tag_list;

This setup is all we need to retrieve the multivalue string of tag ids during page load so the combobox can show the list of applied tags in the edit form.

Simplifying Processing of Manual Entries

The value of the P11_TAGS combobox is a colon-separated list of tag ids. These correspond to values of the “return column” of the LOV_TAGS list of values shared component. It defines the tag’s ID as return column and tag NAME as display column. However, recall that the “combo” in combobox means it combines the selection of pre-defined entries with manually-typed-in entries.

The combobox simplifies our job of processing the manually-typed entries by partitioning them into a separate (typically hidden) page item. The Book Manual page (11) has the P11_NEW_MANUAL_TAG_NAMES hidden page item for this purpose. The P11_TAGS combobox references P11_NEW_MANUAL_TAG_NAMES as its Manual Entries Item.

When the page is submitted, we need to process any manual entries in P11_NEW_MANUAL_TAG_NAMES and save any other changes made to P11_TAGS to the intersection table. We’ll do that next.

Before we move on, let’s take a moment to study the Session State > Storage property of the P11_NEW_MANUAL_TAG_NAMES hidden page item. Since it is not related to the form region or a database column, by default it will be created in Page Designer with a value of Per Session (Persistent) for this property. It is super-important to change this to Per Request (Memory Only). Failing to do this means the same manually entered items will appear to be present the next time you visit the edit form. By setting this property to Per Request (Memory Only), its value is submitted to be processed but then will not “hang around” for longer than that.

Saving Applied Tags to Intersection Table

The Book Manual page (11) configures another Invoke API page process in the Processing section of the Processing tab. It declaratively invokes the SAVE_APPLIED_TAG_LIST() procedure in the EBA_DEMO_TRYCOMBO package that has the following API. We pass in P11_ID for the p_book_id, P11_TAGS for the p_delimited_tags, and P11_NEW_MANUAL_TAG_NAMES for the p_new_manual_entries parameters.

procedure save_applied_tag_list(
    p_book_id            in number,
    p_delimited_tags     in varchar2,
    p_new_manual_entries in varchar2);

This Invoke API page process uses the following server-side condition expression to execute only if necessary. Specifically, we only want this “save applied tags” logic to run if the P11_TAGS is different from P11_TAGS_BEFORE or if there are any new manual tag names to process. The nvl() expressions are a compact way of correctly handling the situations where the tag ids value changed from null to non-null or vice versa.

nvl(:P11_TAGS_BEFORE,':') != nvl(:P11_TAGS,':')
or :P11_NEW_MANUAL_TAG_NAMES IS NOT NULL

Pseudocode for Saving Applied Tags List

The SAVE_APPLIED_TAG_LIST() procedure performs the following pseudocode steps to track two lists of tag ids: l_add and l_delete. These lists are the tag ids we need to add to and delete from the intersection table for the current book id.

  1. l_old := list of applied tag ids from book_tag_xref for current book
  2. l_new := list of submitted tag ids
  3. l_manuals := list of manual entries (if any)
  4. loop over l_manuals to…
    • insert new row for each into tag table, and
    • add each’s new system-assigned primary key to l_add list
  5. loop over tag ids in l_old list to…
    • detect if any is missing from l_new list, and if so…
    • add intersection row primary key for missing tag id to l_delete list
  6. loop over tag ids inl_new list to…
    • detect if any is missing from l_old list, and if so…
    • add missing tag id to l_add list
  7. Delete any rows in l_delete from book_tag_xref by primary key
  8. Insert any tag ids for this book id in l_add to book_tag_xref

The result is a pair of Books Manual and Book Manual pages (8 & 11) that let us edit the applied tags of any book. For example, as shown below we’re adding to manually-typed tag names PL/SQL and XSLT to the Building Oracle XML Applications book:

Adding two new manual entries to the Tags combobox for a book

Then we can see they are shown in the Books Manual page on the card for the book just edited, and the new tags also show up as existing tags in the next book that gets edited as shown below:

New tags show up in list of predefined tags and as applied tags to a book

When using the combobox page item, keep in mind that a manually-entered tag value can contain spaces in the name, too. So a new tag value could be Dynamic Action and when you type the [Enter] key, the combobox turns the two-word tag name “Dynamic Action” into a new chip to differentiate between one chip for “Dynamic” and another chip for “Action”.

Generalizing Read_List() + Save_List()

The code explained earlier for READ_APPLIED_TAGS_LIST() and the approach outlined in pseudocode above for the SAVE_APPLIED_TAGS_LIST() procedure implement one particular example of a more general pattern. The APEX_EXEC package is a general-purpose API for data retrieval and manipulation, and we can use it to generalize the approach to work for any use case. Imagine a generic READ_LIST() function and SAVE_LIST() procedure that accept as parameters all the appropriate information required that make each use case distinct. Our generic routines would need to accept as parameters details like the names of the:

  • source intersection table (e.g BOOK_TAG_XREF)
  • source table’s “parent foreign key” column (e.g. BOOK_ID)
  • source table’s “other reference foreign key” column (e.g. TAG_ID)
  • other reference table (e.g. TAG)
  • other reference table’s primary key column name (e.g. ID)
  • other reference table’s description column (e.g. NAME)

While we are at it, we can imagine supporting both theDELIMITED_LIST style of APEX multivalue string, with developer-configured delimiter, as well as the JSON_ARRAYtype of multivalue string.

And since APEX_EXEC provides APIs for working both with local database data sources as well as REST Data Sources, why not also let the generic APIs work with both common types of intersection and other reference sources: DB or REST?

This leads us to create two APIs like the following you will find in the INTERSECTION_MULTIVAL package in the sample app:

function read_list( 
    p_multival_type             in t_multival_type, 
    p_intersection_source_type  in t_source_type, 
    p_intersection_source_name  in varchar2,
    p_other_ref_foreign_key_col in varchar2,  
    p_parent_foreign_key_col    in varchar2,
    p_parent_foreign_key_val    in varchar2,
    p_delimiter                 in varchar2 default ':',
    p_parent_param_name         in varchar2 default null)
    return                         varchar2;

Similarly, for the SAVE_LIST() procedure, we have the following API:

procedure save_list( 
    p_multival_list              in varchar2,
    p_multival_type              in t_multival_type, 
    p_intersection_source_type   in t_source_type, 
    p_intersection_source_name   in varchar2,
    p_intersection_source_pk_col in varchar2,
    p_parent_foreign_key_col     in varchar2,
    p_parent_foreign_key_val     in varchar2,
    p_other_ref_foreign_key_col  in varchar2,
    p_delimiter                  in varchar2 default ':', 
    p_new_manual_entries         in varchar2 default null,
    p_other_ref_source_name      in varchar2 default null,
    p_other_ref_primary_key_col  in varchar2 default null,
    p_other_ref_description_col  in varchar2 default null,
    p_parent_param_name          in varchar2 default null);

The sample app contains the following pairs of pages that illustrate using the generic READ_LIST() and SAVE_LIST() helper package routines against DB and REST sources and using Delimited List and JSON Array types of multivalue page items. Each corresponding Book edit page uses a pair of Invoke API page processes to declaratively configure the call to READ_LIST() and SAVE_LIST() in the INTERSECTION_MULTIVAL package to get the job done:

  • DB, Colon-Delimited: Books Delimited (2) + Book Delimited (3)
  • DB, JSON Array: Books JSON Array (6) + Book JSON Array (5)
  • REST, Colon-Delimited: Books REST (9) + Book REST (10)

Getting the REST Data Sources Working

The sample app contains three REST Data Sources based on a trycombo ORDS module that the sample app installs using its Supporting Objects scripts. As shown below, in my own environment the ORDS services have a particular endpoint URL that will be different when you install the sample into your APEX workspace. So getting the REST Data Sources working in your environment involves adjusting the Remote Server definition to match the base URL of your environment’s ORDS REST service modules.

REST Data Sources in the sample require adjusting remote server base URL to work

To adjust the Remote Server definition appropriately, follow these steps:

  1. Visit the RESTFul Services editor
    • SQL Workshop > RESTFul Services
  2. Expand the Modules Node in the RESTFul Data Services tree
  3. Select the trycombo module node in the tree
  4. Copy the value to the clipboard of the Full URL shown in the panel on the right
    • For example, it might look like https://apex.oracle.com/pls/apex/workspace_alias/trycombo/
  5. Visit the Remote Servers page in the APEX Builder
    • App Builder > Workspace Utilities > All Workspace Utilities > Remote Servers
  6. Edit the remote server with name apex-oraclecorp-com-pls-apex-stevemuench
  7. In the Endpoint URL field, paste the value you copied to the clipboard in step 4
  8. Remove the trailing /trycombo/ from this value, and click (Apply Changes)
  9. Run the sample, visit the “Setup” page, and click the (Setup REST Tags Synchronization) button

If you have done everything correctly, you should see the success message:

  • “REST Synchronization Setup for REST Tags“

Trying Different Generic Combinations

You can now try out any of the pages in the sample app. They all edit the same book, tag, and book_tag_xref data, including the REST-based option. Experiment searching for books by title or tag using the smart filters search bar at the top of each cards page. In addition, notice the multivalue page item selector menu options under the logged-in username shown below. They let you change the APEX multivalue page item used by the Books Delimited, Books JSON Array, and Books REST page pairs. It does not affect the Books Manual page which only has the combobox we studied above.

Menu option to change which multivalue page item to use

To achieve this effect, the menu options set an application item named MULTIVAL_PAGE_ITEM_TYPE to one of the values COMBOBOX, SELECTMANY, or POPUPLOV and the pages reference this value in a server-side condition to conditionally display the appropriate multivalue page item, conditionally execute a computation to copy the “before” tag ids to the appropriate multivalue page item, and conditionally execute page processes for the saving of the tag id multivalue string and new manual entries (if applicable).

Downloading the Sample App

Hopefully this sample helps you better understand what’s possible using the various kinds of APEX multivalue page items and gives you a head start on creating APEX pages in the future that use multivalue page items with intersection row data.

You can download the APEX 24.1 sample app from here.

Enabling Debug on APEX Builder

When reporting a problem in the forums or in an Oracle Support ticket, if that problem occurs in the APEX Builder itself, the first instinct is to report the error message received. For example, “I’m getting ORA-01722: Invalid Number.”

Providing additional context about where that error occurs can greatly help members of the APEX community to diagnose what might be happening. Often you can obtain this extra info – for example, a PL/SQL call stack – by using a second instance of the APEX Builder to enable debug tracing on your original APEX Builder session where you’re experiencing the problem.

Using a Second APEX Builder to Debug

To do this, you need to use two separate browsers (let’s say Chrome and Firefox). It can also be done using an incognito/private window of the same browser, but for simplicity of explanation, I will explain it with Chrome and Firefox. In our scenario, “Chrome APEX” will be the builder experiencing the problem and “Firefox APEX” will be the second instance used to debug the first.

Using “Firefox APEX” Builder to enable debug tracing on “Chrome APEX” Builder’s session
  1. Open the APEX Builder in Chrome and get to the step just before the error will occur when you will click (Next) or (Save) or (Apply Changes)
  2. Open the APEX Builder in Firefox and login to same workspace as in 1
  3. In Firefox APEX builder, click the icon with “person with wrench/spanner” > Monitor Activity > Active Sessions
  4. Study the address bar in Chrome APEX to notice the session id
  5. In Firefox APEX builder, click on the session id from step 4
  6. In Firefox APEX builder, set the debug level to “Info” on the session of the Chrome APEX builder and click (Apply Changes)
  7. In Chrome APEX Builder, click (Next) or (Save) or (Apply Changes) to cause the problem to occur
  8. In Firefox APEX builder, set the debug level back to “Disabled” on session for Chrome APEX and click (Apply Changes). This also refreshes the list of Page Views to show the latest debug tracing requests that were captured.
  9. In Firefox APEX builder, notice the latest debug log entry with highlighted link to view debug trace of Chrome APEX
  10. In Firefox APEX builder, viewing the debug trace of Chrome APEX, once you’ve identified the error is contained in the request log you are viewing, then save the log to your computer by choosing Action > Download to download the log as HTML.

Now, you can attach the HTML file to your forum posting or Oracle Support ticket. In the case of the forum, you may have to rename the file to have a .html.txt extension to make the forum attachment restrictions happy.

This will help anyone reviewing your post or support ticket to better assist you in triaging what might be going wrong and ideally quickly suggest a workaround or alternative way of accomplishing the same task.

Flexing Fusion Apps Filters

I enjoyed returning to the APEX Alpe Adria conference to chat with and learn from members of the APEX community. If you joined me in Maribor, thanks for your interest in extending Fusion Applications with Oracle APEX. You can download the slides from my session here. In a nutshell, using Fusion Apps business objects in APEX is now as easy as working with local tables.

Fusion Apps (FA) business object REST APIs support filtering using SQL-like predicates and sorting using object attribute names. APEX delegates all searching and sorting to the FA service by automatically using these features. In addition, APEX requests only the data for attributes the current region is using to keep payloads small and fast. These optimizations help you deliver a great user experience. This article explains how to use these features in APEX pages, List of Values, and in PL/SQL business logic.

Setting the Stage

Each Fusion Apps pillar – HCM, ERP, CX, SCM, etc. – has a set of standard business objects. It also lets admins with appropriate privilege extend the app data model. To manage data specific to a company’s unique requirements, an FA customer admin can add new custom attributes to standard objects. In some pillars, they can also create new custom business objects and relate them to other objects.

Each standard or custom object has a corresponding REST API. All share a common set of features and include custom attributes automatically. An FA business object REST service can describe itself, retrieve filtered data, and handle create, update, and delete operations. It also supports bulk operations, where a batch of changes can be done in a single transaction.

Whenever a client uses an FA REST API to create or change data, the service evaluates all appropriate business object validation rules and executes any processing logic. These include behaviors defined by the FA developers at the Oracle “factory”as well as ones added by FA admins as customizations. If the requested operation fails, the response includes one or more error messages to help the end-user understand what requires attention before submitting again. APEX automatically presents these messages to the end-user in a familiar way.

Lead Tracking for an Art Gallery

An art gallery using the Fusion CX suite might use the standard Leads object to track potential customers who have shown interest in purchasing art, together with custom objects like Artwork_c, ArtMedium_c, Artist_c, and InterestedArtworks_c to track the artworks, media, artists, and pieces a particular lead is interested in purchasing. The Resources standard object stores the staff members (a.k.a. human resources) working at the gallery who follow-up after taking ownership of a lead. The following diagram shows a simplified version of the business object model. Notice the convention that the names of custom objects and custom attributes have the _c suffix to distinguish them from the names of the standard objects and attributes.

Two Fusion Apps standard objects (Leads, Resources) and four custom objects

FA REST Filter Predicates

Fusion Apps business objects REST APIs support SQL-like filter predicates. You phrase them in terms of the case-sensitive business object attribute names. For example, to retrieve leads with a budget greater than or equal to 10000, you would use the following filter expression with the leads endpoint:

Budget_c >= 10000

You can combine conditions using conjunctions and group conditions using parentheses. So, to find leads with names that start with ‘A’ or ‘Z’ in addition to having the above budget, you can write:

Budget_c >= 10000 and (Name like 'A%' or Name like 'Z%')

For case-insensitive comparisons, use the upper() function around the attribute name on the left-hand-side of the expression. To find leads containing a letter a or z somewhere in the name, you can use:

Budget_c >= 10000 and (upper(Name) like '%A%' or upper(Name) like '%Z%')

Each filter predicate has the name of an attribute on the left hand side –optionally surrounded by upper() – followed by an operator, and finally a literal operand value on the right hand side. When using the between operator, you provide two literal values as in this example:

(Budget_c between 7000 and 1000) and (Name like '%A%' or Name like '%Z%')

When filtering on date or timestamp values, the literal values must be strings in the ISO8601 format, so to find leads created any time on December 31st, 2023 you use the following filter. It references one of the common audit attributes that include CreationDate, CreatedBy, LastUpdateDate, and LastUpdatedBy:

CreationDate between '2023-12-31T00:00:00' and '2023-12-31T23:59:59'

To filter on a date field having no time component, leave off the time portion of the literal date string. For example, to find leads whose decision date is before May 1st, 2024, use:

DecisionDate_c < '2024-05-01'

To find leads with qualified status having a rank of warm or hot, you can write a filter like:

StatusCode = 'QUALIFIED' and Rank in ('WARM','HOT')

To find leads that have specified some decision date, you can use:

DecisionDate_c is not null

Conversely, to check for leads with no decision date provided, use:

DecisionDate_c is null

When an FA REST service exposes so-called accessor attributes to related objects, you can reference them in a filter predicate. For example, to find any leads who have shown interest in artwork whose price is between 500 and 1000, you can write the filter:

InterestedArtworks_c.ArtworksObject_c.Price_c between 500 and 1000

Using an External Filter on a Page

After defining a REST Data Source of type Oracle Cloud Applications (SaaS) REST Service for a particular FA REST API, you can use it in any APEX region. Whenever a region uses a REST Data Source, the Property Editor shows the External Filter and Order By section with an Enable switch. After flipping it to the on position, you can enter an external filter clause and/or external order by clause. The syntax of both is specific to the type of the REST data source. One created for an FA REST API using the Oracle Cloud Applications (SaaS) REST Service type lets you specify any legal filter predicate we saw above.

When writing the one or more predicate expressions in your external filter, you can use a literal value for the right-hand-side operand. Alternatively, you can reference a value from APEX session state using substitution syntax like this:

(DecisionDate_c < &P3_DECISION_DATE.) and
(InterestedArtworks_c.ArtworksObject_c.Price_c 
between &P3_LOW_PRICE. and &P3_HIGH_PRICE.)
and Budget_c > 5000

The value of the page items you reference in the external where clause can be computed by page processes or computations in the Pre-Rendering section of the page. This would come in handy if, for example, the operand for filtering on the lead’s DecisionDate_c field needed to be a function of the current date (formatted in ISO8601 format).

If the region type where you’ve configured an external filter allows end-user filtering operations as well, then any filters added by the end user are combined with the external filter using the and conjunction in the filter expression that APEX sends to the FA REST endpoint.

Using an External Order By on a Page

To configure a particular sort order for a region based on an FA REST API, use an external order by clause. The syntax is simple. Just provide a comma-separated list of case-sensitive attribute names. For example, to sort on the lead name, just mention the attribute name like this:

Name

To sort descending by budget amount, you can use the additional descqualifier:

Budget_c:desc

If you do not provide a sort direction qualifier, then an ascending sort is assumed, but you can always explicitly specify the asc qualifier, too. For example to sort descending by budget and then ascending by name, you can configure the external order by to be:

Budget_c:desc,Name:asc

External Filter & Order By for LOVs

When editing a List of Values on a Fusion Apps REST Data Source, you can use the same external where clause and external order by clause features explained above. Just look for the appropriate fields in the LOV’s Advanced REST Data Source Attributes section.

If your LOV is based on an FA REST data source with infrequently-changing lookup data, you can markedly improve performance by enabling the Use Synchronization Table switch. This will appear after you’ve configured REST Synchronization for the FA REST data source in question. Flipping this switch to the on position causes the LOV to use the locally synchronized data instead of making a REST call on each access. In this case, the External Filter and External Order By fields are no longer applicable.

If you need to apply an external filter to the REST synchronization process itself, you can do that using the p_sync_external_filter_expr parameter of the dynamic_synchronize_data() procedure in the apex_rest_source_sync package. By combining this with a scheduled automation, you can perform the REST synchronization on an appropriate schedule using your custom external filter whenever the synchronization runs against the Fusion Apps REST APIs to which the data source is related.

Using Fusion Apps REST APIs in PL/SQL

Using the procedures and functions in the apex_exec package, you can work programmatically with any REST Data Source. When working with Fusion Apps REST data sources, I like to work with the data using the business object attribute names instead of having to remember the corresponding data profile column names that apex_exec uses. The sample download at the end includes a helper package I created named oca_ds, which is short for Oracle Cloud Applications Data Source. It complements the apex_exec package to simplify working with Fusion Apps REST Data Sources programmatically.

Assuming that leads is the static id of a REST Data Source for working with the Fusion Apps leads endpoint, we can query the top 5 qualified leads with a rank of warm or hot having both a budget and decision date specified like this:

declare
   l_ctx apex_exec.t_context;
   type t_lead is record (
        name varchar(255),
        budget number,
        created timestamp with time zone);
   type t_leads is table of t_lead index by pls_integer;
   l_leads t_leads;
begin
   l_ctx := oca_ds.open_query(
               p_select   => 'Name,Budget_c,CreationDate',
               p_from     => 'leads',
               p_where    => q'~
                              StatusCode = 'QUALIFIED' and
                              Rank in ('WARM','HOT') and
                              Budget_c is not null and
                              DecisionDate_c is not null
                              ~',
               p_order_by => 'Budget_c:desc',
               p_max_rows => 5);
   while apex_exec.next_row(l_ctx) loop
      l_leads(l_leads.count+1) := 
           t_lead(oca_ds.get_varchar2(l_ctx,'Name'),
                  oca_ds.get_number(l_ctx,'Budget_c'),
                  oca_ds.get_timestamp_tz(l_ctx,'CreationDate'));
   end loop;
   oca_ds.close(l_ctx);
   -- Work with top 5 leads in l_leads here...
exception
   when others then
      oca_ds.close(l_ctx);
      raise;
end;

Inserting New Leads in PL/SQL

Of course you can use a Form or Interactive Grid region to easily insert, update, or delete data from the Leads REST data source without writing any code, but should you need to do so programmatically, it’s easy. To insert data using an FA REST data source, use the oca_ds.open_dml function to create an apex_exec.t_context object that works like a temporary rowset. Then for each new row you want to add, call oca_ds.insert_row() and then use oca_ds.set_value() to set the value of each attribute necessary. Finally, to process the pending changes call apex_exec.execute_dml().

For example, you can write the following code to insert two new leads:

declare
   l_ctx apex_exec.t_context;
begin
  -- Insert two new leads
   l_ctx := oca_ds.open_dml(
               p_attributes =>'Name,Budget_c,DecisionDate_c',
               p_from       => 'leads');
   -- Insert first lead
   oca_ds.insert_row(l_ctx);
   oca_ds.set_value(l_ctx,'Name','Remy Ratatouille');
   oca_ds.set_value(l_ctx,'DecisionDate_c',date'2024-07-31');
   oca_ds.set_value(l_ctx,'Budget_c',1800);
   -- Insert second lead
   oca_ds.insert_row(l_ctx);
   oca_ds.set_value(l_ctx,'Name','Anton Ego');
   oca_ds.set_value(l_ctx,'DecisionDate_c',date'2024-05-15');
   oca_ds.set_value(l_ctx,'Budget_c',800);
   -- Save the changes
   apex_exec.execute_dml(l_ctx);
   oca_ds.close(l_ctx);
exception
   when others then
      oca_ds.close(l_ctx);
      raise;
end;

Updating a Lead in PL/SQL

Updating data using an FA REST data source follows a similar pattern. Use the oca_ds.open_dml function to create an apex_exec.t_context object. Then for each row you want to update, call oca_ds.update_row() and then use oca_ds.set_value() to set the value of each attribute necessary. Finally, to process the pending changes call apex_exec.execute_dml(). Often you will start by querying the row first to consider its existing values first, before updating some of them. In that case you can call apex_exec.set_values() to copy all the row values from the queried row into the row to be updated.

For example, to update a lead named ‘Anton Ego’ to increase his budget by 20% and adjust his decision date to be a week (7 days) farther in the future, you can write the following code. Notice that we include the APEX$ResourceKey attribute in the list since it’s the unique identifier of the row that’s needed to perform the update.

declare
   l_ctx     apex_exec.t_context;
   l_dml_ctx apex_exec.t_context;
   -- other declarations here
begin
  -- Retrieve and update opportunity with resource key 98765
  -- to increase its amount by 20% and close date by one week
  l_ctx := oca_ds.open_query(
              p_select => 'APEX$ResourceKey,Budget_c,DecisionDate_c',
              p_from   => 'leads',
              p_where  => q'~Name='Anton Ego'~');
   if not apex_exec.next_row(l_ctx) then
      raise_application_error(-20001,'Lead Anton Ego not found');
   end if;
   l_dml_ctx := oca_ds.open_dml(
      p_from => 'leads',
      p_attributes => 'APEX$ResourceKey,Budget_c,DecisionDate_c',
      p_lost_update_detection => true);
   oca_ds.update_row(l_dml_ctx);
   -- Set the row version checksum used for lost update protection
   apex_exec.set_row_version_checksum(
      p_context   => l_dml_ctx,
      p_checksum  => apex_exec.get_row_version_checksum(l_ctx));
   -- Copy the values of the fetched lead into the update row
   apex_exec.set_values(
      p_context         => l_dml_ctx,
      p_source_context  => l_ctx );   
   -- Adjust the budget and decision date appropriately
   oca_ds.set_value(l_dml_ctx,'Budget_c', 
                    oca_ds.get_number(l_dml_ctx,'Budget_c') * 1.2);
   oca_ds.set_value(l_dml_ctx,'DecisionDate_c', 
                    oca_ds.get_date(l_dml_ctx,'DecisionDate_c') + 7);
   -- Save the changes
   apex_exec.execute_dml(l_dml_ctx);
   oca_ds.close(l_dml_ctx);
   oca_ds.close(l_ctx);
exception
   when others then
     oca_ds.close(l_dml_ctx);
     oca_ds.close(l_ctx);
     raise;
end;

Deleting a Lead in PL/SQL

Deleting data using an FA REST data source is a bit simpler. As before, use the oca_ds.open_dml function to create an apex_exec.t_context object. Then for each row you want to delete, call oca_ds.delete_row() and then use oca_ds.set_value() to set the value of the resource key APEX$ResourceKey. Finally, to process the pending changes call apex_exec.execute_dml().

Assuming the system-assigned resource key for the Anton Ego lead is 4, the following code deletes this lead:

declare
   l_dml_ctx apex_exec.t_context;
begin
  -- Delete lead with resource key '4'
   l_dml_ctx := oca_ds.open_dml(
                   p_from       => 'leads',
                   p_attributes => 'APEX$ResourceKey');
   oca_ds.delete_row(l_dml_ctx);
  -- Set the resource key of the row to be deleted
  oca_ds.set_value(l_dml_ctx,'APEX$ResourceKey', '4');
  -- Save the changes
  apex_exec.execute_dml(l_dml_ctx);
  oca_ds.close(l_dml_ctx);
exception
   when others then
     oca_ds.close(l_dml_ctx);
     raise;
end;

Finding the Logged-in User’s PartyId

As a final use case to explore, let’s imagine we are an art gallery salesperson logging in to the APEX app to work our leads for the day. Fusion Apps represents each art gallery staff member – i.e. human resource – as a Resource object in the system. When a staff member is associated with other business objects, that resource’s PartyId attribute value is used as a foreign key.

For example, a lead gets assigned to an “owner” to contact the potential customer to qualify if there is a real opportunity to sell some art. To find the list of leads owned by a particular salesperson like “Matt Hooper”, we need to know the value of Matt’s PartyId and then query the leads REST API to filter for leads where OwnerId equals the logged-in user’s PartyId value.

To simplify using the current APEX user’s PartyId in external filters on various pages of the app, we can look it up using their Username from the resources REST API after authentication and store that PartyId value in APEX session state for the duration of the user’s session. Assume we’ve created a REST Data Source of type Oracle Cloud Applications (SaaS) REST Service for the Fusion Apps resources endpoint and assigned it a static id of resources. Furthermore, assume we’ve created shared component application items named APP_USER_PARTYID and APP_USER_FULLNAME. You can write the following code in a shared component Application Process with the After Authentication process point to lookup the current user’s PartyId and PartyName from the resources REST data source and store those values in the application items:

declare
    l_ctx apex_exec.t_context;
begin
    l_ctx := oca_ds.open_query(
        p_select=> 'PartyId,PartyName',
        p_from => 'resources',
        p_where => 'upper(Username) = '||
                       apex_exec.enquote_literal(:APP_USER));
    if apex_exec.next_row(l_ctx) then
        :APP_USER_FULLNAME := oca_ds.get_varchar2(l_ctx,'PartyName'));
        :APP_USER_PARTYID  := oca_ds.get_number(l_ctx,'PartyId'));
    end if;
    oca_ds.close(l_ctx);
end set_session_info_for_user;

After setting this up, other pages in the app can reference the value of APP_USER_PARTYID in external filters using substitution syntax as we saw earlier in this article. For example, in a region based on the Leads REST data source, to filter the leads to show only ones owned by the logged-in user, we could use an external filter of:

OwnerId = &APP_USER_PARTYID.

Can Any Attribute Appear in a Filter?

A last consideration worth mentioning is that not all business object attributes can be used in a filter predicate. To discover which attributes you can use, consult the Oracle Cloud Apps Data Source Explorer utility app. There you can see if the attribute has a Y or an N in the Queryable? column. The adjective “queryable” is a bit of a misnomer. An attribute with an N in the Queryable? column can still participate in the query in the select list. However, it cannot appear in a filter predicate. For example, as shown below, the InventoryItemDescription attribute in the Leads object is not queryable.

Reviewing which attributes are Queryable in the Oracle Cloud Apps Data Source Explorer

An attempt to reference InventoryItemDescription in a filter predicate results in the error:

Search filter includes a non-queryable column or unsupported operator

To install the Oracle Cloud Apps Data Source Explorer app visit the Gallery > Utility Apps tab and click the Install button like you’ve done for other sample apps. It helps you explore interesting information about all the Fusion Apps REST APIs your applications use.

Downloading the Helper Package

Hopefully, this information about Fusion Apps REST API filters and how to use them will come in handy the next time you create an APEX application that works with these powerful business object APIs. You can download the source of my oca_ds helper package here. For more information, see the Working with REST Data Sources for Oracle Cloud SaaS Applications section of the APEX developer documentation. It explains all the features for working easily with Fusion Applications REST APIs.

Simplify APEX App REST APIs with JSON Duality Views

When writing your APEX apps, it’s best to put business logic in packages. This practice eases maintenance, improves performance, and favors reuse. I set out to combine an Oracle 23c JSON Relational Duality View with my business logic package to simplify building REST APIs for my APEX apps. Read on to learn what I discovered. If you’re already using Oracle 23c Free, download the sample app at the end to try on your own system.

As we’ll see, the duality of JSON and relational interaction is a superpower. You get equally functional read and write access to the same data from both JSON and relational points of view. This packed a productivity punch for the use case I tried. It lets tools that work best with relational data keep doing so, while simultaneously offering easy access to the same data for JSON use cases like external systems integrations. The best of both worlds!

NOTE: The sample app uses APEX 23.2 and Oracle 23c Free (23.3).

(Technical Note: to see a larger version of any figure, right click on it and choose Open Image in New Tab from the context menu…)

Overview of the Data Model

The sample app uses the simple data model shown below, tracking things organized into categories and stocked in certain quantities in warehouses.

Data Model for a simple app tracking categorized things with stock in warehouses

APEX App and Business Logic

The APEX pages in the sample app include an interactive report of things with a companion form-and-grid page to create or edit a thing and its related warehouse stock entries. Two additional grid pages let users edit the warehouses and categories reference data. We’ll focus on the Thing form-and-grid page since that’s where users create and modify data, and where interesting business logic gets enforced.

APEX app is an Interactive Report with Form + Grid page to edit a thing and its stock

The eba_demo_thing package has functions and procedures that implement the app’s business logic. The table lists their names and describes what they do.

Program UnitDescription
validate_warehouseCheck warehouse is valid
validate_categoryCheck category is valid
validate_quantityCheck stock quantity is natural number
validate_availableCheck available date is no older than a year
validate_priceCheck price is between 0.01 & 100,000.00
consolidate_thing_warehousesConsolidate thing stock for same warehouse
closest_ninety_nine_centReturn closest price that ends with 0.99
check_thing_required_fieldsCheck thing required fields are provided
check_stock_required_fieldsCheck stock required fields are provided
Overview of application business logic program units in eba_demo_thing package

As we saw earlier, the Thing page lets users create and edit a thing and its warehouse stock quantities. As shown below in Page Designer, we engage the business logic functions and procedures using validations and page processes in the Processing tab. Each validation is a one-line call to a function in the eba_demo_thing package that returns a boolean, passing in a page item value to be checked as an argument. Multiple Invoke API-type processes call a procedure in the same package to:

  • Adjust the price to a more user-enticing *.99 cent amount
  • Consolidate stock quantities from the same warehouse, if any
  • Default stock entries with zero quantity for any missing warehouses
Validations and Page Processes use business logic in eba_demo_thing package

We can see the business logic in action in the APEX app’s Thing page. Let’s create a new thing in the BOOKS category as shown below. Notice I’ve entered a price of 36.50 that does not end with *.99, and entered two separate stock entries for the same LAS VEGAS warehouse.

Entering a new thing in the BOOKS category with stock in LAS VEGAS warehouse

After clicking the (Create) button to dismiss the dialog, we can edit the new thing to see the page processes adjusted the price to 35.99, consolidated the 12 & 13 quantities in the LAS VEGAS warehouse, and created zero-quantity stock entries for the missing SEATTLE and BALTIMORE warehouses. So everything is working great in the APEX app.

Observing the effects of the application business logic in the web user interface

Letting External Apps Work With Things

In addition to the APEX web app for end users, let’s suppose that external systems need to create, update, and delete things, too. This requires a set of appropriate APIs, and the de facto standard approach involves REST services with JSON data payloads. The external system will use HTTP requests to a well-known “endpoint” URL like:

http://phoenix:9094/ords/diveintoapex/thing/

on our system, using standard “verbs” like:

  • GET – to retrieve things
  • GET – with a unique identifer added to the URL to access a particular thing
  • POST – to create a new thing (i.e. insert)
  • PUT – to replace an existing thing with a modified version (i.e. update)
  • DELETE – to remove an existing thing

All of the requests involve sending and/or receiving thing data in JSON format. So we need a strategy to represent a thing and its related warehouse stock quantities as a JSON document. As we’ll see next, the JSON Relational Duality View can help!

Add JSON Access with a Duality View

An Oracle 23c JSON Relational Duality View is a database view with a single JSON column named data that you can use to read and write information from one or more underlying tables. You define the view using a syntax that mirrors the structure of the JSON you want it to work with. I created a things_dv duality view using the DDL statement below. Starting with the Oracle 23.4 database release, all duality views will require the specially named attribute _id to uniformly act as their unique identifier. Even though I’m working with database release 23.3, I followed that best-practice in my things_dv definition.

Notice how I’ve mentioned my table names eba_demo_things, eba_demo_thing_categories, eba_demo_thing_stock, and eba_demo_warehouse and provided annotations like @insert, @update, @delete to indicate what DML operations I want to allow on each table. The duality view uses the foreign key constraints to understand how the tables relate. For each property, I indicate the JSON attribute name on the left side of the colon and then the appropriate database column name on the right side. When no annotations are present – as is the case for eba_demo_thing_categories and eba_demo_warehouse – then the duality view only allows referencing existing categories and warehouses, respectively.

create or replace json relational duality view things_dv as
eba_demo_things @insert @update
{
    _id         : id
    name        : name
    description : description
    price       : price
    available   : available
    eba_demo_thing_categories @unnest
    {
        categoryId : id
        category   : name
    }
    stock : eba_demo_thing_stock @insert @update @delete
    {
        thingStockId : id
        quantity     : quantity
        eba_demo_warehouse @unnest
        {
            warehouseId : id
            warehouse   : name
        }
    }
}

I used the @unnest annotation to pull properties like categoryId and category up to the same level in the JSON as name, price, and available. I’ve also used it to pull the warehouseId and warehouse properties up to the same level as quantity. Without using this annotation, the properties from a related object will appear as a “nested” JSON object with an appropriate property name. The choice is up to the developer.

After creating the duality view, we can use our favorite tool like SQLcl or the SQL Developer extension for VS Code to query one of the things to see what it looks like in JSON. Let’s retrieve the one whose unique id is 2. The json_serialize() function takes the JSON data column name, and the returning clob pretty option lets the database know the JSON might exceed 4000 characters and should be formatted with nice indentation. We’ll see the powerful json_value() function multiple times throughout this article. It lets you easily access the value of any part of the JSON document you pass in, based on the flexible JSON Path expression language. Here we’re using the simplest possible expression to refer to the _id property at the “top” or root of the JSON document:

set pagesize 999
set long 2000
select json_serialize(data returning clob pretty) as data
  from things_dv
 where json_value(data,'$._id') = 2
/

The result shows data from all four tables in our data model in a single JSON document reflecting the structure we defined using the DDL duality view definition above. It includes some additional “bookkeeping” info in a _metadata property as well that is automatically added.

DATA
------------------------------------------------------------------------
{
  "_id" : 2,
  "_metadata" :
  {
    "etag" : "DC3F16548E190F49130482301365EF1C",
    "asof" : "0000000000C514BC"
  },
  "name" : "Vibrant Horizons",
  "description" : "A compilation of live performances from the renowned
   jazz festival, capturing the essence of contemporary jazz.",
  "price" : 22.99,
  "available" : "2024-05-20T00:00:00",
  "categoryId" : 2,
  "category" : "MUSIC",
  "stock" :
  [
    {
      "thingStockId" : 4,
      "quantity" : 7,
      "warehouseId" : 3,
      "warehouse" : "BALTIMORE"
    },
    {
      "thingStockId" : 5,
      "quantity" : 2,
      "warehouseId" : 2,
      "warehouse" : "SEATTLE"
    },
    {
      "thingStockId" : 6,
      "quantity" : 6,
      "warehouseId" : 1,
      "warehouse" : "LAS VEGAS"
    }
  ]
}

A duality view is not just for querying data. It supports insert, update, and delete operations, too, if your duality view definition includes annotations allowing those. For example, we can insert a new thing in the BOOKS category with a quantity of 25 in the LAS VEGAS warehouse using the INSERT statement below:

 insert into things_dv(data)
 values(q'~
 {
    "name"        : "Building Oracle XML Applications",
    "description" : "Master Oracle using XML, SQL, XSLT, and XSQL pages",
    "price"       : 32.99,
    "available"   : "2023-09-01",
    "category"    : "BOOKS",
    "stock"       : [
        {
            "warehouse" : "LAS VEGAS",
            "quantity"  : 25
        }
    ]
 }
 ~')

Even before committing the inserted data, we can immediately wield the power of JSON/relational duality by querying the same data back from the underlying relational tables. The figure below shows the result of using SQL Developer in VS Code to run a familiar, relational query in the same pending transaction where we just inserted the JSON document using the duality view. I wanted to find all things with a particular name, a category name of BOOKS, and 25 or more quantity in stock in the LAS VEGAS warehouse. Our newly inserted book shows up in the results! Also notice the joins with my category and warehouse tables work because the duality view used the unique indexes on the category name (BOOKS) and warehouse name (LAS VEGAS) to lookup the respective primary key values. Of course, at this point we can commit the transaction to make the new thing permanent, or rollback to abandon the inserted data.

After inserting thing in JSON via duality view, we can requery it from the tables

With the things_dv duality view in place, we have part of the solution sorted. And we’ve seen we can work with the duality view using SQL operations directly. Next, let’s focus on letting an external app access the JSON from the duality view using an ORDS REST service.

When using REST services in production, you will of course secure their access using one of the robust ways ORDS supports for doing that. But since here we’re in a learning mode, I’m creating services with no authentication for simplicity.

ORDS AutoREST for Duality Views

The easiest way to expose a duality view to REST clients is to enable AutoREST access. We can do this from SQL Developer (Web), by calling the ords.enable_object procedure, or by going to SQL Workshop > RESTful Services in the APEX Builder. By selecting the Enabled Objects in the RESTful Data Services tree, you’ll see a list of all AutoREST enabled objects. I clicked (Create AutoREST Object) and chose the THINGS_DV view from the list to enable it. I left the Object Alias the same as the name of the duality view, but as shown in the figure below you can adjust the name if you prefer. This alias is what appears in the URL that REST clients will use, as reflected in the Full URL field below.

After enabling the THINGS_DV Duality View for AutoREST access

Once we’ve AutoREST-enabled the duality view, we can create a dog_days.json file containing the following example JSON document:

 {
    "name"        : "David Hockney's Dog Days",
    "description" : "Colourful portraits of Stanley and Boodgie",
    "price"       : 32.99,
    "available"   : "2023-09-01",
    "category"    : "BOOKS",
    "stock"       : [
        {
            "warehouse" : "LAS VEGAS",
            "quantity"  : 6
        }
    ]
 }

Then, we can use a command-line utility like curl to insert the Dog Days book as a thing into our database using the AutoREST-enabled duality view.

% curl -X POST \
-H "Content-Type: application/json" \
-d @dog_days.json \
http://phoenix:9094/ords/diveintoapex/things_dv/

The REST service responds with the JSON document that now contains the system-assigned _id property, an additional _metadata section, and a links array with multiple URLs related to the thing we just inserted. For brevity, I’ve replaced the actual links array entries with “…” below.

{"_id":22,"name":"David Hockney's Dog Days",
"description":"Colourful portraits of Stanley and Boodgie",
"price":32.99,"available":"2023-09-01T00:00:00",
"categoryId":1,"category":"BOOKS",
"stock":[{"thingStockId":232,"quantity":6,
"warehouseId":1,"warehouse":"LAS VEGAS"}],
"_metadata":{"etag":"64EC3C30EB434F8825FA09BB76EA49A6",
"asof":"0000000000BE113B"},"links":[...]}

What Checks Does a Duality View Enforce?

An AutoREST-enabled duality view is useful to quickly setup REST access for the same set of apps that you would trust to perform SELECT, INSERT, UPDATE, and DELETE operations against your duality view directly. The database will enforce basic datatype checking, not null columns, as well as ensure all check constraints, unique constraints, and referential integrity constraints are valid. However, the database is not aware of the business logic that you’ve implemented in your APEX application. For example, suppose we update the contents of the dog_days.json file as follows:

{
    "name"        : "David Hockney's Dog Days",
    "description" : "Colourful portraits of Stanley and Boodgie",
    "price"       : 32.85,
    "available"   : "2000-09-01",
    "stock"       : [
        {
            "warehouse" : 'LAS VEGAS'
            "quantity"  : 6
        },
        {
            "warehouse" : 'LAS VEGAS'
            "quantity"  : 5
        }
    ]
 }

After repeating the curl command above to POST the document to the AutoREST-enabled duality view endpoint, we see that the JSON document returned looks like this:

{"_id":23,"name":"David Hockney's Dog Days",
"description":"Colourful portraits of Stanley and Boodgie",
"price":32.85,"available":"2000-09-01T00:00:00",
"stock":[{"thingStockId":233,"warehouseId":1,
"warehouse":"LAS VEGAS","quantity":6},{"thingStockId":234,"warehouseId":1,"warehouse":"LAS VEGAS","quantity":5}],
"_metadata":{"etag":"EFF821D650F846223E9298C5911B0D70",
"asof":"0000000000BE22CC"},"links":[...]}

Understandably, the price of the thing was not adjusted to the nearest 99-cent price. The two quantities related to the LAS VEGAS warehouse were not consolidated, the stock entries for the missing warehouses were not defaulted, and the available date from the year 2000 was allowed even though the application requires it to be within the last year or in the future.

Some kinds of business logic can be written as database table triggers, but more complex use cases like the warehouse quantity consolidation or ones involving querying and changing multiple related tables can run into mutating table errors using that approach. In many cases, developers find it best to represent their business logic in an application layer of PL/SQL packages as we’ve done here with the eba_demo_thing package documented above.

If we know ahead of time that the AutoREST API will be used by an app we trust to always insert and update valid data, or if we have enforced everything we need as table constraints or in table triggers, then the AutoREST-enabled duality view alone is all we’d need. However, if we want to enforce all the same business logic that you implement in your APEX application, then instead of the AutoREST approach, we need to use ORDS REST services that call our own PL/SQL APIs . We’ll explore that option next.

Creating External API with Business Logic

I started by creating a second eba_demo_thing_json package to handle the JSON-specific operations that we’ll surface as web services using ORDS. Doing this makes it possible to write unit tests that exercise the JSON API without involving HTTP requests. As shown below, there are functions to get multiple things; get, post, or put a thing and return it; and a procedure to delete a thing. Notice that I’ve used the new json type instead of json_object_t so I can easily use JSON path expressions on the contents. We’ll see multiple examples below. 

create or replace package eba_demo_thing_json as
    -- Get Things with optional search
    ------------------------------------------------- 
    function get_things(
        p_offset in number   default null,
        p_limit  in number   default null,
        p_search in varchar2 default null)    
        return      json;
    
    -- Get a Thing by id
    -------------------------------------------------
    function get_thing(p_id in number)   
    return json;   
    -- POST (Insert) a new Thing
    -------------------------------------------------
    function post_thing(p_json in json)   
    return json;   
    -- PUT (Update & Replace) an existing Thing by id
    -------------------------------------------------
    function put_thing(p_id in number, p_json in json)   
    return json;   
    -- DELETE an existing Thing by id
    -------------------------------------------------
    procedure delete_thing(p_id in number);   
end eba_demo_thing_json;

If you’ve used json_object_t in the past, no worries. The key difference is that values of json type can also participate in SQL statements directly, while ones of json_object_t type are useful in PL/SQL only. It is straightforward to work with the same JSON document in PL/SQL as either json or json_object_t as necessary. You can pass a json value to the json_object_t constructor to work with it using that interface, and calling the to_json method on a json_object_t value returns it as a json value instead. I prefer using the simpler-looking json type in my APIs to more easily involve its values in json_value and json_transform expressions in my code explained later.

A third package eba_demo_thing_ords encapsulates the ORDS-specific aspects of the service implementation, like writing out the HTTP response payload. Its procedures are a thin wrapper around the JSON API package program units, and accept the incoming payload as CLOB arguments, since that is how ORDS makes it available to us as we’ll see in the next step.

create or replace package eba_demo_thing_ords as 
    -- GET Things with optional search
    ------------------------------------------------- 
    procedure get_things(
        p_offset in number   default null,
        p_limit  in number   default null,
        p_search in varchar2 default null);
  
    -- GET a Thing by id
    --------------------------------------------
    procedure get_thing(p_id number); 
    -- POST (Insert) a new Thing
    --------------------------------------------
    procedure post_thing(p_json clob); 
    -- PUT (Update & Replace) an existing Thing
    --------------------------------------------
    procedure put_thing(p_id number, p_json clob);
    -- DELETE an existing Thing by id
    --------------------------------------------
    procedure delete_thing(p_id number);   
end eba_demo_thing_ords;

With these two packages in place, I used the APEX RESTful Services editor to define a thing module with templates for / and :id, and added appropriate one-line PL/SQL block handlers for each one. The figure below shows what the POST handler for /thing/ looks like. It’s a one-line call to eba_demo_thing_ords.post_thing passing in the special ORDS :body_text bind variable that contains the incoming request payload as a CLOB.

Handler for /thing/ template’s POST operation, passing request payload with :body_text

The complete set of operations I configured includes:

  • GET /thing/ →  eba_demo_thing_ords.get_things
  • POST /thing/ →  eba_demo_thing_ords.post_thing
  • DELETE /thing/:id →  eba_demo_thing_ords.delete_thing
  • GET /thing/:id →  eba_demo_thing.get_thing
  • PUT /thing/:id →  eba_demo_thing.put_thing

Trying External API with Business Logic

Now, if we use the endpoint URL for our PL/SQL-based ORDS REST service to post the Dog Days JSON document from the command line:

% curl -X POST \
-H "Content-Type: application/json" \
-d @dog_days.json \
http://phoenix:9094/ords/diveintoapex/thing/  

We get back an error that the category is missing, which I had not noticed before. 

{"$error":"Required field missing: Category"}

After updating dog_days.json to have a category property and trying again to post the file using curl…

{
    "name"        : "David Hockney's Dog Days",
    "description" : "Colourful portraits of Stanley and Boodgie",
    "price"       : 32.85,
    "category"    : "BOOKS", 
    "available"   : "2000-09-01",
    "stock"       : [
        {
            "warehouse" : "LAS VEGAS",
            "quantity"  : 12
        },
        {
            "warehouse" : "LAS VEGAS",
            "quantity"  : 13
        }        
    ]
}

We get an error that the available date is too far in the past:

{"$error":"A new thing's available date must be in the past year or the future"}

Hoping the third time’s the charm, after correcting the dog_days.json available date value to be 2023-09-01 and trying the curl command again… It works and we get the following response below. Notice the price got adjusted to 31.99 and the categoryId & warehouseId got filled in appropriately. The stock quantities for the two LAS VEGAS warehouse rows got consolidated, and the missing warehouse entries were defaulted to have a zero stock.

{ "_id":21,
  "name":"David Hockney's Dog Days",
  "description":"Colourful portraits of Stanley and Boodgie",
  "price":31.99,
  "available":"2023-09-01T00:00:00",
  "categoryId":1,
  "category":"BOOKS",
  "stock":[
    { "thingStockId":221,
      "quantity":25,
      "warehouseId":1,
      "warehouse":"LAS VEGAS"}, 
    { "thingStockId":224,
      "quantity":0,
      "warehouseId":2,
      "warehouse":"SEATTLE"},
    { "thingStockId":225,
      "quantity":0,
      "warehouseId":3,
      "warehouse":"BALTIMORE"}
   ],
   "_metadata":{ "etag":"2EA7CCB8CA550DCAE2E0180D61267D9F",
                 "asof":"0000000000BF5695"}}

Next let’s try an update. If we change our dog_days.json file to contain the contents returned by our most recent curl command above, we can then edit it to adjust the values of the price, available, and quantity values for the three warehouses as shown below:

{ "_id":21,
  "name":"David Hockney's Dog Days",
  "description":"Colourful portraits of Stanley and Boodgie",
  "price":29.45,
  "available":"2024-03-04",
  "categoryId":1,
  "category":"BOOKS",
  "stock":[
    { "thingStockId":221,
      "quantity":11,
      "warehouseId":1,
      "warehouse":"LAS VEGAS"}, 
    { "thingStockId":224,
      "quantity":22,
      "warehouseId":2,
      "warehouse":"SEATTLE"},
    { "thingStockId":225,
      "quantity":33,
      "warehouseId":3,
      "warehouse":"BALTIMORE"}
   ],
   "_metadata":{ "etag":"2EA7CCB8CA550DCAE2E0180D61267D9F",
                 "asof":"0000000000BF5695"}}

After saving the edits to this file, we can try updating the thing business object by using curl to PUT the document back at its specific URL that includes the 21 value as its resource key at the end of the URL:

% curl -X PUT \
-H "Content-Type: application/json" \
-d @dog_days.json \
http://phoenix:9094/ords/diveintoapex/thing/21 

Notice in the response that the price was adjusted to the nearest ninety-nine cents, the warehouse quantities were updated, and the etag value changed.

{ "_id":21,
  "name":"David Hockney's Dog Days",
  "description":"Colourful portraits of Stanley and Boodgie",
  "price":28.99,
  "available":"2024-03-04T00:00:00",
  "categoryId":1,
  "category":"BOOKS",
  "stock":[
    { "thingStockId":221,
      "quantity":11,
      "warehouseId":1,
      "warehouse":"LAS VEGAS"},
    { "thingStockId":224,
      "quantity":22,
      "warehouseId":2,
      "warehouse":"SEATTLE"},
    { "thingStockId":225,
      "quantity":33,
      "warehouseId":3,
      "warehouse":"BALTIMORE"}],
  "_metadata":{ "etag":"AD67594C22A913DB020442DC2F68D2DA",
                "asof":"0000000000BF9CED"}}

By leveraging the power of a duality view and ORDS REST services, and combining them with our business logic in a PL/SQL package, we achieved the goal we set out to accomplish. We now have the same application logic shared by both our APEX app and JSON-based REST APIs. Now let’s learn a bit more about the implementation details.

Flexing Our JSON/Relational Duality

If we switch back to the APEX application, we can edit the new thing we were just working with using JSON to see the latest information about David Hockney’s Dog Days book in the web UI.

Editing the thing in APEX that was just updated through the JSON REST API

Let’s see what happens if we use the APEX application to edit something about this thing, for example the quantity available of David Hockney’s Dog Days book in the SEATTLE warehouse. After adjusting the amount from 22 to 99 and clicking (Apply Changes), let’s repeat the curl command to update the same thing from the command line.

% curl -X PUT \
-H "Content-Type: application/json" \
-d @dog_days.json \
http://phoenix:9094/ords/diveintoapex/thing/21

This time the update fails and we see the error that the thing was changed by another user.

{"$error":"Thing changed by another user. Get the thing again and retry."}

The duality view automatically computes the etag as a checksum of the thing object’s data, including data of its nested stock information. The act of updating the SEATTLE warehouse quantity above changed data that was intrinsic to the David Hockney’s Dog Days thing object, so that caused its etag checksum to change. The value of the etag present in the dog_days.json file we are trying to PUT back to the JSON REST API no longer matches and the duality view automatically fails the update. By default the etag checksum takes all object properties into account, but the developer can use the @nocheck annotation when defining the duality view on properties that she doesn’t want to be included in the checksum calculation.

Exploring JSON Service API Implementation

The implementation of the post_thing function in the eba_demo_thing_json package performs four basic steps:

  1. Performs before insert or update processing
  2. Inserts JSON payload into things_dv duality view, returning _id value
  3. Executes after insert or update processing
  4. Returns the latest version of thing object from the duality view by id
-- PACKAGE: eba_demo_thing_json 
-----------------------------
-- POST (Insert) a new Thing
-----------------------------      
function post_thing(  
    p_json in json)   
    return    json   
is   
    l_id number;
    l_json json := strip_links(p_json);  
begin  
    before_insert_or_update(l_json,c_operation_insert); /* 1 */      
    insert into things_dv(data)                         /* 2 */
    values (l_json)    
    returning json_value(data,'$._id') into l_id;  
    after_insert_or_update(l_id,c_operation_insert);    /* 3 */
    return get_thing(l_id);                             /* 4 */
end post_thing;

The put_thing function is nearly identical. It does an update statement on the things_dv duality view instead of an insert and passes the constant c_operation_update to the before and after processing procedures.

-- PACKAGE: eba_demo_thing_json 
--------------------------------------------------------------
-- PUT (Update & Replace) an existing Thing by id
--------------------------------------------------------------       
function put_thing(p_id number, p_json json)   
return json   
is   
    l_json json := strip_links(p_json);  
begin  
    before_insert_or_update(l_json,c_operation_update);  /* 1 */
    update things_dv                                     /* 2 */
    set data = l_json   
    where json_value(data,'$._id') = p_id;  
    after_insert_or_update(p_id,c_operation_update);     /* 3 */
    return get_thing(p_id);                              /* 4 */
end put_thing;

Take another look at the elegant simplicity of the code above. By incorporating the duality view, we reduce a huge amount of coding work to a single line of declarative SQL. The INSERT statement does the “magic” to create a new JSON-based thing, and the UPDATE statement handles changing an existing thing.

This saves us from writing any code related to saving the initial or modified JSON document to the underlying tables, performing the lookups related to the category and warehouse, as well as detecting whether the row has been changed by another user. We don’t have to detect what has changed, figure out which underlying tables to involve, or anything really. We just supplement the simple SQL INSERT or UPDATE operation with the additional business logic before and after that is unique to our application.

Dissecting Before Insert/Update Logic

The before_insert_or_update procedure is below. It receives the JSON document as an in out nocopy parameter that it can reason over, pass around to other procedures, and modify as necessary before the resulting document will be inserted into or updated in the duality view. We’ve organized the code to make it easy to understand. We perform required fields checks, ensure there’s at least one warehouse, and adjust any missing or null warehouse quantities to be zero. Then we validate the available date if we’re performing an insert, and then validate the price, category, and warehouse and quantity values. Finally we adjust the price to the nearest 99-cent value.

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- BEFORE INSERT OR UPDATE ON Thing business logic
--------------------------------------------------------------        
procedure before_insert_or_update(  
    p_json      in out nocopy json,  
    p_operation t_operation)  
is  
begin  
    check_required_fields(p_json);
    check_at_least_one_warehouse(p_json);
    adjust_missing_or_null_qty(p_json);
    if p_operation = c_operation_insert then
        validate_available(p_json); 
    end if;
    validate_price(p_json);
    validate_category(p_json);
    validate_warehouse_and_qty(p_json);
    adjust_price_to_99_cents(p_json);
end before_insert_or_update;

The helper procedures are all part of the sample app you can download and study, so we won’t analyze every line here. However, we’ll focus on three that taught me interesting techniques I discovered while building the demo.

In adjust_price_to_99_cents, notice how we can use json_value to access the value of any property in the JSON document using an appropriate JSON path expression. Here we’re accessing the price to pass it to another function that computes the closest 99-cent price. If the l_new_price is different from the l_price then we use the json_transform function to update the value of the price property.

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- Adjust price to nearest *.99 
-------------------------------------------------------------- 
procedure adjust_price_to_99_cents(p_json in out nocopy json)
is
    l_price     eba_demo_things.price%type;
    l_new_price eba_demo_things.price%type;
begin
    l_price     := json_value(p_json,'$.price');
    l_new_price := eba_demo_thing.closest_ninety_nine_cent(l_price);
    if l_price != l_new_price then 
        select json_transform(p_json,  
                set '$.price' = l_new_price)
            into p_json;
    end if;
end adjust_price_to_99_cents;

In the adjust_missing_or_null_qty procedure we’re using a slightly more complex JSON path expression to update any stock array entries with a null or missing quantity property to set their quantity = 0. These examples only scratch the surface of what json_transform can do. Please see the documentation to learn more about its abilities to easily make multiple changes to a JSON document with a compact, familiar syntax.

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- Adjust missing or null quantities to zero 
-------------------------------------------------------------- 
procedure adjust_missing_or_null_qty(p_json in out nocopy json)
is
begin
    select json_transform(p_json,  
            set '$.stock[*]?(@.quantity == null ||
                             !exists(@.quantity)).quantity' = 0)
        into p_json;  
end adjust_missing_or_null_qty;

The last interesting thing I’ll highlight is the ability for json_path to return values into user-defined types, including collection types. This makes processing JSON data a lot more natural for PL/SQL developers. In the validate_warehouse_and_qty procedure, notice I’ve defined a t_stock record type and a t_stock_tab type that’s a table of t_stock records. With the new support in 23c we can use the returning t_stock_tab clause of the json_value function to easily assign the array of stock objects to the l_stock_tab variable of the same t_stock_tab type. Then, we can use a familiar PL/SQL loop to process the results. Here I’m using it to validate the quantity and warehouse values of each stock array entry.

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- Validate warehouse and quantity values
--------------------------------------------------------------       
procedure validate_warehouse_and_qty(p_json in json)
is
    type t_stock is record( 
        warehouse   eba_demo_warehouse.name%type,
        warehouseId eba_demo_thing_stock.warehouse%type,
        quantity    eba_demo_thing_stock.quantity%type
    );
    type t_stock_tab is table of t_stock;
    l_stock_tab           t_stock_tab;    
begin
    l_stock_tab := json_value(p_json,'$.stock'
                     returning t_stock_tab error on error);
    for j in 1..l_stock_tab.count loop
        if not eba_demo_thing.validate_quantity(
                  l_stock_tab(j).quantity) then
            error('INVALID_QUANTITY');  
        end if;
        if not eba_demo_thing.validate_warehouse(
                  l_stock_tab(j).warehouse,
                  l_stock_tab(j).warehouseid) then
            error('INVALID_WAREHOUSE');  
        end if;
    end loop; 
end validate_warehouse_and_qty;

Examining After Insert/Update Logic

In the after_insert_or_update procedure, we consolidate the warehouses and if we’re performing an insert operation, default any missing warehouses. Here again I took advantage of the JSON/Relational duality since I’m reusing shared business logic from the eba_demo_thing package that I wrote using familiar relational table processing code.

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- AFTER INSERT OR UPDATE ON Thing business logic
--------------------------------------------------------------         
procedure after_insert_or_update(  
    p_id        in number,  
    p_operation t_operation)  
is  
begin  
    eba_demo_thing.consolidate_thing_warehouses(p_id);
    if p_operation = c_operation_insert then
        eba_demo_thing.default_missing_warehouses(p_id);
    end if;
end after_insert_or_update;

For example, in default_missing_warehouses I found it easiest to get the job done using a merge into statement with a when not matched clause. With JSON/Relational duality, you can use the right tool for the job without worrying about keeping the JSON and relational worlds in sync.

-- PACKAGE: eba_demo_thing
--------------------------------------------------------------
-- Default any missing warehouses for a thing with zero stock
--------------------------------------------------------------
procedure default_missing_warehouses(p_id in number)
is
begin
    merge into eba_demo_thing_stock tgt
    using (select id from eba_demo_warehouse) src
    on (src.id = tgt.warehouse and tgt.thing_id = p_id)
    when not matched then
    insert (thing_id,warehouse,quantity)
    values (p_id,src.id,0);
end default_missing_warehouses;

Sharing Both Logic and Error Messages

As shown below, the APEX application defines translatable error messages as Shared Component text messages.

Translatable text messages used to inform end-users about business logic errors

In the context of the Thing page in the APEX app, the validations reference these messages using the built-in substitution variable syntax &APP_TEXT$MESSAGE_KEY. with the prefix APP_TEXT$ followed by the name of the translatable text. For example, as shown below the Validate Category validation references &APP_TEXT$INVALID_CATEGORY. in the Error Message setting.

Using APEX_TEXT$ substitution string to reference INVALID_CATEGORY text message

I wanted all the JSON API business logic to use the same text messages, so I implemented the error() function below to let the eba_demo_thing_json package code get the job done. By setting the name of the workspace, it sets the the right context to reference any error message by key for a given app id using the apex_lang.message function.

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- Raise error based on translatable message using message key
--------------------------------------------------------------       
procedure error(
    p_key    in varchar2,
    p0       in varchar2 default null)
is
begin
    apex_util.set_workspace(c_workspace);
    raise_application_error(-20001,
        apex_lang.message(p_name           => p_key,
                          p0               => p0,  
                          p_application_id => c_app_id));
end error;

Then, throughout the code in eba_demo_thing_json, code calls this error() function. One example of this is in the validate_category function below:

-- PACKAGE: eba_demo_thing_json
--------------------------------------------------------------
-- Validate category
--------------------------------------------------------------       
procedure validate_category(
    p_json in json)
is
begin
    if not eba_demo_thing.validate_category(
                             json_value(p_json,'$.category'),
                             null) then  
        error('INVALID_CATEGORY');  
    end if;   
end validate_category;

Pagination, Filtering & Total Count in SQL

For my last “trick”, I found I needed to implement pagination and searching functionality for the GET /thing/ endpoint. My first instinct was to define the GET /thing/ handler as an ORDS Collection Query type handler with the query:

select data
from things_dv

However, this setup returned the data in a way that was different from the AutoREST-enabled duality view’s GET /things_dv/ endpoint we saw earlier. In particular, each JSON document is understandably presented as the value of the data column in the query result like this:

{
  "items" : [
      {"data":{"_id":1, ... }},
      {"data":{"_id":2, ... }},
      {"data":{"_id":3, ... }}
  ],
  "links" : [
    :
  ]
}

After checking with colleagues on the ORDS team to ask if there was some declarative way to suppress the data column’s appearance in the results, I learned there wasn’t currently a way to achieve that. This makes sense, since AutoREST is optimized for ease of use. When you enable a table, view, or duality view for AutoREST access, you instantly get a rich set of filtering, pagination, and CRUD capabilities. However, once I needed to route the the POST, PUT, and DELETE operations through my own PL/SQL package API to enforce app-specific business logic, I learned it wasn’t yet possible to take advantage of the AutoREST functionality only for the GET operation.

In the end, I found a SQL-only solution to achieve a simple set of pagination and filtering features for my GET handler that I implement in the get_things procedure of the eba_demo_thing_json package:

---------------------------------------
-- Get Things with optional search
--------------------------------------- 
function get_things(
    p_offset in number   default null,
    p_limit  in number   default null,
    p_search in varchar2 default null)
    return      json;

After creating the thin wrapper procedure in eba_demo_thing_ords, I defined the GET /thing/ handler as shown below, with skip, pageSize, and search parameters defined as shown.

Configuring the GET /thing/ handler

The get_things function body is below. It returns the JSON document created by a SQL statement whose three parts include:

  1. Common table expression x to select thing ids based on an optional p_search string (matching on name and description properties) and compute total rows
  2. Common table expression y to select up to p_limit rows from things_dv using the ids x identifies and also determine has_more status (true/false)
  3. Main query that constructs JSON document with an items array of thing duality view documents from y, adding in skip, pageSize, hasMore, and totalRows properties.
-------------------------------------------------
-- Get Things with optional search
------------------------------------------------- 
function get_things(
    p_offset in number   default null,
    p_limit  in number   default null,
    p_search in varchar2 default null)    
    return      json
is
    l_ret json;
begin
    with x as ( 
        select json_value(data,'$._id') as id,  
                count(*) over ()         as total_rows 
            from things_dv 
            where(   p_search is null   
                or upper(json_value(data,'$.name'))
                     like '%'||upper(p_search)||'%'   
                or upper(json_value(data,'$.description'))
                     like '%'||upper(p_search)||'%' )                      
        order by json_value(data,'$._id.number()') 
        offset nvl(p_offset,0) rows  
        fetch next nvl(p_limit,3) + 1 rows only 
    ), 
    y as (select data,  
                    case  
                    when (select count(*) 
                            from x) > nvl(p_limit,3)  
                    then true  
                    else false 
                    end as has_more 
            from things_dv 
            where json_value(data,'$._id')
                  in (select id from x) 
            order by json_value(data,'$._id.number()') 
            fetch first nvl(p_limit,3) rows only
    ) 
    select json_object( 
            'items' value ( 
                select json_arrayagg(data  
                            order by json_value(data,
                                    '$._id.number()')  
                            returning clob)  
                from y), 
            'pageSize'   value nvl(p_limit,3), 
            'skip'       value nvl(p_offset,0), 
            'hasMore'    value (select has_more 
                                  from y
                                 fetch first row only), 
            'totalRows'  value (select total_rows 
                                  from x
                                 fetch first row only) 
        returning clob)
        into l_ret;    
    return l_ret;
end get_things;

The result is that GET-ing a URL like:

http://phoenix:9094/ords/diveintoapex/thing/?pageSize=2

returns a response like:

{
    "items": [
        {
            "_id": 1,
            "name": "Echoes of Silence",
            "description": "Debut album featuring a mix of instrumental and electronic sounds by emerging artist Luna Wave.",
            "price": 15.99,
            "available": "2024-03-01T00:00:00",
            "categoryId": 2,
            "category": "MUSIC",
            "stock": [ ... ],
            "_metadata": {
                "etag": "FBC495C12B222462B92C943C65C1D951",
                "asof": "0000000000C68DB7"
            }
        },
        {
            "_id": 2,
            "name": "Vibrant Horizons",
            "description": "A compilation of live performances from the renowned jazz festival, capturing the essence of contemporary jazz.",
            "price": 22.99,
            "available": "2024-05-20T00:00:00",
            "categoryId": 2,
            "category": "MUSIC",
            "stock": [ ... ],
            "_metadata": {
                "etag": "DC3F16548E190F49130482301365EF1C",
                "asof": "0000000000C68DB7"
            }
        }
    ],
    "pageSize": 2,
    "skip": 0,
    "hasMore": true,
    "totalRows": 9
}

Adding a search parameter to narrow the results like this:

http://phoenix:9094/ords/diveintoapex/thing/?search=world&pageSize=2

yields a response like:

{
    "items": [
        {
            "_id": 5,
            "name": "The Hidden Truth",
            "description": "Documentary uncovering the mysteries of the ancient world, narrated by acclaimed historian Dr. Emily Santos.",
            "price": 9.99,
            "available": "2024-07-22T00:00:00",
            "categoryId": 3,
            "category": "MOVIES",
            "stock": [ ... ],
            "_metadata": {
                "etag": "7674537EF60C2FB955256B1B3A1AA824",
                "asof": "0000000000C68D4B"
            }
        },
        {
            "_id": 6,
            "name": "Comedy Nights",
            "description": "Box set of the most hilarious stand-up comedy specials from the decade, featuring top comedians worldwide.",
            "price": 19.99,
            "available": "2024-08-05T00:00:00",
            "categoryId": 3,
            "category": "MOVIES",
            "stock": [ ... ],
            "_metadata": {
                "etag": "B6CBA70E784BFA0810D87A0E4CF83A72",
                "asof": "0000000000C68D4B"
            }
        }
    ],
    "pageSize": 2,
    "skip": 0,
    "hasMore": false,
    "totalRows": 2
}

Installing the Sample

Since the JSON Relational Duality View feature is new in Oracle 23c, running the sample app yourself requires your downloading and using Oracle 23c Free. The APEX sample app is from version APEX 23.2, so you will also have to download the latest APEX release and install it into your Oracle 23c database. Using the latest available ORDS release is always recommended as well. This is the combination I’ve tested and documented here. Then, you can download the sample app from here and import it into your APEX 23.2 workspace.

In addition to these prerequisites, the only other installation step is to edit the source of the eba_demo_thing_json package to update the value of the two constants c_workspace and c_app_id to reflect the name of the workspace into which you install the sample and the application id the sample gets assigned in your workspace:

create or replace package body eba_demo_thing_json as 
    -- TODO: Change the values of these two constants
    -- ~~~   to reflect your workspace and app id
    c_workspace constant varchar2(50) := 'DIVEINTOAPEX';
    c_app_id    constant number       := 100;
  :
end eba_demo_thing_json;

Hope you find the example useful!

Dipping My Toe into Workflow

APEX workflow is a new feature I was keen to experiment with. In a familiar visual designer, you automate a business process with a flow diagram. Its sequence of activities can include business logic, conditional branching, local or remote API calls, time delays, email, and push notifications. It’s also easy to wait for approvals or action tasks end users need to complete. Should your needs go beyond the core activity set, you can develop custom ones or use community-created activities using process plug-ins. I’m eager to share my initial experiences to offer insights I gathered along the way. You’ll find links at the end to download the sample app and to read additional APEX Workflow articles of interest by my colleagues.

(Technical Note: to see a larger version of any figure, right click on it and choose Open Image in New Tab from the context menu…)

Calculated Simplicity

To learn APEX workflow, I thought of the simplest process a math major’s mind could muster: adding two numbers. “Hey! That’s not a business process!” you rightly note, but humor me. This approach helped me understand all the “moving parts” first before embellishing my simple calculator workflow to include decisions and notifications. I decided to start small.

I created an eba_demo_simplecalc package and wrote the following compute() function to represent some parameterized business logic that my app needs to perform. It accepts two number operands and the operation to perform on them (plus, minus, multiplication, or division):

-- In package eba_demo_simplecalc
function compute(
    p_operand1  in number,
    p_operation in varchar2,
    p_operand2  in number)
    return         number
is
begin
    return case p_operation
                when '+' then p_operand1 + p_operand2
                when '-' then p_operand1 - p_operand2
                when '*' then p_operand1 * p_operand2
                when '/' then p_operand1 / p_operand2
           end;
end compute;

Then I built the simple Calculate Without Workflow page below to start with a baseline calculator page that did not use workflow at all. It lets a user enter two number values and has a select list with the four available operations. A display-only page item shows the computed result after the user clicks the (Calculate) button.

Calculate Without Workflow page invokes parameterized business logic and shows the result

As shown below, this simple page has a single entry in the Processing section that invokes the compute() function using an Invoke API page process. It has a Server-side Condition to run when the Calculate button is pressed, and I configured its three parameters to get their values from the corresponding page items and return the function result into the display only page item. I quickly had a working calculator page that let a user add, subtract, multiply, or divide any two numbers. In this baseline page, no data is stored in any table. The operands, operation, and computed result only live in APEX session state.

Invoke API page process calls the COMPUTE() function, passing page item values as parameters

Reusing Logic in a Business Process

Next, I learned how to reuse my compute() business logic from a workflow. Under Shared Components > Workflows, I clicked (Create) to get started. I was already familiar with the Invoke API page process, so using the Property Editor I instinctively changed the type of the default activity in the visual designer from Execute Code to an Invoke API type activity instead. The Workflow Designer looks and works like Page Designer, so I already knew how to use it. Next, I needed to configure the compute() function’s inbound parameters and its function return, so that made me think, “Where will these values come from and where will the computed result go?”

Workflow with three input parameters, one result variable, and a Calculate Result activity

A workflow is a “headless” process that the APEX engine runs in the background using a session that’s distinct from those of end users accessing pages. So, the workflow has no access to pages or page items. Instead, it accepts initial values at start time using parameters. These values are read-only. While the workflow is running – which can last for a few seconds or many weeks depending on the business process – APEX automatically manages the storage and retrieval of workflow variable values you can change over time. As shown in the figure above, my Simple Calc (Only Params) workflow accepts three parameters to let the workflow initiator pass in two number operands and the operation. It also has one variable to store the computed result of the calculation. Both parameters and variables have a static ID that you use to reference their value as substitution strings or bind variables. They also have a display label that shows in the navigator tree to improve readability. Here, I opted for parameter static IDs of P_OPERAND1, P_OPERATION, and P_OPERAND2 and a variable static ID of V_RESULT. The P_ and V_ prefixes are not mandatory, but they helped me remember which names were parameters and which were variables.

The workflow Title property – which determines how end-users will see the workflow instance in the console – can reference parameters as substitution strings to include dynamic info. Therefore, I set my Title to the string below:

Calculate &P_OPERAND1. &P_OPERATION. &P_OPERAND2. (Using Only Params)

Workflow Versions

As business requirements change, a process can evolve over time. APEX supports this using workflow versions. When you create a new workflow the Workflow Designer also creates the first version for you. While the name can be anything meaningful, I just named mine “1.0“. Notice it shows in the workflow navigator tree as “1.0 [Dev]” to indicate this version is in development mode.

Very observant readers may have noticed my workflow’s Result variable is actually indented under the “1.0 [Dev]” version node in the navigator tree. Since the set of variables can change over time as well, they are part of the workflow definition that’s included in the workflow version. This is why they’re called “version variables.”

While building your app, you can use your workflow in development mode when running from the APEX Builder. However, to run the app outside of the APEX Builder or to deploy it another environment, you will need to activate the development version of all workflows in use before end-users can use them. For simplicity, in this article we’ll just keep all the workflows to a single development-mode version and always run the app from the builder.

Workflow Definition Versus Instance

The workflow version diagram represents the definition of a business process. It starts with a start activity and shows how to continue on to other activities until eventually concluding in an end activity. It will always have a single starting point, but may allow multiple possible paths to reach an end activity. Think of it like a recipe, blueprint, or template that describes how each particular concrete example of the workflow will behave.

A workflow instance is a particular example of the workflow definition that your application starts, usually by passing in one or more parameter values that provide the context information that makes this example unique. The APEX workflow designer lets you specify the definition and the Workflow page process (or APEX_WORKFLOW PL/SQL API) lets your application start a new workflow instance that will follow the activities you laid out in the designer. Now let’s put these abstract ideas into practice in this example. To perform a calculation using my Simple Calc (Only Params) workflow definition, I needed to create a page that uses the Workflow page process to start a new instance of the workflow to perform a specific calculation.

Calculating Using the Workflow

I copied my original No Workflow page to a new Workflow Params Only page and changed the page process type from Invoke API to be type Workflow instead as shown below. In the Settings section of the Property Editor, I set the Type of workflow action to Start since clicking the (Calculate Using Workflow) button should start the workflow. I chose the Definition of Simple Calc (Only Params), the name of the workflow I created above. Finally, I configured each of the three workflow parameters to get its value from the corresponding page item.

Using a Workflow page process of type Start to start a workflow

Running the page, after entering two operands and an operation and clicking the (Calculate Using Workflow) button, as shown below, it appears that nothing happens. However, behind the scenes the workflow has started. The result is not immediately visible in the page as it was in my original No Workflow page. The workflow is now responsible for computing the result and it is doing that in the background. So we need a different way to see the value of the result, since it lives inside the workflow version variable V_RESULT with display label Result. In the next section we’ll create a workflow console page to inspect the workflow instance and see the result of the calculation in the Result variable value.

After submitting the calculation for 4587 x 4 using a workflow to compute the result

Reviewing Results in the Console

A Workflow Console page lets you see the progress of workflows. You create one using the Create Page wizard by choosing the Workflow Console page type. The wizard will create two pages: one lists the workflows, and the other shows the details of a selected workflow in the list. As shown below, I named the list page Workflows Started and the form page Workflow Detail. The Report Context setting determines whether the list page shows the current user the workflows they initiated, those in which they are a participant, or the ones they can administer. I chose “Initiated by Me” so it will show all the workflow instances I start.

Creating a workflow console list and details page to show workflows the current user initiated

Refreshing the browser and navigating to this new Workflows Started page, I see the list below containing the one workflow instance I have started so far. We can see from the badge that it has already completed. To see the workflow instance’s details, we just click on the title whose text we notice was computed using our Title property we configured above that used substitution strings.

Console page showing Simple Calc (Only Params) workflow we started by clicking (Calculate)

As shown below, the Workflow Detail modal drawer page appears showing the workflow activities completed and the value of the Result variable.

Inspecting the result of the calculation in the workflow details page

Seeing Workflows for One App or All?

One small change I made to the default Workflows Started page was to adjust the SQL query of its Initiated by Me region of type Content Row so that it only showed the workflow instances related to the current application. By default it shows workflows from all apps in the workspace, but that’s not what I needed for this simple learning app, so I tweaked the query to look like the following to pass the :APP_ID bind variable as the value of the p_application_id parameter to the pipelined table function that returns the workflow instance information:

select ..., 
       title,
       initiator,
       ...
  from table ( apex_workflow.get_workflows (
                   p_context => 'INITIATED_BY_ME',
                   -- Leave out this param to show workflows
                   -- from all apps in the workspace
                   p_application_id => :APP_ID
                   ) )

Exploring a Faulted Workflow

There’s not much that can go wrong when performing a simple math calculation, but there is one thing: dividing by zero! I wanted to explore what would happen if I asked my simple workflow-based calculator to perform a calculation that would generate an error. I also wanted to learn how the workflow administrator could fix a faulted workflow in the console by editing a problematic variable value involved and retrying the faulted activity. Recall from above that the value of a workflow parameter like P_OPERAND2 is read-only. So I needed to slightly change the workflow to:

  1. Add a second version variable V_OPERAND2 of type NUMBER,
  2. Initialize its default value from the value of the parameter P_OPERAND2, and
  3. Adjust the Invoke API activity to pass V_OPERAND2 to the compute() function

After doing this, I entered the values you see below to compute 5678 divided by zero (0) and clicked (Calculate Using Workflow).

Starting the workflow to generate an error by calculating 5678 divided by zero (0).

Again, it appears that nothing has happens in the page, but as shown below the Workflows Started console page shows us what’s happened. The workflow started and faulted due to the divide by zero error.

Workflows Started page showing faulted workflow

Clicking on the title of the faulted workflow shows the details you see in the figure below. We see the error that caused the fault, and can edit the value of the V_OPERAND2 variable (with label Operand2) by clicking the Edit link to the right of the variable.

Workflow details for a faulted workflow due to divide by zero error

We can fix the error by entering a corrected value for the workflow version variable. For example, let’s enter the value 1 instead and click (Apply Changes) as shown below…

Fixing a faulted workflow by editing the value of a version variable

Lastly, we can click (Retry) as shown below to have the APEX engine try processing the faulted activity again…

Retrying a faulted workflow after correcting a version variable value

The workflow now completes successfully and we can see the result by clicking on the workflow title in the Workflows Started page as shown below.

Workflow completed successfully after retry

Associating a Workflow to a Row by PK

In the previous workflow, we passed in all of the initial information required using parameters. However, very often the information a workflow needs is already contained in the columns of a table in your app schema. In those cases, the only parameter value you may need to pass is a primary key from the table in question so it’s clear to which row the new workflow instance is associated.

In fact, this use case is so common that you don’t even need to create your own workflow parameter to handle it. Every APEX workflow has an optional, predefined parameter named APEX$WORKFLOW_DETAIL_PK whose display label is Details Primary Key. I set out next to investigate using this feature with my simple calculator workflow.

The first step was creating an EBA_DEMO_CALCULATION table shown below to store the calculation rows to which each workflow instance will be associated by primary key. The table has a primary key (ID) and then columns for the two operands, the operation, and the result of the calculation.

APEX Quick SQL diagram of the EBA_DEMO_CALCULATION table

Next I created a Calculations Interactive Report page with an associated form page named Calculation. These pages let me quickly list, create, edit, and delete calculation rows. Then I created the Simple Calc (Details PK) workflow you see below. To associate it with the EBA_DEMO_CALCULATION table, as shown below I clicked on the “1.0 [Dev]” version node in the navigator tree on the left, and then in the Additional Data section of the Property Editor, set the table name and configured the primary key column (ID) to use.

Configuring Simple Calc (Details PK) to associate it with EBA_DEMO_CALCULATION table

This table name configuration is one of the two required ingredients to make a table-associated workflow function correctly. The other important step occurs in the configuration of the page process that starts this workflow.

In the Calculation form page, the wizard has already created a page process of type Form – Automatic Row Processing (DML) to save the calculation row, and a Close Dialog page process to dismiss the dialog and return to the calling page. I added a new Workflow page process just between these two. As shown below, I configured it to start workflow Simple Calc (Details PK), but this time I’ve added one additional configuration value that makes a big difference. I set the Details Primary Key Item to P4_ID. This is the name of the page item that will provide the primary key value of the EBA_DEMO_CALCULATION table row to which the new workflow instance will be associated. The P4_ID page item is the primary key in the form region that creates, edits, or deletes a row in the EBA_DEMO_CALCULATION table. However, since its item type is Hidden it does not appear on the visual layout.

Set Details Primary Key Item to provide a value at workflow start time for APEX$WORKFLOW_DETAIL_PK

Now we have a workflow with a Details Primary Key Item value being passed in for the built-in APEX$WORKFLOW_DETAIL_PK parameter, and we’ve configured the Additional Data on the workflow version so APEX knows to which column in what table this primary key parameter value applies. This combination produces an interesting benefit. The APEX engine will automatically make all column values available by name for the appropriate EBA_DEMO_CALCULATION table row for the duration of the workflow. This lets us easily refer to the value of the first operand using its OPERAND1 column name, the second operand using OPERAND2 and the operation to perform using the column name OPERATION. As shown below, I’ve configured the parameters of the Calculate Result Invoke API activity in the workflow using these column name values.

Using details table column names for EBA_DEMO_CALCULATION row associated with the workflow

The second activity Store Result is another Invoke API activity that calls the UPDATE_RESULT procedure in the EBA_DEMO_SIMPLECALC package to store the computed result in V_RESULT back into the RESULT column of the appropriate EBA_DEMO_CALCULATION table row. For procedure parameter p_calculation_id it passes the item value APEX$WORKFLOW_DETAIL_PK and for parameter p_result it uses the V_RESULT item’s value.

Also notice that I introduced a single P_SUBJECT parameter (with label Subject) to let the page that starts the workflow pass in the entire subject line value. I did this since the workflow title cannot reference the workflow version-level values like variables or details table column names as substitution variables. By introducing the P_SUBJECT parameter and configuring the workflow title to be &P_WORKFLOW. I can easily set the workflow instance subject line to a useful value including dynamic contents in the page that starts the workflow instance to perform the calculation.

The figure below shows how the value of the P_SUBJECT parameter is set in the Calculation page’s workflow start page process. It includes the values of page items to achieve an appropriate subject, which the new workflow instance uses as its title.

Configuring dynamic content of the workflow P_SUBJECT parameter using page items values

I then used the new Calculations interactive report page and clicked (Create) to open the Calculation form to add a new calculation row. I entered the values shown below and clicked (Calculate Using Workflow). This inserted the new EBA_DEMO_CALCULATION row, started the workflow passing in the system-assigned primary key in P4_ID as the details primary key value, and closed the dialog.

Creating a new calculation row, with result computed by a working using a Details PK

When the dialog closes, I saw the page below. The workflow ran so quickly that it had already computed the result of 80 and stored it in the EBA_DEMO_CALCULATION row’s RESULT column using the details primary key value to identify which calculation row to update.

Calculations interactive report page after first Calculation row got created

Checking the Workflows Started page, I could also inspect the details of the workflow instance using that approach as shown below. I added the Details Primary Key collapsible region to the Workflow Details page to make it easier to see which details primary key value is associated to the current workflow instance being shown. As we see, the system assigned a primary key value of 1 to the first EBA_DEMO_CALCULATION row created. Our page passed it into the APEX$WORKFLOW_DETAIL_PK built-in parameter at workflow start time. It also passed in the workflow’s P_SUBJECT parameter, whose value included the substitution variable &P4_ID. so the primary key value 1 also reflects in the title of the workflow instance.

Viewing the completed workflow instance that used a details primary key for the calculation

Adding Some Fancier Features

With these basic examples under my belt, I spent some time to create a fancier version of the details-primary-key-using workflow above. In the sample app it’s called Fancier Calc (Details PK) and it appears below. It expands on the previous example by adding conditional notification of the result by email and/or by push notification. This required adding a few new parameters to let the page starting the workflow pass in: the current username, an email address to notify, a number of minutes to delay before notifying, and a boolean parameter to control whether to notify the current user using push notification.

Gettin’ fancy wit it: adding conditional email, push notification, and wait to a workflow

It uses several new Switch activities to change the route of the workflow depending on various conditional evaluations. The Any Notifications? switch uses a PL/SQL expression to test whether :p_email_to_notify is not null or whether :p_push_notify_current_user is equal to ‘Y‘. The Any Wait? switch checks if :p_minutes_delay_before_notify is non-null and greater than zero (0). The Notify Email? switch tests if :p_email_to_notify is not null, and the Notify Push? switch evaluates if :p_current_username is not null and :p_push_notify_current_user equals ‘Y‘.

Notice that even though this last parameter is defined to be of type BOOLEAN, when referencing its value in PL/SQL or as a substitution parameter it will use the implicit or explicitly configured True Value and False Value as text. I chose to explicitly configure these values to ‘Y‘ and ‘N‘ respectively in the Property Editor for the Push Notify Current User parameter. I configured the Send Email activity using the &P_EMAIL_TO_NOTIFY. email address as the recipient, and the Push Notify activity using the &P_CURRENT_USERNAME. as the user to notify. Also notice the transition lines that emerge from the Switch activities have names on them. I chose to use “Yes” and “No” for the names of all of them, but of course you can use any descriptive names that make your workflow easier to maintain.

Consequently, I also enhanced the Calculation page to let the user choose between the simple or fancier workflow, as well as providing additional values if they choose to use the fancier version as shown below. Hopefully if you try out sample app, you can experiment using the fancier option to send yourself email and push notifications.

Enhanced Calculation page allowing either simple or fancier workflow choice

NOTE: If you install the sample app and try it yourself, make sure to (1) regenerate the Push Notification Credentials after importing the app and (2) opt-in to receive push notifications in the user settings page of the runtime application. You’ll find the (Regenerate Credentials) button in APEX Builder’s Shared Components > Progressive Web App page. The push notification opt-in is in the Settings page you can access in the dropdown menu under the logged-in username at runtime.

Why Pass in Current Username?

You may ask yourself why I needed to pass in the value of the current username as a workflow parameter, and that would be an excellent question! I originally tried to get the Push Notification activity working by simply referencing the familiar &APP_USER. substitution variable. But I frustratingly found it would never deliver the notification as expected. After reaching out to colleagues for advice, they reminded me the workflow is happening in the background and is always operating as the nobody user, which is the value of &APP_USER. when no user is logged in.

Therefore, my experiments to send a push notification to the nobody user were ignored since no user named nobody was enrolled in receiving push notifications. By passing in the p_current_username parameter value from the Calculation page, its value at workflow start time gets evaluated in the context of the page starting the workflow. This gives the workflow parameter the correct value of the end-user logged in using the page. Subsequently, the Push Notification activity’s use of &P_CURRENT_USERNAME. as the recipient of the push notification ensures it gets delivered to the logged-in user accessing the page that starts with workflow if they have opted-in to receive notifications.

NOTE: I subsequently realized I could have avoided passing in the current username in my simple example app by instead using the built-in workflow substitution string APEX$WORKFLOW_INITIATOR as the push notification recipient username. However, I decided to leave the sample they way it is in case the user to be notified by push notification is not the same username that starts the workflow in the application. Just be aware that APEX already keeps track of the username of the workflow initiator using this special item name.

Using SQL Query for Additional Data

In the Simple Calc (Details PK) workflow discussed earlier, we configured the Additional Data section of the workflow version to name the EBA_DEMO_CALCULATION table and its ID primary key column. This let the automatic row lookup work based on the Details Primary Key Item value passed in the APEX$WORKFLOW_DETAIL_PK parameter by the Workflow page process that started the flow. In turn, that allowed us to reference any of the columns of the EBA_DEMO_CALCULATION table by name anywhere in the workflow as needed. The Fancier Calc (Details PK) workflow shows off another useful alternative: using a SQL Query instead of a table name for Additional Data.

It specifies the following query that references the value of the :apex$workflow_detail_pk in its WHERE clause. Since the automatic lookup by primary key is not happening in this situation, the developer is responsible to perform the primary key lookup in their query using this built-in bind variable.

select id,
       operand1,
       operation,
       operand2,
       case operation
        when '+' then '+'
        when '-' then '-'
        when '*' then '×'
        when '/' then '÷'
       end as operation_translated
  from eba_demo_calculation
  where id = :apex$workflow_detail_pk

Just like what happened when using the table name and primary key column approach, all of the SELECT list column names in the Additional Data query are made available to reference throughout the workflow wherever needed. In the sample app, the Send Email activity references the value of the OPERATION_TRANSLATED column above by using &OPERATION_TRANSLATED. This lets your workflow perform a join or use the power of SQL to bring in whatever useful data might be relevant to every activity in the workflow. Each activity in the workflow also has an optional Additional Data query you can use to retrieve information that’s only relevant to a single activity.

NOTE: When using the SQL Query style Additional Data, if your query inadvertently retrieves no rows, then your workflow will fault with a “user-defined error” message, so be aware that this is what that error means. One way I encountered this error while creating the sample app was by accidentally using a bind variable named :apex$workflow_details_pk (with a plural “details” in the name when it needed to use the singular “detail”). Since no bind variable by this name-with-a-typo exists, its value evaluated to null and that caused my Additional Data SQL query to return no rows.

Cleaning Up Dev-Mode Workflow Instances

The apex_workflow package comes with a handy procedure called remove_development_instances() that lets you delete workflow instances you created during your development. It accepts an optional p_application_id parameter to provide the ID of the app whose dev-mode workflow instances you want to delete. If you don’t pass an app id, it cleans up the dev-mode workflow instances for all applications in the workspace. I added a button shown below to the Workflows Started page in the sample app that invokes this new procedure, passing in the :app_id bind variable to affect only the workflow instances for the current sample application.

Handy button in the sample to clear development-mode workflow instances for the current app

On the Calculations list page, as shown below I added another button that deletes the rows from the EBA_DEMO_CALCULATION table that are not referenced by a workflow.

Useful button to remove calculation rows not referenced by a workflow

Using the combination of these two buttons, you can first delete the dev-mode workflow instances you have accumulated while trying out the sample. Then you can click the other button to remove the calculation rows that no longer reference a workflow (since you just deleted them in the step before!).

NOTE: While the simple example workflows in this article are not using approval or action tasks, one interesting corollary of the new remove_development_instances() procedure is that it will also clean up any approval or action task instances that were initiated by the deleted dev-mode workflows.

Tips on Moving & Connecting Activities

While exploring the workflow capabilities to prepare this article, I gradually became more confident in how to achieve a nice-looking diagram with neatly aligned activities and transition lines that looked how I wanted them to. It took time to discover the way that felt most productive, so I wanted to share what I learned here in case it could save you some time. The video below puts the following tips into action, showing how to:

  • Drag and drop an activity from the palette
  • Grab the arrowhead to reattach a transition
  • Use anchor dots to position a line
  • Add and remove “elbow” points to bend lines
  • Create and connect activities in one gesture
  • Multi-select to move groups of activities
  • Drop a new activity between two others
Video with tips to achieve clean layouts with Oracle APEX Workflow Diagram Builder

Creating a Custom Activity

As a last exercise, I wanted to try extending the core set of APEX workflow activities to add a custom one into the mix. Sticking with the simple calculator theme, my goal was to create a Simple Calculator activity that any APEX Workflow could use. I started by creating a process type plug-in, and made sure to check the Workflow Activity checkbox in the Supported for section. I defined four plug-in attributes to capture the (page or workflow version) item names to provide Value1, Operation, Value2, and Result. To make it self-contained, I copied the source of the compute() function from the beginning of this article into the plug-in’s PL/SQL code area, and also added the following function whose name I configured as the plug-in’s Execution Function Name:

-- function compute() removed for brevity
-- see eba_demo_simplecalc package for source
function simple_calculator (
    p_process in apex_plugin.t_process,
    p_plugin  in apex_plugin.t_plugin)
    return       apex_plugin.t_process_exec_result 
is
    c_value1      number        := nv(p_process.attribute_01);
    c_operation   varchar2(200) :=  v(p_process.attribute_02);
    c_value2      number        := nv(p_process.attribute_03);
    c_result_name varchar2(200) :=    p_process.attribute_04;
    l_result apex_plugin.t_process_exec_result;
begin
    apex_session_state.set_value(
        c_result_name, 
        compute(c_value1,c_operation,c_value2));
    l_result.success_message := 'Success'; 
    return l_result;
end simple_calculator;

The code uses the v() and nv() functions to retrieves the string and number values of the items named by the first three custom attributes. From the fourth custom attribute, it gets the name of the item into which the result should be set. Finally it uses the set_value() function in the apex_session_state package to set the value of the result item to the result returned from the compute() function.

With this simple custom activity plug-in ready, next I created the Simple Calc Using Process Plugin workflow you see below. Notice how the custom activity Simple Calculator automatically appears in the Activities Palette. To configure it, I used the Property Editor to assign the names of the workflow parameters to use for Value 1, Operation, and Value 2 attributes, and the name of the version variable to use to hold the result.

Workflow using a custom Simple Calculator activity we created above

A new Workflow Process Plugin page is a slight modification of the Workflow Params Only page we created earlier, but its Workflow page process starts the new Simple Calc Using Process Plugin workflow instead.

Sample page to create new workflow instance using the Simple Calc Using Process Plugin workflow

After trying out the new page, a quick check of the Workflows Started console page shows the results of all of our tests during this article.

Workflows Started page showing all workflows we tried in this article

Summary

Of course, you’d never really use APEX workflow to just add two numbers, but hopefully this simple exercise that I found helpful to learn the basics will also prove useful to you as you think about real-world business processes you can now automate with ease.

I encourage you to download the sample app from here and give it a spin. After importing the app, there are two additional setup steps to perform before the push notification will work. First, regenerate the Push Notification Credentials. In addition, ensure you have opted-in to receive push notifications in the user settings page of the runtime application. You’ll find the (Regenerate Credentials) button in APEX Builder’s Shared Components > Progressive Web App page. The push notification opt-in is in the Settings page you can access in the dropdown menu under the logged-in username at runtime. Also check that your mobile device or desktop is not set in “do not disturb” mode that suppresses delivery of push notifications. If it was in this mode, you’ll find your push notifications have quietly accumulated in the “notification center” on your device.😊

For additional reading on APEX Workflow including business-focused use cases involving approvals and tasks see my colleague Ananya Chatterjee’s blog series Simplify Business Process Management Using APEX Workflow and her article Multi Level Expense Approval using APEX Workflow. To learn more about APEX Workflow Development Lifecycle and Management check out my colleague Ralf Mueller’s article. For more background on end-user action tasks, see my colleague Richard Allen’s Using Action Tasks blog post. Many thanks to Ralf, Ananya, Richard, and Ottmar on my team who answered a lot of questions I had as I worked my way through this first encounter with APEX workflow.

Planning with Gantt & Calendar #JoelKallmanDay

Overview

Building an app to solve a personal need is a great learning opportunity. Sometimes I plan multi-day trips and must decide among overlapping events. The annual animation festival in Annecy, France, is one example. Hundreds of talks and screenings span various venues in a lovely little lakeside locale. Another is a periodic pilgrimage to New York City to see as many musicals and museums as I can fit in a week. An APEX app helps me plan my trip activities. This year I celebrate Joel Kallman Day by sharing my app and explaining the smorgasbord of APEX tricks I learned by building it.

(Technical Note: to see a larger version of any figure, right click on it and choose Open Image in New Tab from the context menu…)

Recurring Trips with Overlapping Events

Both the Annecy Festival and my New York City trips happen periodically during a particular year over a series of days, so a trip name and year represent each edition of a trip (e.g. New York City, 2023). Each edition has a start and end day and consists of events to attend that may repeat multiple times during the week. These different event runs might be in distinct venues, like a movie that shows in different cinemas around Annecy. In contrast, all the event runs might be in the same Broadway theatre. In the case of the Annecy Festival, films can be from a particular country, in an original language, and have subtitles in another language, so as shown below my data model also includes countries and languages.

Data model for Activity Planner application

Gantt Chart Showing Per-Day Show Runs

To easily see the overlapping events on each day of the trip, I use a day selector and Gantt chart as shown in the figure below. The query for the Gantt chart region includes columns DAY_START and DAY_END whose values come from combining the selected day with user preferences for day start time and day end time. The query also includes STARTS_AT and ENDS_AT datetime values for each event run that falls on the selected day. It concatenates the name of the event and the name of the venue into a NAME column that’s used as the task name. To suppress the display of task name along the left edge of each row, I selected the Attributes tab of the chart region, and set Show Row Axis to OFF.

Gantt Chart showing many overlapping event runs on Wednesday, September 20th

Day Selector’s Data-Driven Date Range

The EVENT_DAYS Shared Components List of Values (LOV) driving the day selector was an interesting query to write. In the EBA_DEMO_ANN_EDITIONS table for a trip edition we have a START_DATE and END_DATE for the consecutive sequence of days over which the trip edition takes place. I needed a query that returned all of the dates in order starting with the start date and ending with the end date for the current trip edition. The application item CURRENT_EDITION holds its ID value. The query ended up looking like this:

SELECT
  to_char(FIRST_DAY + LEVEL - 1,'fmDy DD Mon') AS DAY_NAME_IN_PERIOD,
  LEVEL AS DAY_NUMBER
FROM (SELECT FIRST_DAY, LAST_DAY
        FROM EBA_DEMO_ANN_EDITIONS
       WHERE ID = :CURRENT_EDITION)
CONNECT BY
  FIRST_DAY + LEVEL - 1 <= LAST_DAY
ORDER BY
  DAY_NUMBER ASC

This query drives the day selector below with an ordered list of formatted day names between start date and end date for the current trip edition:

Day selector for the current trip edition on the Schedule page

Stretching Chart to Fill the Viewport

I could have hard-coded the height of the Gantt chart region on the Attributes tab of the property editor, but that didn’t meet my needs. In the weeks leading up to a trip, I use the app on different desktop computers with differently-sized monitors as well as on my iPad, so I explored how the Gantt chart could stretch to use whatever vertical space is available.

After many failed experiments, I landed on the following technique. It uses the CSS calc() function to compute the value of the height property. First, I defined a CSS class on the page called vertical-stretch and assigned this class to the Gantt chart region. Then I defined the class in the page-level CSS > Inline settings using the syntax:

.vertical-stretch {
   height: calc(100vh - 17rem);
}

The calculation references the full (100%) viewport height by using 100vh and subtracts from that the height of the rest of the elements on the page above the Gantt chart. Using CSS rem units that represent the height (i.e. font-size) of the root element on the page, through trial and error I discovered 17rem closely matched the space used by the other content on the page. Applying the vertical-stretch CSS class to the Gantt chart region means, “Your height is the viewport space remaining after accounting for the vertical space occupied by the elements above the Gantt chart”. This approach made the app look exactly how I wanted on every monitor size and iPad where I use it.

When resizing the window interactively, the chart also reacts as expected as shown in this short video:

Editing Events by Clicking on a Bar

The Event page (7) in the app lets the user create, edit, or delete an event and all its related event runs. When looking at a particular day’s events in the Gantt chart on the Schedule page (14), I wanted clicking on any bar to open the event related to that particular event run. So I set out to find how to obtain the event run ID related to each bar in the Gantt chart. I started by configuring the Gantt chart’s Task Id property to get its value from the event run ID column in the region’s query.

Next, I hoped to find the value of this task ID somewhere in the HTML markup in the page. By right-clicking on a Gantt bar rectangle and choosing Inspect Element, the browser developer tools showed me that each bar was a Scalable Vector Graphics (SVG) group element <g> containing two <path> elements and a <text> element like this:

<!-- Example SVG group of shapes for Task Id 162 "bar" -->
<g id="_dvtActiveElement43100180_162">
  <path d="..." class="oj-gantt-task-backdrop"></path>
  <path d="..." class="oj-gantt-task oj-gantt-task-bar"></path>
  <text         class="oj-gantt-task-label" ... >
    Local Fauna Preview (Philippe Labaune Gallery)
  </text>
</g>

The <path> element containing the class="oj-gantt-task" looked like the right element to target for a click handler. So I defined a dynamic action to react to the Click event on the CSS class oj-gantt-task using the jQuery Selector with a dot before the class name as shown below:

Configuring a dynamic action on the Click event of the element with CSS class oj-gantt-task

I also noticed the Gantt Chart Task Id value was part of the value of the id attribute on the <path> element’s containing parent <g> element. For example, for an event run with ID = 162, the id attribute of the <g> parent element for the clicked-on bar had a value like “_dvtActiveElement43100180_162“. I learned from my colleagues that the dynamic action event handler can access the triggering <path> element using the expression this.triggeringElement. So, my dynamic action event handler passes the triggering element to an eventRunIdForClickedGanttBar() helper function in my page. It returns the Task Id value by parsing it out of the parent element’s id attribute like this:

function eventRunIdForClickedGanttBar(p_this) {
  return extractNumberFromUnderscoreString($(p_this).parent()[0].id);
}

The calling dynamic action event handler looks like this. It sets the value of a hidden page item P14_SELECTED_EVENT_RUN_ID to hold the event run ID that was clicked on, sets the Request to EDIT_EVENT_RUN, and submits the page.

const lTaskId = eventRunIdForClickedGanttBar(this.triggeringElement);
// Set Task id to hidden page item, and submit
// the page with EDIT_TASK request
apex.page.submit({
    request: "EDIT_EVENT_RUN",
    set: {
        "P14_SELECTED_EVENT_RUN_ID": lTaskId
    }
});

In the page’s Processing tab, an After Processing branch redirects to the Event page (7) when the Request = EDIT_EVENT_RUN, passing along this hidden page item value for the target page’s P7_EVENT_RUN_ID page item value. It also passes its own page number 14 as the value of the P7_CALLING_PAGE_ID page item. In contrast, when the Home page (1) calls the Event page to edit an event, its link passes in the value 1 for this page item.

Passing the event run ID for the clicked-on Gantt bar to the Event page

The Event page uses the P7_CALLING_PAGE_ID item value to conditionally return to the right calling page when the user cancels or applies their changes in the Event page. The target Event page has a Pre-Rendering page process Get Event from Event Run Id that looks up the parent event ID to edit based on the “child” row’s event run ID passed in.

Saving Time During Event Data Entry

Each event has a duration in minutes, and each event run has a start time and end time. Using two dynamic actions on the Event page shown below, I speed up data entry by computing the duration if it’s null when a pair of Starts At and Ends At times are entered for an event run. In addition, once the duration is set, any other event runs entered benefit from another dynamic action that automatically computes the Ends At time once the user enters a Starts At and tabs out. This feature makes it very quick to enter all the dates and times a particular musical like “& Juliet” is offered during a trip edition.

Event page editing the Broadway musical & Juliet and its event runs

The page contains the following two helper functions in its JavaScript Function and Global Variable Declaration section the the property editor:

function addMinutesToTime(time, minutes) {
  return apex.date.format(
           apex.date.add(
             new Date("2023-01-01 "+time),
             parseInt(minutes),
             apex.date.UNIT.MINUTE),
           "HH24:MI");
}
function minutesBetween(time1, time2) {
  var date1 = apex.date.parse(time1, "HH24:MI");
  var date2 = apex.date.parse(time2, "HH24:MI");
  var diff = Math.abs(date2.getTime() - date1.getTime());
  return Math.floor((diff / 1000) / 60);
}

Then, the dynamic action on the interactive grid’s ENDS_AT column has a Set Value action step that executes JavaScript to call minutesBetween() to compute the duration of the event:

minutesBetween($v('STARTS_AT'), $v('ENDS_AT'))

It only runs if the duration is currently null and both the STARTS_AT and ENDS_AT columns in the current row have a value using the client-side condition boolean expression:

$v('STARTS_AT')       !== '' && 
$v('ENDS_AT')         !== '' &&
$v('P7_DURATION_MIN') === ''

Similarly, the dynamic action on the grid’s STARTS_AT column has a a Set Value action step that executes JavaScript to call addMinutesToTime() to compute the ENDS_AT time, based on the duration of the event:

addMinutesToTime($v('STARTS_AT'), 
                 $v('P7_DURATION_MIN'))

It only runs if the duration is not null, the STARTS_AT has a value, and the ENDS_AT is null using the client-side condition boolean expression:

$v('STARTS_AT')       !== '' && 
$v('P7_DURATION_MIN') !== '' &&
$v('ENDS_AT')         === ''

Marking Event Runs as Starred/Ticketed

When reviewing a large list of events, there’s a good chance that you’re not interested in all of them. The app lets the user “star” a particular event run to mark it as a “favorite”. This results in setting the STARRED column value to “Y“. In addition, some trips involve a booking or ticketing process to confirm a seat for a particular event run. The app lets the user indicate they’ve confirmed their place by setting a Tickets switch to ON. In the figure below, I’m starring both Wednesday, September 20th performances of musical “& Juliet” and marking the 14:00 showing as ticketed.

Marking a particular event run as both Starred and Ticketed

Conditionally Coloring Selected Bars

It can be useful to conditionally color the Gantt chart bars based on application-specific criteria. In my app, I want “starred” events to show with one color that makes them distinct, while “ticketed” events show with another unique color. This was easy to accomplish. I first included the following SELECT list expression in the query for the Gantt chart region in the Schedule page (14):

case 
   when r.tickets           = 'Y  then 'tickets'
   when r.event_starred     = 'Y' 
     or r.event_run_starred = 'Y' then 'starred'
end as css_classes

Then I set the Task CSS Classes property of the Gantt chart to &CSS_CLASSES. The final step was adding the following simple CSS class definitions to the page’s CSS > Inline section in the property editor:

.tickets {
   fill: blue;
}
.starred {
   fill: yellow;
}

After the user applies changes on the Event page, it conditionally branches back to the calling page. Since the Schedule page passed its page number 14 into the P7_CALLING_PAGE_ID page item, the user returns to the Schedule page. As shown below, we immediately see conditionally colored event run bars reflected in the Gantt chart. As dictated by the above CSS classes, the bar for the ticketed event run is now blue while the bar for the starred one is now yellow.

Gantt bar for starred showing appears in yellow, while ticketed showing is blue

Narrowing Focus: Hiding Clashing Events

When you have identified your starred event runs, it can be useful to narrow your focus to only see your favorites. This can help you decide which particular event runs to book or buy tickets for. The Schedule page has a switch at the top labeled Hide Starred Session Clashes. When this is switched on, as shown below the Gantt chart suppresses all other event runs that would overlap with any of your favorites. Very useful, since you can’t be in two places at once!

Hiding event runs that clash with your favorites to focus your attention

This feature is implemented directly in the SQL query for the Gantt chart region using an appropriate WHERE clause predicate. It uses the switch page item as a bind variable and uses a NOT EXISTS(…) clause to include any unstarred events that do not have clashes with any starred ones.

and (:p14_hide_starred_session_clashes = 'N' 
     or
     (:p14_hide_starred_session_clashes = 'Y' and
       ((r.event_starred='Y' or r.event_run_starred='Y')
         or
         (r.event_starred='N' 
          and not exists (
            select 1
              from eba_demo_ann_event_runs_v starred_run
             where (
               starred_run.event_starred='Y' or
               starred_run.event_run_starred='Y')
               and ((
                 /* starts before and ends after current */
                 starred_run.starts_at < r.starts_at and
                 starred_run.ends_at   > r.ends_at)
                 or (
                 /* starts after and ends before current */
                 starred_run.starts_at > r.starts_at and
                 starred_run.starts_at < r.ends_at)
                 or (
                 /* ends after current starts & before current ends */
                 r.starts_at         <= starred_run.ends_at and
                 starred_run.ends_at <= r.ends_at)
                 or (
                 /* starts after current & before current ends */
                 r.starts_at           <= starred_run.starts_at and
                 starred_run.starts_at <= r.ends_at)))))))

Focusing Further: After Getting Tickets

After you booked your seat or bought your ticket for one particular showing, you no longer need to see all of the other available opportunities to see the same event. The Schedule page has a second switch at the top labeled Hide Already Ticketed Events. As shown below, the Tuesday 19th September showing of “& Juliet” does not appear since above we already marked the Wednesday 2pm showing as being ticketed.

Hiding other showings of an event you have already bought tickets for

Again, this feature is implemented with a different SQL WHERE clause predicate using this other switch page item as a bind variable. When the variable is set to “Y” it uses an event_id not in (…) with a subselect to exclude event runs for ticketed events from days other than the currently selected day (:p14_day).

and (nvl(:p14_hide_ticketed_from_other_days,'N') = 'N' 
     or (
       nvl(:p14_hide_ticketed_from_other_days,'N') = 'Y' 
       and 
       event_id not in (
         select distinct event_id
           from eba_demo_ann_event_runs_v
          where tickets='Y' 
            and event_day !=  :p14_day)
       )
     )

Since the Gantt chart query references three different page items as bind variables, we have to ensure that its Page Items to Submit property mentions all their names so that their values are sent to the server whenever the region refreshes its query results. The figure below shows the three page item names in a comma-separated list.

Always ensure Page Items to Submit includes names of any page items used as bind variables

Geocoded Date in the Venues Page

As shown below, the Venues page uses the techniques explained in my article Refitting Filtered Map Points to provide a filterable list of venues with an automatically-synchronized map that zooms and recenters to show the locations of the filtered venue list.

Filtering the list of venues to see the narrowed results on a map

The companion Venue page for creating and editing venues uses a Geocoded Map page item to simplify finding the longitude and latitude for a given address. In order to have the Geocoded Map page item show the appropriate pin on the map when editing an existing venue, we use the following expression in the region query to create an appropriate SDO_GEOMETRY value based on the v.longitude and v.latitude values stored in the table. Importantly, the Query Only property of the corresponding P11_GEOCODED_ADDRESS page item is set to ON so that the APEX engine doesn’t try to update any SDO_GEOMETRY column in the underlying EBA_DEMO_ANN_VENUES table that has LATITUDE and LONGITUDE instead.

/* 
 * if latitude/longitude have values, then
 * return sdo_geometry structure representing
 * the point (longitude,latitude)
 */
case 
  when v.longitude is not null 
   and v.latitude is not null then
     sdo_geometry(2001,
                  4326,
                  sdo_point_type(
                    v.longitude,
                    v.latitude,
                    null),
                  null,
                  null)
        end geocoded_address

Since we’re not asking APEX to save the SDO_GEOMETRY value directly to an underlying SDO_GEOMETRY column, when the user enters a new address and triggers the geocoding, we need to pick out the longitude and latitude so they get stored correctly in the respective, underlying columns of the same name. The P11_GEOCODED_ADDRESS page item has a dynamic action named When Address Confirmed that reacts to the Result Selection [Geocoded Address] event to trigger two Set Value dynamic action steps to set the P11_LONGITUDE and P11_LATITUDE page items with the values the geocoding service has returned. They reference the this.data.longitude and this.data.latitude JavaScript expressions to get the job done.

Geocoded Address page item lets APEX resolve longitude and latitude automatically

APEX’s geocoding service depends on knowing the country code of the address the user is typing in. The Venue page has a Pre-Rendering page process in the Before Header section named Default Trip Info for Current Edition. It contains the following PL/SQL block that looks up the trip ID and country code based on the value of the current edition the user is working with. Note that it only assigns the P11_TRIP_ID if the primary key P11_CODE is null (meaning that we’re creating a new venue):

for j in (select e.trip_id,t.country_code 
            from eba_demo_ann_editions e 
            left join eba_demo_ann_trips t on t.id = e.trip_id
           where e.id = :CURRENT_EDITION) loop
    if :P11_CODE is null then
        :P11_TRIP_ID := j.trip_id;
    end if;
    :P11_CURRENT_TRIP_COUNTRY_CODE := j.country_code;
end loop;

Then, the Geocoded Address page item is configured to use the P11_CURRENT_TRIP_COUNTRY_CODE page item for geocoding country information. This ensures that when defining a venue for my Annecy trip it uses FR for France as the geocoding country and when creating one for my New York City trip, it uses US for the United States.

Configuring geocoded address country

Let’s suppose we defined another trip for “Annecy Festival” with the country of France configured, then defined a new edition of this trip for the year 2024, and then finally set this new “Annecy Festival 2024” as the current trip event to work with using the Settings page explained in the next section. Then as shown below, the geocoding of venue addresses would be done with France as the country.

Venue address geocoding uses current trip edition country code

Settings Page

The Settings page (8) lets the user change four user preferences: the current trip edition to view/edit, whether to show countries and languages information, and the start time and end time to use in the Schedule Gantt chart page. The page shown below uses conditional Before Header computations to set the values of the page’s items based on first trying to use user preferences, then conditionally falling-back to using the same-named application items. The application item names (which match the corresponding user preferences) are CURRENT_EDITION, SHOW_COUNTRIES_AND_LANGUAGES, DAY_START_TIME, and DAY_END_TIME. I admit that I wasn’t super excited about the way I had to capture the Day Start Time and Day End Time using four separate select lists, but this was the user experience I had in mind for the task. Maybe a future APEX release will offer a time picker page item for this purpose.

Setting four different user preferences that affect the Activity Planner app

To save the Settings page item changes back to the user preferences, the page has one page process in the Processing section for each user preference that each use the built-in User Preferences page process type to save the updated value to the corresponding same-named user preference. Another four Execute Code page processes in the same section set the preference values into the corresponding application items, since those are what get referenced in various SQL queries around the app.

Focusing Calendar on a Date Range

Once you’ve marked one or more event runs as ticketed, get a bird’s eye view of your trip on the Calendar page. This page focus the users attention on the current trip edition by ensuring the right start day is shown immediately. It sets the calendar to use the START_DATE of the current edition to be the current date displayed, and to start the week display with this date, too.

Calendar gives bird’s eye view of ticketed event runs during the current trip edition

Assuming the calendar region has a static ID of calendar, setting its initial display date requires just one line of JavaScript in the Execute when Page Loads section of the page properties that calls the gotoDate() function on the calendar widget:

apex.region("calendar")
    .widget()
    .data('fullCalendar')
    .gotoDate($v('P12_START_DATE'))

With similar ease, after first computing the day of the week the START_DATE represents, we can set the first day of the calendar week in the calendar region’s Initialization JavaScript Function. The calendar expects its firstDay property to be a day number: 0=Sunday, 1=Monday, etc. I use a Pre-Rendering computation in the Before Header section to compute the value of a hidden P12_ZERO_BASED_FIRST_DAY using the following SQL query:

select to_number(to_char(first_day,'D'))-1
from eba_demo_ann_editions
where id = :CURRENT_EDITION

Setting the calendar’s firstDay property is essentially a one-line task. The code for the Initialization JavaScript Function on the Attributes tab of the property editor for the calendar region appears below. It assigns the value of the zero-based first day to the firstDay property of the pOptions object passed in.

function ( pOptions ) {
    pOptions.firstDay = $v('P12_ZERO_BASED_FIRST_DAY');                                    
    return pOptions;
}

Augmenting App Title with Page Name

It’s a personal preference of course, but as you can see in the previous screenshots, I like the “Top” style Navigation Menu layout since its options stay visible. I combine this style with the use of the After Logo position that lets me place additional context like the page name in the application navigation bar. This leaves more room for content on each page. For example, on the Calendar page I added a Page Title Static Content region with the following HTML Code:

<span class="apex-logo-text"> -&nbsp; Calendar</span>

This uses the same font as the logo text, and adds a hyphen separator and the name of the current page. As shown below, I set its Template to None and its Position to After Logo. This combo gives me a result that feels clean and spacious, and I’ve done the same thing on every non-modal page of the app.

Adding custom content like the page name to the After Logo position in the navigation bar

Grouping Event Runs by Event

The application’s Home page (1) has an Interactive Report showing a searchable list of all event runs for the current trip edition. To group the list of event runs by the event they belong to, I ran the page and used the Actions > Format > Control Break menu to add the Event break column as shown below.

Adding control break group to an Interactive Report to group event runs by event name

Next, to ensure the event runs sort in the most logical sequence for the end user, I used the Actions > Data > Sort menu to configure the report to first sort by Event then sort by Event Day Number as shown here.

Defining the sort order to show event runs in a logical day order

Finally, I saved the current state of the Interactive Report as the new default primary report for all users. As shown below, I used the Actions > Report > Save Report menu, choosing As Default Report Settings in the Save Report dialog, then ensured the Default Report Type was set to Primary in the Save Default Report dialog before clicking Apply.

Updating the primary default report to use current settings of the Interactive Report

Of course, the option to update the primary default report does not appear for end users. I was able to access it since I ran this page as a developer from the APEX Builder.

Formatting a URL as a Hyperlink

My colleague Jayson Hanes helped me figure out how to display an info icon on the Event page to open the event URL in a new browser tab. This should have been a simple task, but the first several approaches I tried didn’t work like I wanted. Luckily, Jayson gave me this helpful tip. The trick was to add a P7_URL_LINK page item of type Display Only to my page, set its Icon property to the fa-info-circle-o, then use the Advanced > Pre Text setting to add an enclosing, open tag for the hyperlink. It uses the substitution string &P7_URL!ATTR. in the value of the <a> tag’s href attribute. The special suffix !ATTR makes sure the value is escaped correctly to appear in an HTML attribute. I added a title attribute so the hyperlink would gain a “tooltip” when the end-user hovers over the info icon with their mouse.

<a href="&P7_URL!ATTR." 
   target="_blank" 
   title="Click to open this page in a new tab">

Then I used the corresponding Post Text setting on the page item to add the closing </a> tag. This produced exactly the desired effect I wanted as shown below. Thanks, Jayson!

Giving the end user an easy way to open the Event URL in a new browser tab

PWA: Instant Desktop/Tablet Use

I took most of the screenshots of the Activity Planner app in this article while running it as a Progressive Web App (PWA). This lets me launch the app directly from the dock of my Mac laptop whenever I need to use it. Combined with my enabling the Persistent Authentication feature at the APEX instance level, this produces a great user experience. I launch the planner app like any other app on my desktop or iPad and instantly continue my trip planning work.

Enabling an APEX app for PWA use takes just two clicks on the Shared Components > Progressive Web Page page in the APEX Builder. Just switch the Enable Progressive Web App switch to ON, as well as the Installable switch just below it. Enabling Persistent Authentication is an APEX instance administrator task in the Manager Instance > Security page.

Saving Quick SQL in Supporting Objects

The idea for this final tip was hatched after realizing every script in the Supporting Objects area of an APEX app could be made conditional. The feature uses the same options as a Server-side Condition elsewhere in the Builder. It offers a powerful way to control which scripts should run when. A simple corollary is that I can easily store the Quick SQL text for my application data model as a script with Condition = Never. This makes it easy to reference and maintain the QuickSQL as requirements change and enhancements get implemented, but ensures that APEX never tries to execute it.

Consistent Switch Value Labels in Grids

While doing final edits of this article I noticed that the Starred and Tickets switch columns in the Event page’s interactive grid would show values Yes and No for unmodified event run rows, but changed to show On and Off for rows I had edited. I resolved that by explicitly setting the On Label and Off Label properties for the Switch component settings in my app under Shared Components > Component Settings> Switch as shown below.

Explicitly setting the On and Off labels for Switch components

Planning Your Next Trip?

Just in case you might find my Activity Planner app useful, or even just educational to experiment with, you can download the app here. Its Supporting Objects scripts install the event run data for my most recent New York City trip, with none of the event runs starred or ticketed yet so you can experiment with those aspects yourself. Enjoy!

Broadway musical “& Juliet” cast members take their bows

Only Unused Items in Grid LOV

I saw an interesting question in the APEX discussion forum this morning asking how a Popup LOV in an interactive grid could show only the items that were not already referenced in the grid. It’s a question I’d asked myself multiple times before, but one whose answer I hadn’t researched until today…

My solution combines a tip from my colleague Jeff Kemp’s article Interactive Grid: Custom Select List on each row with the get_ig_data plug-in that simplifies using pending grid data in SQL or PL/SQL. Fellow Oracle employee Bud Endress had mentioned the plug-in to me multiple times and I watched the APEX Instant Tips #66: Getting Oracle APEX IG data video with Insum’s Anton Nielsen and Michelle Skamene to learn more. You can download the sample app at the end to try it out yourself.

Overview of the Data Model

The sample uses the following simple data model to create or edit orders for items from an inventory comprised of twenty-six different food items from Apple and Banana to Yuzu and Zucchini.

Data model for the sample app

The simple order create and edit page lets you type in an order comment, and an interactive grid of food items and their quantities. The goal was to have the popup LOV display only the items that aren’t already mentioned in other grid rows. As shown below, if the order being created or edited already includes Banana and Apple then the LOV for another row leaves those out of the list.

Getting JSON Grid Data in a Page Item

The get_ig_data dynamic action plugin copies data from an interactive grid and places it in a JSON format into a page item. I first downloaded the get_ig_data plug-in from here on apex.world, and installed it into my app. Then I created a hidden page item P3_GRID_JSON_CONTENT to contain the JSON. Finally, I added a dynamic action step using the plug-in to execute when the value of the ITEM_ID column in the grid gets changed.

As shown in the figure below, it’s configured to only include the value of the ITEM_ID column in the grid rows and to return the JSON into the (hidden) P3_GRID_JSON_CONTENT page item. Notice that I ensured the Fire on Initialization switch was set to ON so that on page load the JSON would be populated into the hidden page item as well.

Configuring the Get IG Data dynamic action plug-in as an Value Changed action step

Including the JSON in the LOV Query

Initially I left the P3_GRID_JSON_CONTENT page item visible by setting it to be a Display Only item type. This let me study the JSON format the plug-in creates. After selecting Banana and Apple, I saw that the JSON looked like this:

[
  {"ITEM_ID":{"v":"2","d":"Banana"},"INSUM$ROW":1},
  {"ITEM_ID":{"v":"1","d":"Apple"},"INSUM$ROW":2}
]

I noticed that it was an array of JSON objects, each containing an ITEM_ID property for the column I’d configured the plug-in to include. Since the ITEM_ID is an LOV-based column, the plug-in provides the value as another JSON object with v and d properties, giving me access to both the return value and the user-friendly display string. For my purposes, I only needed the return value, so I updated the ITEM_ID column’s Popup LOV SQL Query to look like the statement below. It includes a where id not in (...) clause to elminate the set of item ids we get from the grid JSON document. Using an appropriate json_table() clause in the subselect, I query just the v property of the ITEM_ID property of each row in the JSON.

select name d, id r
from eba_demo_item
where id not in (select item_id
                   from json_table(:P3_GRID_JSON_CONTENT, '$[*]'
                      columns (
                         item_id number path '$.ITEM_ID.v' 
                      )) where item_id is not null)
order by name

Sending Pending Grid Data to the Server

Notice that the LOV SQL Query above references the page item bind variable :P3_GRID_JSON_CONTENT. That hidden page item contains pending edits that might not yet be saved in the database, so we need to ensure that this client-side information is submitted to the server when the Popup LOV performs its request to the server to retrieve its data. This is where the trick I learned from Jeff Kemp’s article came into play. I was unable to find the Page Items to Submit field in the property editor, where I knew I needed to type in P3_GRID_JSON_CONTENT to achieve the result I wanted. His tip involved first setting the Parent Column(s) property to the primary key column name. In my case, this column is named ID. That column won’t be changing, but providing a parent column value “unlocks” the ability to set the Page Items to Submit property. After performing those two steps, the page designed looked like the figure below.

Configuring the Popup LOV SQL Query to return items not already in the grid

This was all that was required to achieve the goal. Unfortunately, I later learned that the original forum question asker was using APEX version 19.1 and verified that the earliest APEX version the get_ig_data plug-in supports is 20.1… d’oh! So while my sample didn’t provide him a ready-to-use solution for APEX 19.1, it did help me learn a few cool new tricks about APEX in the process.

Downloading the Sample

You can download the APEX 23.1 sample app from here and give it a spin for yourself. Thanks again to Jeff, Anton, and Michelle for the useful article and video.

P.S. If the grid may be long or filtered, you might consider augmenting this idea by changing the where id not in (select …) statement to also include the list of existing ITEM_ID values in the EBA_DEMO_ITEM_ORDER_LINES where the ORDER_ID = :P3_ID and then also passing the P3_ID as one of the “Page Items to Submit”.