14-12. Creating an Input Form with AJAX
Problem
You need a web application that can interactively retrieve data based on partial data entered by the user. The data must be retrieved before the user clicks the Submit button to process the page.
Solution
Use JavaScript and Ajax to dynamically retrieve data as the user enters data into the web form. This recipe uses the EMPLOYEES table in the HR schema.
The data entry screen is built with all managers in a single dropdown list, which includes a call to a JavaScript procedure that invokes Ajax to retrieve subordinate data. Once the user selects a manager, the employee dropdown list populates with the manager's subordinates. The subordinates' dropdown list is defined with an ID, which is required by JavaScript to access the list and load the manager's subordinates.
The package contains the procedure xml, which is required to produce the XML data required by the Ajax call. The PL/SQL procedure ajax.xml is called by the web browser within the AjaxMgr.js procedure.
CREATE OR REPLACE PACKAGE ajax IS
PROCEDURE html;
PROCEDURE xml (ID INTEGER);
END ajax;
CREATE OR REPLACE PACKAGE BODY ajax IS
PROCEDURE html is
CURSOR driver IS
SELECT employee_id, last_name
FROM employees
WHERE employee_id in
( SELECT DISTINCT manager_id
FROM employees)
ORDER BY last_name;
BEGIN
common.header ('Manager/Employee Example'), -- See recipe 14-2 for the common package.
htp.p ('<SCRIPT LANGUAGE="JavaScript" SRC="' ||
owa_util.get_cgi_env ('REQUEST_PROTOCOL') || '://' ||
owa_util.get_cgi_env ('HTTP_HOST') ||
'/js/AjaxMgr.js"></SCRIPT>'),
htp.formOpen ('#', 'POST'),
htp.p ('Select a Manager:'),
htp.formSelectOpen ('mgr', cattributes=>'onChange="loadEmployees(this.value);"'),
htp.formSelectOption ('', 'SELECTED'),
FOR rec IN driver LOOP
htp.formSelectOption (rec.last_name, cattributes=>'VALUE="'||rec.employee_id||'"'),
END LOOP;
htp.formSelectClose;
htp.br;
htp.p ('Select a Subordinate:'),
htp.formSelectOpen ('emp', cattributes=>'ID="emp_list"'),
htp.formSelectClose;
htp.br;
htp.formSubmit;
htp.formClose;
common.footer;
END html;
PROCEDURE xml (ID INTEGER) IS
BEGIN
-- see recipe 14-11 for more information on the gen_xml procedure.
gen_xml ('SELECT employee_id, last_name ' ||
'FROM employees ' ||
'WHERE manager_id = ' || ID ||
' ORDER by 1'),
END xml;
END ajax;
How It Works
The recipe begins by defining the package specification with two packages, html and xml. The html package generates the HTML data entry form and the xml procedure generates the XML data required by the call to Ajax.
The html procedure generates the opening HTML code with a call to common.header. Next, the procedure generates a <SCRIPT> tag that identifies the location of the JavaScript to include in the user's browser. The <SCRIPT> tag of this form is one of the few HTML tags not pre-defined in the PL/SQL Web Toolkit.
The <SCRIPT> tag takes advantage of the owa_util package, which is also part of the PL/SQL Web Toolkit. It dynamically generates the web address of the JavaScript based on Apache environment values. This method avoids hard-coding the URL into the procedure and allows it to run in any environment—development, integration, production, etc.
Note The JavaScript, AjaxMgr.js, is included in the media but not reproduced here.
An HTML form is opened with two dropdown lists defined. The first list is populated with the names of all managers from the employees table. The second dropdown list is intentionally left empty. It will be populated at runtime when the user selects a manager from the first dropdown list. Figure 14-7 shows the initial data entry screen generated by the html procedure, prior to the user selecting a manager from the manager's dropdown list.
The manager's dropdown list, mgr, is created using the htp.formSelectOpen procedure with an additional parameter to define the JavaScript to execute when the selected item in the list changes. A change to the manager's dropdown list invokes the JavaScript procedure loadEmployees, which was defined earlier in the <SCRIPT> tag.
The employee's dropdown list, emp, is also created using the htp.formSelectOpen procedure with an additional parameter to define the ID name of the object in the Web browser's DOM2. This ID is required by the JavaScript to dynamically rebuild the employee dropdown list if the value in the manager dropdown list changes. Figure 14-8 shows the data entry form after a Manager is selected by the user. Note the Subordinate list is now populated.
____________________
2 A DOM (Document Object Model) “is a cross-platform and language-independent convention for representing and interacting with objects in HTML, XHTML and XML documents.” – Wikipedia.
The xml procedure calls the gen_xml procedure, created in Recipe 14-11, to generate the data required to populate the employee dropdown list via the Ajax call. The gen_xml procedure is generic in that it only requires the select statement need to produce the XML output.
Figure 14-7. Manager dropdown list with empty subordinate dropdown list
Figure 14-8. Subordinate list after being populated by Ajax