Oracle APEX: How to refresh a data grid (classic report) asynchronously using javascript

Today I’ll show you how to refresh a classic report in APEX asynchronously.

We need to define a “SELECT List” for the available departments and a classic report to display the employees. Then we add a “Dynamic Action” that will be fired whenever you change the selected department. If the dynamic action of type “Submit page” then the whole page is sent to the server and the client is blocked until the response comes from the server:

To change this behavior and make the action asynchronous, we will use some javascript.

At first we have to change the dynamic action to “execute javascript code” and set the code parameter to refreshGrid(), this is the name of the javascript function that we will create in the next step.

At page level you add the code of the refreshGrid() in “Function and Global Variable Declaration”

function refreshGrid() {
  apex.server.process(
    'refreshGrid',    // Process or AJAX Callback name
	{x01: apex.item("P6_DEPT_ID").getValue()}, //Parameter "x01"
	{
	  success: function (pData) {          // Success Javascript
	    apex.event.trigger("#MyReport", "apexrefresh");
		},
		dataType: "text"                   // Response type (here: plain text)
	}
 );

In my case “P6_DEPT_ID” is the name of the department “select list”.

The function refresh the HTML element with the static id “MyReport” (this is the region of your report). The refresh happens only if the PL/SQL-Block bound to this action is successfully executed. This PL/SQL-Block should be defined as Ajax-Callback process:

begin
   mypackage1.def_attr_in_context('deptid',APEX_APPLICATION.g_x01);
end;

This block set the global variable deptid with the value of the parameter x01 coming from the javascript code.

To define a global variable we use a context. In my opinion using a context is the best way to create a global variable. This is because APEX use connection pooling:

Create a PL/SQL context:

Create context context1 using mypackage1 accessed globally;
/
create or replace package mypackage1 as
    procedure  def_attr_in_context(attr_nam in varchar2, attr_val in  
           varchar2);
end mypackage1;
/
create or replace package body mypackage1 as
    procedure def_attr_in_context(attr_nam in varchar2, attr_val in varchar2) is
    begin      
         DBMS_SESSION.set_context
          (namespace => 'context1'
          ,attribute => attr_nam
          ,value     => attr_val
          ,client_id =>sys_context('USERENV','CLIENT_IDENTIFIER'));
    end def_attr_in_context;
end mypackage1;

You have to adjust the query of the report as following:

select * from emp where deptno = sys_context('context1','deptid')

And now as you can see the report is asynchronously refreshed: