Thursday 8 January 2009

Oracle Apex and Ajax

In this post I will try to explain how you can create an Ajax Search Page, in just few steps and very little code on your Apex pages.

I have already created my Ajax Search Page, see here, by reading instructions from an example in a book called Pro Oracle Application Express book by John Edward Scott and Scott Spendolini . A very good all-round concepts book on Oracle Apex Development.

You will need 2 pages to create the Ajax Search Page.

1. Create a new blank page in your application with a search query

You need to create a new blank page in your application and you need to use the Printer Friendly page template as it has to be minimal looking with no headers, footers or tabs. This page will also have a Report region where you will write the SQL query which the Ajax Search Page will use to search your chosen table on your chosen column. In my case this page was page7.

The SQL I used for searching the "fish" table in my application is like below

select f_name_gb "English", f_name_gr "Greek", f_latin "Latin", f_name_tr "Turkish" from fish
instr(upper(f_name_gb), upper(nvl(:P7_SEARCH, f_name_gb))) > 0
instr(upper(f_name_gr), upper(nvl(:P7_SEARCH, f_name_gr))) > 0
instr(upper(f_name_tr), upper(nvl(:P7_SEARCH, f_name_tr))) > 0
instr(upper(f_latin), upper(nvl(:P7_SEARCH, f_latin))) > 0

2. Modify the Printer Friendly Page Template you used in step 1 above

In page7 above, you will also need to change the Body section of the Printer Friendly template to use the code below, that is the template code around the tag #BOX_BODY# , should be modified like below.

<div id="BOX_BODY">

Now you can run page7 and it will display all records from your table without applying the search.

Now the most important bit is the fact that we will call and display page7 with Ajax into a region in another page. That is why the use of the Printer Friendly template on page7. Because page7 will display via a <div> tag in page1. One more thing, is that page1 which calls the AjaxReport page7, must have a Search Box Text item for the search field where users will type. You must create this text field in page1. This text field is your Search Box, it is where users will type and search.

3. Do an Ajax call on the page you have just created above from another page.

When users enter text in the Search filed in page1, a call is made to the Ajax page page7 from page1 via the JavaScript function on page1, see below. The search is performed and the SQL is executed. The Javascript you will add to the Header of the calling page1 performs the Ajax call and retrieves the report from page7 and places it in the <div> tag which is in a region of page1. You will see what the region with the <div> contains in later steps.

Just include your page number in lines 5 and 6 below. As you see because my Ajax page number is 7, it has 7 and P7_SEARCH included in these lines. Modify this to reflect your page number.

<script type="text/javascript">

function AjaxReportRefresh(pThis){
var l_val = pThis.value;
var get = new htmldb_Get(null,$x('pFlowId').value,null,7);
gReturn = get.get(null,'<ajax:BOX_BODY>','</ajax:BOX_BODY>');
get = null;
$x('AjaxReport').innerHTML = gReturn;

4. Create a new HTML region on page1 call it AjaxReport

Next, I created a new HTML region on page1 and called it AjaxReport. This is where the results from page7 will be displayed. The source of this region is the <div> tag :

<div id="AjaxReport"><br /></div>

5. Last Step, call the Javascript function created in step 3

Everytime something is entered into the search field P1_SEARCH on page1 the JavaScript function of step 3 will be called. To do that enter the code below to the HTML Form Element Attributes section for P1_SEARCH (search box Text field item) page item on page1.

onkeyup=" AjaxReportRefresh (this)"

You are ready to test the page, type some letters and see how the page responds by filtering records from your table as you type the letter. Enjoy!

No comments: