AJAX in APEX

AJAX is becoming important in the world of web applications. APEX has provided us a very easy way to create an AJAX process, by using dynamic actions. Using PL/SQL Actions in Dynamic Actions to communicate with the database without submitting the page will suffice in most cases, but the downside is that the code is not very re-usable, and when you want to write a plug-in you simply don’t have access to Dynamic Actions. In this blog you will learn how to code your own AJAX process.

An AJAX process in APEX consists out of three parts

  • The JavaScript code that calls the AJAX PL/SQL Process
  • The PL/SQL Process that might or might not return a value
  • The JavaScript code that catches the return value and possibly does something with it

In APEX there are three ways to create an AJAX process from JavaScript:

  • The htmldb_get() method: undocumented but this used to be the only method available (without installing external libraries)
  • jQuery.ajax(): since jQuery was added to APEX, it has been quite common to use this method. It’s well documented on the jQuery homepage, but the downside is you need to write more code
  • apex.server: this new APEX API has been recently added (I believe at APEX 4.2). It is actually a wrapper of jQuery.ajax(), so it supports the same functionality with some additional APEX specific features. It is thoroughly documented in the APEX documentation, and this is the reason I prefer this method, and I will explain how you too can use it

The first thing we do is create a test application. In our case we have a table called “JOBS” that looks like this:

jobstable

In my jobs table I just inserted one job with a salary of 2800 of an unknown currency.

In our APEX application we have an Item of the type select list where the user can select a job, and then the minimum salary will be filled in.

Our page looks like this:

page

Next we write our JavaScript code.  This includes our change event and the apex.server.process . Double click your page name to go to the page definition, and scroll down to “Execute when page loads”.

javascript call

  • AJAX_GET_MIN_SALARY is the name of our future AJAX process.
  • X01 is the variable we pass, in this case the value of our #P17_JOB_ID item
  • Finally we declare that our expected return type is plain text. If we don’t do this, then by default the function expects a JSON string returned. Furthermore we declare in this function what we do with this return data. The return data will be delivered asynchronous, meaning we will get this data from our AJAX Callback function as soon as the AJAX Callback process is ready.

Now we can create our AJAX_GET_MIN_SALARY Ajax Callback process. Just right click on Ajax Callbacks . Click “Create” and select PL/SQL. Here we can put our PL/SQL code:

ajax_process

There are two things here that are worth mentioning:

  • TO_CHAR(apex_application.g_x01): this is how we catch the variable that is passed from our JavaScript code. We use TO_CHAR to identify that it’s a character.
  • HTP.Prn(v_min_salary): here we return the minimum salary back to our page

There, all done!  Let’s test out our application, shall we? Before you do anything it’s best to open the developer toolbar in the browser. In Chrome you can do this by pressing ctrl+shift+J.  It’s  a good practice to reload the page and to check if any JavaScript errors pop up on the console. If our JavaScript code shows no errors in the console go to the ‘Network’ tab, and select a job in the application.

items

You will now see www_flow.show appear. Click it. There are two tabs here that are vital to investigating this function for debugging, if needed. The first is the header, it shows what data is send to our AJAX Callback function.

toolbar

The second tab that’s important is our Response tab. It tells us what data is send back from the PL/SQL Process. If you remember our PL/SQL Process you will notice that we did not include an exception for when no data was found. Select “null” as job and you will get an error. If you then check out the response of the AJAX call you will see it gives our ORA error.

error

If you managed to read this far then you have gained some insights on how you can create your own AJAX function using the new APEX JavaScript API, how it works and how you can debug it should not everything go as planned.

Get triggering element in dynamic action

Last week, I ran into a problem with a dynamic action in APEX 4.0.

I have a series of 8 images of persons which I generate with PL/SQL. They are all speakers on a conference. Every image is clickable and should insert the name of the speaker in the database.

I gave a class “speaker” to every image. The ID of the image is the primary key of the person in our database. By doing this, I am able to create a dynamic action which I can bind to the JQuery selector ".speaker".

In this case, I wanted to use AJAX to perform my actions so I created an AJAX callback insert_speaker which contained the PL/SQL block with the insert command. Based on the PK of the person, the name of the speaker will be inserted into the database, together with some extra information.

In my dynamic action, I added a true action of type “Execute JavaScript code”. Here, I ran into a problem: how do I get the ID of the image the user clicked on?

On the internet, I found two solutions with JQuery to get the ID of the triggering element of an event:

$(this).attr("id");

and

event.target.id;

There is a little difference between these two, found it out here, so I tested both and concluded I needed event.target.id.

I completed the JavaScript block with the AJAX request, the ID parameter and the other lines and tested the page.
Everything worked perfectly. Well done, I tought…
…untill I tested my page with Mozilla Firefox. Firebug returned an error ‘event is not defined’.

Hmm…and it all worked fine in Google Chrome (I use this browser for application development)? Strange.
After a while of searching, I figured out there is another solution, something specific to APEX:

this.triggeringElement.id;

Sounds logical, but it isn’t when you have no idea this line can be used in APEX.

The only official documentation I could find about this, is the help text of the textarea labeled “Value” in the When block of a dynamic action.
Help text

When I changed

event.target.id;

to

this.triggeringElement.id;

in my code, the page works fine in both Google Chrome and Mozilla Firefox.

For further development with APEX 4.0: make sure you test your pages in multiple browsers!
The example above states  very well that not all browsers interpret code the same way!

Oracle Forms 11g and Apex using external events

I remembered an old post of Roel Hartman where he integrated a form into apex.
He used a part of OraFormsFaces by Wilfred van der Deijl: the CommunicatorBean.
Using this CommunicatorBean forms could react on “external messages”.
Now with Forms 11g reacting on external events, this CommunicatorBean isn’t necessary any more(sorry Wilfred).

This is how I did it using external events…
First things first: set up the advanced queueing mechanism.
Check this tutorial which includes setting up advanced queueing.
I created a little form based on emp(nothing fancy)…

The new feature in forms:

With the following properties:

What should forms execute when this event happens?
This has to be specified in the When-event-raised trigger.

In this case we retrieve the payload and extract the empno from it.
The empno is used to set the default where clause on the block.
When there’s an empno on the queue, forms will query that employee.
That’s all for the forms part…
Now I created a little Apex page with two regions:

The Employee details will be our form.
So I put our form in the HTML using an iframe:

Using a “select list” it’s possible to select an employee.

This is the result:

Now the purpose of the select list is to choose an employee and show the detail information in our form.
In order to do this, the select list calls a javascript function.

This javascript function is created in the HTML header

The code behind this:

<script language=”JavaScript” type=”text/javascript”>
function getEmployee (){
var emp = $x(‘P2_EMPNO’);
// send request
var ajaxRequest = new
htmldb_Get(null,&APP_ID.,’APPLICATION_PROCESS=get_emp’,0);
ajaxRequest.add(‘P2_EMPNO’,emp.value);

// get response
ajaxResult = ajaxRequest.get();
ajaxRequest = null;
}
</script>

This javascript function calls an application process and uses the empno as parameter.
The application process put the empno on the queue.

When changing the select list, the form is queried

This is a solution to integrate forms into another application whether it’s Apex, ADF or another web applicaton.
When it can put something on the queue, forms can react on it.
And yes, I could do it using the javascript feature in Forms 11g. I know…
And for Apex it’s probably a better solution, as we can skip the AQ part and make calls to and from forms in Javascript.

Using AJAX within Oracle Application Express

Everybody is already convinced about the strength and benefits of AJAX. There is only one thing that keeps bothering me when I’m using AJAX within Apex. For every AJAX call, you have to define a new application process.

I worked out a generic solution where you only need to create one application process for all your AJAX calls!
How to implement this generic solution
Step 1: Within Oracle Application Express
a) Create an application process and give it the name AJAX_DYNAMIC_PROCESS


b) Create an application item with the name AJAX_PROCEDURE

Remark:
Instead of writing your PL/SQL code for the AJAX call in the application process, you have to write it on the database as a procedure (or a package procedure) Ex: pck$ajax_examples.prc$first_example

Step 2: Javascipt

Now we can put (by using javascript) the name of this procedure in the application item AJAX_PROCEDURE. The execute-immediate will execute the pck$ajax_examples.prc$first_example procedure.

I have written some javascript functions that simplify the use of AJAX:


Now implementing an AJAX call becomes as easy as this…

The ajaxResult variable will contain the result of your AJAX call!

This method is only recommended in an inTRAnet environment because your database is open to SQL Injections. You can also build in extra security, for example by checking your AJAX call againt a database table to make sure the call is permitted.

In a next blog I will explain how you also can generalise the passing of params to the Ajax call.

ApEx AJAX Text Filter

A while ago, a customer asked me to develop a report page with an option to search on a title. For every character the user typed, the results should immediately be adjusted. I did some research and came out on an AJAX text filter.

I couldn’t find a step by step tutorial about this so I made my own.


1. Things to do before we start

Create an Apex application
Create a new page
Create a report region

2. Making the text filter

2.1. Create the necessary fields
Create a text field on your ApEx Page and name it P1_TEST1 and place it above your report.. This field will serve as our search field.

Create an application item F138_SEARCH_NAME. This item will hold our search string.

2.2. Add the JavaScript function to your page

The javascript code can be found here.

The ‘searchTitle‘ string indicates which application process must be called to search for any possible results.

Edit your text field P1_TEST1 and put the next code under HTML Form Element Attributes: ‘ onkeyup=”f_TestOnDemand()” ‘

2.3. Create an empty html region

Create an HTML region, give it the title ‘Result Region‘ and place it under your search field. The region source is a ‘div’ tag with “test2” as id.

JavaScript will replace the content of the div with the matching results.

2.4. Create the application process

Create an “On Demand” application process “searchTitle” in the Shared Components. Later on, Javascript will refer to this process.

The code for the process can be found here.

You will see that in the query, the search item refers to the application item.

3. Result

I made a working example available here.

Apex: integration of new Google API’s

Google has released 2 new features on it’s AJAX Search API:

  • Blog bar
  • News bar

Both bars allow you to incorporate dynamic content into the layout of your site.

I have tried to implement both features into ApEx and they were succesful. In this blog you can read a step by step guide explaining how to integrate the Blog Bar into your apex application.

The Blog Bar is offered in horizontal and vertical styles. You have the ability to let your users see specific Blog Search results or you can select only blogs specified by yourself.


The first thing you need to do is to register with Google.
Then you can go to Google Blog Bar API page and run the wizard to get the right example code and key. Save the example code and the key so you can use it later.

Now we can implement it into your ApEx page:

Paste your css and javascript code into the html header of your ApEx page but leave the next code out of it:

<div id=”blogBar-bar”>
<span style=”margin:10px;padding:4px;”>Loading…</span>
</div>

Now create an empty html region and paste the code you left out of your header into it:

Run your page:

The following example shows a horizontal style(only 1 line):

A working example can be found here.

The following example shows a vertical style:


A working example can be found here.

More info about customizing the google blog API can be found here.