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.

Wednesday, April 16, 2014

HGV Levy

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

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.

Sunday, January 26, 2014

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:


(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>
<input type="hidden" name="f01" value="0" />


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

That's it - just that.

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

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

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

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

Wednesday, September 18, 2013

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:

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

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:

 <item type="editor" node="ViewNode"  vertical="true">
     || ':'
     type  = 'VIEW'

Friday, June 14, 2013

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.