Checkbox Persistence in Tabular Forms (Reports)

Exclusive offer: get 50% off this eBook here
Oracle Application Express 3.2 - The Essentials and More

Oracle Application Express 3.2 - The Essentials and More — Save 50%

Develop Native Oracle database-centric web applications quickly and easily with Oracle APEX with this book and eBook

$35.99    $18.00
by Arie Geller Matthew Lyon | June 2010 | Enterprise Articles Oracle Web Development

In this article by Arie Geller and Matthew Lyon, authors of the book Oracle Application Express 3.2 – The Essentials and More, we will see how to save the checked checkboxes into a database table.

(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.

Oracle Application Express 3.2 - The Essentials and More Develop Native Oracle database-centric web applications quickly and easily with Oracle APEX with this book and eBook
Published: June 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

(For more resources on Oracle, see here.)

The toggleAllCB() JavaScript function

Th e following is the related code for this function:

function toggleAll(pItem,pIdx){
var l_fname;
if (pIdx < 10) {
l_fname = 'f0' + pIdx;
}
else {
l_fname = 'f' + pIdx;
}
var elm = document.getElementsByName(l_fname);
var status = pItem.checked;
for (i=0; i < elm.length; i++) {
elm[i].checked = status;
}
var get = new htmldb_Get(null, $v('pFlowId'),
'APPLICATION_PROCESS=updateCB_all',$v('pFlowStepId'));
get.addParam('x01',array_to_string(elm));
get.addParam('x02',pItem.checked);
get.GetAsync(function(){return;});
get = null;
}
function array_to_string(pArr) {
var str = '';
for (i=0; i< pArr.length; i++) {
str = str + pArr[i].value + ':'
}
str = str.substr(0,str.length-1);
return (str);
}

In the first step of the function, it uses the second parameter—pIdx—to construct the HTML name of the checkboxes column. Using that name, the function collects all the column elements into an array called elm.

Using the first parameter—pItem—the function determines the status of the column header checkbox and uses this status to check/clear all the elements in the elm array.

In the next step, we are passing the elm array, as a parameter, to another JavaScript function—array_to_string(). This function takes the elm elements and generates a single colon-delimited string out of them. We are using this string as the value of x01, setting it with the addParam() method. Using the same method, we are setting x02 to the status of the column header checkbox.

As usual, the x01 and x02 will be available to us inside the on-demand PL/SQL process updateCB_all, which is the server-side logic in this AJAX call, as it was defined while generating a new instance of the htmld_Get object.

All the reasons that led us to use asynchronous AJAX call in the updateCB() function are still valid for the toggleAll() function so the AJAX request is fired using the GetAsync() method. As we still don't need to process any server response, the parameter of GetAsync() is an empty function in this case.

The header_CB_Status() JavaScript function

This function doesn't fire any AJAX process. However it helps us to maintain the proper functionality and status of the column header checkbox. Just as we need special processes to preserve the status of the "regular" column checkboxes (because of the absence of Session State), we need special process to preserve, or in this case to compute, the proper status of the column header checkbox. This checkbox should be checked if all the checkboxes in the column are checked.

function header_CB_Status(pItem, pIdx){
var status = true;
var l_name;

if (pIdx < 10) {
l_fname = 'f0' + pIdx;
}
else {
l_fname = 'f' + pIdx;
}

elms = document.getElementsByName(l_fname);
for (i=0; i < elms.length; i++) {

if (!elms[i].checked){
status = false;
}
}
$x(pItem).checked = status;
}

This function accepts the same parameters as the toggleAll() function, and it also uses them in a similar manner. At first, header_CB_Status() generates the proper HTML name for the checkboxes column, and with this name, it collects all the column checkbox elements into the elm array. Next, it passes through all the elements in the array and determines if all the checkboxes in the column are checked. If they are, the function checks the column header checkbox.

As our example deals with a cross APEX session persistence situation, a case where the first view of the report includes a full checked first column, is possible and valid (for example, a different user, or even you in a previous APEX session, checked all the entries of the report first view). Hence, the header_CB_status() function should be fired the first time the report is loaded and after that, every time pagination is used. Locating a call to header_CB_status() in the report Region Footer will fire the function for the first view of the report, and if we are not using the PPR option (Partial Page Refresh) it will also be fired for every pagination action.

Checkbox persistence in Tabular Forms (Reports)

Using the PPR option makes things a bit more complicated, as the page is not reloaded upon pagination, so the JavaScript function located at the Region Footer will not be fired. In this case, we must involve the pagination code itself.

The pagination code is generated based on the Pagination Subtemplate section of the report template. As report templates are accessible to us, this is a good place to add a call to the header_CB_status() function. I created a copy of the report Standard template, and because the JavaScript function includes very specific parameters, I named it 'Standard—page xx'.

The following is a screenshot of the Next Page Template section of the report template, prior to any changes:

Checkbox persistence in Tabular Forms (Reports)

The active pagination code, as the APEX engine generates it, is "hiding" under the #LINK# Substitution String. As this code includes specific, hardcoded details pertaining to the report region, we don't want to change or tamper with it. It will stay as is. In addition, and as we want header_CB_status() to be fired after the pagination action, we'll add it just after the #LINK#. The new template code should look similar to the following:

Checkbox persistence in Tabular Forms (Reports)

At runtime, after the APEX engine replaced #LINK# with its code, we are getting a similar code to the following:

<a href="javascript:$a_report('6076516344846584',
'11','10','10'); header_CB_Status('CB',10);" class=
"t10pagination">Next
<img src='//dgdsbygo8mp3h.cloudfront.net/sites/default/files/blank.gif' data-original="/i/themes/theme_10/paginate_next.gif"
alt="Next"></a>

This is exactly the code we need. The header_CB_Status() function will be fired as part of the pagination action, but only after the pagination process itself (which also uses AJAX) has finished and a new report view has been injected into place.

After duplicating this action to all the other pagination templates in the Pagination Subtemplate section, our column header checkbox status will be updated with every pagination action.

This concludes our work on the client side.

The AJAX server-side processes

There are two On Demand PL/SQL Anonymous Block processes involved in this AJAX example server side.

update_CB

This process, which is called from the JavaScript function updateCB(), updates the CUSTOMERS_VIP table with the status of a specific row checkbox in the report view.

declare
l_code number;
l_status varchar2(10);
begin

l_status := wwv_flow.g_x02;
l_code := to_number(wwv_flow.g_x01);

if l_status = 'true' then
insert into customers_vip
values(l_code);
else
delete from customers_vip
where cust_id = l_code;
end if;
end;

As we are in a PL/SQL anonymous block, we must use a proper syntax for the global package variables. In this example, we are qualifying the variable by using the original name of the package—wwv_flow.g_x01, which stands for the x01 in the client-side JavaScript code, and wwv_flow.g_x02, which stands for x02.

updateCB_all

This process, which is called from the JavaScript function toggleAll(), updates the CUSTOMERS_VIP table with the status of the entire column checkboxes for the current report view.

declare
l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
begin
l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(apex_application.g_x01);

if apex_application.g_x02 = 'true' then
FOR i IN 1..l_vc_arr2.count LOOP
begin
insert into customers_vip
values(l_vc_arr2(i));
exception
when others then null;
end;
end loop;
else
FOR i IN 1..l_vc_arr2.count LOOP
delete from customers_vip
where cust_id = l_vc_arr2(i);
end loop;
end if;
end;

In this example, we are qualifying the global package variable by using the wwv_flow package synonym apex_application. So first, we are using apex_application. g_x01—a colon delimited string that was generated by the JavaScript array_to_ string() function and includes all the column checkboxes values—as a parameter to the APEX built-in apex_util.string_to_table() function. The result is a PL/SQL array—l_vc_arr2 that holds all the values we need to insert into, or delete from, the CUSTOMERS_VIP table. The exact nature of the DML operation—insert or delete—is determined by the value of apex_application.g_x02, which holds the status of the column header checkbox.

As can be seen in the code of both these on-demand PL/SQL processes, it doesn't generate any server-side response. Hence, the client side doesn't have to wait for the server side, we can use an asynchronous AJAX call and we don't have to define a function to deal with the server response.

This concludes our discussion on the AJAX server-side aspects, and by that we have finished reviewing all the relevant AJAX aspects of this example.

Bear in mind:

For simplicity sake, we ignored concurrency control in a multi-user environment. As the persistence we are using is crossed APEX users, all the users access the same table and the same records. We should ensure that only one user at a time will use the report and change its checked records. Otherwise, the final result will be a mess.

Summary

In this article, we saw how to save the checked checkbox when user is browsing the report using "next" or "previous" links.


Further resources on this subject:


Oracle Application Express 3.2 - The Essentials and More Develop Native Oracle database-centric web applications quickly and easily with Oracle APEX with this book and eBook
Published: June 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Arie Geller

Arie Geller is an independent IT consultant, with more than 30 years of experience with systems analysis, software development, IT infrastructure, etc. He started to use HTML DB 1.6 (the former name of APEX) to develop data centric Web applications in the Oracle environment, and continue doing so, with all the following versions, until today.

Arie has specialized in developing Right-To-Left-oriented applications, and he brings his expertise and experience in this field into the book. Arie is also an active member of the APEX community on OTN, where he assists other members and shares his knowledge and experience as much as he can.

Matthew Lyon

Matthew Lyon is a Senior Consultant at SRA Information Technology in Adelaide, South Australia. His involvement in enterprise software development extends across analysis, design, development, architecture, and project management, giving him a deep appreciation of the effective use of software technology. He has experience with a wide variety of technologies but has a passion for Oracle APEX and Java. Matthew runs APEX training courses for clients and is active in the Oracle community as a presenter and as a committee member of the South Australian Oracle User Group. Matthew has more than 10 years experience in software development and has been using APEX since HTMLDB version 1.6.

Books From Packt

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Oracle JRockit: The Definitive Guide
Oracle JRockit: The Definitive Guide

Amazon SimpleDB Developer Guide
Amazon SimpleDB Developer Guide

jQuery 1.4 Reference Guide
jQuery 1.4 Reference Guide

Django 1.2 E-commerce
Django 1.2 E-commerce

Liferay Portal 6 Enterprise Intranets
Liferay Portal 6 Enterprise Intranets

Oracle Database 11g – Underground Advice for Database Administrators
Oracle Database 11g – Underground Advice for Database Administrators

Oracle Coherence 3.5
Oracle Coherence 3.5

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software