Learn how to use all features of the Oracle APEX platform
My major “feature” for APEX 26.1 was writing a book to help devs understand and use the full breadth of functionality Oracle APEX provides. This post on the official APEX blog gives a short overview of all 25 chapters. Download the PDF version from the APEX books page for offline use.
Hope the resource is useful to you, to your teammates, or to anyone you know who may be trying to be successful with Oracle APEX. It is packed with screenshots and figures to try and help you focus on following the ideas of each topic rather than on trying to reproduce the result click by click.
P. S. The title of the book is a nod to my late father who in 1993 suggested the title of my first book Oracle: Forms Developer’s Companion. Dad thought “Companion” sounded like a friend who was there to help you be successful, so it felt appropriate since that was my goal with the new book, too.
An Oracle colleague asked how to create an editable Interactive Grid against an APEX collection. I share the solution here along with a few observations I learned while creating it.
Client Side Grid Model
An interactive grid manages an in-memory record set in the browser. This is called the model. An editable grid’s model also tracks rows the end user has added, modified, and deleted. When the user submits the page or clicks the grid toolbar’s (Save) button, the changes in the model are sent to the server for processing by the Interactive Grid – Automatic DML page process.
The client-side model uniquely identifies rows using the values of the region columns marked as Primary Key and assumes those values won’t change. For rows the user creates, it generates a temporary unique key.
Grid Primary Key Cannot Change
When the grid saves a new row, the grid-assigned temporary key gets replaced by the database-assigned key. That’s the only key-change situation the grid allows.
I had begun by using the APEX_COLLECTIONS view’s SEQ_ID as the grid’s primary key column, thinking it was a safe choice for an unchanging unique key. This wrong assumption was the gotcha I encountered when pairing the grid with an APEX collection. I learned that it’s definitely unique, but it’s not necessarily unchanging. In certain situations, the collection’s sequence ID can get reused or changed.
The more obvious way this can happen is if the developer calls any of the APEX_COLLECTION package’s procedures that affect the ordering of the collection like: SORT_MEMBERS, MOVE_MEMBER_UP, MOVE_MEMBER_DOWN, or RESEQUENCE_COLLECTION.
But what I ran into was something more subtle. Assume we use a collection for a simple shopping cart with columns:
C001 as ITEM_CODE
N001 as QUANTITY, and
D001 as NEED_BY_DATE
I ran into trouble with the following scenario:
Add “Banana” to the shopping cart grid
Click (Save) on the grid toolbar to persist that changes in an AJAX request
Delete “Banana” from the grid
Add “Cherry” to the shopping cart grid
Click (Save) on the grid toolbar again
At this point, I received an error in the JavaScript console:
Uncaught TypeError: Cannot read properties of null (reading 'serverOffset')
at Object._resequenceServerOffset (modelViewBase.min.js?v=24.2.2:4:40633)
at Object._clearChanges (modelViewBase.min.js?v=24.2.2:4:39956)
at modelViewBase.min.js?v=24.2.2:4:13563
How Collection Assigns SEQ_ID
The APEX_COLLECTION package assigns a unique sequence ID to each member as you add it. It uses the straightforward approach of using one greater than the maximum sequence ID currently in the collection, or one (1) if the collection is empty.
In the above scenario, my “Banana” row got assigned SEQ_ID = 1. Then when I deleted “Banana” and inserted “Cherry,” at save time APEX Grid Automatic DML page process performs deletes first. This removed “Banana” from the collection, leaving no members. Then the insert of “Cherry” was performed. At that time, since there were no members in the collection, “Cherry” was a assigned a sequence of one (1).
When the grid’s AJAX save request returned, it needs to consolidate the rows in its client-side model with the new data returned from the server. At that time, its in-memory row set it still has “Banana” with primary key value 1 (marked as deleted). But in the new data returned in the AJAX response, it finds a new row “Cherry” also with primary key value 1 marked as a new row. At this point the model gets confused because it assumes the primary keys never change unless it’s the grid-assigned temporary key changing to the database-assigned key value during insert.
Using a SYS_GUID Instead
The solution is to generate a unique key value and store that into the collection in one of the generic columns. Here we’ll use C002 to store a value we’ll assign using the SYS_GUID() function. By then configuring the grid to use this unchanging unique value, the above use case works fine.
Shopping Cart Package API
I created the following PL/SQL package API to encapsulate my use of the APEX Collections API behind a more meaningful set of function and procedure names related to adding items to a shopping cart:
The shopping cart API above hides the details of working with the APEX_COLLECTION package from the rest of my application pages. A final touch was creating a database view to hide references to the APEX_COLLECTIONS generic view from my pages as well. I created the following view:
create or replace view shopping_cart_v as
select c001 as item_code,
c002 as id,
n001 as quantity,
d001 as need_by_date
from apex_collections
where collection_name = 'SHOPPING_CART'
The Solution Sample App
This let me create a grid based on the SHOPPING_CART_V view and configure the grid’s DML page process to have custom PL/SQL-based save-time logic based on the SHOPPING_CART_API package. The Grid DML page process settings appear below. Notice I’ve disabled row locking and lost update protection. Since the collection data is private to the current user session, there’s no chance another user can lock or change a row.
The complete code for the PL/SQL Code to Insert/Update/Delete looks like this:
Since APEX treats all page items and grid columns as strings, I use the GET_NUMBER and GET_TIMESTAMP functions in the APEX_SESSION_STATE package to get a correctly typed number and date value, respectively. These functions automatically take into account any format mask configured on the item or column if present.
Declaratively Wiring Button to Grid Action
The demo includes two pages. Page 1 has a grid with the toolbar turned off and footer turned off, forcing the save to happen using a normal page submit (Save) button. Since the (Add Item) button is part of the grid toolbar, if the toolbar is hidden the end user has no way to add a row. So I wanted to put an (Add Item) button on the page that let users add a new row to the grid.
My colleague John Snyders taught me a declarative technique for doing this by:
Assigning the grid a static ID like shoppingcart
Setting the Action of the button to Defined by Dynamic Action, and
Configuring the button’s Custom Attributes property to be data-action="[shoppingcart]selection-add-row"
This instantly made my button perform the grid’s native add row action and let me position the button anywhere on my page instead of having to enable the grid toolbar just to have the (Add Item) button.
One Final Quandry Resolved
After configuring the (Add Item) button, I was puzzled when I noticed my button label “Add Item” got changed at runtime to “Add Row“. It turns out when you declaratively “wire” a button to an action, then on page load the button label takes on the action label automatically. The grid lets you configure the label of the (Add Row) toolbar button, however that property value is only visible and editable and persisted if the grid toolbar is on. So, with no developer-overridden action label saved for the “Add Row” operation in the grid region metadata, it reverts to the default label of “Add Row”.
I solved this by setting the label of the selection-add-row action to “Add Item” in my grid region’s JavaScript initialization function using the following tiny bit of code:
I later learned I could do avoid writing the line of JavaScript that sets the action label by adding an additional custom attribute data-no-update="true" to my (Add Item) button. John pointed me to the Buttons section of the grid JavaScript docs that mentions this tip in a sentence that says, “If you don’t want the label, title, or icon to be updated add [this] attribute…”
Processing the Shopping Cart Contents
The sample app does not do this, but just for completeness know that your app can process the contents of the shopping cart using a PL/SQL cursor for loop. This use case offers another compelling reason to create a view to hide direct access to the APEX_COLLECTIONS view. It makes your business logic easier to read as well. Such a loop in this shopping cart example would look like:
for j in (select item_code, quantity, need_by_date
You can download the sample app from here to install in APEX 24.2 or later. It includes Supporting Objects code for the SHOPPING_CART_API package you can study further. Enjoy!
Thanks to everyone who came out to see my talk at APEX Alpe Adria in Zagreb. Here are the slides I presented. Hope to see you next year!
All of the sample apps I showed during the talk require an Oracle 23ai database, although the Books Against JSON Collection can work fine against a table containing a JSON CLOB/BLOB column in 19c. The two Books-related sample apps require a couple of APEX fixes that are in the APEX 24.2.5 patch set. You can download the three sample apps from here:
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 Rivianbrand 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_insuranceOUT 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 OptionsandRefresh. 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 theAttributes 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:
Retrieve the static id of the region from the Affected Elements
Lookup the grid region to work with using that static id.
Use region object to get grid’s jQuery view and model containing its data.
Assign custom attribute values passed in the data parameter to local variables
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!
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
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”:
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.
l_old := list of applied tag ids from book_tag_xref for current book
l_new := list of submitted tag ids
l_manuals := list of manual entries (if any)
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
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
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
Delete any rows in l_delete from book_tag_xref by primary key
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:
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)
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:
Visit the RESTFul Services editor
SQL Workshop > RESTFul Services
Expand the Modules Node in the RESTFul Data Services tree
Select the trycombo module node in the tree
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/
Edit the remote server with name apex-oraclecorp-com-pls-apex-stevemuench
In the Endpoint URL field, paste the value you copied to the clipboard in step 4
Remove the trailing /trycombo/ from this value, and click (Apply Changes)
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.
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:
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
-- 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:
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:
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.
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 Unit
Description
validate_warehouse
Check warehouse is valid
validate_category
Check category is valid
validate_quantity
Check stock quantity is natural number
validate_available
Check available date is no older than a year
validate_price
Check price is between 0.01 & 100,000.00
consolidate_thing_warehouses
Consolidate thing stock for same warehouse
closest_ninety_nine_cent
Return closest price that ends with 0.99
check_thing_required_fields
Check thing required fields are provided
check_stock_required_fields
Check 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
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.
"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:
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",
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",
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:
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",
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",
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",
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:
Performs before insert or update processing
Inserts JSON payload into things_dv duality view, returning _id value
Executes after insert or update processing
Returns the latest version of thing object from the duality view by id
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.
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.
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.
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.
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.
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.
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.
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.
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:
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
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)
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.
"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
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 Titleproperty 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:
Add a second version variable V_OPERAND2 of type NUMBER,
Initialize its default value from the value of the parameter P_OPERAND2, and
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:
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.😊
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_DAYSShared 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" -->
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:
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.
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.
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:
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 Ticketsswitch 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
