Tag: Conditional Visibility

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.

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:

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.

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'.

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;
/