Using AJAX in APEX

In Application Express(APEX) it is possible to communicate with the database without submitting the entire page. This is extremely useful when your APEX application is being run on a very slow network. This on demand communication is possible in APEX using Asynchronous JavaScript and XML(Ajax). Using this method sends only the minimum information required to retrieve a value from the database. I will explain using a simple example involving the famous emp table.

lets say I have a page that has three items and one button

  • P1_EMPID. this will be a textfield used to enter the emp_id
  • P1_ENAME. this will be used to display the ename
  • P1_DISPLAY with the relevant ename for the given emp_id
  • SUBMIT. this will be a button that will submit the page. this page submission will fire off a process that will populate

The issue with the above example is that the entire page has to be reloaded when in fact only one small section will be updated, namely P1_DISPLAY. the following example will demonstrate how to retrieve the name from the database without refreshing the page.

1. Move the database code from the page process and place it in an application process. Name the process “GET_ENAME” and give it a type of “On Demand”. example code is below

v_ename emp.ename%type;
select ename
into v_ename
from emp
where emp_id = :P1_EMPID;
exception when others then htp.prn(‘An error occurred retrieving the name’);

2. Create the following javascript function.

function get_ename(){

retrieve the emp_id from the page
var emp_id = document.getElementById(‘P1_EMPID’).value;

define the Ajax call. The only variable of note in this example is the</span><span style=”font-weight: bold;”> application_process, which I have set to be the same name as step 1.
var get = new htmldb_Get(null,html_GetElement(‘pFlowId’).value, ‘APPLICATION_PROCESS=GET_ENAME’,0);

add the value in P1_EMPID into the session value for P1_EMPID. this is important as without this step the APEX server would not know
<span style=”font-weight: bold;”>what value the user had entered

call the ondemand process and accept the returning ename
var gReturn = get.get();

set the field on the page to equal the ename retrieved from the database
document.getElementById(‘P1_ENAME’).value = gReturn;


3. Finally alter the SUBMIT button so that it uses a URL redirect and in the URL field enter

javascript:get_ename();return false;

and thats it, when you press the submit button it will now send the emp_id to the database and populate P1_ENAME with the relevant name. and all without refreshing the page.

obviously this is a trivial example but it gives an idea of the possibilities of Ajax.

Leave a Reply

Your email address will not be published. Required fields are marked *