Oracle APEX Grand Tour

I joined the APEX dev team in 2021 after falling in love with its low-code app-building productivity and ability to fully embrace the Oracle database. But when I looked for a unified demo and presentation showcasing all its low-code features, I came up empty-handed. Over the following months, I learned everything I could and created the one hour webinar I wished had existed when I was starting out with the product. The goal? Educate the viewer on what is possible with APEX by seeing it in action.

If you’re reading this blog, you probably have already employed most of APEX’s awesome capabilities in your own apps. But, when you want to educate others about the wonders of APEX, consider sending them this link: https://apex.oracle.com/go/videos/medipay-mobile-app

If the people you evangelize like what they see, then suggest next that they take our new APEX Foundations course to roll up their sleeves and use the product with free guidance from our instructors.

The desktop and mobile MediPay demo in the webinar shows off the following APEX features in action:

  • Progressive Web Applications for desktop and mobile devices
  • Persistent authentication (“Remember me”)
  • Mobile install screenshots (now declarative in 22.2)
  • Mobile native camera to snap image of receipt to reimburse
  • Mobile image resize and upload (using plugin based on open-source JS library)
  • GPS from mobile device (now a built-in dynamic action in 22.2)
  • Approvals task definition for approving reimbursement payment
  • Custom task details page for claim payment approvals
  • Flows for APEX payment processing model
    • Email to customer if validation of payment method fails
  • View current state of Flows for APEX task model (using plugin)
  • REST invocation of 3rd party payment processing using two-legged OAuth2 authentication
  • Spatial query for fraud detection (highlighting when upload occurs over 500km from user’s home address)
  • Geocoding lookup on user profile address page
  • Minimap Display-only Map Widget on attachment detail page
  • Smart Filters and Map Region against REST(Recent Payments) working both “live” over REST and using REST synchronized local cache table
  • Faceted Search and cards against remote Oracle DB with REST-enabled SQL (Leads Referrals) working both “live” over REST and using REST synchronized local cache table
  • Web-style multiword search using “Tokenize row search”
  • One-click Remote Deployment from Team Dev to Team UAT environment
  • Calendar for Leads Meetings with drag and drop to reschedule or adjust meeting length
  • Master/Detail Interactive Grids page with Coverage Plans
  • Interactive Report with All Claims
  • Dashboard page with charts
  • Machine Learning algorithm integrated for showing prediction probability of approve/reject.
  • Regions in tabs on the User Profile Page
  • Image upload and display in User Photo tab, with automatic BLOB storage
  • Dynamic Actions to react to value changes to hide/show fields on User Profile payment method tab
  • ACL security (MediPay Staff Member | MediPay Customer) controlling access to pages, regions, items, and columns
  • Server-side JavaScript User Profile page to lookup user id from logged in user name at page load and in “Clear Unselected Payment Methods” process
  • VPD security for MED_CLAIMS_V view so users only see their own claims in the desktop portal

Building a Mini SQL Workshop #JoelKallmanDay

Two new features in APEX 22.2 let me easily create a Mini SQL Workshop app. Users type any query in a code editor and instantly visualize the resulting data. I wired my colleague Ronny Weiss’ Monaco Code Editor region plug-in to a new CLOB-valued page item to support editing the SQL query. A dynamic action reacts to the code editor save event to refresh the new Dynamic Content region to show the query’s results. For an interesting twist, the database’s native support for XML and XSLT produces the HTML markup for the dynamic content region rather than looping over data and concatenating strings ourselves.

On this second annual Joel Kallman Day, and just past my first anniversary on the APEX dev team, this example happily reunited me with some of the ideas I loved so much back in the late 1990’s that they compelled me to write my book Building Oracle XML Applications. Thanks for the inspiration, Joel. Miss ya lots!

Since APEX makes the job easy, unsurprisingly the page looks simple, too. The code editor region has a dynamic action that refreshes the Results region, which returns its HTML contents using an html_for_sql() package procedure. The P1_SQL page item is typed as a CLOB so the SQL query can be as large as necessary.

Code Editor region, CLOB-valued P1_SQL page item & Dynamic Content region for query results

Creating a CLOB-valued Page Item

I started by creating a hidden page item named P1_SQL in my page to store the query the user will edit in the code editor. Since the query might exceed 32K, I took advantage of the new CLOB data type in the Session State section of the property editor. When you create hidden page items, as well as ones that can hold a large amount of text, you can now change the default session state data type from VARCHAR2 to use a CLOB instead.

Hidden page items and ones that can contain large amounts of text can now use a CLOB data type

Wiring the Editor to a CLOB-valued Page Item

Next, I downloaded the latest version of the Monaco Code Editor region plugin, extracted the zip file, and imported the plugin into my new application. I created the Code region and set it to have the type APEX-VS-Monaco-Editor [Plug-In]. Next I configured the source of the code editor to retrieve the value of the P1_SQL CLOB-valued page item by setting its SQL Query to:

select :p1_sql as value_edit, 
       'sql'   as language
from dual

On the Attributes tab of the code editor region, I setup the code editor to save its contents in the CLOB-valued page item by entering the following one-line call in its Execute on Save property. The plug-in sets up the :CLOB bind variable and we use the new apex_session_state package’s set_value() procedure to assign that value to the P1_SQL page item.

begin
  apex_session_state.set_value('P1_SQL',:clob);
end;

And with those two simple steps, the code editor for the SQL query was sorted.

Refreshing the Dynamic Content Region

To refresh the dynamic content region whenever the user clicks the (Save) button on the code editor, I added a dynamic action to react to the plug-in’s event Upload of Text finished [APEX-VS-Monaco-Editor]. It contains a single action step using the Refresh action type, and uses yet another new 22.2 feature to make the action step more self-documenting by entering a more meaningful name of “Refresh Results”.

Using a custom name for an action step to make more maintainable apps

Using XML & XSLT to Get HTML for any SQL

With the new 22.2 Dynamic Content region, you no longer use the trusty HTP package to print HTML markup into a buffer. Instead, you configure a function that returns a CLOB containing the HTML the region should render. This change was required to make the region dynamically refreshable, which was a longtime request from APEX developers in the community. You can create the CLOB full of HTML markup in any convenient way, but the way I find most elegant and declarative is using the combination of XML and XSLT stylesheets.

The Oracle database contains native functionality to produce an XML document representing the results of a query using the DBMS_XMLGEN package’s getxml() function. To produce an XML document from an arbitrary SQL query contained in a variable like p_sql, you just need the following few lines of code. The call to the SetNullHandling() procedure asks DBMS_XMLGEN to use an empty tag to represent a NULL value rather than omitting the XML element for a NULL column in the result.

ctx := dbms_xmlgen.newcontext(p_sql);
dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.empty_tag);
l_xml_clob := dbms_xmlgen.getxml(ctx);
dbms_xmlgen.closecontext(ctx);

The getxml() function produces an XML document that will have a canonical structure like this with a <ROWSET> element containing one or more <ROW> elements, each of which contains child elements named after the columns in the result set.

<ROWSET>
   <ROW>
     <DEPTNO>10</DEPTNO>
     <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW>
     <DEPTNO>20</DEPTNO>
     <DNAME>RESEARCH</DNAME>
   </ROW>
</ROWSET>

The XML Stylesheet Language (XSLT) is a powerful, concise, declarative way to recursively format the contents of an XML document to produce a result like HTML. If you are a fan of Oracle Reports and its approach of applying “repeating frames” to data, then you’ll understand XSLT intuitively. If not, it may take a bit longer to have its charms grow on you, but given its Swiss Army knife applicability to many jobs, it’s well worth your time to learn more about it.

If we write the XSLT stylesheet in a generic way, the same transformation can produce an HTML table from the results of any XML in the <ROWSET>/<ROW> format above. The XSLT stylesheet that gets the job done looks like the one below. Starting with the root (match="/") of the document, its templates recursively apply other matching style templates to the XML elements in the document “tree” of nested elements. The stylesheet contains templates that match a ROWSET element, a ROW element, and any child element of a row ROW/* to produce an HTML <table>. This table contains <thead> and <tbody> elements, <tr> elements for the rows of the table, and <th> and <td> elements for the table cells containing the data from the query result XML document it’s presented to format.

<xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
      <xsl:apply-templates/>
  </xsl:template>
  <xsl:template match="ROWSET">
    <table class="sql-results" cellspacing="0">
      <thead>
        <xsl:apply-templates select="ROW[1]/*" mode="ColumnHeaders"/>
      </thead>
      <tbody>
        <xsl:apply-templates/>
      </tbody>
    </table>
  </xsl:template>
  <xsl:template match="ROW">
    <tr><xsl:apply-templates/></tr>
  </xsl:template>
  <xsl:template match="ROW/*">
    <td><xsl:apply-templates/></td>
  </xsl:template>
  <xsl:template match="ROW/*" mode="ColumnHeaders">
    <th><xsl:value-of select="name(.)"/></th>
  </xsl:template>
</xsl:stylesheet>

To transform the XML document produced from the query into HTML, the demo app’s html_for_sql() function in the eba_demo_xslt package uses the Oracle SQL function xmltransform(). It uses the stylesheet above to transform the result into HTML using a single SQL statement like this:

select xmlserialize(content
         xmltransform(l_xml,c_stylesheet) as clob)
into l_html
from dual;

Since the DBMS_XMLGEN package and the XMLTRANSFORM() and XMLSERIALIZE() functions are all implemented natively inside the database engine, they get the job done quickly. To tweak the formatting of the results, we can simply adjust the declarative XSLT stylesheet and sprinkle in some appropriate CSS style rules on the page.

Returning Dynamic Content Region HTML

The last step in the process is configuring the function body that will return the new Dynamic Content region’s HTML markup. To accomplish this, I set the PL/SQL Function Body returning a CLOB property of the Results region to the following to employ our XML and XSLT approach above. I simply pass in the value of the P1_SQL CLOB-valued page item into the html_for_sql() function.

return eba_demo_xslt.html_for_sql(
           apex_session_state.get_clob('P1_SQL'));

With all the pieces in place, we can type in any valid query of any size or complexity, click on the code editor’s (Save) button, and immediately see the query results in the page.

Query results from two APEX dictionary views

Ensuring the User-Entered Query is Valid

The sample’s eba_demo_xslt package also contains a validate_sql_statement() function that ensures the query starts with SELECT or WITH as well as guarantees that it parses correctly. The function returns NULL if the query is valid, or otherwise it returns an error message to be displayed in the page to help the user understand what’s wrong with the query.

To checkout the sample, download it from here and import it into APEX 22.2 to give it a spin.

Managing Intersection Rows with Drag & Drop

Overview

After publishing my last article about reordering cards using clicks, Peter Raganitsch suggested on Twitter that I try the same use case using drag and drop with the free FOS – Drag and Drop plugin. While exploring the idea, I expanded the use case to more closely resemble my conference organizer app where each yearly edition of a conference can have a different “lineup” of speakers. So I introduced a Lineup table into the sample app’s data model and document here how I created a page to reorder the employees in one or more lineups, including the ability to add unassigned employees to the current lineup or remove an employee from the lineup. The result is a UX pattern that may work well for other situations involving the data in an intersection table like EmpLineup below when the user needs to impose a preferred ordering on them. The expanded data model looks like this now:

Data Model for the Emp Lineup Reordering sample application

Choosing the Lineup to Work On

I started with a page containing a Cards Region named Employee Lineup based on the following query that joins the EmpLineup and Emp tables to present the names of the employees in a lineup:

select e.id, e.name
from eba_demo_dragdrop_emp_lineup lu
left join eba_demo_dragdrop_emp e on e.id = lu.emp_id
order by lu.sort_sequence nulls last, e.created

Then, to allow the user to choose which lineup she wants to work on, I added a select list P1_LINEUP page item to a static content region above the cards region, with the simple List of Values SQL query to show the available lineups:

select name, id
from eba_demo_dragdrop_lineup
order by name

With the “Lineup Chooser” select list in place, I updated the cards region’s SQL query to add a WHERE clause to filter the intersection table rows to only retrieve the ones related to the selected lineup, and ensured that the P1_LINEUP page item name was listed in the Page Items to Submit list for the region.

select e.id, e.name
from eba_demo_dragdrop_emp_lineup lu
left join eba_demo_dragdrop_emp e on e.id = lu.emp_id
where lu.lineup_id = :P1_LINEUP
order by lu.sort_sequence nulls last, e.created

To immediately see the list of employees in the newly selected lineup reflected below in the cards region, I added a dynamic action event handler named On Change Lineup to the P1_LINEUP page item to handle its Change event, and configured its first action step to use the built-in Refresh action to refresh the Employee Lineup region.

Since the end-user will be changing the value of the chooser, APEX’s default behavior is to warn them if they attempt to navigate away from the page with unsaved changes. In this case, since I know the value of P1_LINEUP is only used as a query filter, I set the page-level Warn on Unsaved Changes switch to “off” to avoid this warning.

This gave me the following in-progress page letting the user choose which lineup to work on:

Cards region filtered by “Lineup Chooser” select list above

Adding Employees to the Lineup

To allow the user to add new employees to a lineup, I started by adding a button named ADD_TO_LINEUP to the page, and set its position to Before Navigation Bar. I’d used modal dialog pages many times before, but wanted to learn how to also use inline dialogs in APEX, and this use case seemed like a good one for an inline dialog. I wanted to quickly present a shuttle control in a modal dialog when the user clicks on the (Add to Lineup…) button, that shows employees that are not yet assigned to the current lineup and lets the end-user to pick one or more employees to add.

After not finding any obvious way to directly create an inline dialog in the APEX builder, my next intuition was that doing so must involve creating a region. But I also failed to find any “Inline Dialog” region type. At this point, I figured it must be a static content region with a particular template setting. Seeing a position heading in the rendering tree called Dialogs, Drawers, and Popups gave me a clue that the region position needed to be configured to fall in this category. So I added a static content region “Add Employees” to contain the shuttle page item, set its Position to Dialogs, Drawers, and Popups and set its Appearance > Template to Inline Dialog.

I added a page item P1_EMP_IDS_TO_ADD to this new static region and set the type to Shuttle. I set its Label to “Unassigned Employees”, and Template to Optional – Above to get what looked best to me from previous shuttle experiments I’d done. To have the shuttle show only employees who were not yet assigned to the current lineup, I configured the shuttle’s List of Values SQL query as follows and set the Display Extra Values to the off position:

select name,id
  from eba_demo_dragdrop_emp
 where id not in (
   select emp_id
     from eba_demo_dragdrop_emp_lineup
    where lineup_id = :P1_LINEUP
 )
order by name

To have the (Add to Lineup…) button created above open the inline dialog, I added a dynamic action event handler to its Click event, configured the first action step to clear the value of the P1_EMP_IDS_TO_ADD page item, a second action step to refresh the page item to update its list of available employees, and a third action step to show the inline dialog.

Initially tried to show the dialog using the Show dynamic action step to show the corresponding “Add Employees” region, but when that didn’t work, I looked again through the list of built-in actions available and tried the Open Region action instead which worked great.

Back in the “Add Employees” inline dialog’s static region, I added a button named CANCEL to the Close position of the region, and a dynamic action event handler for its Click event to perform the corresponding action step of Close Region.

To process the addition of any selected employees in the shuttle, I added a button named ADD to the Create position of the inline dialog region, and added a dynamic action event handler for its Click event having three action steps:

  1. Execute Server-side Code
  2. Close Region (Region: Add Employees)
  3. Refresh (Region: Employee Lineup)

The PL/SQL code for the server-side code in step 1 looks like this, using the handy SPLIT_NUMBERS() function in the APEX_STRING package to parse the colon-separated list of employee ids from the shuttle that need to be inserted into the eba_demo_dragdrop_emp_lineup table as intersection rows. I also made sure that both P1_LINEUP and P1_EMP_IDS_TO_ADD were listed in the Items to Submit field.

insert into eba_demo_dragdrop_emp_lineup(lineup_id,emp_id)
select :P1_LINEUP,column_value
from apex_string.split_numbers(:P1_EMP_IDS_TO_ADD,':');

With these steps I now had a working add dialog to add one or more employees not currently in the lineup:

Inline dialog with shuttle to add unassigned employees to the current lineup

Deleting an Employee from the Lineup

I thought the easiest way for a user to remove an employee from a lineup would be to offer a little “x” icon on each card the user could click. A trick I learned while working on the last article came in handy again here. I decided to use a link with the new named action syntax in its href attribute to trigger a named action. This named action would store the value of the action parameter named id in a page item, and then trigger a custom dynamic action event handler to perform the server-side code to remove the employee from the lineup. This combination let me use a maximal amount of declarative APEX functionality.

I started by adding a hidden page item P1_EMP_ID_TO_REMOVE to the page. This will be used to store the id of the employee to remove from the lineup. Next I defined a custom dynamic action event named remove-emp-from-lineup-event with Selection Type of jQuery Selector and jQuery Selector of body. It contains two dynamic action steps:

  1. Execute Server-side Code
  2. Refresh (Region: Employee Lineup)

The PL/SQL block for the first action step contains just the delete statement below, with P1_EMP_ID_TO_REMOVE and P1_LINEUP both listed in the Items to Submit.

delete from eba_demo_dragdrop_emp_lineup
where emp_id = :P1_EMP_ID_TO_REMOVE
and lineup_id = :P1_LINEUP;

Next, I defined a named action remove-emp-from-lineup-action by adding the following block of JavaScript to the Execute When Page Loads section of the page:

apex.actions.add([
{
  name: "remove-emp-from-lineup-action",
  action: function( event, element, args)
  {
    // Store the value of the EMP id from the action args
    apex.items.P1_EMP_ID_TO_REMOVE.value = args.id;
    // Trigger custom event to perform the server-side call
    $("body").trigger("remove-emp-from-lineup-event");
  }
}])

To complete the job, I updated the HTML Expression for the card region’s Body section and changed it from:

<strong>&NAME.</strong> 

To add the following <a> element before the <strong> element. Its class attribute references the name of the fa-remove icon I wanted to use, and its href attribute contains the special (new in 22.1) named action link syntax to trigger the action named remove-emp-from-lineup-action passing along a parameter named id with the value of the ID column in the current row (i.e. card):

<a class="fa fa-remove" 
   href="#action$remove-emp-from-lineup-action?id=&ID."
></a><strong>&NAME.</strong> 

These steps produced the clickable remove icon next to the employee name in the body of each card, and clicking the “x” on any card removes it from the lineup. Since I also wanted clicking on the main card area to open a normal modal dialog to review/edit the employee’s details, I changed from using the “Full Card” action type to instead use the “Media” card action for that. In this way, the end-user can perform multiple actions on each card in a non-intrusive way.

Clickable “remove” icon on every card triggers named action to do the work

Reordering Employees Using Drag and Drop

Saving the most interesting feature for last, I downloaded the FOS plug-ins from here. The download was a sample app illustrating the use of all the FOS plug-ins, but my interest for this article was just the FOS – Drag and Drop one. After importing the FOS sample application, I visited its Shared Components > Plug-ins page in the APEX builder and exported just the FOS – Drag and Drop plug-in to a SQL file whose name APEX automatically chose for me. Importing the plug-in into my own app involved simply clicking the (Import>) button from the Plug-ins shared component page and choosing the name of this SQL file and completing the wizard, as shown below:

Importing the FOS – Drag and Drop plug-in into my own application

I enabled the drag and drop functionality on my page by adding a new dynamic action named “On Page Load Setup Drag/Drop” to the Page Load section and setting the Action of its first action step to FOS – Drag and Drop [Plug-In]. This plugin configures the drag and drop event listeners without having to understand what that means or how it works. I appreciated this, since I’m not an accomplished JavaScript programmer myself.

In the Affected Elements section of this action step, I set the Selection Type to Region and chose “Employee Lineup” from the Region list. This tells the plug-in that you want to have the drag and drop effect be applied to the cards in this region. In the sample app that FOS provides, they show how drag and drop can be easily applied to lots of other situations in APEX as well.

I noticed that the Mode was already set to Sort, which is the behavior I wanted. To configure the business logic that will execute when one card it dropped in the place of another card, I set the Drop Action to Execute PL/SQL Code and entered the following one line of PL/SQL in the Execute PL/SQL Code field, remembering to list P1_LINEUP in the Page Items to Submit field below it:

eba_demo_dragdrop.move_source_to_target(:P1_LINEUP,:DRAG_ID,:DROP_ID);

The :DRAG_ID and :DROP_ID bind variables are automatically set by the plug-in and evaluate to the primary key of the dragged card and that of the card currently in the dropped position, respectively. This was almost too easy.

Running my page, I was able to drag an employee card and drop it, but I noticed that when I’d refresh the page the order of the employees had not actually changed. To debug the problem, I added in one line of code to my move_source_to_target() package procedure to log the values of the source and target employee ids:

apex_debug.info('lineup=%s,source=%s,target=%s',
                 p_lineup_id,
                 p_emp_source_id,
                 p_emp_target_id); 

After enabling Debug mode for the page, this info() log message allowed me to understand that the drag and drop plug-in was passing values that were not my primary keys that I expected. This gave me a clue to check the “Employee Lineup” cards region to make sure I had correctly configured a column as the primary key, and in fact I had not done so yet. For the drag and drop to work as expected, I needed to select the cards region, activate its Attributes tab, and set the Primary Key Column 1 list to ID to let APEX know which column represented the primary key. After doing this small tweak, the drag and drop was working perfectly…

…until I tried changing the Lineup chooser select list to switch to working on a different lineup, or until I added some new employees to the current lineup, or until I removed an employee from the current lineup. After doing any of these actions that resulted in refreshing the cards region, the drag and drop behavior would cease to work.

Getting Drag and Drop to Work After Region Refresh

Googling around for a solution, I found an article that said I needed to re-execute the FOS – Drag and Drop [Plug-in] action step after the region refresh. It explained that the act of refreshing the region caused an AJAX request that resulted in changing the HTML elements in the cards region to which the drag and drop event listener was listening. This made sense, so I dutifully repeated the steps I did to originally configure the Page Load event action step above to “wire-up” the drag and drop event listener after each region refresh. I did that by adding an additional dynamic action step to each dynamic action step sequence where I was refreshing the cards region. However, to my chagrin, this additional effort did not remedy the problem.

Luckily, since I had met Peter Raganitsch in person at the APEX Alpe Adria conference back in April 2022 in Maribor, Slovenia, I felt comfortable contacting him by email. I let him know I was working on implementing his suggestion, but that I’d hit an issue I wasn’t sure how to debug. To my surprise and delight, he responded in short order with a solid tip that I probably would not have figured out on my own.

In his reply, he explained that I was doing the right thing to re-wire the drag and drop listener but that I needed to use that action step in a different dynamic action event handler responding to the Page Change [Cards] event instead of using the action step as part of the same action sequence that triggered the region refresh in the first place.

This has to do with timing of actions, meaning the “Refresh” of the Cards region wasn’t finished when you re-enabled Drag & Drop. Using the “Page Change [Cards]” event you make sure the Cards region is finished re-querying (or paginating). As DB and PL/SQL developers we often think about procedural and serial execution. In JavaScript that doesn’t always apply due to asynchronous operations. i.e. DA “Refresh” sends an asynchronous call to the DB and continues with the next action without waiting for the refresh result.

Peter Raganitsch

Enlightened by this new kernel of wisdom from Peter, I removed the three “re-wire the drag and drop” action steps I had added above. Then, on the Dynamic Actions tab, I added a new dynamic action event handler named “On Page Change Setup Drag/Drop Again”, set its When section’s Event to Page Change [Cards]. Repeating my steps done above, I set the Action of its first action step to FOS – Drag and Drop [Plug-In]. Again I set the Selection Type to Region and chose “Employee Lineup” from the Region list. Leaving the Mode set to Sort, I again set the Drop Action to Execute PL/SQL Code and configured the Execute PL/SQL Code to the same one-line of code as above, again remembering to list P1_LINEUP in the Page Items to Submit field below it.

After making that change, the drag and drop worked flawlessly, not only when the page renders originally, but also after adding new employees to a lineup, removing employees from a lineup, or switching to work on a different lineup. My final page looked like this:

Final lineup management page with add, remove, and drag/drop reordering functionality

Thanks again to Peter Raganitsch for the suggestion that resulted in my learning many new things about APEX and plug-ins. In addition, double thanks for the tip that helped me understand the right event handler to use to avoid timing problems due to JavaScript’s asynchronous nature when redefining event listeners after AJAX-driven page changes occur, like region refreshing.

If you’re interested in checking out the working sample, you can download it from here for use in APEX 22.1 or later.

Reordering Cards Using Clicks

Sometimes your data needs to be explicitly ordered rather than sorted by an intrinsic property like a name, salary, or hire date. In these cases we introduce an additional number column like SORT_SEQUENCE into the data model and sort on that manually-assigned position number. For a volunteer project I’m working on, I needed the ability to explicitly order the speakers at a conference, and easily adjust it as the organizer moves speakers around in the lineup. Before implementing the feature in my actual application, I built a simpler example based on employee names first to get the basic idea working. This article explains how I used a new feature of APEX 22.1 called Declarative Action URLs along with a dynamic action custom event to let users easily adjust the explicit ordering by clicking on a source card and a target card in a cards region.

Sorting Cards by Sequence in Related Table

To more closely mimic the data model of my actual conference management application, I have a simple employee table EBA_DEMO_REORDER_EMP with just an ID and NAME column and a separate table called EBA_DEMO_ORDER_EMP_LINEUP that contains an EMP_ID column referencing the ID primary key of the main table, along with the SORT_SEQUENCE number column. Out of a possibly larger set of employee names, a certain set get introduced into the “lineup” and then their explicit ordering is established as part of that lineup.

I started by building a cards region based on the following query, that joins the two tables and orders by the SORT_SEQUENCE column in the employee lineup table. I configured card title to use the NAME column and the badge to use the CARD_NUMBER.

select e.id, 
       e.name, 
       row_number() over (order by lu.sort_sequence nulls last,
                                   e.created)
       as card_number
from eba_demo_reorder_emp_lineup lu
left join eba_demo_reorder_emp e on e.id = lu.emp_id
order by lu.sort_sequence nulls last, e.created

This quickly produced the basic card layout for the lineup of employee names.

Cards region showing the explicitly ordered lineup of employee names

Getting the Reordering Working

The lineup in my actual application can include hundreds of names, so I decided to let the user click on the card of the employee that needed to move, then click on the card of the place they’d like to move that employee. Using these two clicks, the end-user identifies first a “source” employee and then chooses a “target” employee position.

Inspired by the “source” and “target” naming, I created two hidden page items, P1_EMP_ID_TO_MOVE and P1_EMP_TARGET_POSITION, each with its Maintain Session State property set to Per Request (Memory Only). My strategy was to populate the first page item with the employee ID value of the initial click, and set the value of the second page item with the ID of the second click.

I wrote the PL/SQL package procedure to accept the source and target employee ids and perform the automatic reassignment of the SORT_SEQUENCE values of the affected rows:

create or replace package eba_demo_reorder is
   procedure move_source_to_target(p_emp_source_id number,
                                   p_emp_target_id number);
end;

With this backend business logic in place, the two remaining tasks were:

  1. Handle the card click to assign the hidden page item values, and
  2. Invoke the above procedure once both source and target were defined, refresh the cards region, and clear out the hidden page items again.

I chose to tackle the second task first using a Dynamic Action custom event to maximize the amount of APEX’s declarative functionality I could take advantage of.

Using a Custom Event to Maximize Low-Code

Assuming the two hidden page items have the source and target employee ids populated, executing the server-side PL/SQL code, refreshing the cards region, and clearing out the hidden page items are all actions I can easily accomplish using dynamic action steps in response to a custom event. As shown below, I created a dynamic action event handler for a custom event with event name move-source-to-target-da. The Selection Type is jQuery Selector and I used the page’sbody as the jQuery Selector to be the anchor element for the event listener. I chose the page body at the recommendation of my colleagues John and Stefan who reminded me that refreshing the cards region would remove any event listeners on the cards themselves. The body targets the event listener on a element of the page that contains the cards region, but which is not itself getting refreshed.

Custom dynamic action event anchored to the page body.

The dynamic action steps include an Execute Server-side Code step to run this block of code to perform the reordering, making sure to include both P1_EMP_ID_TO_MOVE and P1_EMP_TARGET_POSITION in the page Items to Submit list:

eba_demo_reorder.move_source_to_target(
   p_emp_source_id => :P1_EMP_ID_TO_MOVE,
   p_emp_target_id => :P1_EMP_TARGET_POSITION);

That is followed by a Refresh step to refresh the cards region on the page, and finally a Clear step to clear the values of the two hidden page items.

Wiring a Full Card Click to a Named Action

To tackle the remaining task of handling the click on the card, I added a card action and set the action Type to be Full Card. Following a suggestion from my colleague John, I used the new Declarative URL action invocation syntax he describes more in depth in his blog article Exploring new APIs in APEX 22.1. To put it to use, for the link type I chose Redirect to URL and provided a special URL syntax that invokes a named action, passing along one or more parameters in the process:

#action$move-source-to-target?id=&ID.

A URL of this syntax lets a click on my card invoke an action named move-source-to-target, passing along a parameter named id whose value is provided by the ID column of the current employee card.

Defining the named action at the moment requires a bit of JavaScript code. I added the following to my page’s Execute when Page Loads code block. If the P1_EMP_ID_TO_MOVE item is blank, it sets its value to the value of the id argument passed in. If P1_EMP_ID_TO_MOVE is set but P1_EMP_TARGET_POSITION is blank, then it sets the target and triggers the custom event named move-source-to-target-da that we configured above to perform the server-side PL/SQL call, refresh the cards region, and clear out the two hidden page items again.

apex.actions.add([
{
   name: "move-source-to-target",
   action: function( event, element, args)
           {
              /* If both are blank, set emp to move */
              if (apex.items.P1_EMP_ID_TO_MOVE.value      === '' && 
                  apex.items.P1_EMP_TARGET_POSITION.value === '') {
                 apex.items.P1_EMP_ID_TO_MOVE.value = args.id;
              }
              // If emp to move is set and target blank, set target
              // and trigger the custom event to complete the job
              // using declarative DA action steps to invoke the
              // server-side PL/SQL package procedure to move the
              // source emp to the slot where the target is.
              else if (apex.items.P1_EMP_ID_TO_MOVE.value      !== '' && 
                       apex.items.P1_EMP_TARGET_POSITION.value === '') {
                 apex.items.P1_EMP_TARGET_POSITION.value = args.id;
                 // Trigger custom event to perform the server-side call
                 $("body").trigger("move-source-to-target-da");
              } 
           }
}
] );

My colleague Stefan gave me the great idea to use a custom event for this and to trigger it programmatically from the named action code. This allowed me to benefit from the simple action URL-wiring syntax as well as from the simplicity of using declarative dynamic action steps to perform the rest of the functionality.

The result is the click-click card reordering you see in this short video:

Example app for reordering cards with two clicks

If you’d like to try out the working example, download the app from here.

Using Scriptable DevOps to Save Time

While developing an APEX app, I check my latest changes into a Git repository after reaching a feature milestone. In this article I’ll explain how I took advantage of APEX’s scriptable devops features to automate multiple steps in my daily workflow that I had previously been doing manually. The result are two simplified commands:

  • apex2git – exports an APEX app to a Git work area, staging changed files
  • git2apex – imports an app from a Git work area to an APEX workspace

In case the scripts could be useful to you to study or use, I’ve included a link to download the source at the end of the article along with a short video that illustrates how I use these commands in my daily work.

My Workflow Before Optimization

When adding a new feature to an existing app, I used to do all of the following steps one by one:

  • pull the latest app version from remote Git repo to my local work area
  • create a Git branch named after the feature ticket I’m working on
  • import the app from my Git work area into my APEX dev workspace
  • implement the new feature using the APEX App Builder
  • export the enhanced app to a temporary staging directory (e.g. /tmp/f1234_stage):
    1. as a single SQL file (e.g. f1234.sql), and also
    2. as individual component SQL files organized into directories, and also
    3. as readable YAML for easy diffing
  • copy new f1234.sql file to Git work area as my-app-name.sql
  • review the diffs between temp dir files and those in the Git work area
  • cherry-pick changed files from the temp dir and copy to Git work area
  • add the changed files to the Git staging area
  • commit the staged changes to the local Git repo
  • push the changes up to the remote team Git repo
  • initiate a merge request for my branch’s changes

That’s a lot of steps. Luckily I was able to simplify some of them as I learned a few new tricks.

Git Automatically Ignores Unchanged Files

The first optimization opportunity I discovered was a happy accident. Prior to this revelation, I was using a tool — Beyond Compare on Windows or Kaleidoscope on Mac — to compare the APEX export files in the temporary directory with corresponding files in the Git work area to identify which ones my feature work had changed. Then I would carefully copy only the changed files over to the Git work area.

Once, by mistake, I copied all of the files from the temp directory to the work area, and even mistakenly added them all to the Git staging area. After doing this unintentional blunder, I typed git status . To my surprise, despite all the files having a later timestamp, Git had automatically ignored adding to the staging area any file whose logical contents had not actually changed! This productivity gift meant I could simply bulk copy all the exported files to my work area without manually cherry-picking the changed files myself. Bravo, Git!

Using SQLcl to Export APEX Apps

While exporting and importing APEX apps in the browser is straightforward, I researched how I could do it from the command line instead. This felt like an enabling ingredient to automating more of my common workflow. I learned that SQLcl supports the apex export command that looked promising.

Through a series of trials, I converged on the following command that, given an application’s numeric id (e.g. 1234), would export it as a single SQL file into a target directory (e.g. /tmp/f1234_stage), including supporting objects, using original component ids, and skipping the inclusion of the export date. While the command is intended to be entered as a single line, I’ve formatted it below for better readability:

$ sql appuser/password@host:port/servicename
SQL> apex export -applicationid 1234
                 -dir /tmp/f1234_stage
                 -skipExportDate
                 -expOriginalIds
                 -expSupportingObjects Y 
                 -expType APPLICATION_SOURCE

The result of running the apex export command above is the single file f1234.sql containing the entire application source for application 1234, and this file gets created in the /tmp/f1234_stage directory.

The option to include supporting objects is self-documenting, but it’s important to understand the other two. Excluding the export date ensures component files with no other changes don’t differ from the existing version only due to the date on which they were exported. Using original ids maintains internal id numbers of components as teammates import the app and export it again from other APEX workspaces. Failure to use either of these two latter options could lead to unnecessary differences in files. Of course it’s also important to follow the best practice of using the same workspace id and be running the same APEX release (including patch set!) across different environments like Dev, Test, Prod, and private development. Failure to follow this best practice can cause uninteresting differences in the component files due only to the different workspace id values or different patchset levels.

While the single SQL file is useful for one-click installation, I also want to retrieve the individual application component SQL files organized into a directory hierarchy. This makes it easier to understand what components have been added, changed, or removed with each new feature. This requires adding the -split command line option. So the command to do an application export split into separate component SQL files is:

SQL> apex export -applicationid 1234
                 -dir /tmp/f1234_stage
                 -skipExportDate
                 -expOriginalIds
                 -expSupportingObjects Y 
                 -expType APPLICATION_SOURCE
                 -split

This results in a directory named f1234 containing all the split SQL component files organized into subdirectories, and the f1234 directory gets created inside the /tmp/f1234_stage directory.

Starting with APEX 22.1, the readable YAML export option allows much easier code reviews since it presents the application component metadata in an easy-to-understand text outline with property names and values that match what you see as a developer in the APEX builder. To export the readable YAML version of an app, we can use the new READABLE_YAML export type:

apex export -applicationid 1234
            -dir /tmp/f1234_stage
            -skipExportDate
            -expOriginalIds
            -expSupportingObjects Y 
            -expType READABLE_YAML

This results in a directory named readable containing readable YAML files for application artifacts organized into subdirectories, and the readable directory gets created inside the /tmp/f1234_stage directory.

Using SQLcl 22.1 against an APEX 22.1 workspace schema, it’s possible to include multiple export types in the same export command by using a comma-separated value for the -expType command line option. So I was able to combine the export of the “split” application component SQL and the readable YAML into a single command like this:

apex export -applicationid 1234
            -dir /tmp/f1234_stage
            -skipExportDate
            -expOriginalIds
            -expSupportingObjects Y 
            -expType APPLICATION_SOURCE,READABLE_YAML
            -split

This results in a directory named f1234 containing both the readable directory of the readable YAML files as well as all the split SQL component files organized into appropriately-named subdirectories, and the f1234 directory gets created inside the /tmp/f1234_stage directory.

Automating APEX to Git Export

The recipe I identified to export an APEX app with id 1234 into the Git work area for repository my-app in my home directory includes these steps:

  1. Create a temporary staging directory /tmp/f1234_stage
  2. Use SQLcl to apex export app 1234 to this staging directory
  3. Copy /tmp/f1234_stage/f1234.sql to ~/my-app/my-app.sql
  4. Deep copy /tmp/f1234_stage/f1234 to ~/my-app
  5. Add all files to Git staging area (Git includes only ones that really changed)

Since I was already familiar with Apache Ant for build automation, I used it to implement the recipe above and created an apex2git shell script and Windows batch file that simply invoke the Ant build script, passing along the command line arguments for application id, repo directory name (assumed relative to my home directory), and database connection string:

$ apex2git 1234 my-app appuser/password@host:port/servicename

Since it is common for a larger APEX application to be assembled out of several smaller applications, I implemented the Ant build script to accept the name of the Git repo directory or a subdirectory. So, for example, if you were implementing an application called MediPay that was broken down into a backoffice application and a mobile application, you might export the two distinct applications into distinct subdirectories of a medipay Git repo directory like this:

$ apex2git 1001 medipay/backoffice $MEDIPAY_CONNECTION
$ apex2git 1002 medipay/mobile     $MEDIPAY_CONNECTION

In this case involving a subdirectory, the Ant build script first converts the directory separator characters \ or / to a hyphen to end up exporting the more meaningful application export SQL scripts into:

~/medipay/backoffice/medipay-backoffice.sql
~/medipay/mobile/medipay-mobile.sql

Using SQLcl to Import an APEX App

Importing an APEX application from the command line involves calling the apex_application_install package to set the target workspace name, the target application id, and preference related to the installation of supporting objects, followed by running the application’s single-file SQL script.

So, for example, to import application 1001 from the medipay/backoffice Git repo directory into workspace medipay , we can using SQLcl’s sql or sqlplus to run the following PL/SQL block followed by the application’s SQL script:

begin
   apex_application_install.set_workspace('medipay');
   apex_application_install.set_application_id(1001);
   apex_application_install.set_auto_install_sup_obj(
                               p_auto_install_sup_obj => true );
end;
/
@/home/smuench/medipay/backoffice/medipay-backoffice.sql

Automating Git to APEX

The recipe I identified to import an APEX app with id 1234 from Git work area my-app in my home directory into workspace target-wksp includes these steps:

  1. Create a temporary staging directory /tmp/f1234_stage
  2. Copy ~/my-app/my-app.sql to directory /tmp/f1234_stage/f1234.sql
  3. Use SQLcl to set install options, then run /tmp/f1234_stage/f1234.sql

Again I employed an Ant build script to carry out these steps. Then I created a git2apex shell script and Windows batch script that invokes the Ant build script, passing along the command line arguments for application id, repo directory name (assumed relative to my home directory), target workspace name, and database connection string:

$ git2apex 1234 my-app target-wksp appuser/password@host:port/servicename

As before, to accommodate solutions comprising multiple smaller APEX apps, I implemented the Ant build script to accept the name of the Git repo directory or a subdirectory. So, for the MediPay application comprised of a backoffice application and a mobile application, you might import the two distinct applications from distinct subdirectories of a medipay Git repo directory into a workspace named medipay like this:

$ git2apex 1001 medipay/backoffice medipay $MEDIPAY_CONNECTION
$ git2apex 1002 medipay/mobile     medipay $MEDIPAY_CONNECTION

Ant: Using Templatized SQL Scripts

The apex2git command runs a apex2git.xml Ant build script that copies an apex2git.sql template SQL script to the temporary staging directory, substituting @APPID@ and @DIR@ tokens in the process using the values of the application id and repository directory parameters passed in:

REM apex2git.sql - v1.0
apex export -applicationid @APPID@ -dir @DIR@ -skipExportDate -expOriginalIds -expSupportingObjects Y -expType APPLICATION_SOURCE
apex export -applicationid @APPID@ -dir @DIR@ -skipExportDate -expOriginalIds -expSupportingObjects Y -expType APPLICATION_SOURCE,READABLE_YAML -split

Then it runs the resulting SQL script using SQLcl’s sql command against the provided database connection to produce the single SQL file, multiple component SQL file, and YAML exports in the temporary staging directory. It then proceeds to copy the exported files to the Git work area and add them all to the Git staging area.

Similarly, the git2apex command runs a git2apex.xml Ant script that substitutes command-line parameters into the following git2apex.sql template SQL script and then uses sqlto run it against the provided database connection.

REM git2apex.sql - v1.0
REM First setup the import options
begin
   apex_application_install.set_workspace('@WORKSPACE@');
   apex_application_install.set_application_id(@APPID@);
   apex_application_install.set_auto_install_sup_obj( p_auto_install_sup_obj => true );
end;
/
REM Then install the application by running its SQL script
@@DIR@/f@APPID@.sql

My Streamlined Workflow

Using the apex2git and git2apex scripts, I’ve streamlined my development workflows. When working alone on an APEX app, I tend to eschew branches and just push my changes directly to the repository, accompanied by a descriptive commit message that explains what new feature I’ve implemented:

  • pull the latest app version from remote Git repo to my local work area
  • import the app to my private dev workspace using git2apex
  • implement the new feature using the APEX App Builder
  • export the enhanced app to Git using apex2git
  • commit the staged changes to local Git repo mentioning feature I finished
  • push the changes up to the remote Git repo

In contrast, when working with others, I use feature branches and Git pull requests to facilitate code review by my peers:

  • pull the latest app version from remote Git repo to my local work area
  • create a Git branch named after the feature ticket I’m working on
  • import the app to my private dev workspace using git2apex
  • implement the new feature using the APEX App Builder
  • export the enhanced app to Git using apex2git
  • commit the staged changes to the local Git repo
  • push the changes up to the remote team Git repo
  • initiate a merge request for my branch’s changes for peer review

Checking Out the Scripts

In case you want to explore the scripts to see how they work, you can download them from here. Unzip the archive in your home directory, and make sure that the ./utils subdirectory it creates is in your system path. To keep things simple, the scripts currently rely on having all the apex2git* and git2apex* files in this utils subdirectory of the currently logged-in user’s home directory. Of course, you’ll also need to have SQLcl 22.1 and Apache Ant installed and have both sql and ant commands be available in your system path.

The simple usage documentation for the scripts is:

git2apex appId repoDirectory workspaceName connectionString

apex2git appId repoDirectory connectionString

The repository directory is interpreted relative to your home directory. You can see the commands in action in the short example video below.

Example of git2apex & apex2git in Action

In this short video, I show using git2apex and apex2git without using a feature branch for simplicity. This is the process I use when working alone on an APEX app that I source-control in a private Git repository up on GitHub. For example, the donor-tracking system I built for a local Italian non-profit and several personal productivity apps follow this approach. When working on sample apps as part of my day job at Oracle, we use an internal Git repository instead of GitHub for that. The git2apex and apex2git scripts work the same regardless of whether the git repo is only on your machine or whether it’s remote.

Next Stop: CI/CD Pipelines

APEX’s scriptable devops features let you create custom solutions to automate frequent tasks in your app development lifecycle. We’ve seen a simple example of their use in this article with apex2git and git2apex commands. My colleague Carsten has also documented a technique for exporting and importing APEX applications using a REST service in scenarios where that approach proves useful.

These options make it straightforward to incorporate APEX application export and import into more involved “pipelines” of continuous integration and continuous delivery. These automations are often triggered automatically by the merging of new code to the main branch of your Git repository, and can include steps that setup test data, execute unit tests and integration tests, and finally install a validated build of your application into a target environment if all tests pass. In future releases of Oracle APEX, we aim to make doing some of these steps even simpler from directly inside the builder. However, it’s good to know that in the meanwhile any kind of build automation is already possible to achieve with the ingredients APEX provides today.

SmartFiltering Multiple Regions

A fellow Oracle employee wielding the power of APEX to solve an internal business problem for her team asked an interesting question on our internal discussion forum this week. Her app shows resources partitioned into three categories in side-by-side cards regions. She wanted to use a single SmartFilter region to filter the results showing up in these three regions, each based on the same underlying table with a slightly different WHERE clause.

Wherever possible, my brain tends to map questions onto the familiar EMP and DEPT tables to simmer a question down to its essence. So I imagined side-by-side cards regions with employees in three different departments as shown below.

Three EMP-based cards regions partitioned by department

Setting Up the Page

I started by setting up the three cards regions based on the EMP table, and including an appropriate DEPTNO=10 where clause in the “Accounting” region, DEPTNO=20 predicate in the “Research” region, and DEPTNO=30 in the “Sales” region. Then I added a SmartFilter region and positioned it in the Before Navigation Bar slot with the w100p class to make to stretch to 100% width and fill up the space left in the center of the navigation bar.

A Twist on The Gift That Keeps on Giving

By default, a SmartFilter region filters the results of only one other region on the page. However, my colleague Carsten’s article Add a Chart to your Faceted Search Page is the gift that keeps on giving, because it inspired me with the idea I used to achieve the result our fellow APEX user desired. Carsten explains therein how to use the open_query_context() function in the apex_region package in a pipelined table function that returns the results from another region’s data source, automatically taking into account the search filters currently applied by the end user in that region. The implementation uses functions in the apex_exec package to retrieve the data.

My twist on his idea was to create a pipelined table function get_region_results_pk_values()to return only the primary key values of a given region’s filtered results. Assuming a numeric primary key column, the get_region_results_pk_values() function can accept three parameters:

  1. Page id
  2. Static ID of a region on that page
  3. Name of the primary key column whose value we should return

The function’s pipelined result rows will each contain a NUMBER column named ID containing the primary key values identifying the rows that the filtered region is returning. For our employee-based example here, it will return ID values representing the EMPNO values of the filtered region’s query results. With this function in place, we can update the WHERE clause of the three employees cards regions to reference get_region_results_pk_values() in an additional predicate like this:

  • “Accounting”
    • deptno=10 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))
  • “Research”
    • deptno=20 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))
  • “Sales”
    • deptno=30 and empno in (select id from get_region_results_pk_values(:APP_PAGE_ID,'RESULTS','EMPNO'))

Configuring the Filtered Region

To play the role of the SmartFilter region’s Filtered Region, we need an additional region based on the EMP table with no WHERE clause of its own. This is because using any of the Accounting, Research, or Sales cards regions would only filter the employee rows of one particular department and consequently its list of filtered EMPNO primary key values would not correspond to any employees in the other cards regions who, by design, belong to a different department.

So, I added an EmployeesResults classic report region based on the EMP table and configured this classic report region to be the SmartFilter region’s Filtered Region. I set the static ID of this region to RESULTS as referenced in the three WHERE clause function calls above.

A Chain Reaction of Filtering

When the end-user changes the filters on a SmartFilter, it automatically refreshes its filtered region. So in our example, the EmployeesResults classic report region will be refreshed. When this happens, we need the other three cards regions on the page to also get refreshed. Luckily, this is simple to achieve with a dynamic action event handler on the EmployeesResults region’s AfterRefresh event. I included three dynamic action steps in the TRUE branch of the event handler. Each one uses the built-in Refresh action to refresh one of the cards regions.

AfterRefresh dynamic action on filtered region refreshes employees cards regions

At this point, I had a working page with a SmartFilter at the top, whose search filters would restrict the rows in the filtered EmployeesResults region at the bottom, and whose filtered list of primary keys were correctly filtering the three separate Accounting, Research, and Sales cards regions as desired. However, seeing the classic report at the bottom was ruining the magic effect, so I sought out a way to hide it.

Classic report filtered region displays, ruining the magic

Hiding the Filtered Region

My first experiment was to set the Server-side Condition on the filtered EmployeesResults region to Never. This succeeded in hiding the classic report, but it also broke the chained filtering functionality being implemented by the dynamic action. I guess a region that’s not rendered on the server can’t be used to trigger events on the client, which I could understand.

For my second attempt, I went looking for properties of the classic report region that I could adjust to limit the data that it was retrieving. After all, I primarily cared about the filters applied to its data source, not seeing the results themselves in the classic report. These applied filters are important to correctly produce the filtered list of primary keys to filter the Accounting, Research, and Sales cards regions appropriately.

With the classic report EmployeesResults region selected, I studied the Attributes tab of the property palette and noticed a Number of Rows property which I tried setting to zero (0). I figured the classic report functionality related to pagination might be producing some content on the page, so I also set the Pagination > Type to the value No Pagination (Show All Rows). Since I knew that the SmartFilter region uses partial page refresh to update the filtered region, I kept the Partial Page Refresh switch in the ON position.

Setting the classic report to retrieve no rows & skip pagination, keeping partial page refresh

These changes got me very close to the desired result, but as shown below the column headings were still showing.

Almost There! The Filter chaining works, but the filtered region’s column headers still show

I managed to resolve this final issue by hiding the classic report’s column headings using the CSS property display:none. I accomplished this by adding a CSS class called hidden to the page-level CSS > Inline settings:

hidden {
   display: none;
}

After doing this, I configured the EmployeesResults classic report region’s Column CSS Classes to be the name of my hidden CSS class. After doing that, I had a working SmartFilter that was filtering the three separate cards regions, without showing the classic report region involved in triggering the chain reaction of filtering.

Filtering Code In the Demo

In practice, the APEX 21.2 demo app that you can download using the link below is based on an EMP-like table called EBA_DEMO_WORKLIST_EMP and the filtering code for the function is in a package named EBA_DEMO_WORKLIST whose specification looks like this. The package body looks very similar to Carsten’s blog article, but it’s simplified to only return the single primary key column of results in each row.

create or replace package eba_demo_worklist as 
    type eba_demo_worklist_pk_t_row is record (
        id number
    );

    type eba_demo_worklist_pk_t_tab is table 
        of eba_demo_worklist_pk_t_row;

    function get_region_results_pk_values( 
        p_page_id          in number,
        p_region_static_id in varchar2,
        p_pk_column_name in varchar2 )  
    return eba_demo_worklist_pk_t_tab pipelined;
end;
 

You can download the demo app from here, and install it into an APEX 21.2 (or later).

Upgrading Dynamic Sort to 22.1 Order By Page Item

Overview

Your end users appreciate seeing your application data in the order that best suits the task at hand. While some region types like Interactive Report offer end-user sorting as a native capability, other popular types like Cards lacked this feature. While it was definitely possible before APEX 22.1 to create Cards regions with user-controlled sorting, the new Order By Page Item feature in APEX 22.1 now makes it incredibly easy to implement. This article explores upgrading an existing dynamic sorting cards region to the new 22.1 Order By Page Item to improve the clarity and maintainability of your application.

Understanding the Existing Implementation

We’ll look at upgrading an existing APEX 21.2 example app with a single Cards page showing a list of friends (Download). The region’s select list page item allows end users to sort the friends list by name, age, or time you’ve known the person.

APEX 21.2 Friends app cards page with dynamic sorting

The page contains a P1_SORT_ORDER select list item with a static list of values showing the end-user the different available sorting orders, with a corresponding code value. The default value for this page item is set to the value NAME so the default sorting order will be alphabetical by friend’s name. The page item uses the Maintain Session State setting of Per User (Disk), so the user’s preferred sort order is remembered automatically across sessions:

Static list of values behind the P1_SORT_ORDER select list

The cards region’s data source is a Function Body returning a SQL Query. It returns a SQL query with an ORDER BY clause determined using a CASE statement that depends on the value of the :P1_SORT_ORDER page item as shown below. Note that the NVL() function is used here to ensure that the APEX builder can successfully parse the function body’s resulting SQL query at design time (where the value of the :P1_SORT_ORDER bind variable will be null).

return  q'[  select id,
                   name,
                   to_char(birthday,'Mon fmdd') born_on,
                   trunc((sysdate - birthday)/365) age,
                   static_image_file_name,
                   apex_util.get_since(met_on) met_them
              from friends
              order by
        ]'||
        case NVL(:P1_SORT_ORDER,'NAME')
            when 'NAME'               then 'name     asc'
            when 'AGE_OLDEST_FIRST'   then 'birthday asc'
            when 'AGE_YOUNGEST_FIRST' then 'birthday desc'
            when 'KNOWN_LONGEST'      then 'met_on   asc'
            when 'KNOWN_SHORTEST'     then 'met_on   desc'
        end;

Notice the order by clauses use a combination of ascending and descending sorting clauses. Finally, the P1_SORT_ORDER page item is configured with a Page Action on Selection property set to Submit Page, so the page re-renders to reflect the new sorting order. This also has the side-effect of sending the new value of the P1_SORT_ORDER item to the server so it can be saved into per-user session state by the APEX engine.

Setting the Order By Page Item

After importing the 21.2 Friends starting app into an APEX 22.1 workspace, editing the page, and selecting the Friends cards region in the Page Designer, we see it currently has No Order By Item.

Clicking on the No Order By Item button opens the Order By Item dialog where we can choose the existing P1_SORT_ORDER item in the page as the Order By Item for this region. After doing this, the dialog helpfully updates automatically to reflect the display and return values of the existing list, reminding us of which sorting option key values we need to provide ORDER BY clauses for:

Order By Item dialog after setting Order By Item name to existing P1_SORT_ORDER select list

We proceed to fill in the Clause field for each entry, using the ORDER BY clause fragments currently returned by the CASE statement in the Function Body Returning SQL Query code. After completing this task, the dialog will look like what you see below:

Order By Item dialog with ORDER BY clauses filled in based on existing CASE statement fragments

After clicking OK, the Order By Item reflects the name of the order by item and indicates how many Order By options are available in the related select list:

Order By Item property reflecting name of order by item and number of order by clauses

Simplifying the Region Query

Now that we’ve “refactored” the dynamic query clause selection to be done declaratively using the new Order By Item, we can simplify the Friends region source to be an easier-to-read-and-maintain SQL query instead of the function body returning SQL query. We start by copying the text of the SELECT statement to the clipboard so we’ll be able to easily paste it into the SQL Query property after changing the region source type. Next, we change the region source type to SQL Query. Then, we paste the query saved in the clipboard into the SQL Query property to result in the following situation. Notice that we left out the order by text from the query that was there before because the APEX engine will add that for us at runtime.

Upgraded cards region with simpler-to-understand SQL query

Trying Out the First Cut

If we run the application it appears to work fine, showing us the cards with our friends’ smiling faces initially sorted by the default NAME column value. However, if we choose one of the other sort orders, we get a runtime error like this:

ORA-00904: "BIRTHDAY": invalid identifier ORA-06512: at "APEX_220100.WWV_FLOW_PAGE", line 2062 ORA-06512: at "APEX_220100.WWV_FLOW_DISP_PAGE_PLUGS", line 1576 ORA-06512: at "APEX_220100.WWV_FLOW_CARD_REGION", line 1099 ORA-06512: at

This error occurs because the ORDER BY clause that the APEX engine adds at runtime based on the value of the region’s Order By Item is applied in an outer query that “wraps” the region’s original SQL query, using it as an inline view. In fact, after choosing to sort by age, the error dialog or APEX debug log shows the query in error. Note that I’ve added some additional comments and removed some query hints for clarity.

select *
from (
  select a.*,row_number() over (order by null) apx$rownum 
  from (
    select *
    from (
      select *
      from (
       /* ---vvv--- REGION SQL QUERY ---vvv--- */

       select id,
              name,
              to_char(birthday,'Mon fmdd') born_on,
              trunc((sysdate - birthday)/365) age,
              static_image_file_name,
              apex_util.get_since(met_on) met_them
       from friends

       /* ---^^^--- REGION SQL QUERY ---^^^--- */
      ) d
    ) i 
    /* ---vvv--- ORDER BY ITEM CLAUSE ---vvv--- */

    order by birthday asc

    /* ---^^^--- ORDER BY ITEM CLAUSE ---^^^--- */
  ) a
)
where apx$rownum <= :p$_max_rows

Adding Columns to the SELECT List of the Region’s Query

To avoid the error, we need to study the ORDER BY clauses in play in the Order By Item and ensure that any column names referenced by those order by clauses are included in the SELECT list of the region. This guarantees that they will “shine through” to the outer, wrapping SQL statement where the dynamic ORDER BY is applied.

In our Friends example app, this means adding the BIRTHDAY and MET_ON date columns into the region’s SQL Query select list so that the query now becomes:

select id,
       name,
       to_char(birthday,'Mon fmdd') born_on,
       trunc((sysdate - birthday)/365) age,
       static_image_file_name,
       apex_util.get_since(met_on) met_them,
       birthday,
       met_on
  from friends

After doing this adjustment to the SELECT list, rerunning the application shows that the query works perfectly with all of the configured dynamic sorting options. However, the page appears to be getting refreshed twice each time the end-user changes the sort order. We’ll fix that next.

Avoiding Double Page Refresh

While the query with dynamic order by now executes without an error, the presence of two spinning progress indicators (and the time required to refresh the page) gives the impression that the page is being refreshed twice.

The page refresh shows two progress indicators and the page/region is refreshed twice

This effect is the result of the following two factors:

  1. The original page’s P1_SORT_ORDER page item is configured with a Page Action on Selection property set to Submit Page, so the page re-renders to reflect the new sorting order, and
  2. The APEX 22.1 Order By Page Item feature automatically refreshes the region when the related order by page item’s value is changed.

The solution to the double-refresh issue is restoring the P1_SORT_ORDER page item’s Page Action on Selection property value to the None setting. This avoids its submitting the page since that action is no longer necessary.

Removing the previously configured page submit when the P1_SORT_ORDER item changes value

With this change in place, the dynamic sorting is now using the maximally-declarative approach, we’ve improved the readability and maintainability of our region’s SQL query, and the page refreshes a single time and is looking great.

Updating the Theme to Use New Sort Order Position

APEX 22.1’s updated Universal Theme adds a new Sort Order template layout position designed to contain the region’s components that the end-user uses to configure the region’s sort order. To have our upgraded Friends page use this new layout position, we need to refresh our application’s theme before the Page Designer will show us this new position name.

So, I navigated to Shared Components and clicked the Refresh Theme button at the top of the page.

Refreshing the theme to 22.1 to use the new Sort Order layout position

After visiting the User Interface application settings to restore the application’s Redwood Light theme style, returning to edit the page in the Page Designer allows us to now set the P1_SORT_ORDER page item to have the new Sort Order layout position:

Choosing the new Sort Order layout position for the Order By Item P1_SORT_ORDER

Conclusion

With these changes in place, we have upgraded our existing dynamic sorting implementation for a 21.2 cards region to leverage the latest 22.1 declarative region sorting feature. The result is an application that is easier for colleague developers to understand and maintain. It also ensures the existing pages offering dynamic sorting are implemented in the same way that new pages created in 22.1 will be when dynamic sorting is added automatically by the new Create Page wizard. If you care to study the before and after applications, you can download the upgraded 22.1 version of the Friends example app from here.

Enabling Persistent Auth in an Existing APEX App

Overview

A new APEX 22.1 feature I’ve been eagerly waiting to use in my production APEX apps is persistent authentication. It will allow my users to avoid having to login again every time they open my app in their phone or browser. After enabling the feature at the instance level, I needed to slightly adjust my existing application’s login page to take advantage of this new capability. This article explains how I figured out the minimal changes needed to update my existing APEX application to use this new sought-after “Remember me” functionality.

Enabling Persistent Auth at Instance Level

Persistent authentication is controlled by an instance-level security option that is disabled by default, so I needed to enable it for my APEX instance. After logging in to APEX’s administration services, I chose Manage Instance > Security and set Allow Persistent Auth to Yes. The default 30-day lifetime met my application’s needs, but to change it you can adjust the value of the Persistent Authentication Lifetime Days property. This is the number of days that the persistent authentication will last before the user needs to enter their password again.

Using YAML Export with SQLcl to Diff Existing and New Login Pages

An existing application’s login page needs a few minor changes to offer the “Remember me” feature. In contrast, when creating a new application in APEX 22.1, ticking the Install Progressive Web App checkbox in the Features section of the Create Application wizard will generate a login page that supports it. I took advantage of this fact to create a new application called Temp App to compare its Login page 9999 with my existing application’s Login page 9999 to determine the minimum adjustments needed.

The easiest way to compare APEX artifacts in 22.1 is using the new readable YAML export format. SQLcl 22.1 supports the new READABLE_YAML value for the -expType option of its apex export command, so I ran the following commands to export my existing (103) and new Temp App (102) applications in readable YAML format so I could compare the two page 9999 Login pages’ metadata:

$ sql workspace_schema_username/password@host:port/service
sql> apex export -applicationId 103 -dir f103 -expType READABLE_YAML
sql> apex export -applicationId 102 -dir f102 -expType READABLE_YAML

Using Visual Code to compare the two application’s p09999.yaml files, I noticed the following three interesting differences:

  1. The newly-generated Login page has a new checkbox page item named P9999_PERSISTENT_AUTH with a server-side condition to show the checkbox only when the persistent_auth_enabled function in the apex_authentication package returns true.
YAML diff showing new P9999_PERSISTENT_AUTH checkbox page item
  • The new page’s Login process passes the value of this new P9999_PERSISTENT_AUTH checkbox to a new p_set_persistent_auth parameter of the login() procedure in the apex_authentication package:
YAML diff showing additional argument on login() procedure call in the Login process
  • The P9999_REMEMBER checkbox has a new server-side condition to only render if the persistent_auth_enabled function returns false.
YAML diff showing additional server-side condition in P9999_REMEMBER checkbox

The value of the persistent_auth_enabled function corresponds to the instance level security setting I enabled above, so these three differences result in having the login dialog behave the same as before if the instance-level persistent authentication setting is disabled, and to show the new “Remember me” checkbox if the persistent authentication instance-level setting is enabled.

Adjusting the Existing Login Page

After identifying the three important changes to make above, I edited my existing application’s Login page to do the following:

  • Added a new checkbox page item with:
    • Name = P9999_PERSISTENT_AUTH
    • Label = Remember me
    • Server-side Condition Type = Expression
    • Server-side Condition = apex_authentication.persistent_auth_enabled
    • Maintain Session State = Per Session (Disk)
  • Added the additional argument to the existing Login process, referencing the value of this new page item:

apex_authentication.login(
p_username => :P9999_USERNAME,
p_password => :P9999_PASSWORD,
p_set_persistent_auth => nvl(:P9999_PERSISTENT_AUTH,'N')='Y');

  • Adjusted the server-side condition of the existing P9999_REMEMBER checkbox to be:
    • apex_authentication.persistent_cookies_enabled and not apex_authentication.persistent_auth_enabled

With these changes in place, the persistent authentication feature started working as expected in my existing application. However, while studying the differences between the login pages, I noticed one additional interesting change:

YAML diff showing change to how login page icon is specified

The method used to express the login page’s icon changed from using an icon property to instead use a file-url. This is due to another new feature in APEX 22.1 related to simplifying how an application’s icon can be configured at any time using a built-in or custom icon image.

Reflecting New App Icon in the Login Page

My application was still using the original default icon that got set by the APEX Builder when I first created the application. At the time, I took the default icon because I figured I’d be able to easily set the icon later. However, before 22.1 changing an application’s icon after creation was not obvious. Now in 22.1, doing so is very simple. To flex this additional newfound icon power, I visited my application’s Shared Components > User Interface Attributes settings page and clicked on the Change Icon button to set a new custom Vincent van Gogh icon.

Then, following the observation I noticed above, I copied the Reference path #APP_FILES#icons/app-icon-512.png of the “Large icon” version of the application icon image to the clipboard so I could paste it into the file-url property of the Login page’s static region, and then clicked on Apply Changes to make my new icon change permanent.

Returning to my Login page, I selected the static content region and removed the existing “app-icon” value in the Icon property, and set the value of the File URL property to #APP_FILES#icons/app-icon-512.png

With these changes, my application’s login page was now not only more beautiful but also more functional, and my end users don’t need to login every time anymore.

21.2: Fine-Tuning Initial Render of Tabs & Cards

If you use a Static Content region with the Tabs Container template to present subregions on separate tabs, in APEX 21.2 you might notice during initial page render that you momentarily see the contents of all the tab “pages” before they disappear behind the initially selected tab. Depending on the contents of the tab pages, the result may be more or less noticeable. However, since one of my applications presents multiple tabs of colorful Badge Lists, the “Easter eggs” below compelled me to search for a solution…

Three Badge List regions situated in a Tabs Container all render momentarily during page load

Luckily, my colleagues Tim and John helped me with a combination of simple CSS suggestions that solved the problem. After adding two rules to an application-level CSS file, the undesirable effect vanished.

While the two CSS rules that avoid the flashing tabs could be applied to each page where needed, I prefer a “one-and-done” solution. An application-level CSS file is best for rules that apply to all pages, so I added the two rules in there. Under Shared Components > Static Application Files I created an app.css file with the contents shown below:

App-level app.css file with two rules to suppress flashing of all tab containers’ contents

Next, I clicked on the copy icon next to the #APP_FILES#app#MIN#.css name under Reference to copy that file path to the clipboard so I could paste it into the list of CSS files that my application will load with every page. That configuration is also under the Shared Components settings, on the User Interface Attributes page, in the Cascading Style Sheets section. I pasted the file reference on its own line in the text area as shown below:

List of App-level CSS file URLs that APEX will load with every page

With this app-level CSS file in place, my landing page was looking sharp.

Landing page with Tabs Container of three Badge Lists and a Chart region

Energized that my app’s tab pages were looking great now, I turned my focus next to my application’s Cards regions. I noticed that APEX shows a row of placeholder cards when the page initially draws to help the end-user understand an asynchronous data request is in progress to retrieve the actual card information.

APEX Card regions initially show placeholder cards, then actual cards appear once data loads

This employs a familiar technique that other modern web applications like LinkedIn, Facebook, and YouTube use.

YouTube shows placeholder cards initially, then actual cards appear once data loads

In situations where the card data takes time to retrieve, placeholders are a useful affordance for the end-user. In my particular app, it seemed to add less value since my cards render almost instantly.

I used Chrome Dev tools to explore the structure of the page to see if a similar CSS technique might be able to hide the placeholder cards. After none of my experiments panned out, again my colleague Tim nudged me back onto the happy path with a suggestion. I edited my app.css file to add the one additional rule you see below that delivered the results I was hoping for.

/* Contents of app.css */
/*
 * Suppress tab container contents from flashing
 * during initial page render
 */
.a-Tabs-panel {
	display: none;
}

.no-anim .t-TabsRegion-items > div {
    display: none;
}

/*
 * Suppress cards region from rendering initial row
 * of placeholder cards during initial page render
 */
.no-anim .a-CardView-item > div {
    display: none;
}

These CSS rules use a combination of class selectors ( .someClassName) and the child element selector ( > someElement). Effectively they say:

  • Hide elements with the a-Tabs-panel class
  • Hide div child of parent with classt-TabsRegion-items inside element with class no-anim
  • Hide div child of parent with class a-CardView-item inside element with class no-anim

Keep in mind that these rules only affect the initial display state of the affected elements since APEX automatically makes the relevant elements visible again once they are ready for the end-user to see.

With these three rules in place, the user sees only the selected tab’s Badge List as expected and my quick-loading cards without placeholders. In case you want to try the example application, download it from here. Try commenting out the CSS rules in app.css in the example to see the difference with and without so you can decide what’s best for your own application use cases.

Card region showing Paul Theroux books

Nota Bene

I’ve tested these techniques in APEX 21.2 but need to remind you that the particular structure of how APEX universal theme generates HTML elements in this release is not guaranteed to remain the same across future APEX releases.

Trick to Reference Field Value in Dynamic Action on Grid Column

The Oracle APEX interactive grid region is a richly functional component with a JavaScript API for all aspects of its multi-row user interface and client-side data model, but this article concerns something simpler. I was recently building an application with an interactive grid and wanted the change in one date field in the row to update the value of another date field in the same row. I knew it would be simple to achieve, but there was a little trick I needed to learn from a colleague to crack the nut.

Consider the interactive grid below that displays an Order Date, Ship Date, and Bill Date for some line items. Let’s investigate the two easiest ways I could find to set the Ship Date and the Bill Date to the value of the Order Date whenever the end-user changes the Order Date.

Interactive grid with three date fields per row

To react to the change in Order Date, I right-clicked on the ORDER_DATE grid column in the Page Designer and created a dynamic action event handler named “When Value Changed” keeping the default “Change” event.

Creating a dynamic action on the ORDER_DATE interactive grid column

Since I wanted to set the value of the Ship Date field to the value of the Order Date in the current row, the “Set Value” type of action step felt like a good choice. I wanted it to affect the Ship Date field, so I chose the SHIP_DATE column name in the Affected Elements section. I needed the ORDER_DATE column in the current row to provide the value to assign. My first instinct was to look for a Set Type of “Column”, but that wasn’t listed among the types available. The next most likely candidate for Set Type was “JavaScript Expression”, so I selected that and used the expression: $v('ORDER_DATE') Alas, after trying this expression, I wasn’t seeing the desired result. Luckily, my colleague Interactive Grid master John Snyders taught me the trick I needed to get things working.

In the JavaScript world of the grid, the ORDER_DATE is the name of the column. Here what I learned I needed to reference instead was the value of the “column item”, the name of the particular item in the current row of the ORDER_DATE column. John explained that by setting the Static ID of the ORDER_DATE column to some name other than ORDER_DATE, I could assign a meaningful name to the column item for this column. Just to make it really clear what I was doing, I set the Static ID for the ORDER_DATE column to be COLUMN_ITEM_FOR_ORDER_DATE. Then I updated my “When Value Changed” dynamic action event handler’s “Set Value” action step to reference $v('COLUMN_ITEM_FOR_ORDER_DATE') as shown below for the JavaScript expression providing the value to set.

After setting Static ID of the ORDER_DATE column, referenced it as column item in the “Set Value”

After doing that, the Ship Date in the current row suddenly started updating interactively as I had hoped whenever the Order Date got changed. This technique illustrates the way to implement the requirement on the client-side without incurring a round-trip to the server to perform the assignment.

To assign the Bill Date, I wanted to show off the other technique I experimented with. Building on the same dynamic action event handler, I added a second action step with action type “Execute Server-side Code”. For the PL/SQL code, as shown in the figure below, I simply used the page item names as bind variables and APEX automatically interprets them as the values in the current row:

:BILL_DATE := ORDER_DATE;

As the name of the action type implies, this approach executes the PL/SQL on the server-side, so we need to remember to configure the Items to Submit to include the ORDER_DATE and the Items to Return to include the BILL_DATE as shown below.

Setting interactive grid column Bill Date to the value of Order Date using Server-side PL/SQL

In this second server-side approach, I was able to directly reference the name of the ORDER_DATE and BILL_DATE columns without involving the Static ID name. That additional trick was only required for use in the JavaScript expression shown earlier.

Of course, since the “Set Value” using the JavaScript Expression is performed completely on the client-side in end-user’s browser, it is faster than the Execute Server-side code technique that requires an additional HTTP round-trip to the server to perform the assignment. It’s good to understand both approaches and pick the one that meets your needs best in your own applications.

You can download the sample application if you want to check out the technique in your own APEX workspace.