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 PL/SQL Process that might or might not return a value
- 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:
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:
- 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:
There are two things here that are worth mentioning:
- HTP.Prn(v_min_salary): here we return the minimum salary back to our page
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.
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.