Never write down your New Year resolutions

It's a brand new year, and I'm back from a short holiday, refreshed, re-invigorated and ready to make  some positive changes in 2013.

Now you should never write your New Year's resolutions down, particularly where somebody else might see it.

After all, 88% of all resolutions end in failure, so do you really want to leave a record to remind yourself at the end of the year of all the things you promised to do, and never managed to achieve?

Despite that, here's my list of personal goals for the year.

1. Start playing pennant squash again  

Late last year I started playing squash socially against one of the young guys at work after not touching a racquet in more than 10 years. While I was left barely able to walk, and redder than a ripe cherry by the end of the first match - I really enjoyed it. We've played several times since, each time the quality of the match has noticeably improved and I'm running around the court with much less effort.

We are getting pretty serious now, both armed with new racquets, and gearing up to play in a competition in March. Between now and the competition start we need to join a club, play a few grading games and get match fit. To that end, I've bought a pedometer and am trying to do 10,000 steps a day - squash is the only sport I know of that specifically covers vomiting in the rules.

2. Rebuild the AUSOUG website

This is something I volunteered to do, and have been working in most evenings over the last year, but not published yet. The aim is to go live in late February.

So far, I've built a content management system in APEX to maintain the website, produce eBulletins, schedule events, process conference paper submissions and the like. All that has been straight forward.

The website itself is built using Oracle Portal 10.1.4 which is much more challenging (read annoying). It's quite amazing that there are so many similarities with APEX, yet it is such a dog of a product.

3. Write an article or two for a User Group magazine

It's a real challenge facing all the Oracle user groups these days to get people to submit articles. So I'm going to do my bit - what about you?

If you're interesting in submitting an article just email me at the address below, I'll pass it on to the AUSOUG editor. We share articles with UKOUG and ODTUG, so you can get published all around the world.

4. Do a series of blogs on integrating Twitter Bootstrap into APEX

I've been using Bootstrap with APEX 4.0 (and also Oracle Portal) for about a year now, and am keen to see how much easier it is to implement in the latest APEX 4.2.1 release, which now supports grid based layouts.

Topics I plan to blog on:

• Basic integration into page templates
• Specific templates like standard reports, possibly IRR styling
• List templates combined with Bootstrap components (Navbar, toggable tabs)
• Plugin integration (AJAX enabled carousel)
• whatever else I encounter along the way.

Anyway, that's enough resolutions from me - hope you have a great year.

Responsive Design in Oracle APEX - not just APEX 4.2

One of the hottest topics in web design in 2012 has been Responsive Design - the ability for a web page to dynamically re-size to fit on a desktop, tablet or mobile phone.

The reason it is such a hot topic is simple; there is no such thing as a standard screen size any more. You can't even pick a common denominator such as 1000 pixels wide and say "that will cover most computers, from laptops up to the massive dual screen desktop you told your boss you couldn't be without".

Sometime in the near future mobile devices will out-number desktops. According to which source you read it's somewhere between 2014 and 2016. Which means you should start factoring it into your applications today.

I'm really excited that APEX 4.2 supports declarative responsive design, but it may be a while before I get to  use it. In the mean time, there's no reason why you can't build it into your applications in older APEX versions today. It just requires a little more thought and ingenuity.

That's part of what I'm presenting on at the AUSOUG 2020 Foresight Conference in Perth next week.

Here's a couple of screenshots from a very simple responsive application written in APEX 4.0.

And here's the same page on a mobile phone

What's that charming expression about "eating your own dog food".

Loading images into Oracle XDB

There are a number of different ways to load images (and other filetypes) into Oracle XDB. You can use FTP, WebDAV or PL/SQL.

For APEX installations using the Embedded PL/SQL Gateway (EPG) images are loaded onto the server, and then loaded into XDB using PL/SQL script apxldimg.sql. With a couple of minor edits to change destination folders, the apxldimg.sql script can be re-purposed for your own applications.

The script creates a database directory, and then reads a XML file (default is imagelist.xml) to identify the directories to create and files to upload.

To generate your own imagelist file, you can use the following Windows batch script from a command window directing the output to a filename.

   imagelist.bat > imagelist.xml

@echo off
@REM *****************************************************************
@REM * File:    imagelist.bat
@REM * Author:  Mark Lancaster May 2012
@REM * Purpose: For APEX applications using the Embedded PL/SQL Gateway.
@REM             Generate XML list of directories and files suitable
@REM             for loading into Oracle XDB.
@REM             
@REM             Refer APEX file "apxldimg.sql" for example usage.
@REM
@REM             Direct output to a file e.g.  imagelist.xml
@REM *****************************************************************

setlocal enabledelayedexpansion

echo ^<upload^>
echo     ^<directories^>

REM list directories with relative path

for /F "tokens=*" %%c in ('dir /ad /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<directory^>!relpath!^<^/directory^>
)

echo     ^<^/directories^>
echo     ^<files^>

REM list files with relative path and leading slash

for /F "tokens=*" %%c in ('dir /a-d /ogn /b /s') do (
  set abspath=%%~fc
  call set "relpath=%%abspath:%cd%\=%%"
  set relpath=!relpath:\=/!
  echo         ^<file^>^/!relpath!^<^/file^>
)

echo     ^<^/files^>
echo ^<^/upload^>

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 http://apex.oracle.com/pls/otn/f?p=PLAYPEN:2025

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.

apex_dynamic_actions_4_0:

setValue : function(){
        ...snipped...
    
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lResult = eval(lJavaScriptExpression);
            if (lResult){
                this.affectedElements.each(function() {
                    $s(this, lResult);
                });
            }
        }
    },

apex_dynamic_actions_4_1:

setValue : function(){
        ...snipped...
    
        } else if (lSetType === 'JAVASCRIPT_EXPRESSION') {
            lJSExpression = eval(lJavaScriptExpression);
            this.affectedElements.each(function(i){
                // 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
      ,a.application_id
      ,a.application_name
      ,a.page_id
      ,a.page_name
      ,a.attribute_01
      ,a.dynamic_action_name
      ,a.action_name
      ,a.action_sequence
      ,a.dynamic_action_event_result
      ,a.affected_elements
  from APEX_APPLICATION_PAGE_DA_ACTS a
 where a.action_code = 'NATIVE_SET_VALUE'
   and a.attribute_01 in ('JAVASCRIPT_EXPRESSION','STATIC_ASSIGNMENT')
   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.

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 apex.oracle.com 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:

<html>
<head>
    <title>#TITLE#</title>
    #HEAD#
    <link rel="stylesheet" href="#WORKSPACE_IMAGES#playpen.css" type="text/css">
</head>

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 http://apex.oracle.com/pls/otn/wwv_flow_file_mgr.get_file?p_security_group_id=441224701954687600&p_fname=add.gif
  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:

data:[<MIME-type>][;charset=<encoding>][;base64],<data>

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.

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:

• Tom Kytes slides
• Real World Performance Series Videos

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 11.2.0.2.

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