Wednesday, July 15, 2015

Another new APEX-based public website goes live

Another APEX public website I worked on with Northgate Public Services has just gone live:

This is a website to handle appeals against parking fines and other traffic/environmental fines issues by London local authorities.

It is built on APEX 4.2 using a bespoke theme that uses the Bootstrap framework.  A responsive design has been used so that the site works as well on a mobile phone as on a desktop.

Rumours that appeals against any parking tickets with my car's registration number on them are automatically approved by the system are completely unfounded.

Monday, February 02, 2015

Why won't my APEX submit buttons submit?

I hit a weird jQuery issue today that took a ridiculous amount of time to solve.  It is easy to demonstrate:

  1. Create a simple APEX page with an HTML region
  2. Create 2 buttons that submit the page with a request e.g. SUBMIT and CANCEL
  3. Run the page
So far, it works - if you press either button you can see that the page is being submitted.  

Now edit the buttons and assign them static IDs of "submit" and "cancel" respectively.  Run the page again - the buttons no longer work!  If you check for Javascript errors you will see that you are getting "Uncaught TypeError: object is not a function" (in Chrome) or similar.

Apparently this is a known issue with jQuery (see 
Forms and their child elements should not use input names or ids that conflict with properties of a form, such as submit, length, or method. Name conflicts can cause confusing failures. For a complete list of rules and to check your markup for these problems, see DOMLint.

Wednesday, September 17, 2014

Ignoring outliers in aggregate function

This is another aide-memoire for myself really.  I want to calculate the average load times per page for an application from timings stored in the database, and see which pages need attention. However, the stats can be skewed by the odd exceptional load that takes much longer than a typical load for reasons that are probably irrelevant to me.

Here is a fictitious example:

create table timings (id int, timing number);

insert into timings
select rownum, case when rownum=50 then 1000 else 1 end
from dual
connect by rownum <= 100;

This example has 99 timings of 1 second plus an oddity of 1000 seconds.

A simple average gives a skewed picture:

SQL> select avg(timing) from timings;


It suggests that users are waiting 11 seconds on average for a page to load, when in fact it is usually 1 second.

The analytic function NTILE(n) can solve this.  This divides the set of results into n "buckets" and then tells us which bucket a particular value falls into.  If we do that with a suitable number of buckets, say 10, we will be able to exlude the highest 10% and lowest 10% of the values:

SQL> select avg(timing) from 
  2  (select timing, ntile(10) over(order by timing) bucket
  3   from timings)
  4  where bucket between 2 and 9;


Thursday, September 11, 2014

Why use CASE when NVL will do?

I've found that many developers are reluctant to use "new" features like CASE expressions and ANSI joins. (By new I mean: this millennium.)

But now they have started to and they get carried away.  I have seen this several times recently:

    CASE WHEN column1 IS NOT NULL THEN column1 ELSE column2 END

Before they learned to use CASE I'm sure they would have written the much simpler:

    NVL (column1, column2)

Now I now that NVL is Oracle-specific and CASE is portable, but (a) we aren't ever going to be porting our millions of lines of PL/SQL, and (b) I can guarantee they didn't do it for that reason. They have got a new hammer and now everything looks like a nail.

Saturday, August 30, 2014

Handy pre-defined Oracle collections

Note to self:

  1. SYS.DBMS_DEBUG_VC2COLL is a handy pre-defined TABLE OF VARCHAR2(1000)

Both are granted to public.

Thanks to Eddie Awad's blog for these.

Tuesday, June 10, 2014

Hiding APEX report pagination when trivial

The users are quite happy with pagination like this:

However, they don't like it when the report returns less than a pageful of rows and they see this:

(Fussy, I know).

This is one way to do it.  First, ensure that the pagination area itself is identifiable.  I put a div around it with a class of "pagination":

Then add some Javascript to the "Execute when page loads" attribute of the page:

$('div.pagination').each(function() {
    if ($(this).find('td.pagination a').length == 0
       && $(this).find('div.msg').length == 0
       ) {

It looks for pagination areas that contain no links and no “reset pagination” error message, and hides them.

The Javascript could go into the page templates to fix the issue across all pages.

Monday, May 26, 2014

APEX boilerplate translation

APEX provides a mechanism for translating applications into other languages:

Applications can be translated from a primary language into other languages. Each translation results in the creation of a new translated application. Each translation requires a mapping which identifies the target language as well as the translated application ID. Translated applications cannot be edited directly in the Application Builder.
Once the translation mappings are established the translatable text within the application is seeded into a translation repository. This repository can then be exported to an XLIFF for translation.
Once the XLIFF file is populated with the translations, one file per language, the XLIFF file is uploaded back into the translation repository. The final step is to publish each translated application from the translation repository.
A translated application will require synchronization when the primary application has been modified since the translated version was last published. Even modifications to application logic will require synchronization. To synchronize, seed and publish the translated application.
I have never used this method, but I have worked on a number of APEX applications that can be translated into other languages by other means.  Essentially this is a matter of "soft-coding" all boilerplate such as region titles and item labels - holding them as data in tables and selecting the appropriate values at run time.

For item labels there is a neat solution involving APEX shortcuts.  First we define a table to hold the item labels in all the languages we support something like this:

101P1_EMPNOENGEmployee identifier
101P1_EMPNOESIdentificación del empleado
101P1_EMPNOFRIdentifiant des employés

Now we need a function to get the label text for an item in the current language, something like:

function label_text (p_app_id number,
                     p_item_name varchar2,
                     p_language varchar2)

Then we can create an APEX label template with "Before label" HTML like this:
"LABEL_TEXT" is a reference to an APEX shortcut which we can now define using PL/SQL function body:
return translate_pkg.label_text(:APP_ALIAS,'#CURRENT_ITEM_NAME#',:AI_LANGUAGE)
(AI_LANGUAGE is an application item defining the user's preferred language.) All we now need to do is use the new label template on all our page items and leave the item's label attribute blank.

It would be nice if we could do something similar for region titles, but unfortunately APEX shortcuts are not currently supported in region titles. Instead we have to make do with creating a hidden item e.g. P1_EMP_REGION_TITLE that we set to the desired title, and then set the region title to &P1_EMP_REGION_TITLE. That still leaves the matter of translating error messages and of course the actual data, but I won't go into that in this post.