Clear session state in Oracle APEX after an AJAX action

How to clear the session state in Oracle Application Express? Well it is quite easy to do with PL/SQL however tricky to do with javascript or jQuery. Basically I have implemented an AJAX request based on couple of page items but I want clear the session state for this items after the AJAX call and action performed.

First of all, I have created couple of hidden page items as above. Now I want to set the values for these two items on page load. I have used After Header page PL/SQL page process to set the session and item variables on page load. Below is the PL/SQL script to set the values, this script also includes a delete action along with setting values. I wanted to delete a record from the table based on page item value. So before the item has been deleted I wanted to get and set the values for those items in the hidden fields.

DECLARE
   CURSOR c_del
   IS
      SELECT activity_id
        FROM tb_activity
       WHERE activity_id = : P30_DEL_ACTIVITY;

   l_cat_id   NUMBER;
   ac_id      NUMBER;
   ac_name    VARCHAR2 (60);
BEGIN
   OPEN c_del;

   FETCH c_del INTO l_cat_id;

   IF c_del%FOUND
   THEN
      -- set the values in the page item
      SELECT activity_id, activity_name
        INTO : P30_DEL_ACTIVITY_ID, : P30_DEL_ACTIVITY_NAME
        FROM tb_activity
       WHERE activity_id = : P30_DEL_ACTIVITY;

      -- Delete Activity
      DELETE FROM tb_activity
            WHERE activity_id = : P30_DEL_ACTIVITY;
   END IF;

   : P30_DEL_ACTIVITY := '';

   CLOSE c_del;
END;

What happen in the above script? If user click on a delete child records button in the page, it redirects to the same with by passing the parameter value to P30_DEL_ACTIVITY item then execute the page process to delete the activity if P30_DEL_ACTIVITY is not null.

Now, I want to add an ajax post request to send the delete request to remote server. This request will basically delete the record from the external site when the record is delete from the internal system. Here is the AJAX post request –

< script type = "text/javascript" >

$(document).ready(function () {
  var ac_id = $('#P30_DEL_ACTIVITY_ID').val();
  var ac_name = $('#P30_DEL_ACTIVITY_NAME').val();

  var activity = ["ABSEILING", "BUSH", "CANOEING", "CAVING", "DIVING", "FISHING", "HANG", "HORSE", "MOUNTAIN_BIKE", "OFF_ROAD", "POWER_BOAT", "ROCK", "SNORKEL", "SURFING", "SWIMMING"];
  if (ac_id !== null && ac_name) {
    alert(ac_id);

    if ($.inArray(ac_name, activity) !== -1) {

      $.ajax({
        username: "user_name",
        password: "user-password",
        type: "POST",
        cache: false,
        url: "http://remote.site.com/integrator",
        data: {
          token: "cryt4kYPbvw721rAPm3U",
          type: "activity",
          id: ac_id,
          action: "delete"
        },
        success: function (data) {
          $('#P30_DEL_ACTIVITY_ID').val("");
          $('#P30_DEL_ACTIVITY_NAME').val("");
          alert('This activity has been deleted from public site.');
        },
        error: function (er) {
          alert("Sorry cannot delete the activity from public site.");
        }

      });
    }
  }
});

< /script>

In the above javascript I have used jQuery on load function. I have extended this script to check the value of the page items in a static array list because I don’t care if the deleted activity is not in the list and I don’t need to post the request to public site. Get the values from two hidden page items and set the variables with the value then check the value in the array list, if the value is exist in the array then execute the AJAX post request.

Now this will work fine but the problem is after the AJAX post request APEX page still holds the session variables for these two hidden items and it will execute the script again and again on every page load unless we can clear the session state somehow without logging out of the application.

I have done this by creating a page process and make sure the process point is set to on load after footer. This means it will execute at the end of page load process when the javascript function executed, at the end of the page load it will clear the session variables for these two items. Now javascript will not execute unless the session variables or item value is not null. Here is the PL/SQL to set the session variable to null –

Here is the script –

BEGIN
    APEX_UTIL.SET_SESSION_STATE('P30_DEL_ACTIVITY_NAME',null);
    APEX_UTIL.SET_SESSION_STATE('P30_DEL_ACTIVITY_ID',null);
END;

Enjoy programming.