Pushing the PWA Envelope

If you have a smartphone, you’re already familiar with push notifications. They’re the timely alerts with some useful info from your favorite apps. In 23.1, your APEX solutions can send them, too, using a page process or send_push_notification() API. You simply set the message details and APEX securely notifies the user on all their subscribed devices, if any.

In this article, see a sample app in action, how to try it out yourself, and where to download the slides from my recent APEX Alpe Adria conference talk with more in-depth info about push notifications in APEX.

Overview of the Sample App

The sample app uses APEX’s application access control with roles for Customer and Staff. App pages use an appropriate authorization scheme so customers can request reimbursements while staff members approve them. The app declaratively enables Progressive Web App (PWA) installation and push notifications, and the overview video below shows you how the app works on iPhone and Mac desktop PWAs for a customer user BO and a backoffice user PAT.

Regenerating the Signing Key Credentials

After downloading and importing the sample app, the first required setup step is regenerating the public/private key pair credentials. It’a is a one-time step that’s necessary the first time an APEX app using push notifications is imported into a new workspace. This is a new kind of credential used to cryptographically sign the push notifications your app sends.

In practice, it’s a one-click operation. Under Shared Components > Progressive Web App > Push Notifications, click the (Regenerate Credentials) button, and then in the confirmation dialog that appears click (Regenerate Credentials) again to confirm. Ok, you got me. It’s a two-click operation! You can regenerate the credentials any time in the future as well, but be aware that doing so invalidates and removes any existing user push notification subscriptions. Therefore, should you decide later to regenerate the credentials, your users will need to opt-in again to receive your app’s push notifications.

Creating the Two Sample Users

The sample app depends on a backoffice staff user named PAT who is configured as the approver on the Reimbursement Approval task definition and another user BO who is a customer. As shown in the demo video above, customer BO uses the app to request reimbursement of an expense, then backoffice user PAT approves or rejects the request, which results in sending the requesting user a push notification to alert them of the outcome.

If you have recently tried the Sample Approvals app from the gallery, you might already have workspace users PAT and BO, but if not then you’ll need to create them. Login to your APEX workspace as a workspace admin user and click the Administration (i.e. “person with wrench”) icon in the toolbar and choose the Manage Users and Groups option. Add the missing account(s) on this page using the Create User button.

Assigning Sample Users an Application Role

Once users PAT and BO exist, next you need to assign them an appropriate application role so that the staff member user PAT sees the approval pages and the customer BO sees the reimbursement request pages. To do this, in the context of the sample application in the App Builder, click Shared Components > Application Access Control. Use the (Add User Role Assignment) button twice on this page to add two user role assignments:

  • PAT -> Staff
  • BO -> Customer

HTTPS & Trusted Certificate Requirement

Keep in mind if you’re trying the sample on your own APEX instance that both on-device PWA installation as well as subscribing to push notifications depend on a secure, trusted connection. This means your desktop or mobile browser needs to access the APEX app over HTTPS and the client device must trust the server’s security certificate. If your APEX is running plain HTTP or it’s using a self-signed certificate that you haven’t configured your client device to trust, exploring the sample won’t work as expected. In that case, I recommend trying it on apex.oracle.com or your Oracle Cloud always free tier APEX instance instead.

Installing the Sample App as a PWA

The public APEX PWA Reference App’s Push Notifications page documents the compatibility matrix of supported operating systems and devices. If you are an iPhone user, notice that subscribing to push notifications on iOS and iPadOS require version 16.4 or later, as well as your installing the app as a PWA first. Other supported combinations allow push notifications either from the browser or when installed as a PWA. In the video above, I used Chrome on MacOS Ventura to install the PWA for user BO, Microsoft Edge to install the PWA for user PAT, and an iPhone 11 Plus running iOS 16.4 to install the mobile PWA for BO. You can use any of the supported combinations highlighted in the compatibility matrix.

I recorded the demo video on an APEX instance with instance-level settings Allow Persistent Auth set to Yes and Rejoin Sessions set to Enabled for All Sessions, so users can stay logged in for a month by default and where tapping on the push notification does not require the user to login again to see the related detail information in the APEX PWA application.

Opting-In to Receive Push Notifications

As shown in the demonstration video above, each user of your app needs to opt-in to receive push notifications from your app. And they need to do this on each device where they want to receive the notifications. When creating a new app with the Push Notification feature enabled in the Create App wizard, APEX generates a user settings modal drawer page containing the link to the Push Notification Settings page. For an existing app, there is a button on the Push Notifications page of the PWA app settings to generate the user settings page with a single click. At runtime, if the settings page shows a “Not Supported” badge, check to make sure you’re using HTTPS and a trusted certificate. If tapping or clicking on the Enable push notifications checkbox produces an error, that’s a signal you probably forgot to regenerate the push notification key pair credentials after importing the app the first time.

Using an App Page as Push Notification Target URL

When sending a push notification, you can configure a target URL that the device uses when the user taps or clicks on the notification. It must be an absolute URL, so for example the Reimbursement Approval task definition in the sample app contains an action that executes in response to the Complete event. Its action PL/SQL code sends the push notification about the task approval or rejection using the following code:

apex_pwa.send_push_notification(           
    p_user_name  => :CREATED_BY,
    p_title      => 'Reimbursement '||initcap(:APEX$TASK_OUTCOME),
    p_body       => 'Your reimbursement of ' || :AMOUNT || 
                    ' from ' || :RECEIPT_FROM || 
                    ' was ' || lower(:APEX$TASK_OUTCOME)||'.',
    p_target_url => apex_util.host_url||
                    apex_page.get_url(
                       p_page   => 'reimbursement-notification',
                       p_items  => 'p7_id',
                       p_values => :APEX$TASK_PK)          
);

Notice how the value being passed to the p_target_url parameter prefixes the result of the apex_page.get_url() result by the apex_util.host_url expression. This ensures that the URL is a fully-qualified absolute URL. When using a declarative Send Push Notification page process, the APEX engine handles this for you, so this tip only pertains to the send_push_notification() API.

When the target URL is a page of your APEX app, the page must have the following properties configured:

  • Authentication = Public
  • Deep Linking = Enabled
  • Rejoin Sessions = Enabled for All Sessions
  • Page Protection = Arguments Must Have Checksum

This public target page will typically use a Before Header branch to redirect to an authenticated page in the app, passing along appropriate parameters to show the user the expected detail information for the notification. When combined with the use of APEX’s persistent authentication “Remember me” functionality, this combination gives the most seamless user experience. Page 7 in the sample app, whose alias reimbursement-notification appears in the send_push_notification call above, meets all of these requirements and performs the forwarding to the authenticated page 6, passing along the reimbursement request id to show the end-user the request details.

Granting Outbound Network ACLs for Push Services

The APEX engine sends push notifications by invoking secure notification REST services from Apple, Google, Microsoft, and Mozilla depending on the subscribing user’s device. If you try the sample app on apex.oracle.com, you won’t have to worry about granting outbound network access for these notification service domains, however it’s a task you must perform when you use push notifications on your own APEX instance. In case you need it, the following PL/SQL block is an example of how to grant the appropriate ACLs to the push notification REST API domains and optional a proxy server if you are behind a corporate firewall. If your APEX applications are already using REST services, you will likely already be familiar with these steps. It’s included here for reference:

-- Run as SYS or DBA user
declare
    l_principal varchar2(20) := 'APEX_230100';
    l_proxy varchar2(100)    := null; -- e.g. 'proxy.example.org'
    l_proxy_port number      := 80;
    l_hosts apex_t_varchar2  := apex_t_varchar2(
                                '*.push.apple.com',
                                '*.notify.windows.com',
                                'updates.push.services.mozilla.com',
                                'android.googleapis.com',
                                'fcm.googleapis.com');
    procedure add_priv(p_priv varchar2, p_host varchar2, p_port number) is
    begin
        dbms_network_acl_admin.append_host_ace (
            host       => p_host, 
            lower_port => p_port,
            upper_port => p_port,
            ace        => 
                xs$ace_type(privilege_list => xs$name_list(p_priv),
                            principal_name => l_principal,
                            principal_type => xs_acl.ptype_db));
    end;
    procedure add_priv_resolve(p_host varchar2) is
    begin
        dbms_network_acl_admin.append_host_ace (
            host       => p_host,
            ace        => 
                xs$ace_type(privilege_list => xs$name_list('resolve'),
                            principal_name => l_principal,
                            principal_type => xs_acl.ptype_db)); 
    end;
begin
    if l_proxy is not null then
        add_priv('connect',l_proxy,l_proxy_port);
        add_priv_resolve(l_proxy);
        add_priv('http',l_proxy,l_proxy_port);
    end if;
    for j in (select column_value as hostname from table(l_hosts)) loop
        add_priv('connect',j.hostname,443);
        add_priv_resolve(j.hostname);
        add_priv('http',j.hostname,443);
    end loop;
    commit;
end;

Configuring Wallet to Validate Certificates

On your own APEX instance, in addition to the outbound REST service ACLs required for push notification, you may also need to add certificates into the wallet your instance is using for validating secure HTTP communications. If you are running APEX in the Oracle Cloud, this step should not be necessary. However, on your own instance you may find my colleague Daniel Hochleitner’s open-source Oracle CA Wallet Creator script useful for that purpose.

Downloading the Sample App

You can download the sample app from here.

Downloading My Slides

You can download the slides from my recent talk on this topic at the APEX Alpe Adria 2023 conference from here: APEX 23.1: Native Push Notifications & Easy Background Processing.

Simplify Sharing Region Data

I’ve frequently found it useful to share one region’s data in another. For example, as a user narrows her results with a faceted search or smart filter, a chart, map or dynamic content region shows the same results in a different way. My colleague Carsten explains the technique in his Add a Chart to your Faceted Search Page blog post, and it has come in handy for me many times. But after the fifth pipelined table function and object types I authored to enable the data sharing, I set out to automate the process to save myself time in the future. As an APEX developer, the ability to create development productivity apps for yourself is a cool super power!

The Region Data Sharing Helper app featured in this article lets you pick a region from any app in your workspace and easily download scripts to share that region’s results in another region in the same application. After first explaining how to use the app, I highlight some interesting details of its implementation.

Overview

The app lets you select regions for which to generate data sharing artifacts. Since data sharing requires both data and a unique region identifier, the app only shows regions with a static id having a source location of Local Database, REST Enabled SQL, or REST Source. After adding a region to the selected list for artifact generation, if needed you can adjust the columns to include and the base name. Then, you can download the generated artifacts for that region. By incorporating the SQL scripts into your APEX app, you can configure additional regions in the same app to use the original one’s data using the SELECT statement provided in the accompanying README file.

The app maintains the list of selected regions to remember the subset of columns you configure and the base name of the generated artifacts you prefer for each region. If you later update one of the original regions in the App Builder in a way that affects its columns, just click the (Regenerate) button to refresh its datasource info and download the artifacts again.

Choosing a Region

As shown in the figure below, choose a region whose data you want to share with other regions in the same app. Click (Add Region) to add it to the list of selected regions below.

Region Data Sharing Helper app showing Employees region in HR Sample app

Sometimes the selected region is immediately ready for artifact generation, but other times it may take a few seconds to show a status of Ready. If you see a status of Working, as shown below, wait 10 seconds and click the refresh icon to see if it’s ready yet.

Click (Refresh) after 10 seconds if status shows as Working

Once the status of a selected region is Ready, you can adjust the base name and included columns and save any changes. If you’ve modified the original region in your app in a way that affects its data source columns, click Regenerate to refresh the set of available columns to choose from.

Adjusting the included columns and base name as necessary

Once you’ve saved any changes, the download button will reappear. Click it to get a zip file containing the generated SQL scripts and a README file explaining what they are and how to use them.

Clicking the download button to produce the data sharing artifacts

When you no longer anticipate needing to download data sharing artifacts for a selected region, you can remove it from the list of selected ones by clicking the Delete button. This simply removes it from the helper app’s list of selected regions. You can always add it again later as a selected region if the need arises.

Exploring the Downloaded Artifacts

After clicking the download button for the Employees region on page 1 of the HR Sample application shown above, since the Base Name was employees a zip file named employees-region-data-sharing-artifacts.zip is downloaded. Extracting the zip file reveals three generated files as shown below.

Mac finder showing the contents of the downloaded zip file

The README.html is the place to start, since it explains the other two files.

README.html file explains the generated artifacts and suggests SELECT statement to use

The employees_types.sql script defines the employees_row and employees_tab types used by the pipelined table function in the other file. The employees_function.sql defines the employees_data pipelined table function and depends on the types. You can include these scripts directly into your APEX application as Supporting Objects scripts, or add them to the set of your existing installation scripts. Due to the function’s dependency on the types, however, just ensure that the types script is sequenced before the function script.

For example, incorporating the generated SQL scripts into the HR Sample app above as supporting objects scripts would look like this:

Supporting Objects scripts after adding the two data sharing SQL files

Using Pipelined Table Function in a Region

The README file contains a suggested SELECT statement to use as the source of the region where you want to reuse the original region’s data. After running the types SQL script then running the function SQL script, you can try the suggested statement in another region in the same HR Sample application. In the example below, I’ve used it as the source of a chart region on the same page as the original faceted search region.

Using the SELECT statement suggested in the README file in another region in the same app

The query I’ve used appears below, and of course I could leave out columns that are not necessary in the new region. For simplicity, I used the statement verbatim as I found it in the README file:

select ename,
       empno,
       sal,
       comm,
       deptno
  from table(employees_data)

After also configuring a dynamic action on the After Refresh event of the Employees region to declaratively refresh the Salaries chart region, we immediately see the data sharing working in action, reflecting the filtering the current user performs in the original region, too.

HR Sample app showing generated data-sharing artifacts in use to reflect filtered data in a chart

The rest of the article explains some interesting details of the Region Data Sharing Helper app implementation. If you’re primarily interested in trying out the functionality, you’ll find the download link at the end.

Cascading Select Lists for App, Page, Region

An extremely handy consequence of Oracle APEX’s model-driven architecture is that all application metadata is queryable using SQL. By using the APEX dictionary views, it’s incredibly easy to create new APEX applications that introspect application definitions and provide new value. In the case of the Region Data Sharing Helper app, I needed three select lists to let the user choose the application, page, and region for artifact generation. The query for the P1_APPLICATION select list page item appears below. It uses appropriate where clauses to avoid showing itself in the list and to only show applications that have at least one region with a non-null static id configured and a local database, remote database, or REST service data source.

select a.application_name||' ('||a.application_id||')' as name,
       a.application_id
from apex_applications a
where a.application_id != :APP_ID
and exists (select null
              from apex_application_page_regions r
             where r.application_id = a.application_id
               and r.static_id is not null
               and r.location in ('Local Database',
                                  'Remote Database',
                                  'Web Source'))
order by a.application_name

The query for the P1_PAGE select list page item is similar, retrieving only those pages in the selected application having some qualifying region. Notice how the value of P1_APPLICATION is referenced as a bind variable in the WHERE clause:

select page_name||' ('||page_id||')' as name, page_id
from apex_application_pages p
where p.application_id = :P1_APPLICATION
and p.page_function not in ('Global Page','Login')
and exists (select null
              from apex_application_page_regions r
             where r.application_id = p.application_id
               and r.page_id = p.page_id
               and r.static_id is not null
               and r.location in ('Local Database',
                                  'Remote Database',
                                  'Web Source'))
order by p.page_id

By simply mentioning P1_APPLICATION in the Parent Items(s) property of the P1_PAGE select list, the APEX engine automatically handles the cascading behavior. When the user changes the value of P1_APPLICATION, the value of P1_PAGE is reset to null, or its default value if it defines one. It also implicitly submits the value of any parent items to the server when the select list’s query needs refreshing on parent value change. To save the user a click, I’ve defined the default value for P1_PAGE using a SQL query to retrieve the id of the first page in the available list of pages.

The P1_REGION select list page item uses a similar query against the apex_application_page_regions view, listing P1_PAGE as its parent item and providing an appropriate query for the item’s default value to automatically choose the first region in the list whenever the list gets reset by the cascading select list interaction.

Adding Chosen Region to Selected List

When you choose a region and click the (Add Region) button to add it to the list selected for artifact generation, the Add Region to Selected List page process runs. It uses the built-in Invoke API action to call the add_region_to_selected_list() function in the eba_demo_region_data_sharing package. If it’s the first time this combination of app id, page id, and region static id has been added, it inserts a new row in the eba_demo_reg_data_requests table to remember the user’s selection. Then it proceeds to describe the “shape” of the region’s data source: the names and data types of its columns. That info will be recorded in the xml_describe column in this row by a background job. I reveal next why a background job was required…

Describing a Region’s Data Source Profile

No dictionary view provides the names and data types of a region’s datasource in a way that works for all kinds of data-backed regions, so I had to think outside the box. I applied a meta-flavored twist on Carsten’s data-sharing strategy and created a pipelined table function get_region_source_columns() to programmatically fetch the region datasource column metadata I needed using the following approach:

  1. Use apex_region.open_context() on the region in question
  2. Retrieve the column count using apex_exec.get_column_count()
  3. Loop through the columns to discover the name and data type of each
  4. For each one, call pipe row to deliver a row of region column metadata

The complication I encountered was that apex_region.open_context() only works on regions in the current application. However, when the Region Data Sharing Helper app is running, it is the current app in the APEX session. I needed a way to momentarily change the current application to the one containing the region to describe.

I first tried using an APEX automation to run the region describe process in the background. I hoped a call to apex_session.create_session() inside the job could establish the appropriate “current app” context before using apex_region.open_context() to describe the region. However, I discovered the APEX engine already establishes the APEX session for the background automation job, and my attempt to change it to another app id didn’t produce the desired effect.

Carsten suggested trying a one-time DBMS Scheduler job where my code would be the first to establish an APEX session without bumping into the built-in session initialization. Of course, his idea worked great! Things went swimmingly from there. The code I use inside add_region_to_selected_list() to run the DBMS Scheduler one-time background job looks like this:

-- Submit one-time dbms_scheduler job to process the
-- request to describe the region in some app in the
-- workspace other than the current utility app
dbms_scheduler.create_job (
    job_name        => dbms_scheduler.generate_job_name,
    job_type        => 'plsql_block',
    job_action      => replace(c_gen_xml_job_plsql,c_id_token,l_id),
    start_date      => systimestamp,
    enabled         => true,
    auto_drop => true);

The PL/SQL block submitted to the scheduler comes from the c_gen_xml_job_plsql string constant whose value appears below, after substituting the #ID# token with the primary key of the row in eba_demo_reg_data_requests representing the region that needs describing:

begin
  eba_demo_region_data_sharing.describe_region(#ID#);
  commit;
end;

When the background job runs describe_region(12345), that procedure retrieves the application id, page id, and region id from the eba_demo_reg_data_requests table using the id provided, calls create_apex_session_with_nls() to establish the right application context, then calls the xml_for_sql() function in my eba_demo_transform_group package to retrieve an XML document that represents the query results from the following query against the region metadata pipelined table function:

select * 
  from eba_demo_region_data_sharing.get_region_source_columns(
                                     :app_id,
                                     :page_id,
                                     :region_static_id)

It then updates the row in eba_reg_data_requests to assign this XML region column profile as the value of its xml_describe column. This XML document will have the following format:

<ROWSET>
  <ROW>
    <NAME>EMPNO</NAME>
    <DDL_NAME>empno</DDL_NAME>
    <DATA_TYPE>number</DATA_TYPE>
    <DECLARED_SIZE/>
  </ROW>
  <ROW>
    <NAME>ENAME</NAME>
    <DDL_NAME>ename</DDL_NAME>
    <DATA_TYPE>varchar2</DATA_TYPE>
    <DECLARED_SIZE>50</DECLARED_SIZE>
  </ROW>
  <!-- etc. -->
</ROWSET>

If it’s the first time we’re describing this region, it also assigns a default value to the include_columns column to reflect that all columns are selected by default. The situation when it’s not the first time we’re performing the region describe has an interesting twist I explain later when we explore regenerating the artifacts for a region.

Forms with Previous/Next Navigation

The two user-editable fields in the eba_demo_reg_data_requests row are basename and include_columns . The former represents the base name that will be used to generate the name of the object type (basename_row), the collection type (basename_tab), and the pipelined table function (basename_data). The latter is a stored as a colon-separated list of included column positions, relative to the positional order they appear in the xml_describe data profile XML document. Since you can add multiple regions to the selected list, I wanted to let the user page forward and backward through those selected entries.

To implement that row navigation, I learned a new trick by reading an article by my colleage Jeff Kemp. It revealed a neat feature of the APEX Form region that supports easy paging through an ordered set of rows. You configure it with a combination of settings on the form region itself, as well as on its Form – Initialization process in the Pre-Rendering section of the page.

On the form region, you set the data source and make sure to impose a sort order. That’s important to establish a predictable next/previous ordering for the rows the user navigates. For example, in the helper app the form region’s source is the local table eba_demo_reg_data_requests with an Order By Clause of generate_requested_on desc . This ensures the user sees the requests in most recently generated order.

The other half of the setup involves the Form – Initialization process. As shown below, after creating three page items with in-memory-only storage to hold their values, you configure the Next Primary Key Item(s), Previous Primary Key Item(s), and Current Row/Total Item with the respective names of the page items.

Form Initialization process settings for next/previous navigation

Informed by the form region’s source and sort order, along with these navigation related settings, the APEX engine automatically computes the values of these page items when the page renders. I left the P1_REQUEST_COUNT visible on the form as a display only page item so the user can see she’s on region “3 of 5”. I made the other two page items hidden, but referenced their value as appropriate in the Handle Previous and Handle Next branches I configured in my After Processing section of my page’s Processing tab.

I chose to handle the navigation buttons with a Submit Page action combined with branches so the form’s Automatic Row Processing (DML) process would save any changes the user made on the current page before proceeding to the next or previous one. If the form had been read-only, or I didn’t want to save the changes on navigation, the values of P1_NEXT_REQUEST_ID and P1_PREVIOUS_REQUEST_ID could also be referenced as page number targets in buttons that redirect to another page in the current application. Lastly, I referenced these page item values again in the server-side conditions of the NEXT and PREVIOUS buttons so that they only display when relevant.

Using Transform Group to Generate Artifacts

The artifact generation and download is handled declaratively using a transform group, a capability I explain in more detail in a previous blog post. For generating the region data sharing artifacts to be downloaded in a single zip file, I added the following generate-data-sharing-artifacts.xml static application file. It includes a single data transform whose parameterized query retrieves the region’s column names and data types, filtered by the developer’s choice of columns to include in the generated artifacts. The SELECT statement uses the xmltable() function to query the region’s data profile stored in the xml_describe column. This offered me a chance to learn about the for ordinality clause to retrieve the sequential position of the <ROW> elements that xmltable() turns into relational rows. This made it easy to combine with the apex_string.split() function to retrieve only the columns whose sequential position appears in the colon-separated list of include_columns values.

<transform-group directory="{#basename#}-region-data-sharing-artifacts">
    <data-transform>
        <query bind-var-names="id">
            select x.name, x.ddl_name, x.data_type, x.declared_size
            from eba_demo_reg_data_requests r,
            xmltable('/ROWSET/ROW' passing r.xml_describe
                        columns
                        seq           for ordinality,
                        name          varchar2(255) path 'NAME',
                        ddl_name      varchar2(80)  path 'DDL_NAME',
                        data_type     varchar2(80)  path 'DATA_TYPE',
                        declared_size number        path 'DECLARED_SIZE'
            ) x
            where r.id = to_number(:id)
            and x.seq in (select to_number(column_value)
                        from apex_string.split(r.include_columns,':'))
            order by x.seq
        </query>
        <transformation stylesheet="generate-types.xsl" 
                        output-file-name="{#basename#}_types.sql"/>
        <transformation stylesheet="generate-function.xsl" 
                        output-file-name="{#basename#}_function.sql"/>
        <transformation stylesheet="generate-readme.xsl" 
                        output-file-name="README.html"/>                                          
     </data-transform>
</transform-group>

The transform group includes three transformations that each use an appropriate XSLT stylesheet to transform the region data profile information into a SQL script defining the object types, a SQL script defining the pipelined table function, and a README.html file.

Replacing Strings in XSLT 1.0 Stylesheets

XSLT 2.0 has a replace() function that works like Oracle’s regexp_replace(), but the Oracle database’s native XSLT processor implements only the XSLT 1.0 feature set. Therefore, we need an alternative to perform string substitution in a stylesheet that generates an artifact by replacing tokens in a template.

For example, the generate-readme.xsl stylesheet in the helper app defines a variable named query-template with an example of the SQL query you’ll use to select data from the pipelined table function. This template contains a token #COLUMNS# that we’ll replace with the comma-separated list of selected column names. It also has #FUNCNAME# token we’ll replace with the name of the pipelined table function.

<xsl:variable name="query-template">select #COLUMNS#
  from table(#FUNCNAME#)</xsl:variable>

After first computing the value of the variable columns by using an <xsl:for-each> to loop over the names of the selected columns, the stylesheet performs the double token substitution while defining the example-query variable. If we were able to use XSLT 2.0, the example-query variable definition would look like this:

<!-- XSLT 2.0 string replace example -->
<xsl:variable name="example-query"
  select="replace(replace($query-template,'#COLUMNS#',$columns),
                  '#FUNCNAME#',$function-name)"/>

However, as mentioned above we need to limit our stylesheets to functionality available in XSLT 1.0 to use the native Oracle database XSLT processor. Instead, we use nested calls to a named template replace-string. Think of a named XSLT template like a function that accepts parameters as input and returns an output. So, the following example-query variable declaration calls the replace-string named template to replace the token #FUNCNAME# in the value of the stylesheet variable query-template with the value of the stylesheet variable named function-name:

<!-- Partial solution, replace first #FUNCNAME# token -->
<xsl:variable name="example-query">
   <xsl:call-template name="replace-string">
     <xsl:with-param name="text" select="$query-template"/>
     <xsl:with-param name="replace">#FUNCNAME#</xsl:with-param>
     <xsl:with-param name="with" select="$function-name"/>
   </xsl:call-template>
</xsl:variable>

But the result of the above would be the query template with only the #FUNCNAME# token replaced, leaving the #COLUMNS# token intact. XSLT variables are immutable: once assigned their value cannot be updated. So we are not allowed to create multiple, consecutive <xsl:variable> statements that update the value of the same example-query variable, replacing one token at a time. Instead, XSLT relies on nested calls to the replace-string function while performing the initial (and only allowed) assignment of the example-query variable. So after calling the replace-string template once to replace #FUNCNAME# with the value of $function-name, we use that result as the value of the input text parameter in a second, outer call to replace-string to swap #COLUMNS# with the value of $columns like this:

<!-- Final solution, replace #FUNCNAME#, then #COLUMNS# -->
<xsl:variable name="example-query">
  <xsl:call-template name="replace-string">
    <xsl:with-param name="text">
      <xsl:call-template name="replace-string">
        <xsl:with-param name="text" select="$query-template"/>
        <xsl:with-param name="replace">#FUNCNAME#</xsl:with-param>
        <xsl:with-param name="with" select="$function-name"/>
      </xsl:call-template>
    </xsl:with-param>
    <xsl:with-param name="replace">#COLUMNS#</xsl:with-param>
    <xsl:with-param name="with" select="$columns"/>
  </xsl:call-template>
</xsl:variable>

The generate-types.xsl and generate-function.xsl stylesheets perform this same nested invocation of replace-string, but have more tokens to substitute. As expected, this results in more deeply-nested calls. However, the concept is the same as this two-token example from generate-readme.xsl.

Vertically Centering the Add Region Button

When a button appears in the same row of a form as other page items, by default its vertical alignment with respect to its “row mates” doesn’t look as eye-pleasing as it could.

A button’s default vertical alignment in a row with other page items

The trick to improve the button’s visual appeal, is to add the CSS class u-align-self-center to the Column CSS Classes property in the Page Designer like this:

Using u-align-self-center to vertically center button with page items in the same row

Show Buttons Based on Row Existence

I wanted the user to see an (Add Region) button if the region they choose is not yet in the selected list, and instead see a (Regenerate) button if the region is already in the list. And I wanted the correct button to show both when the page initially renders, as well as when the user changes the values of the select list interactively. I implemented this feature using a dynamic action with conditional hide and show action steps based on an existence check query.

As shown below, I started by using drag and drop in the Page Designer’s Layout editor to drag the (Regenerate) button into the same grid cell as the (Add Region) button. Since the user will see only one or the other at a time, they both can occupy that same grid cell just to the right of the P1_REGION select list.

Two buttons stacked in the same grid cell since the user will see only one or the other at runtime

Next, I added a dynamic action on the Change event of the P1_REGION page item. Recall that in the helper app, being in the selected list means that a row exists in the eba_demo_reg_data_requests table with the region’s unique combination of application id, page id, and region static id. The first action step in the dynamic action event handler uses Execute Server-side Code to run the following query that always returns a row with either ‘Y‘ or ‘N‘ into the hidden P1_REGION_IN_SELECTED_LIST page item. This provides the info about whether the region exists in the list or not.

with region_in_selected_list as (
    select max(id) as id 
      from eba_demo_reg_data_requests
     where app_id = :P1_APPLICATION
       and page_id = :P1_PAGE
       and region_static_id = :P1_REGION
)
select case 
         when x.id is null then 'N' 
         else 'Y' end
into :P1_REGION_IN_SELECTED_LIST
from region_in_selected_list x;

Then I followed that action step with four conditional steps that use a client-side condition based on the value Y or N to hide the button that needs hiding and show the button that needs showing. Notice how the new action step name can be very useful in making the steps self-documenting with a more descriptive label than the old “Hide” or “Show” that appeared before 22.2.

Dynamic action on P1_REGION change event to hide/show appropriate buttons

To finish the job, I set the Fire on Initialization switch to true for the four Hide/Show action steps, and provided the same existence SQL query as the default value of the P1_REGION_IN_SELECTED_LIST hidden page item. This ensured that the correct button shows both during the initial page render, as well as after the user interactively changes the region select list.

To Defer or Not to Defer (Rendering)

With the above configuration in place, the appropriate (Add Region) or (Regenerate) button was appearing conditionally as desired. However, I noticed that when the page first rendered I would momentarily see both buttons flash before the inappropriate one for the currently selected region would get hidden by my dynamic action. The solution to avoid the user’s seeing this “behind the scenes” page setup behavior is to enable the Deferred Page Rendering template option shown below. This setting allows you to decide when faster, incremental page rendering is more appropriate, or whether APEX should wait until page-load-time dynamic behavior is complete before revealing the final state of the page to the user.

Deferred Page Rendering option hides page-load-time hide and show activity

Preserving Column Selection on Regeneration

When you click the (Regenerate) button for a region you’ve already added to the selected list, the add_region_to_selected_list() function updates the existing eba_demo_reg_data_requests row to set READY = ‘N‘ and it runs the background job to call describe_region() again. The region might have changed the set of available columns since the previous time we described it, but the user may have carefully decided which of the previous region’s columns to include and exclude. So it’s important for usability to retain the included columns across the region data profile regeneration.

At the moment the describe_region() code has produced the fresh region data profile XML document and is about to update the existing row in eba_demo_reg_data_requests, we have the following “ingredients” available to work with:

  1. The old xml_describe region profile XML document
  2. The old include_columns value with a colon-separated list of index positions relative to the old region profile XML document
  3. The new region profile XML document just produced

What we need to “bake” with these ingredients is a new list of included columns that retains any columns that were previously in the included list while ignoring any of those included columns that are no longer available to reference. Also worth considering is that the index positions of the previous column names might be different in the new region data profile XML document.

After initially writing the code using multiple loops in PL/SQL, I challenged myself to come up with a single SQL statement to accomplish the job. In words, what I needed the statement to do was, “select a colon-separated list of index positions relative to the new XML describe document where the column name is in the list of names whose whose index positions (relative to the old XML describe document) were in the colon-separated list currently stored in include_columns .” I adjusted the query to also handle the situations when the old XML document was null and when the list of include_columns was null. This let me use the same routine to calculate the default value for the include_columns list for both new and updated rows in eba_demo_reg_data_requests. The private get_default_included_columns() function in the eba_demo_region_data_sharing package has the SELECT statement I use to tackle the job.

select listagg(x.seq,':') within group (order by x.seq)
into l_ret
from xmltable('/ROWSET/ROW' passing p_new_xml_describe
        columns 
            seq for ordinality,
            name varchar2(128) path 'NAME') x
    where p_old_xml_describe is null
        or
        x.name in (
    select y.name
        from xmltable('/ROWSET/ROW' 
                passing p_old_xml_describe
                columns 
                    seq for ordinality,
                    name varchar2(128) path 'NAME') y
        where p_before_list is null 
            or y.seq in (
            select to_number(column_value)
                from apex_string.split(p_before_list,':'))); 

Conclusion

This was a fun learning project that taught me many new things about Oracle APEX in the process of building it. Always keep in mind that Oracle APEX is built with Oracle APEX, and that you, too, can use APEX to build yourself any kind of development productivity helper app you can imagine, not to mention plug-ins of all kinds to extend the core functionality of the platform. Thanks to colleagues Carsten, Jeff, and Vlad who offered me tips on aspects of this sample.

Getting the Sample Apps

You can download the Oracle APEX 22.2 export of the Region Data Sharing Helper app from here. In case you’re interested in the HR Sample app used to show off the generated artifacts in action, you can download that from here. The latter requires that you’ve first installed the EMP/DEPT sample dataset from the Gallery. Enjoy the simplified data sharing!

Declarative Data-Driven Downloads

While developing APEX apps, on multiple occasions I’ve needed to generate data-driven artifacts for external use. My app lets staff manage the data that drives their organization, but for historical reasons a related public-facing website can’t be replaced in the near term. The site is usually static files or was built years before on a different tech stack like LAMP (Linux, Apache, MySQL, PHP). This article walks through a simple example of generating all the HTML files for a static website based on database data. It uses a declarative “transform group” capability I wrote for myself to combine the declarative power of SQL, XML, and XSLT to generate and download a zip file of generated artifacts. See the README file that accompanies this article’s sample app to learn more about how transform groups can be applied in your own APEX apps.

Background Motivation

One APEX app I built as a “nerd volunteer” for a non-profit in Italy required generating:

  • HTML pages for a conference schedule in a particular format
  • SQL scripts to update another system’s MySQL database
  • JSON files to import into an auth provider’s user management console
  • PHP source code files to “drop in” to an online portal site

For example, the conference schedule data shown in the APEX app screenshot in the banner above, turns into a static HTML file to display the parallel tracks of the conference program like this:

Static conference program HTML file generated from data managed by an APEX app

A Favorite Technology Trio: SQL + XML + XSLT

For anyone who may have read my O’Reilly book Building Oracle XML Applications published in October 2000, it should come as no surprise that I find the combination of SQL, XML, and XSLT stylesheets very useful. Your mileage may vary, but over the intervening years I have generated many data-driven artifacts using this trio of technologies. It only made sense that I’d reach for them again as APEX became my tool of choice for building new applications over the past few years.

While each distinct task of generating data-driven artifacts is slightly different, the high-level similarities shared by all tasks I’ve had to implement involve:

  • A SQL query to produce XML-formatted system-of-record data
  • One or more XSLT stylesheets to transform the XML data into appropriate text-based artifact files

The Oracle database natively supports generating XML from any SQL query results and transforming XML using XSLT (version 1.0) stylesheets. After learning the APEX_ZIP package makes it easy to generate zip files, I devised a generic facility to use in my current and future APEX apps called “transform groups”.

Defining a Transform Group

I use an XML file to declaratively define the “interesting bits” that make each transform task unique, and include this file along with the XSLT stylesheets required to generate the artifacts as static application files in my APEX app. My “transform group processor” package interprets the transform group file and processes the data transforms in it to download a single zipfile containing all the results. This way, with a single click my apps can produce and download all necessary generated artifacts.

For example, consider the following basic transform group definition file. It defines a single data transform whose SQL query retrieves the rows from the familiar DEPT table and uses the home-page.xsl stylesheet to produce the index.html home page of a hypothetical company directory website.

<!-- generate-hr-site.xml: Transform Group definition file -->
<transform-group directory="hr-site">
    <data-transform>
        <query>
            select deptno, dname, loc
             from dept
        </query>
        <transformation stylesheet="home-page.xsl" 
                        output-file-name="index.html"/>
     </data-transform>
</transform-group>

The in-memory XML document representing the results of the data transform’s query looks like this:

<ROWSET>
  <ROW>
    <DEPTNO>10</DEPTNO>
    <DNAME>ACCOUNTING</DNAME>
    <LOC>NEW YORK</LOC>
  </ROW>
  <!-- etc. -->
  <ROW>
    <DEPTNO>40</DEPTNO>
    <DNAME>OPERATIONS</DNAME>
    <LOC>BOSTON</LOC>
  </ROW>
</ROWSET>

The XSLT stylesheet referenced in the <transformation> element looks like the example below. It contains a single template that matches the root of the XML document above, then uses the <xsl:for-each> to loop over all the <ROW> elements to format a bulleted list of departments.

<!-- home-page.xsl: XSLT stylesheet to generate home page -->
<xsl:stylesheet version="1.0"
     xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html"/>
  <xsl:template match="/">
    <html>
      <body>
        <h1>Departments</h1>
        <ul>
          <xsl:for-each select="/ROWSET/ROW">
            <li>
                <xsl:value-of select="DNAME"/>
                (<xsl:value-of select="LOC"/>)
            </li>
          </xsl:for-each>
        </ul>
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>

When viewed in a web browser, the index.html file it produces will looks like this:

HTML home page showing department data

After saving the transform group XML file and corresponding XSLT stylesheet to our static application files, we’re ready to wire up the transform group download.

Wiring Up a Transform Group Download Button

You’ll typically call the transform group’s PL/SQL API from an APEX application process with a process point of “Ajax Callback”. For example, if we create an application process named Download_Static_Website, its code will invoke the download() procedure like this:

eba_demo_transform_group.download(
     p_file_name    => 'generate-hr-site.xml', 
     p_zipfile_name => 'hr-website.zip');

A button or link in your APEX app can initiate the transform group zipfile download. Its link target will redirect to the current page and have the Request parameter in the Advanced section of the link definition set to APPLICATION_PROCESS=Download_Static_Website

With these elements in place, clicking on the button will download an hr-website.zip file containing a single hr-site/index.html file.

Enhancing the Transform Group Definition

A data transform can process the same SQL query results using multiple, different stylesheets. XSLT stylesheets can accept parameters to influence their output, and the data transform supports an optional <parameter-query> element whose SQL query can return rows to provide values of these XSLT parameters. The column-to-parameter-map attribute defines how a column in the parameter query’s value should map to the name of an XSLT parameter. The transform group processor runs the stylesheet once for each row in the parameter query’s results. On each iteration it maps the XSLT parameter to the corresponding column value in the current parameter query row and runs the transformation to produce an output file. Notice that the output-file-name attribute can also reference parameter names as part of the file name.

<!-- generate-hr-site.xml: Transform Group definition file -->
<transform-group directory="hr-site">
    <data-transform>
        <query>
            select deptno, dname, loc
             from dept
        </query>
        <transformation stylesheet="home-page.xsl" 
                        output-file-name="index.html"/>
       <transformation stylesheet="dept-page.xsl" 
                       output-file-name="dept_{#depid#}.html">
            <parameter-query column-to-parameter-map
                                 ="DEPTNO:depid">
                select deptno from dept
            </parameter-query>
        </transformation>
     </data-transform>
</transform-group>

To generate a page for each employee in each department, we can further enhance the transform group to include a nested set of EMP table rows for each DEPT table row, and add a third <transformation> element to generate the employee pages.

<!-- generate-hr-site.xml: Transform Group definition file -->
<transform-group directory="hr-site">
    <data-transform>
        <query>
            select deptno, dname, loc, 
                   cursor( select empno, ename, job 
                            from emp 
                           where deptno = d.deptno) as staff
             from dept d
        </query>
        <transformation stylesheet="home-page.xsl" 
                        output-file-name="index.html"/>
        <transformation stylesheet="dept-page.xsl" 
                        output-file-name="dept_{#depid#}.html">
            <parameter-query column-to-parameter-map
                              ="DEPTNO:depid">
                select deptno from dept
            </parameter-query>
        </transformation>
        <transformation stylesheet="emp-page.xsl" 
                        output-file-name="emp_{#empid#}.html">
            <parameter-query column-to-parameter-map
                                 ="DEPTNO:depid,EMPNO:empid">
                select empno, deptno from emp
            </parameter-query>
        </transformation>
     </data-transform>
</transform-group>

Next we make appropriate updates to home-page.xsl to generate hyperlinked department names and upload the dept-page.xsl and emp-page.xsl stylesheets to our static application files. After this, clicking on the button now downloads the entire company directory static website in the hr-website.zip file. It contains an hr-site top-level directory with all the generated HTML pages as shown in the Finder screenshot below after extracting the zip file contents.

Mac Finder showing contents of downloaded hr-website.zip file

Exploring the Sample App

The APEX 22.2 sample application you can download from here installs the eba_demo_transform_group package and includes a working example of the declaratively-generated website download explained in this article. After importing the sample and ensuring you have the EMP/DEPT sample dataset installed, just click the Generate and Download Static Site button. The sample has tabs to easily explore the syntax of the transform group XML file and accompanying XSLT stylesheets, too. Its README page provides more tips on how transform groups might be useful to your own APEX apps in the future.

Since XSLT transformations are great at declaratively generating text-based artifacts of any kind, hopefully I won’t be the only APEX developer to benefit from the productivity the transform group functionality offers.

Screenshot of sample application that accompanies this article

Data-Driven Diagrams

I often want to visualize my Oracle APEX app’s data model as an Entity/Relationship diagram to remind myself how tables are related and exactly how columns are named. After recently stumbling on the open source Mermaid JS project, I had a lightbulb moment and set out to build my own data model visualizer app with APEX itself.

Mermaid Diagram Syntax

The Mermaid JS open source project aims to improve software documentation quality with an easy-to-maintain diagram syntax for Markdown files. The typical README.md file of a software project can include an Entity/Relationship diagram by simply including text like this:

erDiagram
DEPT ||--|{ EMP : "Works In"
EMP ||--|{ EMP : "Works For"

Including a diagram like this into your product doc is as simple as shown below:

Adding a Mermaid diagram to a Markdown file

If your markdown editor offers a WYSIWYG experience, the effect is even more dramatic and productive: you immediately see the results of the diagram you’re editing. For example, editing a Mermaid diagram in a readme file using Typora looks like this:

Editing a Mermaid diagram in a WYSIWYG Markdown editor like Typora

Popular source control repository sites like GitHub and GitLab have also embraced Mermaid diagrams. Since Markdown is used to provide check-in comments, on these sites (and others like them) it’s easy to include Mermaid diagrams in the helpful summaries you provide along with every commit.

Mermaid’s Diagram Types and Live Editor

Mermaid supports creating many different kinds of diagrams, each using a simple text-based syntax like the ER diagram above. At the time of writing, supported diagram types include Entity/Relationship, Class, Gantt, Flow, State, Mindmap, User Journey, Sequence, Git branch diagrams, and pie charts. The handy Mermaid Live site provides a “sandbox” editor experience where you can experiment with all the different kinds of diagrams, explore samples, and instantly see the results.

Mermaid Live editor for experimentation at https://mermaid.live

For example, after consulting their excellent documentation, I immediately tried including column details into my ER diagram for the DEPT table as shown below:

Mermaid Live editor showing ER diagram with column info and library of diagram samples

Rendering Mermaid Diagrams in Web Pages

To maximize the usefulness of the diagrams, the Mermaid project provides a simple JavaScript API to incorporate scalable vector graphics (SVG) renderings of text-based diagrams into any web page or web application. After referencing the Mermaid JS library URL, including a diagram into a page in my APEX application took a truly tiny amount of JavaScript: one line to initialize the library and one line to render the diagram from the text syntax.

In order to reference the current version of the Mermaid JS library on my page, I typed this URL into my page-level JavaScript > File URLs property:

https://cdnjs.cloudflare.com/ajax/libs/mermaid/9.3.0/mermaid.min.js

Then, after including a Static Content region in my page and assigning it a Static Id of diagram, the two lines of JavaScript code I added to the page-level Execute When Page Loads section looked like this:

mermaid.initialize();
mermaid.mermaidAPI.render('diagramsvg',
    `erDiagram
    DEPT ||--|{ EMP : "Works In"
    EMP ||--|{ EMP : "Works For"`,
    (svg) => {
    document.getElementById('diagram').innerHTML = svg;
});

These two lines of “When Page Loads” JavaScript do the following:

  1. Initialize the Mermaid library
  2. Render the diagram defined by the text passed in as an SVG drawing
  3. Set the contents of the diagram region to be this <svg id="diagramsvg"> element.

In no time, my APEX page now displayed a text-based Mermaid ER diagram:

APEX page including a Mermaid ER diagram based on its text-based syntax

Generating Mermaid Diagram Syntax from a Query

After proving out the concept, next I tackled generating the appropriate Mermaid erDiagram syntax based on the tables and relationships in the current APEX application schema. I made quick work of this task in a PL/SQL package function diagram_text() that combined a query over the USER_CONSTRAINTS data dictionary view with another query over the USER_TABLES view.

The USER_CONSTRAINTS query finds the tables involved in foreign key constraints as a “child” table, and gives the name of the primary key constraint of the “parent” table involved in the relationship. By joining a second time to the USER_CONSTRAINTS table, I can query both child and parent table names at once like this:

  select fk.table_name as many_table, 
         pk.table_name as one_table
    from user_constraints fk
    left outer join user_constraints pk
                 on pk.constraint_name = fk.r_constraint_name
   where fk.constraint_type = 'R' /* Relationship, a.k.a. Foreign Key */

The USER_TABLES query, using an appropriate MINUS clause, finds me the tables that aren’t already involved in a “parent/child” relationship above. By looping over the results of these two queries and “printing out” the appropriate Mermaid ER diagram syntax into a CLOB, my diagram_text() function returns the data-driven diagram syntax for all tables in the current schema.

I ultimately decided to include some additional parameters to filter the tables based on a prefix (e.g. EBA_DEMO_CONF), to control whether to include column info, and to decide whether common columns like ID, ROW_VERSION, and audit info should be included or not. This means the final PL/SQL API I settled on looked like this:

create or replace package eba_erd_mermaid as
    function diagram_text(p_table_prefix    varchar2 := null, 
                          p_include_columns boolean := false, 
                          p_all_columns     boolean := false )
    return clob;
end;

Wiring Up the Data-Driven Diagram

With the diagram_text() function in place, I added a hidden CLOB-valued page item P1_DIAGRAM to my page, added a P1_TABLE_PREFIX page item for an optional table prefix, and added two switch page items to let the user opt in to including column information.

Next, I added the computation to compute the value of the hidden P1_DIAGRAM page item using the diagram_text() function:

eba_erd_mermaid.diagram_text(
  p_table_prefix    => :P1_TABLE_PREFIX,
  p_include_columns => :P1_INCLUDE_COLUMNS = 'Y',
  p_all_columns     => :P1_ALL_COLUMNS = 'Y')

Lastly, I adjusted the “When Page Loads” JavaScript code to use the value of the P1_DIAGRAM hidden page item instead of my hard-coded EMP/DEPT diagram syntax:

mermaid.initialize();
mermaid.mermaidAPI.render('diagramsvg',
    apex.items.P1_DIAGRAM.value,
    (svg) => {
    document.getElementById('diagram').innerHTML = svg;
});

With these changes, I saw the instant database diagram I’d been dreaming of.

The Mermaid library handles the layout for a great-looking result out of the box. The diagram helped remind me of all the tables and relationships in the APEX app I wrote to manage VIEW Conference, Italy’s largest annual animation and computer graphics conference. It’s one of my volunteer nerd activities that I do in my spare time for fun.

Mermaid ER diagram of all tables in current schema matching prefix EBA_DEMO_CONF

However, when I tried installing my ER Diagram app in another workspace where I’m building a new app with a much larger data model, I realized that the default behavior of scaling the diagram to fit in the available space was not ideal for larger schemas. So I set out to find a way to let the user pan and zoom the SVG diagram.

SVG Pan Zoom

Luckily, I found a second open source project svg-pan-zoom that was just what the doctor ordered. By adding one additional JavaScript URL and one line of “When Page Loads” code, I quickly had my dynamically rendered ER diagram zooming and panning. The additional library URL I included was:

https://bumbu.github.io/svg-pan-zoom/dist/svg-pan-zoom.min.js

The extra line of JavaScript code I added to initialize the pan/zoom functionality looked like this:

var panZoom = svgPanZoom('#diagramsvg');

The combination of Mermaid JS and this SVG pan/zoom library puts some pretty impressive functionality into the hands of APEX developers for creating useful, data-driven visualizations. Even for developers like myself who are not JavaScript experts, the couple of lines required to jumpstart the libraries’ features is easily within reach.

With this change in place, now visualizing larger diagrams including showing column information was possible.

Dream #2: Reverse Engineer Quick SQL

Since I sometimes create APEX apps based on existing tables, a second schema-related dream I had was to reverse-engineer Quick SQL from the current user’s tables and relationships. This would let me quickly add additional columns using a developer-friendly, shorthand syntax as new application requirements demanded them. Googling around for leads, I found a 2017 blog article by Dimitri Gielis that gave me a headstart for the code required. Building on his original code, I expanded its datatype support and integrated it with my table prefix filtering to add a second page to my application that produces the Quick SQL syntax for the tables in the current schema.

Quick SQL syntax reverse-engineered from existing schema’s tables and relationships

I expanded the eba_erd_mermaid package to include an additional quicksql_text() function for this purpose:

create or replace package eba_erd_mermaid as
    function diagram_text(p_table_prefix    varchar2 := null, 
                          p_include_columns boolean := false, 
                          p_all_columns     boolean := false )
    return clob;
    function quicksql_text(p_table_prefix varchar2 := null)
    return clob;
end;

Copying Text to the Clipboard

As a last flourish, I wanted to make it easy to copy the Mermaid diagram text syntax to the clipboard so I could easily paste it into the Mermaid Live editor if necessary. And while I was at it, why not make it easy to also copy the Quick SQL text syntax to the clipboard to paste into APEX’s Quick SQL utility?

After searching for a built-in dynamic action to copy the text of a page item to the clipboard, I ended up looking for an existing plug-in to accomplish that functionality. I found an aptly-named APEX Copy Text to Clipboard dynamic action plugin from my colleague Ronny Weiss to get the job done easily with a few clicks of declarative configuration.

APEX, SQL & Open-Source JavaScript for the Win!

In short order, by using APEX to combine the power of the SQL that I know and some minimal JavaScript (that I don’t!), I was able to build myself two dream productivity tools to improve my life as an APEX developer in the future.

If you want to give the sample application a spin, download the APEX 22.2 application export from here. It installs only a single supporting PL/SQL package, so any tables you visualize with it will be your own.

Further Reading

For a full-featured, web-based ERD modeling solution from Oracle, make sure to check out the Data Modeler in SQL Developer Web.

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.

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.

Checking If a Region’s Data Source Returned Any Data

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

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

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

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

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

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

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

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

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

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

Refreshing the chart region when Department changes

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

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

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

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

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

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

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

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

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

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

Create an ORDS REST Service with Custom JSON In and Out

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

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

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

procedure generic_service(p_request in json_object_t,
                          p_response out json_object_t)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Mixing Positional & Named Params Can Lead to Trouble

Mixing positional and named parameters can lead to trouble.

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

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

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

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

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

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

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

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

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

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