Disclaimer: The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Saturday, 16 June 2007

AJAX retrieval using Javascript Object Notation (JSON)

Introduction...Typically, most of the AJAX examples out there for APEX either use plain text or xml for data transmission. This post details how to implement AJAX functionality using Javascript Object Notation or JSON, retrieving multiple items from an application process. JSON, or the fat free alternative to XML is a simple, lightweight syntax for representing data and can be easily integrated into an APEX application. Due to its light weight, it has performance advantages over using XML and is also very easy to understand and manipulate on the client side.

This post will show how to create a simple example of AJAX with JSON, where a user selects either 'Car' or 'Bike' from a select list, then either car or bike 'models' will be populated into 2 page items.

Pre-requisites...In order to parse the JSON text and create the javascript objects in your javascript code, you will need to download the JSON open source parser. Once downloaded, it must be made available in your APEX environment. The best way to do this is to save the file into the following directory on your web server:

ORACLE_HOME\Apache\Apache\images\javascript

And then reference it by adding the following to your 'Page Template > Definition > Header', just after the '#HEAD#' substitution string:

Alternatively, if you have restricted access to the web server (as with apex.oracle.com), then upload the 'json.js' file as a static file, via 'Shared Components > Static Files', ensure you associate it with your application and then add this reference to the page template:

<script src="#APP_IMAGES#json.js" type="text/javascript"></script>

Note: The static file method will not be as fast in execution as copying the file directly on the server.

Step 3: Create the following 3 page items:- The activator will be a select list, where you either select 'Car' or 'Bike'. Selecting one of these options will invoke the 'onChange' event specified and call the 'Get_Multiple_Items()' javascript function, specified in step 5. Set the following:Type = Select ListItem Name = P1_ACTIVATORRegion = AJAX - JSON RegionList of Values Query = STATIC2:Car;Car,Bike;BikeLabel = ActivatorElement > HTML Form Element Attributes = onChange="get_Multiple_Items();"

So this is where we need to encode the result into the correct JSON syntax, so as to be parsed successfully in the client.

JSON syntax is exactly the same as the syntax used to define normal object and array literals in javascript. For a detailed description of the syntax, see the JSON homepage. In my example, if the user selects 'Bike', the following string would be returned by the process:

{"model":["Suzuki","Ducati"]}

So here we have a single member called 'model', containing an array of the two return values, 'Suzuki' and 'Ducati'.

Step 5: Create a javascript function on the pageWe now need to create the function that will setup, send and receive the request from the server. This will use the built-in 'htmldb_Get' ajax function to call the application process called 'RETURN_MULTIPLE_ITEMS', created in step 4. Set the following:Page Attributes > HTML Header > HTML Header =

This calls the function 'parseJSON()' which parses the JSON text to produce an object, and then...

$x('P1_RETURN_ITEM_1').value = myJSONObject.model[0];

The resulting object 'myJSONObject' can then be accessed like any normal javascript object. In this case, we are retrieving the first value from a member called 'model' (which was created in step 4). This value then sets the 'P1_RETURN_ITEM_1' value.

Now run your page and you should be able to see some nice AJAX and JSON in action! Or take a look here.

Conclusion...So it's a pretty simple little example, but I really like how easy it is to manipulate the object client-side. The only downfall is that the syntax to encode the JSON string in the application process takes a bit of getting used to, but I found it fairly easy to get my head around. Ideally, you would have a server-side encoder for this and there are many available, just none for PL/SQL that I've found*. There are lots available for Java, which could potentially be loaded in the database and called via a PL/SQL wrapper.

I have not done this for this example because I felt it was overkill for what I was trying to achieve. To just return a few items like I have done here, manually encoding the JSON text was fairly painless, although I'm sure it could quickly get very painful if creating large, nested structures, where a proper encoder may be more beneficial (and performance gains would be more apparent).

Also, bear in mind my example doesn't really do anything in the application process which warrants a trip to the database (just checking hard-coded strings, which could be done client-side). The purpose of this post was to highlight how to implement JSON with AJAX in APEX so I tried to reduce complexity elsewhere to focus on the main points.

Hope it helps,Anthony.

* Update...Since writing this article, it appears a PL/SQL JSON library has cropped up from France. I have yet to try it but will take a look and blog about it soon.

Nice one, thanks for the info. Yes I have see the APEX Worksheet Demo on Dimitri's blog and also at Oracle Develop where Marc Sewtz demo'd it. Looks good. I really like using JSON for it's simplicity and speed and think it's great that it will be more integrated in the future.

Thanks for pointing that out to me, I need to look at tweaking the blogger theme to give me more width. It's too narrow and I keep on having to put carriage returns into the code to make it display properly, thus leading to issues like this.

When I get time I will sort the theme, in the mean time I have reformatted that code so the issue should not arise again.

What about submitting the form you load with JSOB-formatted data...I did the same then coudn't submit any more the data of my form.As test case, if i do not load the form via Ajax, but simply fill it manually, i could do the submit.

Thanks for the great example on your site. I have implemented it no problems on my side. however, I would like to ask you if you think I could use queries instead of hard-coded values, as to bring me information from my database. It would go something like this:

You should probably change the JSON element name to be something more meaningful than 'model', say 'emp' for example. If you do this, just bear in mind you will need to change the get_Multiple_Items() function to reference 'emp' instead of 'model'.

I am about to post a whole load of demos and update to this example, which will provide a generic JavaScript function to take care of a lot of the more common Ajax use-cases. Thus keeping all the business logic in the database. Keep posted!

This does exactly what I intended it to :). I will now implement it on the other field (EMPLOYEE_NUMBER) so I can populate the fields (Social Security, EMPLOYEE_NAME) when I provide an Employee Number. I will probably have another procedure that brings me the data I want, filtered by the employee number and activate the procedure on blur (like I did with the first).

I really look forward to your examples, as I get more experienced in APEX. Thanks again!

About Me

My name is Anthony Rayner and I work as a Principal Member of Technical Staff with the Oracle Application Express development team. I am based in Devon in the UK. The purpose of this blog is to discuss the web development tool Oracle Application Express and also other Oracle Database Tool technology.