How to use ajax in apex? What exactly does it do? What can i do? Why doesn't it work? How can i get values from the database on my page?
All are frequently asked questions and i just want to cover some of the basics and provide a headstart.
Built-in AJAX
You may already have encountered ajax functionality without really knowing. Plenty of functionality in Apex has ajax built into it!
A quick grab:
- paginating an IR will fetch the next or previous page from the server and replace the old source
- cascading LOVs: when you change the parent select list of another select list, the values have to be refreshed in the child select list.
- autocomplete items: when you enable lazy loading in an autocomplete item, the values will be refreshed as you enter characters.
- refreshing a region will replace the current html with an updated version fetched from the server
What can i use ajax for with Apex?
Usually you will want to do one of these things:
- Set session state
- Execute a block of PLSQL
- Maybe you just want to update a field of a record when a button is clicked, or a checkbox is changed.
- Fetch values from the database
In these cases you'd require either a process on the on-demand process point of a page or an application process. This process can contain any piece of PLSQL you'd want. Inserts, updates, deletes, validation code,...
Returning values from this process to the ajax call is really easy: you do this by printing values to the http buffer, ie an htp.p call.
If all you want to do is to set the session state of some page items it is not absolutely necessary to use an application process. You might have seen examples of this as usually the process involved has no code except for "NULL;". Even the trick with a dynamic action which executes a PLSQL block and has "Page items to submit" specified has "NULL;" as code. The reason for this is to save on bandwith. The process really does nothing, and submitting to session state is done simply by including a value to a page item (or other variable) in the ajax call, and calling a process or not has no influence over that. However, without specifying a process which essentially does nothing, you will get the entire current page as a response. That's a lot of bytes there! Having an empty process will return nothing at all and thus save a lot of bytes.
Returning values to an ajax call
Returning values is done by making an htp.p call in the PLSQL process. How and if you return data is up to you. It could be just text, it could be JSON.
Session state
Before starting to talk about communication with the server, it is very important that you understand session state in apex.
Let's say we have 2 page items. One is a select list with employees, where the display is ENAME and the return is EMPNO. The second item is a textfield, and it should fetch the JOB of an employee when it gets selected in the list.
This means that there should be a piece of code on the serverside that retrieves the job of a given employee. What is regularly seen is something like this:
SELECT job
INTO l_job
FROM emp
WHERE empno = :P1_EMPNO;
htp.p(l_job);
And then people think "I'm all set up now! I select something, this code gets executed, and in my return value i'll have the job". Well, no.
You have to keep in mind that session state is the state of the item at the server side. This state can be the same as that on the client side, but i can also not be. When you change a value on the actual page, for example by selecting an employee from the select list, then this will not automatically update the session state of the item. If the page loaded and no value was present in the session state of the item, then there will still be no value in the session state when the selection changed on the page (given that there are no submits!). This means that just firing an ajax call and executing the above SQL will not work, as the session state of P1_EMPNO will likely be the session state as it was at rendering time. To make this work, the session state for P1_EMPNO would have to be updated.
You can always check the current session state by clicking the "Session" button on the developer bar. If session state is unfamiliar ground to you and all this sounds new, take a look at the documentation. This is important stuff!
Oracle Documentation on Session State
Dynamic actions
Execute PLSQL, submit and return items
Now before we get into using ajax in javascript, it is important to note that sometimes you can also solve talking to the server through dynamic actions. There is for example the 'Execute PLSQL' true action, which will obviously be run on the database. There is the option to submit page items to the session state so you can use them in the PLSQL block, and also page items to return so that the session state value can be set to the html.
Reports also have a "Page items to submit" option where you can specify page items which have to be submitted to session state when the report is refreshed. If you'd have a report which includes a page item that is for example a select list, you can include the select list item in the "Page items to submit". By then defining an "on change" dynamic action on the select list, you can add a true action which refreshed the report region. This refresh will then submit the current select list value, and the report output will be correctly filtered.
Ajax calls in apex
Synchronous and asynchronous calls
Calls to the server can be made synchronous and asynchronous. In short, synchronous means that your logic will wait for the call to complete and then proceed with the execution, while asynchronous will fire a call and then proceed execution without awaiting a response. When using a asynchronous call, you will have to provide a callback to deal with the response when it gets there. This is important to understand, because in a synchronous call you will remain in the same context all along: variables used and filled in the same scope will remain available (ex: in a function block). This is different from an asynchronous call: since the callback may occur later on, things may have changed. For example, say that you specify in your callback to get the value from an input item. After the ajax code, you immediately remove the input item. The value of the item will only be retrieved when the callback fires, and the item will be gone already.
In apex you have 2 main ways to perform ajax calls to the server: there is the apex htmldb_Get object, or you can use jQuery.
htmldb_Get
Performing a synchronous call with htmldb_Get
var ajaxRequest = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=get_job', $v('pFlowStepId'));
ajaxRequest.addParam('x01', 'ALLEN');
var ajaxResult = ajaxRequest.get();
This is probably the most straightforward way, and will often be picked because of the small amount of lines and code and thus ease of use. All valid reasons, but remember it is synchronous and thus will make the browser wait for a response. This won't always be preferred or even necessary, and making you shouldn't make your user wait for something that is not required. An example could be a report with a button on each row that when clicked should do an update and set a flag to Y or N. There is no advantage to make the user wait for completion of this call, so why would you.
A more valid use would be to perform a validation of a field. When the field is changed or loses focus, you want to check if the value is valid by performing a select on the database. Here you would perform a synchrnous call to check this, and then possibly notify the user of an invalid choice. It wouldn't make much sense to let the focus slip and then after a second come telling them that the previous field isn't valid; rather make them wait.
Performing an asynchronous call with htmldb_Get: (works at least from 4.0 and up)
var ajaxRequest = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=get_job', $v('pFlowStepId'));
ajaxRequest.addParam('x01', 'ALLEN');
ajaxRequest.GetAsync(function(pResponse){
console.log(pResponse.readyState);
if(pResponse.readyState==4 && pResponse.status==200){
console.log(pResponse.responseText);
};
});
The GetAsync method takes a function as a parameter, and this function can take the response object as a parameter. You will need to check the response object to check when the ajax call has completed (readyState 4) successfully (status 200 HTTP OK), as shown above. (When you check your debug console you'll see 4 readyStates pass by if you put the console.log lines in as i've done in the example code).
Adding values to the request
Using the temporary items x##
ajaxRequest.addParam()
The f## arrays
Name-value pairs
uses p_arg_names and p_arg_values
These are arrays.
ajaxRequest.add()
Source code
If you are wondering at how htmldb_Get works and you understand javascript, then you can always take a look at the code for this object in the apex javascript source file. For example, for version 4.1 this is in the file "javascript/apex_4_1.js" in the apex_images folder (or "/i/").
/**
* @constructor
* @param {Dom node | String} [obj] object to put in the partial page
* @param {String} [flow] flow id
* @param {String} [req] request value
* @param {String} [page] page id
* @param {String} [instance] instance
* @param {String} [proc] process to call
* @param {String} [queryString] hodler for quesry string
*
* */
function htmldb_Get(obj,flow,req,page,instance,proc,queryString)
You probably will never need to bother with obj, proc and queryString.
The callable application processes could be processes defined at the On Demand process point of a page, or as an application process. If you want to call an application process then provide 0 to the page id parameter.
apex.server.process (apex 4.2+)
Essentially replaces htmldb_Get and offers you more flexility because the jQuery functionality is readily exposed. I like the implementation and gives pure jQuery a run for its money, in my opinion :)
Oracle Documentation on apex.server.process
apex.server.process ( "MY_PROCESS", {
x01: "test",
pageItems: "#P1_DEPTNO,#P1_EMPNO"
}, {
success: function( pData ) { ... do something here ... }
} );
Example usage:
apex.server.process('GET_TYPE_VALUE',
{pageItems: '#P3_TYPE'},
{success: function(data){
$s("P3_VALUE", data);
},
dataType: "text",
loadingIndicator: '#P3_VALUE'}
);
Take note of "dataType": this is defaulted to JSON! When you use apex.server.process and do NOT return JSON values in your application process, you will get javascript errors if you did not set the dataType parameter accordingly.
jQuery
Performing an asynchronous call with jQuery
jQuery offers several methods to perform ajax calls, but the most base form is the $.ajax function. The $.post function is just a shorthand method which is based on the $.ajax function. It may look a bit more complex, but i think it is quite clear and not as unnerving once you know what does what.
It is important to read the documentation on the ajax functionality of jQuery. The documentation is very well done and will provide you with lots of information.
jQuery documentation on $.ajax
jQuery documentation on $.post
$.post('wwv_flow.show',
{"p_request" : "APPLICATION_PROCESS=xxx",
"p_flow_id" : $v('pFlowId'),
"p_flow_step_id" : $v('pFlowStepId'),
"p_instance" : $v('pInstance'),
"x01" : 'GET_EXISTING_FILTER'
},
function(data){
//do something FUN with the return -> data
}
);
Which could be written to use the $.ajax functionality:
var ajaxData = {"p_request" : "APPLICATION_PROCESS=xxx",
"p_flow_id" : $v('pFlowId'),
"p_flow_step_id" : $v('pFlowStepId'),
"p_instance" : $v('pInstance'),
"x01" : 'GET_EXISTING_FILTER'
};
$.ajax("url":'wwv_flow.show',
"data": ajaxData,
"settings":{"type":"POST","dataType":"text json"}
)
.done(function(data){
//handle the successful return -> data
});
Adding values to the request
Adding data to the request is not that hard. You simply add data to the data option of the ajax or post request. jQuery expects an object.
There is again a difference in what you specify where
using the temporary items x##
The f## arrays
Name-value pairs
uses p_arg_names and p_arg_values
These are arrays.
"p_arg_names" : ['P1_EMPNO', 'P1_ENAME'],
"p_arg_values" : ['9999', 'ROCKSMITH']
Valid parameters
Lets say you mix up add and addParam on htmldb_Get. Would things still work? No.
Putting X01 in .add() will cause a successfull return, but the return is the page html with a error embedded:
ERR-1002 Unable to find item ID for item "x01" in application "299".
Unexpected error, unable to find item name at application or page level.
You can always use the x## items and the f##-arrays however, but not in add():
ERR-1002 Unable to find item ID for item "f01" in application "299".
Unexpected error, unable to find item name at application or page level.
Meaning that only page and application items should be put in the p_arg_names and values arrays.
Example setup
This is just a really small example which demonstrates making a call to the server and return some values.
As a demonstration a value will be given to the x01 item, the f01 array and the P18_EMPNO page item. This should give you an idea on how to provide session state values to a serverside process.
Page item P18_EMPNO
Application process "bogus" at the AJAX Callback execution point.
DECLARE
l_f01 VARCHAR2(200);
BEGIN
FOR i IN 1..apex_application.g_f01.count
LOOP
l_f01 := l_f01 || apex_application.g_f01(i) ||',';
END LOOP;
l_f01 := rtrim(l_f01, ',');
htp.p('P18_EMPNO: '||:P18_EMPNO||' - X01: '||apex_application.g_x01||' - F01: '||l_f01);
END;
var ajaxRequest = new htmldb_Get(null, $v("pFlowId"), "APPLICATION_PROCESS=bogus", $v("pFlowStepId"));
ajaxRequest.addParam("X01","xxxx");
ajaxRequest.addParam("f01",["test1","test2"]);
ajaxRequest.add("P18_EMPNO","8888");
var ajaxReturn = ajaxRequest.get();
Inspecting and debugging calls
firebug console tab
How do you view and inspect ajax calls? Get Firebug for Firefox, or use the developer tools in Chrome. I don't develop in IE, i just use it run the sites as an enduser and fix IE-uses as i go along. Since i have to develop for IE8 i can't even bother with ajax calls since there are no inbuilt tools to inspect those. I have wireshark installed to do this, but i'm sure plenty of companies wouldn't allow use of it.
