Refresh Static region DATA in Oracle APEX

How to refresh Static region data in ORACLE APEX?

I have been receiving couple of queries about the static region refresh. In this article, I'll try to get through it.


Background:

In the Interactive and classic reports data refresh from database is done by AJAX. APEX internally calls the AJAX callback to fetch the data and refresh the report. Reports are followed by their templates, meaning the data is being fetched and merged/replaced with the report templates.

Partial refresh during the report search or applying filters also uses the AJAX callback to update and refresh data.

Let's try out
We need one AJAX callback, One layout, one refresh event and Static Region.

Create an AJAX callback Process. There are two places to create it

• Shared component: Application process - Point AJAX Callback
• Page Process: Point- AJAX Callback

Page Process

Application Process

Process Name: GET_EMP_DATA
Processing Point: AJAX Callback
Process code:

BEGIN

  APEX_JSON.initialize_clob_output;
  APEX_JSON.open_object; -- {

  APEX_JSON.open_array('employees');

  FOR cur_rec IN (SELECT * FROM emp e )
  LOOP
    APEX_JSON.open_object; -- {
    APEX_JSON.write('employee_number', cur_rec.empno);
    APEX_JSON.write('employee_name', cur_rec.ename);
    APEX_JSON.write('mgr', nvl(cur_rec.mgr,'0'));
    APEX_JSON.write('sal', cur_rec.sal);
    APEX_JSON.write('hiredate', cur_rec.hiredate);
    APEX_JSON.close_object;
  END LOOP;
  APEX_JSON.close_array;

  APEX_JSON.close_object;
 htp.p(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;

Create a static region to hold the data.

Add one div code with its class.

<div class="EMP_DATA">
<div>

Create AJAX callback functions to fetch the data.

function get_data() {
    return apex.server.process('GET_EMP_DATA', {
        pageItems: "",
        x01: 'TEST',
        x02: 'p_url'
    }, {
        dataType: 'text',
        done: function (pData) {
            alert('pData');
        }
    }, {
        loadingIndicator: ".EMP_DATA",
        loadingIndicatorPosition: "append"
    });
}
async function run() {
    console.log('Start');
    var result = await get_data();
    parse_json(result);
}
function parse_json(p_data) { //debugger;
    var obj = jQuery.parseJSON(p_data);
    var table_header = $('<div class="tbl-header"></div>');
    var table_header_content = $('<table cellpadding="0" cellspacing="0" border="0"><thead><tr><th>Empno</th><th>Ename</th><th>MGR</th><th>SAL</th><th>HIRE date</th></tr></thead>');
    table_header.append(table_header_content);
    var table_body_div = $('<div class="tbl-content"><div>');
    //var table_body = $('<table class="test" cellpadding="0" cellspacing="0" border="0"><table>');
    var table_tbody = $('<tbody></tbody>');
    var l_record_cnt = 0;
    $(obj.employees).each(function () {
        console.log('ashish');
        l_record_cnt = l_record_cnt + 1;
        var l_emp = $(this);
        // debugger;
        var table_rows = $('<tr></tr>');
        table_rows.append('<td>' + l_emp[0].employee_number + '</td><td>'
            +l_emp[0].employee_name + '</td><td>'
            +l_emp[0].mgr + '</td><td>'
            +l_emp[0].sal + '</td><td>'
            +l_emp[0].hiredate + '</td>');
        table_tbody.append(table_rows);
    });
    console.log(l_record_cnt);
    var l_table = $('<table class="test" cellpadding="0" cellspacing="0" border="0"></table>');
    l_table.append(table_tbody);
    table_body_div.append(l_table);
    var l_cnt = $('<span> 1 - ' + l_record_cnt + '</span>');
    $('.EMP_DATA').append(table_header);
    $('.EMP_DATA').append(table_body_div);
    $('.EMP_DATA').append(l_cnt);
}
function refresh() {
    $('.EMP_DATA').empty();
    run();
}

Clarification:

Function Name	Usage
get_data	To Fetch the data from Process
run	Trigger the get_data function
parse_json	To Parse JSON response from Process
refresh	To refresh the Region Data

Execute the function on page load

Now the static region is ready to load on page load.

How to Refresh On Demand?

• Create a Button to refresh the data on demand.
• Add a dynamic action to trigger the AJAX. 
• Add true action "Execute Javascript" 
• refresh();

CSS Code to styling the Table.

table{
  width:100%;
  table-layout: fixed;
}
.tbl-header{
  background-color: rgba(79, 183, 20, 0.3);
 }
.tbl-content{
  height:300px;
  overflow-x:auto;
  margin-top: 0px;
  border: 1px solid rgba(234, 45, 45, 0.79);
}
th{
  padding: 20px 15px;
  text-align: center;
  font-weight: 500;
  font-size: 12px;
  color: red;
  text-transform: uppercase;
}
td{
  padding: 15px;
  text-align: left;
  vertical-align:middle;
  font-weight: 300;
  font-size: 12px;
  color: #000;
  border-bottom: solid 1px rgba(234, 45, 45, 0.79);
  border-left: solid 1px;
}
  /* for custom scrollbar for webkit browser*/
::-webkit-scrollbar {
    width: 6px;
}
::-webkit-scrollbar-track {
    -webkit-box-shadow: inset 0 0 6px rgba(0,0,0,0.3);
}
::-webkit-scrollbar-thumb {
    -webkit-box-shadow: inset 0 0 6px rgba(0,0,0,0.3);
}

Use of apex.server.process in async mode

apex.server.process | Namespace:server

Design references