(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)
1—obj
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.
2—flow
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.)
3—req
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).
4—page
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.
5—instance
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.
6—proc
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:
'#OWNER#.my_package.my_proc'
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.
7—queryString
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:
'p_arg1=Hello&p_arg2=World'
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.
