Oracle APEX - Updating data in a report using AJAX

Overview

I have a simple report showing hundreds of rows of config type information which I wanted to be able to alter quickly. I don't want to open each row in a form, or have to submit a tabular form to update any changes,.. it's all too slow.

What I want instead is a checkbox type column in the report which when clicked causes an AJAX request will be fired off to the server, which in turn runs an update query.

Sounds simple enough!

Setting up the Report

Let's start by looking at the report..
 

I've added a column called 'Ignore' which looks like a radio field/item. When clicked this will run an update to toggle the value in the database table.

Here's SQL that creates this in the report (notice I'm using a graphic of a radio button instead or a HTML form elements).

CASE WHEN sm.method = '3' THEN
         '<img name='||sd.personkey||' src="#IMAGE_PREFIX#pkt_rb_chk.gif" title="Ignored, click to remove" onclick="clicked(this)">'
       ELSE
         '<img name='||sd.personkey||' src="#IMAGE_PREFIX#pkt_rb.gif" title="Click to ignore" onclick="clicked(this)">'
       END ignore,

My database table stores an ignore as a '3' in the 'method' column. And, the reason why I'm using images is because I wanted to store the record key against the images using its name attribute. (It simplifies the javascript.)

Adding the Javascript

Next I added the following code to the Function and Global Variable Declaration on the report page. It uses some handy JQuery functions to make the code easier.

function clicked(obj){
  var personkey = obj.name;
  var img = obj.src.replace(/http:+\/.+\/+/,'');
  var checked;
  if (img == 'pkt_rb.gif') {
    checked = 1;
    var match = $(obj).closest('td').prev('td').text();
    if (match == 'Automatic' || match == 'Manual'){
      // Prompt for confirmation..
      var msg=confirm("This action will remove the current match, continue?");
      if (msg!=true){
        return;
      }
    }
  }
  else { checked = 0; }
  var ajax = new htmldb_Get(null,$v('pFlowId'),
            'APPLICATION_PROCESS=ToggleIgnore',0);
  ajax.addParam('x01',personkey);
  ajax.addParam('x02',checked);
  ajax.get();
  ajax = null;
  $(obj).closest('td').next('td').text('');
  if (checked == 1){
    $(obj).closest('td').prev('td').text('Ignore');
    obj.src = obj.src.replace(/.gif$/,'') + '_chk.gif';
  }
  else {
    $(obj).closest('td').prev('td').text('-');
    obj.src = obj.src.replace(/_chk.gif$/,'') + '.gif';
  }
}

The function takes the image object and uses a regular expression to find out the image name, and thus whether it's selected or not. (This gives us the initial 'ignore' status) We throw up a confirmation box if ignore is being set and then we start the AJAX call.

The AJAX call uses an APEX standard function called html_Get. This handles the request, the parameters you need to pass, and any browser variations. In our example we don't have any values being returned to the browser, but it would be easy to handle this by altering the 'get' command.

var gReturn = ajax.get();

// then do something with this value 

The final stage is to alter the radio image, and its hover-over text. Again we use a regular expression to alter the image name.

Next we need the back-end code that updates the table.

Adding an Application Process

You possibly noticed the AJAX function above is calling an Application Process called 'ToggleIgnore'.

So I created this Application Process using the name 'ToggleIgnore' and used the following anonymous block of PL/SQL code.

DECLARE
  l_person_key varchar2(20);
  l_checkbox varchar2(1);
BEGIN
  wwv_flow_api.set_security_group_id;
  l_person_key := apex_application.g_x01;
  l_checkbox := apex_application.g_x02;

  // Update the table
  switchboard_pkg.set_ignore(
    p_personkey => l_person_key,
    p_checked   => l_checkbox
  );
END;

The parameters are passed into p_personkey and p_checked, and in my example I've stored the update code in a packaged procedure. (It simply runs an update, but you shouldn't need me to go through that part).

Hopefully that's enough to help you get started building your own custom AJAX update methods.