Category: Oracle

Review all item help texts

The business analyst or QA wants to check all the help texts for all items in your apex application – don’t force them to navigate to each page and click on the labels, one by one; instead, give them a spreadsheet to review at their leisure.

Method 1: use the apex data dictionary viewer

1. Open your application in the Apex application builder

2. Utilities -> Application Express Views

3. Choose APEX Application Page Items


5. Click Filter >

6. Select APPLICATION_ID = <your app id>


8. Click Results >

9. Click Download

Method 2: query the data dictionary directly using your tool of choice

select page_id, page_name, region, item_name, label, display_as, item_help_text
from apex_application_page_items
where application_id = :my_app_id and item_help_text is not null
order by page_id, region, display_sequence;

Custom html for an Apex generated item? jQuery to the rescue

The apex application I’m working on has a search filter on a report page that looks like this:


The list of values is based on a user-defined “ref codes” table, which includes an option “Show By Default”. This option is currently set on the “Closed” and “Deleted” status and means that transactions with that status will not normally be listed in the report, unless the user explicitly selects either of those statuses, e.g.:


If no checkboxes are selected, the report shows all transactions by default, except for Closed or Deleted transactions.

To indicate this behaviour, I added an asterisk (*) next to the label on those checkboxes. I also wanted some hover text so that a user who has forgotten what the asterisk means can get an idea, e.g.:

<input type="checkbox" id="P23_FTS_STATUS_7" name="p_v05" value="CLOSED">
<label for="P23_FTS_STATUS_7" title="* not shown by default">Closed*</label>


However, the default apex Checkbox item doesn’t support putting extra attributes on the generated html labels – so I need to add the hover text by running some javascript after the page is loaded. jQuery to the rescue!

To add the hover text I simply add this to the Execute when Page Loads page attribute:

  .attr("title","* not shown by default")

This searches for all label nodes where the “for” attribute contains my item name (“P23_FTS_STATUS”), where the text contains a “*”. It then adds the “title” attribute with my desired value.

Calculate Age in Javascript

I had a registration form in Apex which asks the applicant to enter their Date of Birth in a date item; I then needed to calculate how old they would be at the start of the event, which determines a number of rules, such as whether we need to obtain their parent’s permission.

In my first release I implemented this with a Dynamic Action which ran SQL something like this:

select round(months_between(start_date
             / 12,1)
from events
where event_id = :P1_EVENT_ID;

This worked fine – it takes advantage of Oracle’s builtin support for date arithmetic. However, it was rather slow, because it needs to do a roundtrip to the database to run the query and return the result.

I wanted a pure javascript implementation to avoid the roundtrip, but my initial searches came up with a number of sub-par solutions involving extracting the year and month portions and applying simple arithmetic which did not take into account leap years.

Instead, I’ve gone with an easier solution taking advantage of the moments javascript package.

  1. Add the path to moment.min.js in the File URLs attribute of the page. You could get your own local copy or point to the relevant file from a cdn:
  2. Add a function to the Function and Global Variable Declaration attribute of the page, which uses the moments object to convert the strings into date objects, and then call the diff method to get the number of years as a floating-point number, e.g.:
    function getAge() {
      var e = moment($v("P1_EVENT_DATE"),"YYYYMMDD")
         ,dob = moment($v("P1_DATE_OF_BIRTH"),"DD-MMM-YYYY");
      return e.diff(dob,'years',true).toFixed(1);
  3. Add a Dynamic Action to the Date of Birth item which calls getAge() and sets the value of the Age display item.

The result is a much quicker response and less load on the database. This is an intentionally simple example, you could do it in different ways to suit your situation (e.g. if you have multiple date items you need to handle on the same page, you might pass them as parameters to the function).

The moments javascript package has an impressive list of features, including pretty-formatting a duration (e.g. a client-side version of the SINCE format e.g. “3 years ago”) documented here.

Sample TAPI Apex Application

If you attended my presentation at AUSOUG Perth earlier this month, or if you’ve had a peek at the slides, you may be interested in a more concrete demonstration of the ideas presented. So if you’d like to install and play with a sample application that includes a TAPI generator, feel free to download this (EDIT: updated, see below).

Disclaimer: this is provided for information (and entertainment) purposes only.

Oracle Application Express 4.2.2 or later

Read the README file for installation instructions.


If you’re only interested in the schema-level TAPI and not the Apex application, the zip file includes the DDL script that you can run directly in a schema without requiring Apex.

EDIT (18/11/2014): updated sample code to do the right thing in WHEN OTHERS triggers.

EDIT (20/11/2014): updated sample code with a further example for a FK to a table, which doesn’t use a surrogate key. Also added an exception handler to the Apex application.

EDIT (2/12/2014): added Grid Edit for event types, as an example of how a tabular form might work with a TAPI; added deployment package; moved code to Bitbucket.

More updates and improvements will be added in the future – watch this space.

Help for your keyboard users

Apex’s Blue Responsive Theme 25 is a great theme for building a user-friendly website, and unlike many other themes which make the item labels clickable to get help, it renders little question-mark icons to the right of each item that has a help message defined.


One issue with this design, however, is that a keyboard-savvy user (such as myself) hates to switch between keyboard and mouse – so they Tab between each field as they fill in a form. With this theme, however, those little question-mark icons are in the tab order, so the user has to Tab twice between each field. If they’re not looking at the page they might forget; and if one item doesn’t happen to have a Help icon, they have to remember to only Tab once. All of this adds up to a poor user experience.

To fix this, we simply tell the browser to move the Help icons out of the tab order – and yet again, jQuery comes to the rescue as we can simply pick out all the elements with the class itemHelpButton and set their tabindex to “-1”:


Put this code in the page attribute Execute when Page Loads – when the page loads, this will set the tabindex on all the help icons on the page, so now the user can tab through the page without interruption.