7 min read

(For more resources on Oracle, see here.)

One of the problems we are facing with Tabular Forms is that pagination doesn’t submit the current view of the Tabular Form (Report) page, and if we are using Partial Page Refresh (PPR), it doesn’t even reload the entire page. As such, Session State is not saved prior to us moving to the next/previous view. Without saving Session State, all the changes that we might have made to the current form view will be lost upon using pagination. This problematic behavior is most notable when we are using a checkboxes column in our Tabular Form (Report). We can mark specific checkboxes in the current Tabular Form (Report) view, but if we paginate to another view, and then return, the marked checkboxes will be cleared (no Session State, no history to rely on).

In some cases, it can be very useful to save the marked checkboxes while paginating through the Tabular Form (Report). Joel Kallman, from the APEX development team, blogged about this issue (http://joelkallman.blogspot.com/2008/03/ preserving-checked-checkboxes-in-report.html) and offered a simple solution, which uses AJAX and APEX collections. Using APEX collections means that the marked checkboxes will be preserved for the duration of a specific user’s current APEX session. If that’s what you need, Joel’s solution is very good as it utilizes built-in APEX resources in an optimal way. However, sometimes the current APEX session is not persistent enough. In one of my applications I needed more lasting persistence, which can be used crossed APEX users and sessions. So, I took Joel’s idea and modified it a bit. Instead of using APEX collections, I’ve decided to save the checked checkboxes into a database table. The database table, of course, can support unlimited persistence across users.

Report on CUSTOMERS

We are going to use a simple report on the CUSTOMERS table, where the first column is a checkboxes column. The following is a screenshot of the report region:

Checkbox persistence in Tabular Forms (Reports)

W e are going to use AJAX to preserve the status of the checkboxes in the following scenarios:

  • Using the checkbox in the header of the first column to check or clear all the checkboxes in the first column of the current report view
  • Individual row check or clearing of a checkbox

The first column—the checkboxes column—represents the CUST_ID column of the CUSTOMERS table, and we are going to implement persistence by saving the values of this column, for all the checked rows, in a table called CUSTOMERS_VIP. This table includes only one column:

CREATE TABLE "CUSTOMERS_VIP" (
  "CUST_ID" NUMBER(7,0) NOT NULL ENABLE,
  CONSTRAINT "CUSTOMERS_VIP_PK" PRIMARY KEY ("CUST_ID") ENABLE
)

Bear in mind:

In this particular example we are talking about crossed APEX users and sessions persistence. If, however, you need to maintain a specific user-level persistence, as it happens natively when using APEX collections, you can add a second column to the table that can hold the APP_USER of the user. In this case, you’ll need to amend the appropriate WHERE clauses and the INSERT statements, to include and reflect the second column.

The report SQL query

The following is the SQL code used for the report:

SELECT
  apex_item.checkbox(10,l.cust_id,'onclick=updateCB(this);',
     r.cust_id) as cust_id,
  l.cust_name, l.cust_address1, l.cust_address2,
  l.cust_city, l.cust_zip_code,
  (select r1.sname
   from states r1
   where l.cust_state = r1.code) state,
  (select r2.cname
   from countries r2
   where l.cust_country = r2.code) country
FROM customers l,
     customers_vip r
WHERE r.cust_id (+) = l.cust_id
ORDER BY cust_name

The Bold segments of the SELECT statement are the ones we are most interested in.

The APEX_ITEM.CHECKBOX function creates a checkboxes column in the report. Its third parameter—p_attributes—allows us to define HTML attributes within the checkbox <input> tag. We are using this parameter to attach an onclick event to every checkbox in the column. The event fires a JavaScript function— updateCB(this)—which takes the current checkbox object as a parameter and initiates an AJAX process.

The fourth parameter of the APEX_ITEM.CHECKBOX function—p_checked_ values—allows us to determine the initial status of the checkbox. If the value of this parameter is equal to the value of the checkbox (determined by the second parameter—p_value) the checkbox will be checked. This parameter is the heart of the solution. Its value is taken from the CUSTOMERS_VIP table using outer join with the value of the checkbox. The outcome is that every time the CUSTOMERS_VIP table contains a CUST_ID value equal to the current checkbox value, this checkbox will be checked.

The report headers

In the Report Attributes tab we can set the report headers using the Custom option. We are going to use this option to set friendlier report headers, but mostly to define the first column header—a checkbox that allows us to toggle the status of all the column checkboxes.

Checkbox persistence in Tabular Forms (Reports)

The full HTML code we are using for the header of the first column is:

<input type="checkbox" id = "CB" onclick="toggleAll(this,10);"
title="Mark/Clear All">

We are actually creating a checkbox, with an ID of CB and an onclick event that fires the JavaScript function toggleAll(this,10). The first parameter of this function is a reference to the checkbox object, and the second one is the first parameter—p_idx—of the APEX_ITEM.CHECKBOX function we are using to create the checkbox column.

The AJAX client-side JavaScript functions

So far, we have mentioned two JavaScript functions that initiate an AJAX call. The first—updateCB()—initiates an AJAX call that updates the CUSTOMERS_VIP file according to the status of a single (row) checkbox. The second one—toggleAll()— initiates an AJAX call that updates the CUSTOMERS_VIP file according to the status of the entire checkboxes column. Let’s review these functions.

The updateCB() JavaScript function

The following is the code of this function:

function updateCB(pItem){
  var get = new htmldb_Get(null, $v('pFlowId'),
      'APPLICATION_PROCESS=update_CB',$v('pFlowStepId'));

  get.addParam('x01',pItem.value);
  get.addParam('x02',pItem.checked);

  get.GetAsync(function(){return;});

  get = null;
}

The function accepts, as a parameter, a reference to an object—this—that points to the checkbox we just clicked. We are using this reference to set the temporary item x01 to the value of the checkbox and x02 to its status (checked/unchecked). As we are using the AJ AX related temporary items, we are using the addParam() method to do so. These items will be available to us in the on-demand PL/SQL process update_CD, which implements the server-side logic of this AJAX call. We stated this process in the third parameter of the htmldb_Get constructor function— ‘APPLICATION_PROCESS=update_CB’.

In this example, we are using the name ‘get’ for the variable referencing the new instance of htmldb_Get object. The use of this name is very common in many AJAX examples, especially on the OTN APEX forum, and its related examples.

As we’ll see when we review the server-side logic of this AJAX call, all it does is update—insert or delete—the content of the CUSTOMERS_VIP table. As such, it doesn’t have an immediate effect on the client side, and we don’t need to wait for its result. This is a classic case for us to use an asynchronous AJAX call. We do so by using the GetAsync() method. In this specific case, as the client side doesn’t need to process any server response, we can use an empty function as the GetAsync() parameter.

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here