Dynamic Row Refresh with Custom Templates in Oracle APEX: A Step-by-Step Guide

Introduction

In this blog post, we’ll explore how to dynamically refresh a row with custom templates in Oracle APEX without refreshing the entire region, even if you need to apply different row templates based on specific conditions. We will cover a step-by-step approach to achieve this, including fetching templates, evaluating conditions and substituting variables to generate the updated HTML.

We will use an advanced solution that dynamically retrieves values from a query and replaces them in the custom template without explicitly specifying substitution variables.

By leveraging dynamic SQL and key-value pairs, we can create a system that automatically selects and substitutes templates based on predefined conditions. This means:

• No Need to Adjust Code for Template Changes: If the custom template changes, you simply update the template in the database.

• Flexible Condition Handling: Conditions can be updated or added without altering the PL/SQL code responsible for refreshing the row.

• Ease of Maintenance: Centralised control over templates and conditions simplifies maintenance and reduces errors.

Steps to Implement the Solution

Here’s a summary of the steps involved:

• Step 1: Create a Region with a Custom Template

• Step 2: Add JavaScript to Handle Row Refresh via AJAX: To refresh the row when an action is triggered, such as clicking a checkbox.

• Step 3: Set Up a Server-Side Process to Return HTML for the Row: Server process in your Oracle APEX application that returns the updated HTML of the row.

• Step 4: Dynamically Retrieve values from a Query: Function to retrieve row data as key-value pairs.

• Step 5: Evaluate Conditions and Substitute Variables: Evaluate the template conditions, this is used to retrieve the correct template.

• Step 6: Fetch Template and Replace Template Variables: Retrieve the templates and conditions for a specific report dynamically. Substitute placeholders in the selected template with actual data from the report.

Step-by-Step Implementation

Step 1: Create a Region with a Custom Template

First, create a region in your APEX application that uses a custom template. This region will be refreshed dynamically based on the conditions you specify.

In my example I created a Classic Report for the demo_proj_tasks table with a checkbox that will refresh the row with different Custom Row Templates, based on Template Conditions when clicked. You can do this by adding a checkbox item in your SQL query and for re-using purposes, I prefer to use a PIPELINED function:

  function fn_get_tasks_p return tt_task_type
    pipelined is
    l_task_tab tt_task_type;

    cursor c_tasks is
      select tsk.id as event_id
            ,decode(tsk.is_complete_yn
                   ,'Y'
                   ,'Completed'
                   ,'Open') as event_type
            ,tsk.name as event_title
            ,tsk.description as event_desc
            ,tsk.start_date || ' - ' || tsk.end_date as event_date
            ,decode(tsk.is_complete_yn
                   ,'Y'
                   ,'is-new'
                   ,'is-removed') as event_status
            ,decode(tsk.is_complete_yn
                   ,'Y'
                   ,'fa fa-check-circle-o'
                   ,'fa fa-exclamation-triangle-o') as event_icon
            ,'data-tsk-id=' || tsk.id as event_attributes
            ,substr(mbr.username
                   ,0
                   ,2) as user_avatar
            ,mbr.username as user_name
            ,apex_item.checkbox2(p_idx        => 1
                                ,p_value      => tsk.id
                                ,p_attributes => decode(tsk.is_complete_yn
                                                       ,'Y'
                                                       ,'checked') || ' class="refresh-row"') as refresh_checkbox
      from   demo_proj_tasks tsk
      join   demo_proj_team_members mbr on mbr.id = tsk.assignee;
  begin
    open c_tasks;
    fetch c_tasks bulk collect
      into l_task_tab;
    for i in 1 .. l_task_tab.count
    loop
      pipe row(l_task_tab(i));
    end loop;
    close c_tasks;
    return;
  end fn_get_tasks_p;

And in your Custom Row Template(s), use this item to display the checkbox. In combination with Row Template conditions you can create multiple templates based on PL/SQL Expressions, for example :EVENT_LINK is null:

<li class="t-Timeline-item #EVENT_MODIFIERS#" #EVENT_ATTRIBUTES#>
  <div class="t-Timeline-wrap">
    <div class="t-Timeline-user">
      <div class="t-Timeline-avatar #USER_COLOR#" role="presentation">
        #USER_AVATAR#
      </div>
      <div class="t-Timeline-userinfo">
        <span class="t-Timeline-username">#USER_NAME#</span>
        <span class="t-Timeline-date">#EVENT_DATE#</span>
      </div>
    </div>
    <div class="t-Timeline-content">
      <div class="t-Timeline-typeWrap">
        <div class="t-Timeline-type #EVENT_STATUS#">
          <span class="t-Icon #EVENT_ICON#"></span>
          <span class="t-Timeline-typename">#EVENT_TYPE#</span>
        </div>
      </div>
      <div class="t-Timeline-body">
        <h3 class="t-Timeline-title">#EVENT_TITLE#</h3>
        <p class="t-Timeline-desc">#EVENT_DESC#</p>
        <p class="u-pullRight">#REFRESH_CHECKBOX#</p>
      </div>
    </div>
  </div>
</li>

Step 2: Add JavaScript to Handle Row Refresh via AJAX

Add JavaScript to your page to handle the row refresh when an action is triggered, such as clicking a checkbox. This script will make an AJAX call to a server-side process to fetch and update the row’s HTML:

var s4s = s4s || {};
s4s.apex = s4s.apex || {};

s4s.apex.rowrefresh = {
    // Initialize the module
    'init': function() {
        document.addEventListener('DOMContentLoaded', function() {
            s4s.apex.rowrefresh.bindEvents();
        });
    },

    // Bind the change event using event delegation
    'bindEvents': function() {
        document.addEventListener('change', function(event) {
            if (event.target && event.target.classList.contains('refresh-row')) {
                s4s.apex.rowrefresh.handleChange(event.target);
            }
        });
    },

    // Handle the change event
    'handleChange': function (element) {
        var id = element.value;
        var checked = element.checked;

        // Make an AJAX call to fetch the row data
        s4s.apex.rowrefresh.fetchRowData(id, checked).then(function(data) {
            // Replace the current row with the new data
            var row = document.querySelector('.t-Timeline-item[data-tsk-id="' + id + '"]');
            if (row) {
                row.outerHTML = data.row_html;
            }
        });
    },

    // Fetch the row data using AJAX
    'fetchRowData': function (id, checked) {
        return new Promise(function(resolve, reject) {
            apex.server.process('REFRESH_ROW', {
                x01: id,
                x02: checked
            }, {
                success: function(data) {
                    resolve(data);
                },
                error: function(error) {
                    reject(error);
                }
            });
        });
    }
};

// Initialize the row refresh module
s4s.apex.rowrefresh.init();

In this code, when the checkbox is checked, an AJAX call is made to an APEX server process to fetch the row data and refresh the row in the report.

Row Refresh Module Definition:

• init(): Initialises the module by binding event listeners after the DOM content has loaded.

• bindEvents(): Sets up an event listener for changes on elements with the refresh-row class.

• handleChange(element): Handles the change event, triggering an AJAX call to fetch and update the row data.

• fetchRowData(id, checked): Makes an AJAX call to fetch updated row data based on the provided id and checked state.

Step 3: Set Up a Server-Side Process to Return HTML for the Row

Create an AjaxCallback named REFRESH_ROW in your Oracle APEX application and implement the server-side PL/SQL process pr_refresh_row that will return the HTML for the row based on the current data and template. This process is called by the AJAX call from the previous step:

  procedure pr_refresh_row(i_id            in number
                          ,i_template_name in varchar2) is
    l_cursor    sys_refcursor;
    l_key_value tt_col_type;
    l_row_html  clob;
    l_template  clob;
  begin  
    -- Open the cursor for the selected row
    open l_cursor for
      select *
      from   table(fn_get_tasks_p) tsk
      where  tsk.event_id = i_id;

    -- Use the function to get cursor data as key-value pairs
    l_key_value := fn_get_cursor_data(i_cursor => l_cursor);

    -- Get the correct template
    l_template := fn_get_template(i_template_name => i_template_name
                                 ,i_col_tab       => l_key_value);

    -- Replace the template variables with values from key-value pairs
    l_row_html := fn_replace_template_vars(i_col_tab  => l_key_value
                                          ,i_template => l_template);

    apex_json.open_object;
    apex_json.write(p_name  => 'row_html'
                   ,p_value => l_row_html);
    apex_json.close_object;
  end pr_refresh_row;

Explanation:

• Open Cursor: The cursor is opened with the query to select the row based on the provided id.

• Get Key-Value Pairs: The fn_get_cursor_data function is called to retrieve the row data as key-value pairs.

• Fetch Template and Conditions: The template and conditions are fetched based on the template name. The conditions are evaluated using the fn_evaluate_condition function. The first condition that evaluates to TRUE determines the template to be used.

• Replace Template Variables: The fn_replace_template_vars function is called to replace the placeholders in the selected template with the actual values from the key-value pairs.

• Return HTML: The resulting HTML string is returned as the response to the AJAX call.

Step 4: Dynamically Retrieve values from a Query

Create the fn_get_cursor_data function to fetch data from a cursor and store it as key-value pairs.

  function fn_get_cursor_data(i_cursor in out sys_refcursor) return tt_col_type is
    l_cursor_id   number;
    l_cursor_desc dbms_sql.desc_tab;
    l_col_count   number;
    l_tmp_value   varchar2(4000 char);
    l_col_tab     tt_col_type := new tt_col_type();
  begin
    l_cursor_id := dbms_sql.to_cursor_number(rc => i_cursor);
    dbms_sql.describe_columns(c       => l_cursor_id
                             ,col_cnt => l_col_count
                             ,desc_t  => l_cursor_desc);

    <<define_columns_loop>>
    for i in 1 .. l_col_count
    loop
      dbms_sql.define_column(c           => l_cursor_id
                            ,position    => i
                            ,column      => l_tmp_value
                            ,column_size => 4000);
    end loop define_columns_loop;

    <<fetch_rows_loop>>
    while dbms_sql.fetch_rows(c => l_cursor_id) > 0
    loop
      <<process_columns_loop>>
      for i in 1 .. l_col_count
      loop
        dbms_sql.column_value(c        => l_cursor_id
                             ,position => i
                             ,value    => l_tmp_value);
        l_col_tab(l_cursor_desc(i).col_name) := l_tmp_value;
      end loop process_columns_loop;
    end loop fetch_rows_loop;

    return l_col_tab;
  end fn_get_cursor_data;

Explanation:

• Converting Cursor to Number: The DBMS_SQL.to_cursor_number function converts the SYS_REFCURSOR to a cursor number, which can be manipulated using the DBMS_SQL package.

• Describing Columns: The DBMS_SQL.describe_columns procedure is used to get metadata about the columns in the cursor, such as column names and data types.

• Defining Columns: For each column in the cursor, DBMS_SQL.define_column is used to specify the data type and size of the column value that will be fetched.

• Fetching Rows: DBMS_SQL.fetch_rows retrieves rows from the cursor one at a time. For each row, DBMS_SQL.column_value retrieves the value of each column.

• Storing Key-Value Pairs: The column name and value are stored in a PL/SQL associative array (tt_col_type) where the column name is the key and the column value is the value.

Step 5: Evaluate Conditions and Substitute Variables

Create the fn_evaluate_condition function to evaluate conditions dynamically. This function handles bind variables and substitutes values from the key-value pair:

  function fn_evaluate_condition(i_condition in varchar2
                                ,i_col_tab   in tt_col_type) return boolean is
    l_cond      varchar2(4000 char);
    l_result    boolean;
    l_cursor_id integer;
    l_dummy     integer;
    l_var_name  varchar2(1000 char);
  begin
    if i_condition is null
    then
      return true;
    end if;

    -- Substitute variables in condition
    l_cond := fn_replace_template_vars(i_col_tab  => i_col_tab
                                      ,i_template => i_condition);

    -- Prepare dynamic SQL for the condition
    l_cursor_id := dbms_sql.open_cursor;
    dbms_sql.parse(c             => l_cursor_id
                  ,statement     => 'BEGIN :result := (' || l_cond || '); END;'
                  ,language_flag => dbms_sql.native);
    dbms_sql.bind_variable(c     => l_cursor_id
                          ,name  => ':result'
                          ,value => l_result);

    -- Handle all variables in the condition string
    <<bind_variables_loop>>
    for r_bind in (select distinct regexp_substr(l_cond
                                                ,':[^:() ,]+'
                                                ,1
                                                ,level) as var_name
                   from   dual
                   connect by regexp_substr(l_cond
                                           ,':[^:() ,]+'
                                           ,1
                                           ,level) is not null)
    loop
      l_var_name := substr(r_bind.var_name
                          ,2);
      dbms_sql.bind_variable(c     => l_cursor_id
                            ,name  => r_bind.var_name
                            ,value => case
                                        when i_col_tab.exists(l_var_name) then
                                         i_col_tab(l_var_name)
                                        else
                                         null
                                      end);
    end loop bind_variables_loop;

    l_dummy := dbms_sql.execute(c => l_cursor_id);
    dbms_sql.variable_value(c     => l_cursor_id
                           ,name  => ':result'
                           ,value => l_result);
    dbms_sql.close_cursor(c => l_cursor_id);

    return l_result;
  end fn_evaluate_condition;

The fn_evaluate_condition function is used to evaluate dynamic conditions specified in the template. Here's a breakdown of what happens in this function:

• Replace Template Variables in the Condition: The fn_replace_template_vars function is called with the condition string (i_condition) and the key-value pairs (i_col_tab). This function replaces placeholders in the condition string with actual values from the key-value pairs.

• Prepare Dynamic SQL: Construct the dynamic SQL string that will evaluate the condition. The condition is wrapped in a PL/SQL anonymous block, and the result of the condition evaluation will be assigned to the l_result variable.

• Bind Variables from: i_col_tab: Iterate through i_col_tab to bind the provided variables. Use a loop to extract all bind variables from the condition string. If a variable is not present in i_col_tab, bind it to NULL.

• Return the Result: The function returns the boolean result of the condition evaluation (TRUE or FALSE).

Fetch the templates and conditions dynamically from an internal APEX table based on the template name. This allows you to adapt to different templates and conditions without changing the PL/SQL code:

  function fn_get_template(i_template_name in varchar2
                          ,i_col_tab       in tt_col_type) return clob is
    l_template clob;
  begin
    <<template_search_loop>>
    for r_temp in (select temp.col_template1
                         ,temp.col_template_condition1
                         ,temp.col_template2
                         ,temp.col_template_condition2
                         ,temp.col_template3
                         ,temp.col_template_condition3
                         ,temp.col_template4
                         ,temp.col_template_condition4
                   from   apex_application_temp_report temp
                   where  temp.template_name = i_template_name)
    loop
      if fn_evaluate_condition(i_condition => r_temp.col_template_condition1
                              ,i_col_tab   => i_col_tab)
      then
        l_template := r_temp.col_template1;
      elsif fn_evaluate_condition(i_condition => r_temp.col_template_condition2
                                 ,i_col_tab   => i_col_tab)
      then
        l_template := r_temp.col_template2;
      elsif fn_evaluate_condition(i_condition => r_temp.col_template_condition3
                                 ,i_col_tab   => i_col_tab)
      then
        l_template := r_temp.col_template3;
      elsif fn_evaluate_condition(i_condition => r_temp.col_template_condition4
                                 ,i_col_tab   => i_col_tab)
      then
        l_template := r_temp.col_template4;
      end if;
    end loop template_search_loop;

    return l_template;
  end fn_get_template;

Explanation:

• Loop through Template Records: The function uses a FOR loop to iterate over the result set of a SELECT statement that fetches template details from the apex_application_temp_report table where template_name matches i_template_name.

• Evaluate Conditions: Within the loop, the function evaluates each condition using the fn_evaluate_condition function.

Step 6: Fetch Template and Replace Template Variables

Create the fn_replace_template_vars function to substitute placeholders in the template:

  function fn_replace_template_vars(i_col_tab  in tt_col_type
                                   ,i_template in clob) return clob is
    l_var       varchar2(1000 char);
    l_result    clob := i_template;
  begin
    l_var := i_col_tab.first;

    <<replace_vars_loop>>
    while l_var is not null
    loop
      l_result := replace(srcstr => l_result
                         ,oldsub => '#' || upper(l_var) || '#'
                         ,newsub => i_col_tab(l_var));
      l_var    := i_col_tab.next(l_var);
    end loop replace_vars_loop;

    return l_result;
  end fn_replace_template_vars;

Explanation:

• Initialize Variables: Start by setting the first key from the associative array i_col_tab and copying the input template to l_result.

• Loop Through Keys: Iterate through each key in i_col_tab.

• Replace Placeholders: For each key, find its corresponding placeholder (#COLUMN_NAME#) in the template, replace it with the associated value from i_col_tab, and update l_result.

• Move to Next Key: Get the next key in i_col_tab and repeat the process until all keys are processed.

• Return Result: The function returns the modified template (l_result) with all placeholders replaced by their corresponding values.

Conclusion

In this blog, we demonstrated how to reproduce the behavior of Oracle APEX Classic Reports using dynamic row templates based on complex conditions. This approach ensures that APEX remains the single source of truth for templates and conditions, providing consistency, flexibility, and maintainability.

Moreover, this method allows you to refresh a complete row without relying on a full report refresh, which is especially useful for reports where other columns might need adjustment or for long, paginated reports where scrolling back up is not desirable.

By adopting this method, you can ensure that your APEX applications remain adaptable and maintainable, even as requirements evolve. This approach leverages the power of dynamic SQL and key-value pairs, creating a robust solution for rendering customized row templates in your APEX applications.