AJAX Implementation in APEX

(For more resources on Oracle, see here.)

APEX introduced AJAX supports in version 2.0 (the product was called HTML DB back then). The support includes a dedicated AJAX framework that allows us to use AJAX in our APEX applications, and it covers both the client and the server sides.

AJAX support on the client side

The APEX built-in JavaScript library includes a special JavaScript file with the implementation of the AJAX client-side components. In earlier versions this file was called htmldb_get.js, and in APEX 3.1, it was changed to apex_get_3_1.js.

In version 3.1, APEX also started to implement JavaScript namespace in the apex_ns_3_1.js file. Within the file, there is a definition to an apex.ajax namespace.

I'm not mentioning the names of these files just for the sake of it. As the AJAX framework is not officially documented within the APEX documentation, these files can be very important and a useful source of information.

By default, these files are automatically loaded into every application page as part of the #HEAD# substitution string in the Header section of the page template. This means that, by default, AJAX functionality is available to us on every page of our application, without taking any extra measures.

The htmldb_Get object

The APEX implementation of AJAX is based on the htmldb_Get object and as we'll see, creating a new instance of htmldb_Get is always the first step in performing an AJAX request.

The htmldb_Get constructor function has seven parameters:

function htmldb_Get(obj,flow,req,page,instance,proc,queryString)


The first parameter is a String that can be set to null, a name of a page item (DOM element), or an element ID.

  • Setting this parameter to null will cause the result of the AJAX request to be assigned in a JavaScript variable. We should use this value every time we need to process the AJAX returned result, like in the cases where we return XML or JSON formatted data, or when we are relaying on the returned result, further in our JavaScript code flow.

    The APEX built-in JavaScript library defines, in the apex_builder.js file, (which is also loaded into every application page, just like apex_ get_3_1.js), a JavaScript global variable called gReturn. You can use this variable and assign it the AJAX returned result.

  • Setting this parameter to the name (ID) of a page item will set the item value property with the result of the AJAX call. You should make sure that the result of the AJAX call matches the nature of the item value property. For example, if you are returning a text string into a text item it will work just fine. However, if you are returning an HTML snippet of code into the same item, you'll most likely not get the result you wanted.
  • Setting this parameter to a DOM element ID, which is not an input item on the page, will set its innerHTML property to the result of the AJAX call.

    Injecting HTML code, using the innerHTML property, is a cross-browser issue. Moreover, we can't always set innerHTML along the DOM tree. To avoid potential problems, I strongly recommend that you use this option with <div> elements only.


This parameter represents the application ID.

If we are calling htmldb_Get() from an external JavaScript file, this parameter should be set to $v('pFlowId') or its equivalent in version 3.1 or before ($x('pFlowId').value or html_GetElement('pFlowId').value ). This is also the default value, in case this parameter is left null.

If we are calling htmldb_Get() as part of an inline JavaScript code we can use the Substitution String notation &APP_ID. (just to remind you that the trailing period is part of the syntax).

Less common, but if you are using Oracle Web Toolkit to generate dynamic code (for dynamic content) that includes AJAX, you can also use the bind variable notation :APP_ID. (In this case, the period is just a punctuation mark.)


This String parameter stands for the REQUEST value. Using the keyword APPLICATION_PROCESS with this parameter allows us to name an application level On Demand—PL/SQL Anonymous Block process that will be fired as part of the AJAX server-side processing. For example: 'APPLICATION_PROCESS=demo_code'. This parameter is case sensitive, and as a String, should be enclosed with quotes.

If, as part of the AJAX call, we are not invoking an on-demand process, this parameter should be set to null (which is its default value).


This parameter represents an application page ID.

The APEX AJAX process allows us to invoke any application page, to run it in the background, on the server side, and then clip portions of the generated HTML code for this page into the AJAX calling page. In these cases, we should set this parameter to the page ID that we want to pull from.

The default value of this parameter is 0 (this stands for page 0). However, this value can be problematic at times, especially when page 0 has not been defined on the application, or when there are inconsistencies between the Authorization scheme, or the page Authentication (such as Public and Required Authentication) of page 0 and the AJAX calling page. These inconsistencies can fail the execution of the AJAX process.

In cases where you are not pulling information from another page, the safe bet is to set this parameter to the page ID of the AJAX calling page, using $v('pFlowStepId') or its equivalent for versions earlier than 3.1. In the case of an inline code, the &APP_PAGE_ID. Substitution String can also be used.

Using the calling page ID as the default value for this parameter can be considered a "good practice" even for upcoming APEX versions, where implementation of page level on-demand process will probably be introduced. I hope you remember that as of version 3.2, we can only define on-demand processes on the application level.


This parameter represents the APEX session ID, and should almost always be left null (personally, I never encountered the need to set it otherwise). In this case, it will be populated with the result of $v('pInstance') or its earliest versions.


This String parameter allows us to invoke a stored or packaged procedure on the database as part of the AJAX process.

The common behavior of the APEX AJAX framework is to use the application level On Demand PL/SQL Anonymous Block process as the logic of the AJAX server-side component. In this case, the on-demand process is named through the third parameter—req—using the keyword APPLICATION_PROCESS, and this parameter—proc—should be left null. The parameter will be populated with its default value of 'wwv_flow.show'(the single quotes are part of the syntax, as this is a String parameter).

However, the APEX AJAX framework also allows us to invoke an external (to APEX) stored (or packaged) procedure as the logic of the AJAX server side. In this case, we can utilize an already existing logic in the database. Moreover, we can benefit from the "regular" advantages of stored procedures, such as a pre-complied code, for better performance, or the option to use wrapped PL/SQL packages, which can protect our business logic better (the APEX on-demand PL/SQL process can be accessed on the database level as clear text).

The parameter should be formatted as a URL and can be in the form of a relative URL. In this case, the system will complete the relative URL into a full path URL based on the current window.location.href property.

As with all stored or packaged procedures that we wish to use in our APEX application, the user (and in the case of using DAD, the APEX public user) should have the proper privileges on the stored procedure.

In case the stored procedure, or the packaged procedure, doesn't have a public synonym defined for it then the procedure name should be qualified with the owner schema. For example, with inline code we can use:


For external code, you should retrieve the owner and make it available on the page (e.g. assign it to a JavaScript global variable) or define a public synonym for the owner schema and package.


This parameter allows us to add parameters to the stored (packaged) procedure that we named in the previous parameter—proc. As we are ultimately dealing with constructing a URL, that will be POSTed to the server, this parameter should take the form of POST parameters in a query string—pairs of name=value, delimited by ampersand (&).

Let's assume that my_proc has two parameters: p_arg1 and p_arg2. In this case, the queryString parameter should be set similar to the following:


As we are talking about components of a URL, the values should be escaped so their code will be a legal URL. You can use the APEX built-in JavaScript function htmldb_Get_escape() to do that.

If you are using the req parameter to invoke an APEX on-demand process with your AJAX call, the proc and queryString parameters should be left null. In this case, you can close the htmldb_Get() syntax right after the page parameter. If, on the other hand, you are invoking a stored (packaged) procedure, the req parameter should be set to null.

(For more resources on Oracle, see here.)

Code examples

Let's see some examples of how to use the htmldb_Get class with various scenarios:

var ajaxReq = new htmldb_Get(null, $v('pFlowId'),

  • With this code, we create a new object instance of htmldb_Get and assign it to ajaxReq.
  • he first parameter is null, and that means that the returned AJAX response should be assigned to a JavaScript variable.
  • Next we set the Application ID using the $v('pFlowId') function. Using this version of the function means that we are on APEX 3.1 or higher instance, and that this code fits either inline code or external JavaScript file.
  • We set the third parameter—req—to point to an application level On Demand PL/SQL Anonymous Block process, called demo_code, as the logic of the AJAX server-side component.
  • We specifically set the page parameter to a value of 0 (zero).
  • As we don't need any of the following parameters, we just closed the parameter list.

Although setting the page parameter to 0 is a very common practice, I mentioned earlier that this is not the best choice, as it can be problematic at times. I consider the following code, with the same functionality, to be the "best practice":

var ajaxReq = new htmldb_Get(null, $v('pFlowId'),
'APPLICATION_PROCESS=demo_code', $v('pFlowStepId'));

Let's review the following code:

var ajaxReq = new htmldb_Get('P10_COMMENTS', $v('pFlowId'),
'APPLICATION_PROCESS=demo_code', $v('pFlowStepId'));

With this code, we set the first parameter to 'P10_COMMENTS', which is a page text item. This means that the returned AJAX response will be assigned directly to the P10_COMMENTS text item. It is our responsibility, as developers, to make sure that the returned response is a simple text.

The following code looks almost the same as the previous one:

var ajaxReq = new htmldb_Get('cal1', $v('pFlowId'),
'APPLICATION_PROCESS=demo_code', $v('pFlowStepId'));

However, in this case we set the first parameter to 'cal1', which is the ID of a <div> tag on the application page. This means that the returned AJAX response will be set as the value of the innerHTML attribute of this <div>. It is our responsibility, as developers, to make sure that the returned response is a valid HTML code that can fit the <div> innerHTML.

In the following example we are invoking a packaged procedure as part of the AJAX process:

function formatItem(pThis) {

var params = 'p_arg1=' + htmldb_Get_escape($v(pThis));
var get = new htmldb_Get(null, null, null, &APP_PAGE_ID.,
null, '#OWNER#.my_package.my_proc', params);
. . .

  • The JavaScript function takes pThis as a parameter, and later we are passing it into the packaged procedure as its parameter.
  • The use of #OWNER# and &APP_PAGE_ID. implies that this snippet of code is part of an inline JavaScript code.
  • We are invoking the my_proc procedure stored in the my_package package. In the example, we are using a full-qualified name of the procedure.
  • The my_proc procedure accepts one parameter—p_arg1. The first line in our code retrieves the value of a DOM node based on the pThis parameter of the formatItem() function—$v(pThis). As we are going to use this value in a URL, we are escaping it—htmldb_Get_escape($v(pThis)). Now, we can complete the build of the quertString parameter for the htmldb_Get as a valid pair of an item name and a value, and assign it to the JavaScript variable params. We are using this variable as the last parameter of our call to a new htmldb_Get instance.

    If the system doesn't find a complete match between the parameters of the stored (packaged) procedure we are invoking and the value of the queryString parameter, it will produce a 404 HTML error message telling us that the procedure we are calling for is not found on the server. As you know, this is not the case, and you should check the arguments list first.

  • As we are using the sixth parameter—proc—to call our packaged procedure, the third parameter—req—is set to null.

The htmldb_Get methods

The htmldb_Get object has several built-in methods that help us to utilize its functionality, and perform the actual AJAX call. In the following sub-sections, we'll review the more public ones; those we most likely need to use ourselves.


If we are invoking a stored (packaged) procedure we can use the queryString parameter to pass the necessary parameters to the procedure. But what about the on-demand PL/SQL process? It's an anonymous PL/SQL block without parameters. How can we pass necessary data from the JavaScript on the client side to the PL/SQL anonymous block on the server side? In general, the answer is Session State. We can use the add() method to set the Session State of application or page items, and those will be available to us in the PL/SQL code.

The add() method accepts two parameters; the first is a String parameter, representing a name of an application or page item, and the second is its value.

As we are dealing with a method, it should be associated with an object. We are going to use the ajaxReq object we created in our previous examples:


In this example, we are using an application item called TEMP1 and we are setting its value with the value of a page item called P10_SEARCH. The value of TEMP1 will be set in Session State, and will be available for us to use in any PL/SQL code in the on-demand PL/SQL process we are invoking as part of the AJAX call. We can reference TEMP1 by using a bind variable notation—:TEMP1—or by using the APEX built-in v('TEMP1') function.

According to our needs, we don't have to only use (temporary) application items. We can also use page items, as used below:


In this case, the Session State value of the page item P10_SEARCH will be set according to its current DOM value, i.e. the value of the item that is being displayed on screen.

Setting Session State with the add() method does not depend on actually invoking an on-demand PL/SQL process. We can use the AJAX framework to just set Session State from JavaScript code without any other server-side activity. This can be used as the client-side equivalent to the APEX API procedure APEX_UTIL.SET_SESSION_STATE(p_name, p_value).


APEX 3.1 introduced 10 new pre-defined global package variables, which we can use with our AJAX calls without the need to define specific, temporary by nature, application items. In the client side we can reference them as x01 to x10, and in the server side, within the on-demand PL/SQL process we are invoking in the AJAX call, we should use apex_application.g_x01 to apex_application.g_x10.

In some code examples out there, you might see a reference of wwv_flow.g_x01 to wwv_flow.g_x10. That means these new variables are actually global variables in the wwv_flow package, which has a public synonym of apex_application. You can use both references as you see fit.

Now it's also easier to explain the difference between client-side and server-side references. In the client side, we are actually setting wwv_flow.show parameters, x01 to x10, while on the server side, in the PL/SQL code, we reference the actual package global variables, g_x01 to g_x10.

APEX 3.1 also exposed the addParam() method, which we can use to set the values of these new variables so that they will be available to us in the on-demand PL/SQL process that we are invoking in the AJAX process. We are invoking the addParam() method in a similar manner to the add() method, although it's important to remember that they don't have the same functionality. With addParam(), instead of defining a special application item—TEMP1—which we'll only use with the AJAX processes, we can use the following:


Now we can use this variable in the on-demand PL/SQL process, for example, as part of a WHERE clause:

select col1, . . .
from my_table
where col1 = apex_application.g_x01;

addParam() is not setting Session State. As such, the g_x01 to g_x10 variables have no persistence features. As we are talking about package variables, their scope is only the on-demand PL/SQL process that we are invoking with the AJAX call. After the on-demand PL/SQL anonymous block has run its course, these variables will be initialized, just like any other package variable, upon new database session.

General remarks

The following are some general remarks about the functionality and relationship of add() and addParam():

  • Both add() and addParam() are actually creating a single string, ampersand (&) delimited, which comprised of name=value pairs. Ultimately, this string acts as a parameter in one of the methods that initiates the AJAX process (the XMLHttpRequest send() method).

    As such, we can call these methods as many times as we need in order to set all the variables we need. For example:

    var ajaxReq = new htmldb_Get(null, $v('pFlowId'),
    'APPLICATION_PROCESS=demo_code', $v('pFlowStepId'));

    . . .

  • The addParam() method is not replacing the add() method. Each has its own unique role in the APEX AJAX framework.

    We should use the add() method when we want to set the values of application or page items and save these values in Session State.

    We can't use add() to set the APEX 3.1 and above x01 to x10 parameters. Doing so will ultimately lead to an error message.

    In version 3.1 and above we should use the addParam() methods to set the values of the x01 to x10> parameters.

    We can't use addParam() to set the values of the application or page items to be used in the AJAX call. Doing so will ultimately lead to an error message.

  • We can't use add() or addParam() to set the parameters of a stored (package) procedure that we want to invoke with AJAX. For that, we must use the queryString parameter of htmldb_Get().

    If we set the queryString parameter of htmldb_Get(), the system will ignore any add() or addParam() calls, and their values will not be set.

  • There are several more global variables, just like the g_x01 to g_x10, that are already defined in the wwv_flow package. It is not advisable at this point (APEX 3.1/3.2) to use these global variables as temporary variables in the AJAX related on-demand PL/SQL processes. Although it will not break anything in these versions, the APEX development team is going to use them in future versions for some other purposes. Using them now could expose your application to upgrade risks in future APEX versions.

.get(mode, startTag, endTag)

The get() method is the method that implements the AJAX call itself by generating the XMLHttpRequest object, and using its methods with the proper parameters that were constructed with the htmldb_Get object and its add() or addParam() methods.

The get() method implements a synchronize POST AJAX request. Until APEX 3.1, a synchronized AJAX call was the only mode that APEX supported. This means that the JavaScript code always waits for the server-side AJAX response before it continues with the JavaScript code flow.

A synchronized AJAX call, as APEX is using, can cause the Web browser to freeze for a moment while it waits for the server-side response. In most cases, it probably will not be noticeable, but it really depends on the complexity of the server-side logic, the amount of the AJAX-returned data, and the quality and bandwidth of the communication lines.

The mode parameter

The first parameter of get() is a String one, and it can be set to null or to 'XML'. This parameter determines the data format of the AJAX response. If set to null, then the returned data will be a String JavaScript object, which should be assigned to a JavaScript variable.

JSON, in this context, is considered a JavaScript String object, so the mode parameter should be set to null.

If this parameter is set to 'XML', then the returned AJAX response must be formatted as a valid XML fragment. It's our responsibility, as developers, to make sure that the returned data that we are generating on the server side, as part of an on-demand PL/SQL process or a stored (packaged) procedure, is formatted properly. Failing to do so will also fail the AJAX process.

The startTag and endTag parameters

The second and third parameters are only relevant when we are pulling a clip of content from an application page using AJAX. In this case, the first parameter should be set to null and the startTag parameter should be set to a String that marks the starting point of the clipping; the endTag parameter should be set to a String that marks the ending point of the clipping.

Although the startTag and endTag parameters can be set to any string text on the pulled page, they should be unique so that the clipped area will be well-defined. As the clipped code is going to be injected into the AJAX caller page, using an innerHTML property, it's best to start the clipping with an HTML tag and end it with its closing tag. As HTML tags are usually not unique, it's best for us to embed our own unique tags to designate the starting and ending points of the clipping.

Code examples

Now, we can see a complete AJAX call:

var ajaxReq = new htmldb_Get(null, $v('pFlowId'),
'APPLICATION_PROCESS=demo_code', $v('pFlowStepId'));

gReturn = ajaxReq.get();
ajaxReq = null;

The AJAX cycle starts by creating a new instance of htmldb_Get and assigning it to ajaxReq. While doing so, we are setting the req parameter to be 'APPLICATION_PROCESS=demo_code', which means that in this AJAX call we want to invoke an on-demand PL/SQL process called demo_code.

Next, we set two "temporary" variables—x01 and x02—with values from the AJAX calling page. The apex_application.g_x01 and apex_application.g_x02 will be available to us within the demo_code on-demand PL/SQL process.

We are firing the AJAX process by using the get() method. In this case, we are using get() without any parameters, which means that the AJAX returned response will be formatted as a JavaScript String object, hence we are assigning it into the gReturn variable, which I hope you remember is a global JavaScript variable, defined as part of the APEX supplied JavaScript library.

It's considered a "good practice" to set the pointer to the AJAX object to null when it ran its course. That allows the web browser engine to collect its memory and avoids memory leaks. In our example, we assign null to ajaxReq.

Let's review the following code:

var ajaxReq = new htmldb_Get(null, $v('pFlowId'),
'APPLICATION_PROCESS=filter_options', $v('pFlowStepId'));
gReturn = ajaxReq.get('XML');
ajaxReq = null;

In this example, we are calling an on-demand PL/SQL process called filter_options. We are setting the value of the page itemP10_SEARCH using the add() method, so it will be available to the filter_options on-demand process.

We are firing the AJAX process by using get('XML'). This means that the AJAX server response, which will be assigned to gReturn, must be formatted as a valid XML fragment. It is our responsibility, as developers, to make sure that the returned information will be formatted properly within the filetr_options on-demand process. Otherwise, the AJAX process will fail.

In the following example, we are using AJAX to clip content from one of our application pages:

var ajaxReq = new htmldb_Get('prev_cal',$v('pFlowId'),null,20);
ajaxReq = null;

In this case, we are using the first parameter of htmldb_Get to determine that the AJAX returned data will be injected into a <div id="prev_cal"> element, using its innerHTML property. The third parameter—req—is set to null, as we are not invoking any on-demand PL/SQL process. The fourth parameter—page—is set to 20. This is the page ID that we want to pull.

In the next line of code, we are using the add() method to set the value of the page item P20_CALENDAR_DATE (on the pulled page) to the value of the page item P40_PREV_MONTH (on the AJAX calling page).

Next, we fire the AJAX process using the get() methods. The first parameter is set to null as it's not relevant in this case. The second parameter is set to '<cal:clip>' and the AJAX process will start clipping the HTML code of page 404 from this tag. The clipping will end with the <cal:clip> tag, the value of the third parameter.

Restrictions with AJAX pulling content

When using AJAX to pull content from another application page, we should avoid clipping code that defines active page elements such as page items, buttons, or pagination components (which I'll address separately, as we can overcome this restriction).

When we create a page element on an application page, this element associates specifically with the page it was created on. This element can't be activated—i.e. submitted or POSTed—on any other page other than the one it was created on, unless we take special measures to allow it.

While clipping the HTML page code, the AJAX process includes all the code between the start and the end tags in the second and third parameters of the get() methods. The clipping process can't differentiate between a code that renders data and code that renders active page elements. The clipped code is injected into the calling AJAX page, using the innerHTML property of one of its DOM elements. If we are not careful enough it can include code to active page element(s). This/these element(s) will be rendered on the AJAX calling page. However they can't be used on their new location, as I mentioned before. If such an element is referenced on the AJAX calling page it will produce an error message saying that this element can't be found on the current page (as in the APEX metadata tables, it's associates with a different page, the one it was created on).

However, sometimes we do need to use page items on the pulled page to use them with the page logic, for example in a WHERE clause or as a starting date to a calendar. One such way of using these page items is to make sure that they are laid out outside the content area we are going to clip. Another option is to not render them on the page. We can do that by conditioning their display to Never. The APEX engine allocates a proper variable for the page item, which can be referenced in the page logic. However, the page item itself will never be rendered on the page, hence its code can't be clipped as part of the AJAX call.

Pulling report with pagination

One of the more common uses of the AJAX capability to pull content from another application page is to pull the content of a report, for example, displaying a filtered report, based on a search term, without submitting the page.

This use of AJAX requires special attention as APEX reports may include, by default, some active elements that need to be taken care of. One element is the option to highlight rows in the report. Another is the report pagination element. Both of these elements include JavaScript functions that use the report region ID (the APEX engine internal number, not the one represented by #REGION_STATIC_ID#). Unfortunately for us, the APEX engine hardcode the region ID into the HTML code of the application page. Moreover, the pagination element also uses the page hidden item pFlowStepId, which holds the application page ID. This value, naturally, is not the same on the AJAX calling page, which runs the pagination element, after it was pulled, and on the original report page, which holds the report query, in which the pagination parameters has a meaning.

A very simple solution to these problems will be to avoid the elements that cause them. Just don't use the report highlight feature and avoid pagination.


The getAsync() method, introduced in APEX 3.1, extends the htmldb_Get functionality to also include an asynchronous AJAX request. An asynchronous AJAX request means that the client side initiates an AJAX request and sends it to the server. The JavaScript code flow continues without waiting for the server-side response. It's up to us, as developers, to monitor the status of the server response and act accordingly.

GetAsync() accepts a single parameter, which represents a function that will be fired each time the server-side response status changes. This is not a regular JavaScript function. It's actually a value of a property—onreadystatechange—of the XMLHttpRequest object that was created by GetAsync() and was assigned to a JavaScript global variable called p. We can use this p variable each time we need to reference the XMLHttpRequest object or one of its properties.

One of the XMLHttpRequest object properties we need to reference, while using asynchronous AJAX request, is the readyState property. This property reflects the status of the server-side response. It can have five different values, starting with 0, and sequentially growing to 4, which state that the server-side response has been completely accepted by the client side. In most cases, this is the status that interests us. However, each time the value of readyState changes, the function stored in onreadystatechange—the function that we used as the parameter of GetAsync()—is fired. Hence, this function should include a code that can handle all the readyState status values and take the proper action for each of them (or as it may be the case for a status other than 4, doing nothing).

Another XMLHttpRequest object property we can use is the responseText. In APEX context, p.responseText holds the server-side AJAX response and we can use it on the client side, just like we are using the synchronous AJAX response.

(For more resources on Oracle, see here.)

The pVar function

The function that we are using as the pVar parameter of GetAsync() can be defined as inline code or as an external (to the getAsync() method) function.

The following is an example of using inline code:


In this case, the function doesn't do anything, regardless of the readyState value.

In the following example, we are using an external function:


function checkAll() {
switch (p.readyState) {
case 1: /* The AJAX request has been set up */
case 2: /* The AJAX request has been sent */
case 3: /* The AJAX request is in process */
case 4: /* The AJAX request is complete */
gReturn = p.responseText;
. . .

In this example, the checkAll function treats the various values of readyState differently. At the beginning of the AJAX process, when readyState is changed to 1, it calls a function called setStatusBar(). It actually ignores the readyState changes to 2 and 3, and when readyState is changed to 4, which is the state that the AJAX request was completed, it calls the clearStatusBar() function and assigns the server-side response to the global gReturn variable. The rest of the code can be of any logic you need to implement using the AJAX request result.

The function we are using as the pVar parameter is not a regular JavaScript function and it can't accept parameters. However, it can call a second JavaScript function, this time a regular JavaScript function, which can accept parameters. For example:


function checkAll() {
if (p.readyState == 4) {
var p_page = $v('pFlowStepId');

function checkOnPage(p_page) {
. . .

Always remember that the checkAll function will be fired for every status change of readyStatus. This means that if the logic of the function is meant to be run only after the AJAX request is completed, it should be conditioned, as in the above example.

It's important to understand the principles and the reasoning behind the processes we are dealing with here. Please don't try to find any real logic in the above specific code. The checkAll, setStatusBar, clearStatusBar, and checkOnPage functions are all figments of my imagination, and I'm only using them to make a point.

Namespace for the APEX AJAX framework

In version 3.1, APEX started to implement a namespace strategy with its supplied JavaScript library to ensure smooth operation with other external JavaScript libraries. The apex_ns_3_1.js file contains the current APEX JavaScript namespace definitions, and it includes a namespace definition for some APEX AJAX elements – apex.ajax.

In some demo code out there, you can see a reference to apex.ajax.ondemand(). This method is a wrapper to an APEX asynchronous AJAX call. The method accepts two parameters. The first is a string parameter that includes the name of the on-demand PL/SQL process we wish to invoke in the AJAX process. As we are dealing with an asynchronous AJAX call, the second parameter points to the onreadystatechange function—this function is fired each time the readyStatus value changes. Usually this function processes the server-side response.

You are encouraged to review the apex.ajax namespace in the apex_ns_3_1.js file to learn more about it.

AJAX support on the server side

So far, we covered the client-side aspects of the AJAX call. Now it's time to review the options available to us on the server side. These options should implement the logic we are seeking in the AJAX call.

Application on-demand PL/SQL process

The htmldb_Get third parameter—req—allows us to define the server-side PL/SQL process we want to invoke as part of the AJAX call. This process must be an application level On Demand: Run this application process when requested by a page process type of process. For short, we'll just call it on-demand PL/SQL process.

In future versions of APEX a full page level on-demand PL/SQL process might be implemented, but for now (APEX 3.2 and earlier), on-demand processes are available to us only on the application level, although we can call them from the page level.

The on-demand PL/SQL process is a regular PL/SQL anonymous block, and it can contain any valid PL/SQL code we need to implement the logic of the server-side AJAX call.

The following is a simple example of an on-demand PL/SQL process code:

l_status varchar2(10);
select 'non-unique' into l_status
from my_table
where col1 = apex_application.g_x01;
when NO_DATA_FOUND then
l_status := 'unique';


This process returns the value 'unique' if the value of apex_application.g_x01 is not found in col1 of the my_table table; it returns 'non-unique' if the value already exists in the table.

In the addParam section of this article, we mentioned the 10 pre-defined, AJAX related, global package variables that we can use with the AJAX process. At the client side, we reference them by x01 to x10. At the server side, as we are in a PL/SQL anonymous block, we must use a proper syntax for these AJAX related global package variables and properly qualify them. We have two options to do that. The first is to use the real name of the package in which they were defined—wwv_flow. In this case, we can reference wwv_flow.g_x01 to wwv_flow.g_x10. The second option is to use a pre-defined synonym of the wwv_flow package—apex_application. In this case, we'll reference apex_application.g_x01 (as in the example code) to apex_application.g_x10.

In the addParam section of this article, we mentioned the 10 pre-defined, AJAX related, global package variables that we can use with the AJAX process. At the client side, we reference them by x01 to x10. At the server side, as we are in a PL/SQL anonymous block, we must use a proper syntax for these AJAX related global package variables and properly qualify them. We have two options to do that. The first is to use the real name of the package in which they were defined—wwv_flow. In this case, we can reference wwv_flow.g_x01 to wwv_flow.g_x10. The second option is to use a pre-defined synonym of the wwv_flow package—apex_application. In this case, we'll reference apex_application.g_x01 (as in the example code) to apex_application.g_x10.

The on-demand PL/SQL process must be able to return the AJAX server-side response—the result of the PL/SQL logic—to the JavaScript function that initiated the AJAX call. We can do this by using the Oracle supplied procedure htp.prn. In this context, you can treat the htp.prn procedure as the return statement of the on-demand PL/SQL process.

Unlike the return statement of a function, you can call the htp.prn procedure as many times as you need, and the AJAX server-side response will be compiled from all the htp.prn calls (the response will be a concatenation of all the htp.prn calls).

Stored (packaged) procedure

Although using an application level on-demand PL/SQL process is the most common way to implement the AJAX server-side component, the htmldb_Get() constructor also allows us to invoke stored (packaged) procedures, which were defined outside the APEX environment, as part of the AJAX server-side logic. We can use the htmldb_Get() sixth parameter—proc—to name the stored (packaged) procedure we want to invoke, and the seventh parameter—queryString—to pass the needed parameters to the procedure (as we described in the htmldb_Get() section).

As with all the stored (packaged) procedures we want to invoke, from within our APEX application, the AJAX invoked procedures should also be "APEX compatible", i.e. they should use bind variables and the v() / nv() functions to access APEX items and Session State values.

Handling errors in the AJAX process

The APEX AJAX framework doesn't excel in error handling. The AJAX process doesn't generate an error report on the APEX level. That means that the APEX application is not stopped due to an error in the AJAX process. It is up to us, as developers, to inspect the server-side response and determine if the AJAX process was successful.

One indication of a failed AJAX process is a server-side response of null. This will happen if the APEX engine was not able to run the server-side logic, such as in the case of security or privileges issues (including APEX authentication, authorization, and page access control failures), or any other error that the APEX engine found in the PL/SQL code that doesn't generate a specific database error. In cases where database or Web server (mod_plsql) errors were generated, the server-side response will include them but no other error message will be issued.

Debugging a failed AJAX process

Debugging a failed AJAX process should include several stages. The first one is to use the JavaScript alert() function to display the server-side returned value. If we are lucky, and the returned value includes an error message, we should resolve this first. If, however, the returned response is empty we should move to the next step.

We should determine if the communication between the client side—the specific AJAX calling page—and the server side is working properly. We can do that by setting the on-demand PL/SQL process to a very minimal and simple code. For example:

htp.prn('Hello from the server side');

If the returned value includes this message, the AJAX communication is working fine. If, however, the returned response is still empty it probably means that you have a security issue. The most common error in this category is to initiate an AJAX call from a public page alongside using page 0, which Require Authentication, as the page parameter in htmldb_Get(). The solution in this case is very simple. Replace page 0 with $v('pFlowStepId').

Using $v('pFlowStepId') as the default page parameter for htmldb_Get(), as we recommend, will prevent this type of error.

If the AJAX communication is working fine, and no specific error message is returned from the server, but we are still not getting the AJAX server-side response we expect, it usually means that the PL/SQL code is not working properly. One of the common problems in this case is a syntax error with a bind variable—the code contains a bind variable name that doesn't exist. No error message is generated, but the PL/SQL code doesn't work. In these cases, I recommend that you copy the PL/SQL code to the APEX SQL Commands utility and try to debug it in there. This is also what you need to do if the AJAX process returns a wrong response from the application logic point of view.


In this article, we reviewed the AJAX framework within APEX. We learned about the basic principles of the AJAX technology and how APEX implements them using both synchronous and asynchronous mode of communication.

Further resources on this subject:

You've been reading an excerpt of:

Oracle Application Express 3.2 - The Essentials and More

Explore Title