Wednesday, March 19, 2008

Preserving checked checkboxes in a report

Paul from Oracle recently sent me an e-mail with the following request:

"I am using the report template to display the database table contents. Before each report data in the display I added a checkbox to allow user to select the data. I can get the checked data id from global package array apex_application.G_F01 when user submits the page. However, this array is cleared whenever the user browses the report to the next page using the ApEx navigation. How could I save the checked checkbox when user is browsing the report using "next" or "previous" links? This issue also appears if you change the number of rows displayed. I looked on the APEX forum on OTN and could not find a solution. Any help you can give is greatly appreciated."

I understood the problem exactly. Paul has a report on a page with a checkbox column (presumably using APEX_ITEM.CHECKBOX). A user will check a few boxes and then click the Next link to display the next set of rows in the report. But now when the user goes back to the first page, the original checked checkboxes are no longer checked. This is because the checked checkboxes are not preserved in session state.

I poked around the OTN forum myself and could not find this addressed. I even browsed Carl's and Denes' demonstration applications, and even though there are all kinds of exotic examples, this simple (and seemingly common problem) didn't seem to be addressed - at least not for the common man.

Using Application Express collections and a little AJAX, we should be able to solve this rather elegantly.

In my workspace on apex.oracle.com, I:

  1. Started the creation of a new application
  2. Added a report, Page Source = Table, Table Name = EMP, Implementation = Classic
  3. Clicked the Create Button

When running this application, you should get something that looks like:




Standard stuff, right?

Now, to add the checkbox using the APEX_ITEM.CHECKBOX API, I changed the SQL query and added one line:

select
apex_item.checkbox(1, empno) cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP"
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)


Now, after shuffling the cbox column up in the report display order and clearing out the column header, it should now look like:


Now here's where the fun starts. You can liken Application Express collections to temporary tables - they're an amorphous bit bucket to store vectors of information, in contrast to the scalar declarative item types. The nice thing about collections is that they are tied completely to an Application Express session. As those get cleaned up automatically, so does the data in collections. So we will use an Application Express collection to maintain a list of the EMPNO values of the checked rows.

First, I created an unconditional On Demand application-level process named "UpdateCheckboxValue" using the following code:

declare
l_value varchar2(4000);
l_seq_id number := 0;
l_collection_name constant varchar2(30) := 'EMP_COLLECTION';
begin
--
-- Get the value of the global which will be set in JavaScript
--
l_value := wwv_flow.g_x01;

--
-- If our collection named EMP_COLLECTION doesn't exist yet, create it
--
if apex_collection.collection_exists( l_collection_name ) = FALSE then
apex_collection.create_collection( p_collection_name => l_collection_name );
end if;

--
-- See if the specified value is already present in the collection
--
for c1 in (select seq_id
from apex_collections
where collection_name = l_collection_name
and c001 = l_value) loop
l_seq_id := c1.seq_id;
exit;
end loop;

--
-- If the current value was not found in the colleciton, add it. Otherwise, delete it from the collection.
--
if l_seq_id = 0 then
apex_collection.add_member(
p_collection_name => l_collection_name,
p_c001 => l_value );
else
apex_collection.delete_member(
p_collection_name => l_collection_name,
p_seq => l_seq_id );
end if;
commit;
end;


So this elegant piece of code will take care of creating a collection named EMP_COLLECTION, and then determine if we should add or remove the value from the collection. The name of the process, "UpdateCheckboxValue", is relevant, as we'll use it next.

Next, after glancing at Carl's simple AJAX and On Demand example, I figured out the JavaScript I needed. So I edited the page attributes of page 1 and added the following to the HTML Header section:

<script type="text/javascript">
<!--

function f_UpdateCollection( cb ){
var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=UpdateCheckboxValue',0);
get.addParam('x01',cb.value);
gReturn = get.get();
get = null;
}
//-->
</script>



Now, I have a JavaScript function on my page which will invoke the On Demand application-level process I defined above. All I need to do is hook it up. To do that, I can now modify the call to APEX_ITEM.CHECKBOX to invoke this function in an 'onchange' event:

select
apex_item.checkbox(1, empno, 'onclick="f_UpdateCollection(this)"') cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP"
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)


If you now run the application and click the checkboxes, you may notice a slight lag. That's because, behind the scenes, a request is being made from your browser to the Application Express engine, which is calling the On Demand application-level process and modify data in the collection for your session.

But we're not quite done. We need to somehow "merge" what we have stored in the collection with the query for our report. This can be achieved by modifying the call to APEX_ITEM.CHECKBOX to add the fourth argument which indicates if a value should be checked or not, and also modifying the query itself to perform an outer join against APEX_COLLECTIONS.

select
apex_item.checkbox(1, empno, 'onclick="f_UpdateCollection(this)"',a.c001) cbox,
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
from "EMP", apex_collections a
where
(
instr(upper("ENAME"),upper(nvl(:P1_REPORT_SEARCH,"ENAME"))) > 0 or
instr(upper("JOB"),upper(nvl(:P1_REPORT_SEARCH,"JOB"))) > 0
)
and a.c001 (+)= empno
and a.collection_name (+)= 'EMP_COLLECTION'



This query will still report on every row in the EMP table, and optionally on the EMP_COLLECTION where the join condition is met. In the reference to a.c001 in the call to APEX_ITEM.CHECKBOX, for those rows in EMP for which there is no match, the resultant value will be NULL. For those rows which do match (a.c001 = EMPNO), then the value of
a.c001 should be the corresponding EMPNO value, and this will result in the checkbox being checked.

There certainly will be a need to clear the collection, so I added a PL/SQL process on the page which will execute on Submit and when the Reset button pressed of:


apex_collection.create_or_truncate_collection(
p_collection_name => 'EMP_COLLECTION' );
commit;


This will clear the collection if it exists, and create it if it doesn't exist. I easily could have used apex_collection.truncate_collection, but I'd have to account for the case where the collection is not already created.

The complete application is here: http://apex.oracle.com/pls/otn/f?p=blog_20080319:1:0

I completely realize some "expert" is going to flame me, telling me this was solved in 2004 and here's the proof. But I was astonished that I couldn't find an article discussing this type of problem and solution.

Caution: The implementation above can be quite "chatty". Every time someone clicks a checkbox, they will make a request to the Web server and the database. There are other ways to implement this solution, but this will work for reports with and without PPR.

Web browser "compatibility"

Is this an article dealing with Oracle Application Express? Barely.

I recently read the latest treatise from Joel Spolsky of "Joel on Software" fame. Joel does an excellent job of explaining the history of the Web "standards" which have gotten everyone into a little bit of a mess. And even though standards have been developed for HTML and Web browsers, it's always a challenge to maintain backward compatibility.

http://www.joelonsoftware.com/items/2008/03/17.html

When I read this, I couldn't help but think of poor Carl on our team who wrestles with these same challenges for every release of Oracle Application Express. It's hard enough to produce a Web application development environment which enables you to build applications to work elegantly with Firefox and Internet Explorer. This is why you don't see certification for Opera, Netscape Navigator, Safari and the like. They're all a little bit different.

I'm still waiting for the Oracle Application Express theme which supports Lynx.

Tuesday, March 18, 2008

Oracle Application Express and NTLM authentication over HTTP

Jason Straub, my friend, work colleague and fellow member of the APEX Columbus Development Organization, has decided to join the Oracle Application Express blogging world. Jason's first posting is about NTLM authentication with Oracle Application Express and how this can be achieved without the use of mod_ntlm.

http://jastraub.blogspot.com/2008/03/ntlm-http-authentication-and.html

Wednesday, March 12, 2008

And the winners are.....

In mid-February, I solicited the feedback from the APEX community about Application Express and which languages we should consider translating to. In later February, Sharon also posted an announcement on the Application Express discussion forum on OTN.

With the crush of the APEX 3.1 release behind us, I decided to review the results. And they were surprising. Out of a total of 149 legitimate responses, the top 5 were:

  1. Hungarian
  2. Russian
  3. Arabic
  4. Dutch
  5. Polish



Granted, I can't claim that this was a scientific survey. It only reached the folks who decided to read my blog posting (unlikely) or follow the link from the announcement on the OTN discussion forum. Or maybe the folks in Hungary started an e-mail campaign and decided to stack the deck. Regardless, this gives me enough information now to approach the managers in these respective regions to assess market opportunity.

Thank you to everyone who provided your invaluable feedback. The full results can be viewed here:

http://apex.oracle.com/pls/otn/f?p=apex_lang_survey:survey_results:0

Oracle Application Express Technology Briefing

On Thursday, March 27, 2008, Oracle and Progressive Skill will present a technology briefing on Oracle Application Express. The event location is Smith & Wollensky Restaurant. Discussions will include:
  • Reasons for failed/delayed custom software projects
  • Advantages of Rapid Application Development
  • How Oracle Application Express facilitates Rapid Development better than any development tool on the market
  • Successful software applications built using Oracle Application Express
  • Real world examples of custom built Oracle Application Express Applications

For more information and to register, you can go here.