Another new APEX-based public website goes live

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

https://londontribunals.org.uk/

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.

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 http://bugs.jquery.com/ticket/1414): 

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.

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;

AVG(TIMING)
-----------
      10.99

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;

AVG(TIMING)
-----------
          1

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.

Handy pre-defined Oracle collections

Note to self:

1. SYS.DBMS_DEBUG_VC2COLL is a handy pre-defined TABLE OF VARCHAR2(1000)
2. SYS.KU$_VCNT is TABLE OF VARCHAR2(4000)

Both are granted to public.

Thanks to Eddie Awad's blog for these.

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
       ) {
    $(this).hide();
    }
})

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.

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:

APP_ID	ITEM_NAME	LANGUAGE	LABEL_TEXT
101	P1_EMPNO	ENG	Employee identifier
101	P1_EMPNO	ES	Identificación del empleado
101	P1_EMPNO	FR	Identifiant 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 for="#CURRENT_ITEM_NAME#">"LABEL_TEXT"

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

HGV Levy

The UK government has introduced a new service for foreign lorry drivers to pay a levy to use UK roads here:

https://www.hgvlevy.service.gov.uk/

It was built by my current employer, Northgate Information Solutions. Guess what technology it runs on?
We had a lot of interesting challenges when building this:

• Compliance with UK Government styling and standards
• Responsive design to work on desktops, tablets and mobiles
• Accessibility
• Usable with Javascript disabled
• Translated into 5 European languages

You can read more about this project here on the Northgate website.

It's a drag...

It really used to be a "drag" putting together a set list for my band using my Oracle database of songs we play: the easiest way was to download all the songs to an Excel spreadsheet, manipulate them there, then re-import back into the database.  I tried various techniques within APEX but none was easier than that - until now.  I have just discovered jQuery UI's Sortable interaction, and in a couple of hour was able to build exactly what I'd always needed:

(That is is one of our recent set lists: I never claimed we were cool - we're called The Love Handles after all.)

The full list of songs (minus any already selected) appears in the first list.  Then there are 4 more lists corresponding to sets (we usually have 2 sets, with a 3rd to hold "spares" in case we under-run or get asked to play on.  Occasionally we play 3 sets.) I can now:

• Add a song to a set by dragging it from the Songs list to the desired Set list
• Remove a song from a set by dragging it from the Set list back to the Songs list
• Move songs from one set to another by drag and drop
• Move a song up and down within a set by drag and drop

It really was incredibly simple to achieve.  First I needed to include the relevant jQuery library into my page:

#IMAGE_PREFIX#libraries/jquery-ui/1.8.22/ui/minified/jquery.ui.sortable.min.js

(This is shipped with APEX but not included in applications by default.)

Then I constructed the HTML for each list as follows:

<ul class="connectedSortable">
<li class="set1"><input type="hidden" name="f01" value="123" />Land of 1000 Dances</li>
...
</ul>
<input type="hidden" name="f01" value="0" />

Explanation:

• Each of the 5 lists has the same class "connectedSortable".  This is what allows us to drag and drop between them.
• Each list item consists of a hidden item containing the song's ID in the database, and the song title to be displayed.  All the hidden items have name="f01" so that they will all appear in an APEX array g_f01 when the page is submitted. (The class "set1" is only there so that I can colour each list differently.)
• After each list I put another hidden item also named "f01" with a value of 0.  This acts as a separator between the lists when processing the g_f01 array - if the value is 0 I know that I have reached the end of a list and am starting the next.
• Originally, I built each list as a report region, with a bespoke region and report template.  But after a while I realised it was simpler to use PL/SQL regions to dynamically render the HTML.

Now for the Javascript that makes it all work:

  $(function() {
    $( ".connectedSortable" ).sortable({
      connectWith: ".connectedSortable"
    }).disableSelection();
  });

That's it - just that.

Finally I just had to write some PL/SQL to process the array when the page is submitted:

declare
   l_set_no integer := 0;
begin
   apex_collection.create_or_truncate_collection ('SETLIST_EDITOR');
   for i in 1..apex_application.g_f01.count
   loop
      if apex_application.g_f01(i) = 0 then
         -- End of set
         l_set_no := l_set_no + 1;
      else
         -- Song: add to current set
         apex_collection.add_member
            ( 'SETLIST_EDITOR'
            , l_set_no
            , apex_application.g_f01(i)
            );
      end if;
   end loop;
end;

I'm using a collection here, which I populated on initial page load from the database. I could have worked on the set list table directly, but this allows me to submit the page if needed for other reasons without saving or losing the changes.

You can see the final result on this apex.oracle.com demo.

I think jQuery is now my second-favourite developer tool (after APEX of course).

SQL Developer: add a "child tables" tab to table definition

I always like to extend SQL Developer's table definition by adding a tab that shows the "child tables" for each table - i.e. the tables that have a foreign key to the table in context.

I have lost count of how many times I have started work at a new environment or on a new PC and had to set this up from scratch, so thought I'd document it here for next time!

First, create a file containing the following XML:

<items>
  <item type="editor" node="TableNode"  vertical="true">
      <title><![CDATA[Child Tables]]></title>
         <query>
             <sql><![CDATA[select cons.table_name, cons.constraint_name
                      from all_constraints cons
                      where cons.constraint_type = 'R'
                      and (cons.r_constraint_name, cons.r_owner) in
                                         (select pk.constraint_name, pk.owner
                                          from   all_constraints pk
                                          where owner = :OBJECT_OWNER 
                                          and table_name = :OBJECT_NAME
                                          and constraint_type in ('P','U'))]]>
             </sql>
         </query>
   </item>
</items>

Then, in SQL Developer go to Tools->Preferences... and open the Database node and select user Defined Extensions.

Click Add Row and on the new row set Type to EDITOR and Location to point to your XML file.

That's it.  You may just need to restart SQL Developer to make it work.

I am indebted to Sue Harper's blog for the detailed instructions on adding tabs to SQL Developer.

Another tab I like to add is one to show the errors for an invalid view definition:

<items>
 <item type="editor" node="ViewNode"  vertical="true">
    <title><![CDATA[ERRORS]]></title>
      <query>
         <sql><![CDATA[SELECT
     ATTRIBUTE, LINE
     || ':'
     ||POSITION "LINE:POSITION", TEXT
FROM
     All_Errors
WHERE
     type  = 'VIEW'
 AND owner = :OBJECT_OWNER
 AND name  = :OBJECT_NAME
ORDER BY
     SEQUENCE ASC
         ]]></sql>
      </query>
 </item>
</items>

(Update 2019: SQL Developer now includes an Errors tab for views already, so this is redundant.)

APEX conditions and performance - part 2

This is a follow-up to my previous post APEX conditions and performance.

Roel Hartman has followed up my post by doing the due diligence and testing the performance of different APEX condition types.  His findings back up what I just asserted. (But I wasn't just guessing, my assertions were based on facts I learned long ago from someone in the APEX team via the Oracle APEX forum - so long ago that I cannot now find the conversation!)

As I said previously, for a more complex condition than "item=value" you will often have to use a PL/SQL expression condition, and that's fine.  However, it is not uncommon to see a page where there is a group of several items that all have the same complex condition.  For example, perhaps if some complex condition is true then we need to collect the user's bank details and contact details - maybe 12 items.  The same complex condition is applied (and has to be evaluated) for each of the 12 items.  In this sort of case it may be appropriate to add child regions to the form region, and apply the complex condition just once - to the child region that contains this group of conditional items.  A suitable region template (perhaps even "no template") can be applied to these new child regions so that the appearance of the page is not changed by adding them.

Example 1: original page

Region: My Form
- Item: Name
- Item: Date of Birth
- Item: Bank Account No (complex condition)
- Item: Bank Sort Code (complex condition)
...
- Item: Address Line 1 (complex condition)
- Item: Address Line 2 (complex condition)
...
- Item: Something else

Example 2: page using child regions

Region: My Form
- Subregion: Personal Details
-- Item: Name
-- Item: Date of Birth
- Subregion: Bank and Address details (complex condition)
-- Item: Bank Account No
-- Item: Bank Sort Code
...
-- Item: Address Line 1
-- Item: Address Line 2
...
- Subregion: More stuff
-- Item: Something else

Example 2 achieves the same result as Example 1 but only evaluates the complex condition once instead of many times.  As well as improving performance this also makes your page simpler: if the condition changes in the future you only have one instance to change.

APEX conditions and performance

Having recently seen some examples of non-optimal code in APEX conditions (e.g. item rendering conditions and read-only conditions)  I thought it worth writing a few words about them.  By putting them here on my blog I can refer to them in future rather than writing them again. Also I may receive useful feedback from readers to improve or correct my advice.

For a very simple condition such as “when the value of item P1_JOB is SALESMAN” there are many condition types to choose from including:

1) Value of item/column in Expression 1 = Expression 2

Expression 1: P1_JOB

Expression 2: SALESMAN

2) PL/SQL Expression

Expression 1: :P1_JOB = ‘SALESMAN’

3) Exists (SQL query returns at least one row)

SELECT NULL

FROM DUAL

WHERE :P1_JOB = ‘SALESMAN’

(Yes I have actually seen this one!)

4) PL/SQL Function returning Boolean

Begin

  If :P1_JOB = ‘SALESMAN’ then

    Return true;

  Else

    Return false;

  End if;

End;

… and so on.

The correct type to use in this case is (1) because it is the only declarative method; all the others require APEX to run dynamic PL/SQL to evaluate the condition.

To evaluate (1) APEX will do something like this:

IF v(expression1) = expression2 THEN…

To evaluate (2) APEX will do something like this:

-- Parse expression1 to look for item references e.g. :P1_JOB

…

EXECUTE IMMEDIATE expression1 INTO l_result USING v(item_name);

IF l_result THEN …

(In fact it will have to use DBMS_SQL not EXECUTE IMMEDIATE because the number of bind values can vary.  The pseudo-code above is merely an educated guess by the way.)

(3) and (4) will involve similar code to (2).

True, the cost of executing the dynamic PL/SQL won’t be huge, but bear in mind that a page may have many items with conditions like this, and the small penalty is then incurred many times per page load, which adds up particularly on a frequently used page.

Of course, if you need a more complex condition such as “Job is manager and location = London” then you cannot use method (1), you will need to use method (2) (not (3) or (4) please!).  Always look for the simplest and most appropriate condition type for your needs.

Also, if the condition involves a function call e.g. (emp_pkg.is_eligible(:P1_EMPNO) = ‘Y’) and is used in more than one place it is probably better to execute the function call once, assign the result to a hidden page item, and then use a type (1) condition “value of P1_IS_ELIGIBLE is equal to Y”.

The curse of the cursor

For some reason, many Oracle developers avoid SELECT INTO as if it were dangerous, preferring to "have more control" over their code by using cursors for everything. This morning I spent over an hour debugging some code written by one such developer, only to find that the issue I was trying to fix was one that would have been caught by the original developer immediately had he used SELECT INTO.

The code resembled this:

procedure do_something (p_empno in number)
is
   l_empno number;
   l_boss_ind varchar2(1) := 'N';
   cursor c_emp (cp_empno number) is
      select job
      from emp
      where empno = cp_empno;
begin
   for rec in c_emp (l_empno) loop
      if rec.job = 'MANAGER' then
         l_boss_ind = 'Y';
      end if;
   end loop;
   insert into some_table (empno, boss_ind) values (p_empno, l_boss_ind);
end;

Sometimes the BOSS_IND column wasn't being set correctly. The code was a lot more complex than this in reality. You can probably easily and quickly spot the error above, but in the real procedure there was a lot of other code that could also set the BOSS_IND under different circumstances, and it was, as I said, about an hour before I spotted the problem which is: variable l_empno was not set when the cursor was opened (it got set further down), so the cursor returned no rows and the code inside the loop was never executed.

If the developer had coded this using SELECT INTO, a very unexpected NO_DATA_FOUND exception would have been raised as soon as he ran this for the first time, and the bug would never have reached system testing (as it had). Assuming of course he didn't decide that SELECT INTO had "caused" the bug, and didn't "fix" it by changing to use a cursor...

Apex MP3 Player item plug-in

I have developed a plug-in item type that renders an MP3 player to play a specified audio file using the free Premiumbeat Single Track Flash MP3 Player. The file may be specified via a URL of as the ID of a file stored in APEX_APPLICATION_FILES. There is a demo of this available here on apex.oracle.com

The plug-in has the following settings available:

They are mostly fairly self-explanatory (and documented via Help). They can generally be left to their default values. The Media Title attribute specifies the title displayed in the player. This can be set to an item reference e.g. &P2_TRACK_NAME. If not specified the URL of the file is displayed.

The value of the item can be either of the following:

• a number, in which case it is assumed to be the ID of an APEX_APPLICATION_FILES row containing the MP3 data


• any URL pointing to an MP3 file

This should shortly be available to download from apex-plugin.com.

Dutch Oracle User Group's Apex Day 2011

This week I attended the Dutch Oracle User Group (OGh)'s Apex Day as a presenter, along with an ex-colleague from Northgate, Nigel Blair. We were talking about how we converted 1500+ Forms modules to Apex. There was quite a lot of interest from the 250 people present. We hope the interest wasn't due to a mix-up in the agenda that had us down as Nigel Andrews and Tony Blair!

Luckily we were on first so I was then able to relax and enjoy the rest of the day, with fascinating presentations on Apex 4.1 (Hilary Farrell), Plug-Ins (John Scott), Apex Translations (Peter Raganitsch) and Apex with Locator/Spatial (Dimitri Gielis).

The OGh certainly know how to run a great event: everyone was well looked after, and the atmosphere was very relaxed. My thanks to Rob van Wijk for inviting me.

UPDATE
The handouts for various presentations, including ours, are now available to download from the Ogh website.

Fame at last for my biggest Apex project to date

I'm very pleased to see that the Apex project I started and worked on for several years is now the subject of an entry under Customer Quotes on the OTN Apex page.

"At Northgate Revenues & Benefits, we have used APEX to replace our legacy Oracle Forms system comprising around 1500 Forms. Our user interface has 10,000 end users daily, across 172 clients, who this year sent out over 12 million Council Tax annual bills worth £15billion and will pay out benefits of over £13billion. Our clients now experience, on average, sub second response times across a working day. We are continuing to leverage our investment in Oracle Application Express by delivering citizen facing solutions as well as launching the conversion of our Social Housing application which will replace 3,500 Oracle Forms running at 100 clients worldwide, with a total of 15,000 end users managing circa 3,000,000 properties. Oracle Application Express has helped us to make the move away from Oracle Forms whilst delivering benefits to our clients and our business.."

-- Alan Powell, Director of Products and Services, Northgate Public Services

This started life as a demo I built (using HTMLDB 1.6) for an alternative, simplified interface for "power users" to work alongside the Forms application, but was so well received by customers that it quickly became the interface, with the Forms interface eventually retired.

Building a seriously large business application with Apex is a very different ball game from the typical Apex "use case", and required a different approach. We had to invent for ourselves some of the features that were built in to later versions of Apex (and some that still haven't been), such as:

• Nested regions
• A Forms to Apex migration tool
  
• Dynamic Actions - i.e. a declarative way for developers to implement Javascript and AJAX functionality, without all of them having to become Javascript experts.
• Declarative tabular forms based on Apex collections, with validation and dynamic actions
  

Why did we go for Apex and not ADF? I blogged about this a few years ago and stand by what I said then (though I haven't had the chance or need to go back and look at ADF 4 years later).

This must be one of the biggest Apex projects ever undertaken, and I'm proud to have played a major part in it.

SQL Overlap Test

Many times I come across SQL where the developer is trying to check for overlap between two ranges (usually date ranges, but sometimes numbers). For example, to meet the requirement "select all employees whose hired from and to dates overlap with the project start and end dates".

The developer sketches out all the possible overlap scenarios and finds four:

1) End of range A overlaps start of range B:
A----
    B---------

2) Start of range A overlaps end of range B:
    A--------
B----

3) Range A falls entirely within range B:
    A---
B--------------

4) Range B falls entirely within range A:
A--------------
    B---

This leads to SQL like this (assuming all values are not null):

where (a.start < b.start 
       and a.end between b.start and b.end)
or    (a.start between b.start
       and b.end and a.end > b.end)
or    (a.start between b.start and b.end
       and a.end between b.start and b.end)
or    (b.start between a.start and a.end
       and b.end between a.start and a.end)

If, as is often the case, the end dates are allowed to be null, meaning "forever", then the SQL becomes yet more complex. In some cases I have seen attempts at this where the developer has got it wrong and missed out one of the cases altogether.

In fact it is much easier to look at the cases where A and B do not overlap, because there are only two such cases:

1) Range A ends before range B starts:
A---
       B-----

2) Range A starts after range B ends:
       A-----
B---

This leads to the much simpler SQL:

where not (a.end < b.start or a.start > b.end)

which can be rearranged to the even simpler (though perhaps less intuitive):

where a.end >= b.start 
and   a.start <= b.end

Even if we have to allow for null end dates this is now very simple:

where nvl(a.end,b.start) >= b.start 
and   a.start <= nvl(b.end,a.start)

I don't claim that any of the above is original, I am sure this algorithm appears in many SQL and other books. But I see variants of the long-winded version (sometimes bug-ridden ones) so often I thought it worth documenting here so I can point to it in future.

Shameless boasting

I hate to boast but...

StackOverflow has become one of my favourite forums for reading and sometimes answering Oracle-related questions (though it covers all programming topics in fact).

Today I am the first person ever to be awarded the Oracle badge for having earned 1000 upvotes for my answers to questions with the Oracle tag:


Of course, this may just mean I have too much time on my hands...

Chris Date Seminar

Chris Date is giving a 2 day seminar called SQL and Relational Theory - How to Write Accurate SQL Code in Edinburgh, 13th-14th May 2010. I really wish I could go to it myself. I attended one of his seminars back in about 1997 and it was both riveting and highly educational.

Apex 4.0 - no more dummy branches required

How many times do you see this error page while building an Apex page?

ERR-1777: Page 1 provided no page to branch to.
Please report this error to your application administrator.

To avoid that, I have a habit of creating an unconditional branch back to the same page in every page I build, with a sequence number higher than all the conditional branches. If I then add new conditional branches I have to ensure that I change the default sequence to a lower value.

A long time ago (in 2007) in this OTN forum thread I asked:

"I don't understand why we have to create these unconditional branches at all - why can't the default behaviour be to branch back to the same page if no other branch is taken? When is the "no page to branch to" error ever useful?!"

In Apex 4.0, this annoyance finally disappears:

"When no branches have been defined then branch page back to itself rather than showing an error."

Sometimes the little things make all the difference!