nobody Logout  
Demo Instructions
Using Google's Incremental Search in HTML DB

Introduction

Google has implemented an interesting feature called Google Suggest.   As you type, suggestions are offered in a drop-down list.
The same technique can be used in an Application Express application.   Try typing a name in the Search box   Here.


How It Works

The Google Suggest page uses JavaScript to send your keystrokes to its servers, which send back formatted responses. This transfer is done using the XMLHttpRequest object.
This technique is often called AJAX.

The JavaScript that does the work is contained in this file: http://www.google.com/ac.js
Download it and save it to a file.

The code has been compressed to the point where it's nearly unreadable. Fortunately, it's not necessary to understand how it all works.
If you are curious, you can read an analysis of it here: http://serversideguy.blogspot.com/2004/12/google-suggest-dissected.html

The main action of the script is to call a URL, with your query at the end. For example:
http://www.google.com/complete/search?hl=en&js=true&qu=transparent%20aluminu

Which returns a result like this:

sendRPCDone(frameElement, "transparent aluminu", new Array("transparent aluminum", "transparent aluminum 3m", "transparent aluminum star trek"), new Array("610,000 results", "41,100 results", "8,540 results"), new Array(""));

The result from the server contains three arrays. The first contains the main results. The second array contains the number of hits which are displayed in small green letters. The third array does not seem to be used. It is always empty.


Modify ac.js

To use the script ourselves, it needs to be changed to send its query to a different location. In this example we will change it to call a stored procedure on an APEX server.
The script uses a relative address. This means that the server address is not contained in the script. Also, the query portion of the URL is assigned at runtime.
The only part that needs to be changed is the part that points to the virtual directory.

Search for "/complete/" in ac.js. Change it to match the Location setting in the dads.conf or marvel.conf file for your HTTP Server.
For the hosted server at htmldb.oracle.com, use "/pls/otn/".

If you are running Application Express on your own server, you can place the modified ac.js on the file system of the web server. If you are using the hosted htmldb.oracle.com, you need to upload it as an Application or Workspace file.


Return Our Own Results

Now that we have modified the script to pass queries to our own server, we need to have something that will receive the requests and return properly-formatted results.
A good way to do this is with a stored procedure.
It needs to have 3 varchar2 parameters called qu, hl, and js. The first one is the query string. I didn't use the other two. It needs to return a message like the script is expecting - with the results concatenated in comma-separated arrays. Here is a simple example using the Employees table from the HR sample schema:

CREATE PROCEDURE IncSearch(qu varchar2, hl varchar2, js varchar2) as
  l_List1 varchar2(4000);
  l_List2 varchar2(4000);
  l_Query varchar2(255);
  l_separator varchar2(10) default '';
BEGIN
  l_Query := qu || '%';

  FOR x IN (select Last_Name, First_Name from hr.Employees where Upper(Last_Name) like Upper(l_Query) order by 1)
  LOOP
    l_list1 := l_List1 || l_separator || '"' || x.Last_Name || '"';
    l_list2 := l_List2 || l_separator || '"' || x.First_Name || '"';
    l_separator := ',';
  END LOOP;
	
  owa_util.mime_header('text/html', false);
  owa_util.http_header_close;

  htp.p('sendRPCDone(frameElement, "'|| qu ||'", new Array(' || l_List1 || '), new Array(' || l_List2 || '), new Array(""));');
END;
/

GRANT EXECUTE ON INCSEARCH TO Public;


Hook into an Application Express Page

The last step is to get it to work in an APEX application. For testing, I used the wizard to create an application based on the Employees table from the HR sample schema. The search field it created on the Report page is called P1_REPORT_SEARCH.

Examining the source of the Google page shows that a script called InstallAC is run when the page loads to hook the javascript to a form field:

<SCRIPT>InstallAC(document.f,document.f.q,document.f.btnG,"search","en");</SCRIPT>

The parameters it takes are the names of the form, the text field, the submit button, the command to call on the server, and the language.

We want to include the ac.js script file in the header of our page. Also, it is convenient to wrap InstallAC in a simple function.

In the Page Attributes of the Report page in the HTML Header section, add:

<script src="wwv_flow_file_mgr.get_file?p_security_group_id=563610923303911988&p_flow_id=22777&p_fname=ac.js" type="text/javascript"> </script>

<script language="JavaScript1.1" type="text/javascript">
function iac() 
  {  InstallAC(document.wwv_flow,document.getElementById('P1_REPORT_SEARCH'),"","YourSchema.incsearch","en"); }  
</script>
Where P1_REPORT_SEARCH is the name of the text field I want it attached to, IncSearch is the name of the stored procedure that returns the results, and YourSchema is the name of the schema where the procedure is located.

The last step is to tie it all together by calling the InstallAC function when the page loads.
In the Page HTML Body Attribute section, add:

onLoad="iac()"

In the Display Attributes section set Cursor Focus to "Do not focus cursor".

Now you should be able to run the page. The list of matching results should appear when you type in the search box.