Saturday, 16 June 2007

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.

Thursday, 7 June 2007

Introduction...Remember switching those conditions to 'Never' on various APEX components and forgotten where and when? Use the awesome APEX repository to have a tidy up.

How...The following query will return all of the following APEX components that have a condition set to 'Never':Breadcrumbs, computations, list entries, list of values, navigation bars entries, branches, processes, regions, report columns, validations, buttons, items, tabs and shortcuts.

The query allows for either viewing components by application, or by workspace. To view by application enter the application ID for the app_id bind variable, to view all applications in a workspace just leave app_id as null.

The APEX repository views that are being used return different information for the different component types, so I have used generic place holders for the results of an inline view, which does a series of unions on queries retrieving data from the views. I have tried to ensure the what is retrieved for each component type is descriptive enough so as to be able to easily locate the component in your application builder and delete it. A sample result is shown below:

By the way, in writing this post, for an easy and accurate way to get a definitive list of all the apex components that could be conditionally displayed, I went to the 'APEX_DICTIONARY' view and applied the following predicate:

upper(column_name) = 'CONDITION_TYPE'

This returned a list of APEX views that have columns with the name 'CONDITION_TYPE'. These views were then used as a basis for my main query.

Tidy app, tidy mind.

Further information...Patrick Wolf's excellent post and presentation slides on 'The Power of the Apex Repository'.

If, like me you have worked on APEX projects with restricted access to the server where the Oracle HTTP Server is installed, there are a few things you can do to view information stored on the server, directly via the browser.

You can look at:

1. Image filesA new feature of APEX 3.0 allows browsing of images that are part of the APEX distribution and available for use, located in following directory:

ORACLE_HOME\Apache\Apache\images\menu

From the 'Application Builder', from the shortcut menu click on the 'Find' torch, pictured below.

This will load a popup, where you can search a whole load of useful APEX stuff, of interest here is the 'Images' tab. Click this, ensure 'Show' is set to 'Standard Images' and you will be able browse the images currently available. By clicking on an image, APEX will also provide you with a link to be used in your application.

Note: This technique can also be used to view cascading style sheets available to you. These CSS shown here are solely the theme independent ones and thus always available to you regardless of theme.

If however, you wish to view a specific CSS file via the browser, read on...

2. CSS and Javascript filesYou can look at other files that are contained within the images directory on the server (not just images!), via the virtual path specified in the following configuration file:

ORACLE_HOME\Apache\modplsql\conf\dads.conf

This can be done directly from the browser by removing everything after the port number and replacing it with a path to the file you wish to look at. This would typically be used to look up a '.css' or '.js' file that the current page references, something like:

src="/i/javascript/htmldb_html_elements.js"

Then use this reference at the end of your url, as shown in the picture below.

3. Activity LogsAlthough you cannot see the exact access_log files from the browser, there is a report which shows similar information, which can be accessed via:

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.