Monday, October 3, 2011

Not happy Jan - follow up

A while ago I posted Not Happy, Jan! about the changed behavior of list templates for hierarchical lists.

The changed behavior in APEX 4.1 meant my atypical hierarchical template which produced JSON  instead of HTML lists no longer worked.

I've re-implemented the template to handle APEX 4.1. To see it in action, and how it works go to

Tuesday, September 27, 2011

Dynamic Actions - Changed behavior in APEX 4.1

APEX 4.1 Dynamic Actions have a changed behavior over 4.0 version when executing a Static Assignment or JavaScript Expression against multiple elements.

Reading that back makes it sound sufficiently obscure that it may not be an issue for you. Hopefully not, but let's run through the changed behavior, the reasons behind it, how to detect it and how to resolve it.

Firstly, some background.
As part of a demo on dynamic actions, I set the value of 3 items using a dynamic action, as shown below:

This worked perfectly fine until I upgraded to 4.1, where the first items' value was set, and the remaining item values are set to null.

Comparing the Dynamic Actions JavaScript for setValue (JavaScript Expression) code for 4.0 to 4.1 reveals why the change occurs.

setValue : function(){
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lResult = eval(lJavaScriptExpression);
            if (lResult){
                this.affectedElements.each(function() {
                    $s(this, lResult);
setValue : function(){
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lJSExpression = eval(lJavaScriptExpression);
                // Set the value to the first affected element, null the rest.
                lValueToSet = (i===0?lJSExpression:'');
                $s(this, lValueToSet, null, lSuppressChangeEvent);
As the comment indicates, the changed behavior is by design.

Reasons behind the change
Anthony Rayner from the APEX development team was kind enough to explain the reasoning behind the change:
This is indeed changed behaviour as you describe, introduced in 4.1. I don't know if you've seen this already, but we added a section to the release notes on just this. I have copied this below for your convenience:

4.9 Dynamic Action Set Value with Multiple Affected Elements

In dynamic actions, prior to Application Express 4.1, it was possible to use the Set Value action to set multiple Affected Elements, but only to the same value, not different values. In Application Express 4.1, we introduced the following new capabilities to set multiple values from dynamic actions:
  • Action 'Set Value' > Set Type 'SQL Statement' - You can now define a SQL Query to return between 1 and 100 columns, which is used to set the 'Affected Elements' in the order defined (1st column is used to set 1st affected element, and so on). Previously, this only supported querying for 1 column, but could be used to set multiple affected elements to that same 1 column value.
  • Action 'Execute PL/SQL Code' - A new 'Page Items to Return' attribute that sets any page item values on the page to their updated values in session state.
With the introduction of some built-in options to retrieve multiple values, Application Express no longer supports the ability to retrieve 1 value that could then be used to set multiple page items. This was done for simplicity and usability. If you had defined a 'Set Value' dynamic action that set multiple Affected Elements to the same value, here is how you can fix this, according to the 'Set Value' dynamic action's 'Set Type':
  • Static Assignment - Define additional 'Actions' for each Affected Element, setting them all to the same value.
  • JavaScript Expression - Define additional 'Actions' for each Affected Element, setting them all to the same value.
  • SQL Statement - Alter your SQL Statement such that it selects the same column value, with different column aliases for each Affected Element.
  • PL/SQL Expression - Consider changing this over to use the 'Execute PL/SQL Code' action type, in conjunction with the 'Page Items to Return' attribute. The 'PL/SQL Code' would need to ensure the page item's value is updated in session state, such that 'Page Items to Return' returned the updated value.
  • PL/SQL Function Body - Consider changing this over to use the 'Execute PL/SQL Code' action type, in conjunction with the 'Page Items to Return' attribute. The 'PL/SQL Code' would need to ensure the page item's value is updated in session state, such that 'Page Items to Return' returned the updated value.

So really this change was prompted by the introduction of support for setting multiple different values from 1 'Set Value > Set Type (SQL Statement)'. This introduced a 'positional' notation of sorts for this set type, whereby affected elements map, by position to values returned from the SQL Statement. We felt with this introduction, that it would be simpler not to continue to support the setting of multiple affected elements to the same value, but rather that if this is desired, that one of the above techniques should be used instead, depending on set type. We felt this in the long run was simpler and more intuitive and that hopefully this would have minimal impact when upgrading from 4.0.
Hmm, that makes the reasoning behind the change a lot clearer. I really like the idea of being able to use a single dynamic action to update a bunch of fields at once.

For example, you could change the shipping method for goods delivery. Using a Dynamic Action with a "PL/SQL Code" action type you could update the shipping charges, subtotal amount, expected delivery date and terms & conditions all at once. Nice!

How to detect if this impacts you
Running the following query in 4.0 or 4.1 will identify any dynamic actions that are affected:

select a.workspace
 where a.action_code = 'NATIVE_SET_VALUE'
   and a.affected_elements_type = 'Item'
   and instr(a.affected_elements, ',') > 0;

Adjusting for the changed behavior
In my example I already have the value I want to assign, the most appropriate alternative here is to use a JavaScript Expression. Instead of defining a single Dynamic Action to affect multiple items, just create multiple actions each updating a single item as shown below.

Sunday, September 11, 2011

APEX CSS Repository: How to include background images easily using Data URIs

One of the challenges with loading CSS files into the APEX CSS Repository is dealing with references to background images.
In the screenshot from my demo site, I'm using a series of background images for the toolbar items.

Typically when you are hosting your own web-server, your web assets (images, CSS and JavaScript files) are loaded onto the web-server.
The CSS file will contain references to background images like:
.ico-add {
    background-image: url( ../images/icon/add.gif ) !important;
.ico-delete {
    background-image: url( ../images/icon/delete.gif ) !important;

The CSS rules reference the background-image by specifying a relative path to the image location.

If you loaded the CSS file into the APEX CSS Repository, you need to adjust the path to the image location to  use an absolute path like:
.ico-add {
    background-image: url( /ux/playpen/resources/images/icon/add.gif ) !important;
.ico-delete {
    background-image: url( /ux/playpen/resources/images/icon/delete.gif ) !important;

Adding a path alias to your web-server config to resolve the path on the server completes the task:
<Context path="/ux"       docBase="C:\playpen\web\ux" reloadable="true" crossContext="true" />

It's more complicated on Hosted APEX websites
On a hosted APEX website like you don't have access to the web-server so you need to load the files into the APEX Repository. Let's also assume you don't have access to 3rd party web-server to host the files either :)

So now your images, JavaScript and CSS files are loaded into the Repository and the page templates have been updated to include the JavaScript and CSS files by using the #APP_IMAGES# or #WORKSPACE_IMAGES# substitution tags:

    <link rel="stylesheet" href="#WORKSPACE_IMAGES#playpen.css" type="text/css">
Ideally, you would like to use the #APP_IMAGES# or #WORKSPACE_IMAGES# substitution tags within the CSS file:
.ico-add {
    background-image: url( #WORKSPACE_IMAGES#add.gif ) !important;
.ico-delete {
    background-image: url( &WORKSPACE_IMAGES.delete.gif ) !important;
But APEX 4.1 and earlier versions do not substitute either the hash tag or substitution variable alternatives shown.

This leaves two less desirable options:
  • embed the stylesheet directly in the page template, which resolves the substitution tags but adds overhead to every page, or,
  • replace the #WORKSPACE_IMAGES# reference with it's resolved name in the CSS file
    e.g. #WORKSPACE_IMAGES#add.gif becomes
    This makes the application less transportable.

Data URIs to the Rescue
Data URIs provide a way for data to be included inline in a web page as though it was an external resource. The data URI format RFC 2397 is pretty simple:

Encoding our previous example looks like:
.ico-add {
    background-image: url(data:image/gif;base64,R0lGODlhEAAQAOZ3AHe8YHO6XDSBLIe4cF+eUKDZlq/fppfVi6vUnLTZparUnW61WFaYSJbUi4/CfTmFMH2+Z73ktpbTiXeuY0CJNnnBZNTrz4e4cbXbqI7MhHzDa7jdr1OWRZXUimyqWkKNNrvcr3i0ZIm+eIrCec7pyVCZQXy6ZKbQloXNeZPNiKDZlWirU3DBY0KLOODy3LbdrESMOrLbqInIf42+e53YknG+X3rGa2WrVYHMdXS3XLfgr7PZpZfMhTSCLMXowJTTirfaqrTapqXbm2+1WLXfrKbPlm+1WX25a3WsYT2INGajVmuqWH6yaXyxZzeEL0OPOHqvZUeOPLPdqYO1bZnRi5jMinm9bsvmw37Hb6TOlGmlWH25an28Zn7BabTdqobJejyHMozLgV2cTY7Sg5LFgICzatzx2K/fpX+5bXS3W5jMh4rPfYbJfNfu0oS2bmqsW77itleeRXK5W5jLhpfHhXC4Wf///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAHcALAAAAAAQABAAAAengHeCg4SFhnduZU0Th4MXWVcWJBtoWocDIC4RQgVnPiMEhQNAZjQpGQ0dYzpHDIR0bSo/MnZ2KBpYRHGDUy8GYWxWtW8AAVRkUYJMcAdftc92dV0KLYJQUms40LV1EAhJgkhVEjYsN9x1cjwzD4MrXhU1ANwLOUFPhEpbMcV1RkNpdnjoUUhMCAxquJiYk2CJgEMcSjg4UUTEh4eN7sCgAMZJxo+DAgEAOw==) !important;
.ico-delete {
    background-image: url(data:image/gif;base64,R0lGODlhEAAQAOZwAPaEbPqdi7dKK+6Sj+BpavGdlvCMd/eTfvmSfvnLxPm5r9pkYfJ3Y99qavqjke2TjupcUMZVQsxbTPWmofi2qONuY8tZSbxQMviMdvSOgbxLMNFeU+l/eOl7cL5PNsFNM/3c2PNzXfupnPiUf/qah/q6rulbT/KtqvzHuupmXOqDff3b1PCRf89hU8RQNNpjX+R4cfivpOZaUu5mUvCFcNZgVPjDvvWjn/aroverocxSPPqTf/SinvN5Y++GfuljW/iGcPqrneNrYeh6deBlXPzUzfSgnuJwZrhKKuZWTOJ7dcBTOvaCaPCDe+6MgddiXN1qaPBkVu6KdfnTzPGcluZYTNxkY7pLLtBPPt5qZ8dXRfWvpb5TONddUfOno71RNeZWUO6EffeRfrhOLttpZPWsofGOevS0qttWS+ZZTtljXeddWe5qXuxfVOlZTf///wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACH5BAEAAHAALAAAAAAQABAAAAeigHCCg4SFhnBQVgtqh4MNDzZTCWdHG4cEJyAlDgEiKB0ShQReKyQsZiMIGBQVWoQqRQEHUgC1AD0xWINZW0EGbcDBDBkcS4JkCjs0b8zNbmwFHoIvOUBMzc4pVBeCT05iIW7i4yZNSleDaDhRM+NuVTI3LoQtQmVtEG5pSWA8NUiFLBCZ4OPHmjBGugg4FEHHkAEDYHxY2AgOly8axlTcOCgQADs=) !important;

While it looks much more complicated and bloated, Data URIs provide a performance benefit because they reduce the number of HTTP requests, allowing pages to load faster.

Most modern browsers support data URIs:
  • Firefox 2+
  • Opera 7.2+ - data URIs must not be longer than 4100 characters
  • Chrome (all versions)
  • Safari (all versions)
  • Internet Explorer 8+ - data URIs must be smaller than 32k
For IE6 and IE7 you need to use a separate browser dependent CSS file, but the rapid decline in usage for these legacy browsers will remove the issue over time.

How to encode Data URIs in your CSS file
I'm using CSSEmbed by Nicholas C. Zakas, a Java command line utility that reads in a CSS file, identifies the images referenced within, converts them to data URIs, and outputs the resulting style sheet.
The basic syntax is:

java -jar cssembed-x.y.z.jar -o <output filename> <input filename>
To learn more about the utility see Nicholas's blog on Automatic data URI embedding in CSS files.

Saturday, August 20, 2011

Oracle Real World Performance Day

I've just got back from attending the InSync11 Oracle Real World Performance Day held in Sydney, Australia.

Absolutely inspiring day, with 3 renowned Oracle performance experts in Tom Kyte, Andrew Holdsworth & Graham Wood sharing the stage the entire day, giving their insights from the developer, dba and system architect perspectives.

The format was very conversational, with a discussion about the principles behind a topic, examples the presenters have encountered and then demonstrations of how changing values or coding approaches impacts performance. There is nothing quite like seeing a live demonstration of row by row processing compared with set based processing, or how setting open_cursors too high can bring down performance.

Materials from the presentations are available:

The day started with a data warehouse story of an ETL performance issue, with the networking team, dba and BI teams in a room, each saying their piece of the puzzle was fine; the database was idling, network was performing fine and the ETL process hadn't changed, but overall performance had recently degraded...

Deja vu, at this point a DBA colleague from work and I looked across at each other - we had been it this exact position a few months earlier. The discussion from the experts around this was insightful; if only they had been present when we were tearing our hair out trying to solve the issue.

The day only got better from there on. Tom even mentioned the APEX 4.0 and Library Cache Latch Contention issue in Oracle DB

If you get the opportunity to attend a Real World Performance Day as they tour the globe, I highly recommend it.

Tuesday, August 2, 2011

APEX 4.1: Not happy, Jan!

"Not happy, Jan!" is a line from a popular TV advertisement that entered the Australian vernacular.
The advertisement showed a manager expressing her displeasure towards the fleeing figure of one of her staff, as she ran having neglected to book an ad in the upcoming year's Yellow Pages.

While I'm looking forward to using the new APEX 4.1 release and welcome the many new features available, my "Not happy, Jan!" moment came when the recently upgraded website broke my application navigation.

For a number of years, I've been using hierarchical list templates to output JSON rather than HTML. This has been very useful allowing me to generate data for a JavaScript tree component using the APEX builtin conditional list entries and authorization to control displaying tree nodes.

Changed functionality now means that the "Between Sub List Items" template attribute is not output unless the corresponding "Sub List Template" attribute contains a "</li>" tag.

Why is this necessary?
I guess its to do with changed functionality to support dynamic SQL lists or mobile themes, but couldn't the developer just leave the template field blank for some templates?

Its possible to code around the issue using plugin functionality, but for now my site is broken until I update it.

I think the APEX team are terrific and do a really great job, but perhaps they got this one wrong.

Monday, July 18, 2011

Packt Publishing discounts on Oracle books

Just noticed that Packt Publishing are celebrating the publication of its 50th Oracle book.

They are offering some attractive discounts on all their Oracle books.

  • Buy any Oracle print book and get 20% off
  • Buy any Oracle eBook and get 30% off

There is also a competition to win a year's free subscription to their online library, so a good time to pick up a book or two.

Saturday, June 18, 2011

Updated Code Bundle for "Oracle Application Express 4.0 with Ext JS"

For those of you patiently waiting for the updated code bundle, its finally available.

The bundle includes the template application shown above.
All you need to do is install the application, including the supporting objects, and then upload the web assets and Ext JS library to your web server.

When you download the code, just extract the highlighted code bundle, the other folder is the original code bundle which can be discarded (I don't know why they included it).

One last change...

Add the following CSS rules to the playpen-book.css file:

.ux-table input[type="text"] {
    background-color: #FFFFFF;
    background-image: url(/ux/extjs/resources/images/default/form/text-bg.gif);
    background-repeat: repeat-x;
    border: 1px solid #B5B8C8;
    padding: 2px 3px 0;
    line-height: normal;

.ux-table input[type="text"] {
    height: 18px; 
    vertical-align: middle;

Gremlins, they're everywhere.

Thursday, March 31, 2011

Book Released: Oracle Application Express 4.0 with Ext JS

I'm very happy to announce that my book Oracle Application Express 4.0 with Ext JS is now officially published.

A printed copy turned up in the mail recently, and I'm very pleased with the final result.

 The table of contents for the book as well as a sample chapter is available on the Packt website.

Sue Harper blogged about the three phases of book writing:

  • before the book - Seems like a good idea
  • during the book - Not a good idea, will this ever end? Who's idea was this anyway?
  • after the book - That was easy ... what's next?

After spending most evenings and weekends writing until midnight for the last 15 months, I've definitely done the first two phases, but haven't quite reached the last phase yet.

Still to do
The code bundle available to download on the Packt website includes code from each of the chapters.
I will be updating it in the next couple of weeks to:

  • include the Ext JS library, so the stand-alone examples work when the bundle is unzipped
  • add the APEX TEMPLATE application, CSS, JavaScript and image files used in the book
  • include a few extras that didn't make it into the book.
I'll write another blog post when the updated code bundle is available.

Then after that my demo site definitely needs an overhaul, it's looking sadly neglected...

Thursday, March 10, 2011

Bookmarklets to extend APEX 4.0

APEX allows you to add help text for your pages at a page level, and also an item level.
Your only given a plain textarea to add your text, as you can see in the following screenshot.
Hint APEX team: can we change this in the next version?

One way to replace the textarea with the APEX Rich Text Editor is to use a Bookmarklet, which allows you to embed JavaScript code in a browser bookmark.
Then simply click the bookmarklet to convert the textarea to a Rich Text Editor when needed:

Simply drag the following links onto your browser bar to save the bookmarklets:
The source code for the Page Level Help bookmarklet is:
javascript: (function(){
    function getScript(url, success){
        var script = document.createElement('script');
        script.src = url;
        var head = document.getElementsByTagName('head')[0];
        var done = false;
        /* Attach handlers for all browsers*/
        script.onload = script.onreadystatechange = function(){
            if (!done && (!this.readyState || this.readyState == 'loaded' || this.readyState == 'complete')) {
                done = true;
    /* Code to execute after script loaded */
    function run(){
        apex.widget.ckeditor3("#F4000_P4301_HELP_TEXT", {
            "language": "en",
            "toolbar": "Basic",
            "toolbarStartupExpanded": true,
            "skin": "kama"
    /* Main */
    if (!window.CKEDITOR) {
        getScript('/i/libraries/ckeditor/3.2/ckeditor.js', function(){
    else {

All we are doing is adding the ckeditor.js file when it's not present, and then converting the specified page item to use the basic CKEditor.
For the advanced version replace "toolbar": "Basic" with "toolbar" : "Full".

You may need to adjust the item reference if your not using Application Express, or a translated version.

Friday, March 4, 2011

APEX_PUBLIC_USER password expires on 11g

Just read Scott Wesley's post on APEX Listener issues which was being caused by Oracle 11g automatically expiring passwords.

The cleanest solution is to use an unlimited password lifetime profile, and assign to APEX_PUBLIC_USER.

This is done simply by:

  PASSWORD_LIFE_TIME UNLIMITED    /** this sets unlimited lifetime */


Sunday, January 30, 2011

Web page optimization with Page Speed

I've only recently become aware of Google's Page Speed Firefox  add-on integrated with the Firebug extension.

Page Speed is Google's equivalent of Yahoo's YSlow, also a Firefox add-on that needs the Firebug extension to work.
Like YSlow it tests the performance of your webpages against a set of criteria, giving an overall score for a page as well as advice on how you can improve your page performance.

Not surprisingly many of the recommendations are similar to Yahoo's, but some are different, giving you an additional perspective on website performance.

As well as telling you how to improve page performance, there are tools built-in to assist you. Page Speed automatically optimizes JavaScript, CSS, and image files referenced from a page when you run the analysis, saving them to a local directory. Drilling into a rule, such as Minify CSS tells you the potential reduction in size as well as a link to the optimized version.

Page Speed also has mod_pagespeed, an open-source Apache module for Apache 2.2 that automatically optimizes web pages and resources on them. I haven't investigated this, but it would be interesting to experiment with.

I use both YSlow and Page Speed, each tool has something unique to offer. Page Speed is a useful addition to your toolbox for web development.