AJAX programming in Application Express: Are you still using htmldb

Application Express: tips, tricks and best practice

AJAX programming in Application Express: Are you still using htmldb_Get?

Publish date	APEX version	Database version
January 2017	5.1	11.2.0.4 or higher

Sending send separate requests to the server and changing contents of an Application Express page dynamically, using AJAX and Javascript, is pretty normal for most APEX developers. In practice, over the years, several different approaches developed for these requirements. And especially in older applications, we often find the htmldb_Get Javascript function being used.

In the latest Application Express Release 5.1, htmldb_Get has been formally desupported; this is stated in the Release Notes. Actually, this function was never documented; the Javascript API section within the Application Express API Reference never contained an entry for htmldb_Get.

So it's time to get rid of htmldb_Get in your applications. We'll illustrate this with a typical, simple AJAX requirement: When a select list item is being changed, another textarea item will be populated with some information. You will learn how to implement such requirements, either declaratively with Dynamic Actions, or programmatically with Javascript and documented API calls. Having read this, it should be easy for you to apply this to your actual applications and requirements.

The situation: when the select list changes, the textarea should get populated with some information

1. Implementation using Dynamic Actions

Using Dynamic Actions is, for sure, the best alternative: All parts of the solution are done declaratively - within the Application Express Repository. All Javascript logic is then done by Application Express - developers can stay within their world of SQL and PL/SQL. The following few sections show how to create dynamic actions for the above requirement. If you already familiar with using Dynamic Actions, you might skip these and directly continue at manual Javascript programming.

In Page Designer, create a new dynamic action for the P1_EMPNO item, which is the select list.

Create a new dynamic action
Configure your dynamic action in the property editor on the right: Choose a name and specify that the dynamic action should be fired on change of the select list item P1_EMPNO, but only when the selected value is not NULL.

The dynamic action fires "on change", when the value "is not null"
After that, specify the TRUE action of your dynamic action. In page designer, on the left side, click the existing TRUE action (which should be highlighted in red) and configure it in the property editor on the right. Choose setValue as Action and SQL Statement as Set Type. Choose the item P1_EMP_INFO as Affected Element.

TRUE action of the new dynamic action
After that, add the SQL Query which is to be executed. Its result value will populate the (already configured) affected element. In this case (one affected element) your SQL Query should return exactly one column and one row. Don't forget to add the P1_EMPNO item as one of the Page Items To Submit; that is important to send the new selected value to the server.

SQL query for your dynamic action

After saving your changes, you might test your page. After choosing a new value from the select list, the textarea contents will change. This has been configured completely declarative - all Javascript and AJAX functionality has been done by Application Express.

Result: After changing the select list, the textarea contents change

As always in Application Express, the declarative option is typically the best one. All configuration is reflected in the APEX repository - it's available for quality assurance and all kind of automated processes - and you have no dependency to any Javascript library at all. Whenever things within the APEX infrastructure change, declarative features will be migrated safely. Therefore, you should use the other alternative, which is manual Javascript programming, only when dynamic actions do not work or are not appropriate.

2. Manual Javascript implementation

Whenever you have some usage of htmldb_Get in your application, this is actually a manual AJAX implementation. And we see its disadvantages immediately: Some API call or Javascript library is being desupported - and we have to change our application. With declarative dynamic actions, this will never happen.

Before trying the manual approach, delete the dynamic actions you just have created (or use conditions to prevent their execution).

An AJAX implementation always consists of a client and a server part. We will start with the server part, which actually executes the SQL query and sends the results back. When the AJAX operation is only being executed within the context of a specific application page, use an AJAX-Callback on the page itself. That should be appropriate in most cases. Use Application Processes in Shared Components, only if the server part is to be called from multiple pages in your application. This how to document shows both approaches.

To create an Application Process, which can be called from everywhere in the application, do the following:

First navigate to Shared Components and then to Application Processes. Create a new process and choose Ajax Callback as Point. Name your process getEmpInfo.

"Ajax Callback" Application Processes can be used everywhere in the application

Use the following PL/SQL code for your application process.
```
declare
  l_emprow emp%ROWTYPE;
begin
  select * into l_emprow
  from emp 
  where empno = apex_application.g_x01;
  htp.p('EMPNO: '||l_emprow.empno||chr(10)||
        'ENAME: '||l_emprow.ename||chr(10)||
        'SAL:   '||l_emprow.sal  ||chr(10)||
        'JOB:   '||l_emprow.job);
end;
```
If your are doing a DML operation (INSERT, UPDATE or DELETE), return at least something (e.g. "SUCCESS"), so that the javascript code on the client side can determine whether the process executed successfully or failed.

If you are about to use the application process on public pages, without authentication, you must check its authorization settings. Navigate to the details of the process and then to the Authorization section. Make sure that the authorization setting iscorrect. For public pages, it's important that Must Not Be Public User is not selected. If the Ajax Callback is called within a protected context, its authorization must match the authorization settings of the calling components.

Adjust the authorization setting if you want to use the process on a public page
To create a page-specific Ajax Callback, perform the following steps:

In Page Designer, within the structure tree on the left side, click the third tab to see all page processing details. Click on the Ajax Callback entry and open its context menu. Then click Create Process. Page Designer will add an Ajax Callback process with some default values.

Create a new page-specific Ajax Callback using Page Designer

Use the above shown PL/SQL code (the same you would use for an Ajax Callback Application Process). Name your Ajax Callback (again) getEmpInfo.

Set name and PL/SQL code for your Ajax Callback

Regarding the PL/SQL code, the same rules as for Application Processes apply. You should make sure to return at least something. If your Ajax Callback performs some DML, at least return a status message. When done, save your changes.

That concludes the server part of your Ajax implementation. Now the client-side javascript code will be covered: In Page Designer, navigate to your application page, then to the page attributes and to the Javascript section. In Function and Global Variable Declaration, add the following javascript code.

function getEmpInfo () {
  apex.server.process(
    'getEmpInfo',                             // Process or AJAX Callback name
    {x01: apex.item("P1_EMPNO").getValue()},  // Parameter "x01"
    {
      success: function (pData) {             // Success Javascript
        apex.item("P1_EMP_INFO").setValue(pData);
      },
      dataType: "text"                        // Response type (here: plain text)
    }
  );
}

In earlier times, that javascript code, would contain calls to htmldb_Get. But today, we use the documented javascript function apex.server.process. You'll find details about it within the Application Express Documentation: JavaScript APIs.

The first argument to apex.server.process is the name of the Ajax Callback or application process. It's followed by values to be sent to the server, Javascript functions to be executed on success or failure of the Ajax request and finally by an information about the type of the expected response from the server.

The selected value from the P1_EMPNO item must be passed to the server, like it has been done with the dynamic action approach. However, the way how this value is passed to the server, is a bit different:

With the above described dynamic action approach, we simply set P1_EMPNO as the Page Items To Submit attribute. That means that the value is being sent to the server, and that Application Express updates its session state with the new value.

However, in this example, the new value is passed as the X01 attribute - the above PL/SQL code references it as APEX_APPLICATION.G_X01. Thus the value is passed to the server, but Application Express session state will not be updated. The selected value is only available to the PL/SQL code of the Ajax Callback itself. That saves on server resources, updating APEX session state leads to a SQL UPDATE statement followed by a commit being executed. If the new value is not needed in APEX session state, it makes sense to save on this part.

The success argument to apex.server.process contains a javascript callback which will be executed when the Ajax request is successful. In this case, apex.item.setValue is used to update the P1_EMP_INFO item. In practice, there should also be a callback to be executed when the Ajax Request fails - at least to show an error message.

The final step is, to call this new javascript function getEmpInfo(), whenever the end user changes the select list. A nice approach is to use a dynamic action for this. Create the dynamic action as described above - but choose Execute Javascript Code as True action. Then simply add a call to getEmpInfo() as Javascript code. Make sure that Fire on Initialization is set to No.

Execute Javascript code with a dynamic action

Another option is to set the event handler programmatically - then you will have no declarative part at all. Navigate to the page attributes, then to the Javascript section. Add the following line to Execute on Page Load.

$("#P1_EMPNO").on("change", getEmpInfo);

Finally save your changes and run the page again. It should work similar to the dynamic action example - the only different is the implementation. As already stated, dynamic actions allow to work fully declarative, whereas the second approach does it all with custom Javascript code.

Result: After changing the select list, the textarea contents change

Summary

The best approach to implement AJAX requirements within an APEX application is to use dynamic actions. These are fully declarative, the APEX repository can be used for Quality Assurance and there is no dependency to any Javascript library. However, if requirements get more complex and would lead to a huge amount of dynamic actions being created, it might be better to implement the logic with custom Javascript code. When doing this, always use the documented apex.server.process javascript function to commumnicate with the APEX server. Do not use htmldb_Get any more.

... back to blogs.oracle.com/apex