Browser and Session value in APEX

For those of us who have been developing in APEX for a while will understand that the value of an item in APEX is not always what it seems. That’s right; an item has two values in APEX. The first value is the session value that we can store in the database (server side), the second value is the value the end user sees on his screen in the browser (client side). On some occasions these values are the same. On others they are not. The difference in value can lead to some confusion, especially for the new APEX developers.

Let’s start with an example so we understand the problem. This is actually based on a real use case. I changed the item names and queries to make it more universal, but the principle remains the same. The developer in question had a page with two Select lists containing the following queries:


SELECT 1 d, 1 r
FROM dual

And

SELECT 1 d, 1 r
FROM DUAL
UNION
SELECT 2 d, 2 r
FROM DUAL

The second select list (called P55_SELECT_LIST_2), also had a default value of 2.

When the user selects a different value on the select list then the second select list had to change values accordingly.

In order to achieve this the developer had created a Dynamic Action. As event he had a change Event of the P55_SELECT_LIST item. The first action was a set value containing the following query (again made simple for the example):

SELECT 1
FROM dual
WHERE  1 = :P55_SELECT_LIST

And page items to submit he had selected the item P55_SELECT_LIST, and affected items he put P55_SELECT_LIST_2

The last action he set was a refresh action of our P55_SELECT_LIST_2 item.

Our page setup now looks like this:

1pagesettup

The developer tried his application, and when he changed the first select list he saw the second select list being refreshed. Yet, instead of displaying the correct value (1), it displayed the default value.

2items

So what went wrong?

Let’s see what exactly happened to figure that out:

  1. The page was loaded. The first select list had nothing selected, the second select list had “2” selected in the browser. Both items were empty in the session3session
  2. The value of the first select list was changed by the end user
  3. This triggered the change event defined in the dynamic action
  4. The first action “Set value” was executed. The value in the browser was changed. The session value of the item P55_SELECT_LIST  was set to one, because it was set with items to submit. An AJAX request was made to set this value. The value item P55_SELECT_LIST_2 remains NULL.4session
  5. The refresh action was executed; an AJAX request was made to get new values for the select list. Since the SELECT_LIST_2 item has no value in the session, the default value is taken, which is 2!                                                                                                                          5network

The solution was simple. Just remove the refresh action. Then the value is set in the browser correctly.  This is handled by APEX with JavaScript/jQuery. The session value of the item will remain NULL until we submit the item to the database by a page submit, a dynamic action, or by changing the value in an AJAX Callback process.

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.

Forms 11g javascript integration: Call others

Forms 11g holds a lot of interesting new features focused on event-driven architecture, one of these is javascript integration. There are two ways of using javascript with Forms 11g: “call others” and “let others call you”.

Javascript can call code in Forms(“Let others call you”) using the new forms trigger “when-custom-javacript-event”.

This post is going to show you the first one: “call others”, in other words call javascript from your Oracle Forms application.

During the Forms Modernization Seminar I showed a google map that could be manipulated from an Oracle Form. It’s an easy implementation with only a few lines of code(most of the javascript is taken from the api examples on the google code site: http://code.google.com/apis/maps/).

  • Build a little form with one (control) block, one text field(to enter an address) and one button(to call the javascript code).
  • Next step is to create an HTML-page to display the form.

This code puts the form(in an iframe) and the map side by side:
(Click to enlarge)

And it will look like this:

  • The javascript that will be called is put in another file google.js:

  • The only thing to do is creating a “when-button-pressed” trigger in forms to call the javascript function showAddress.
    This is done by a new built-in procedure web.javascript_eval_expr:
  • Copy the HTML and javascript file to the following directory:
    <middleware_home>\user_projects\domains\<domain>\servers\WLS_FORMS\tmp\_WL_user\formsapp_11.1.1\e18uoi\war\
  • Create a new configuration using Enterprise Manager:

  • Make sure the parameter EnableJavascriptEvent is set to “true’ in your configuration!

And the working demo…