Wednesday, 14 February 2018

Easier maintenance of standard APEX buttons

One of the strong points of APEX is the use of templates. Combining templates with template options and attributes you can end up with HTML objects that are declarative and in most cases do exactly what you want. When you want to make general changes to these objects just change the template or the underlying CSS. This is a very good implementation of the DRY (Don't Repeat Yourself) principle.

However when generating pages with APEX you generate many identical buttons (CREATE,SUBMIT,DELETE,CANCEL,NEW) for which the label is a hard coded property for each instance of the button (not so DRY). If you there is the need to change the labels ( maybe for a translation ), you will have to go through the entire application and change all the relevant instances of the buttons.
It gets even harder (or more nagging) when after finishing the application you are asked to apply another look-and-feel including a left-side icon.

So you have to change the button on the left to the button on the right.
For each button you need to:
- select the button
- apply the CSS class
- apply the icon CSS class
- open the template options (default icon position is right)
- change the icon position
- close the template options
A lot of work, and that times the number of buttons in your application. That is a lot of work and by its repetitive nature very error prone.
All these scattered definitions are not very DRY either.

A solution I use is to create specific button templates for the most used buttons (CREATE,SUBMIT,DELETE,CANCEL,NEW). The button templates are based on the Text with icon button:

New button templates are created by copying the Text with icon template and adapting it to your needs.In my applications for example for the Save Button, this looks like:

The label is hard coded to Save and the left icon class fa fa-save refers to the save icon (the floppy disk). As we are using the left icon, the code for the right icon is omitted. The class t-Button--iconLeft is needed to display the left icon. And finally the class delphy-hot defines the specific look-and-feel for this application suite.

Now all that needs to be done is to apply these specific templates to the buttons. This took me about half an hour for a medium sized application.
Instead of the list of actions mentioned above you just need to:
- select the button
- change the button template
Look at the animated GIF below to see how fast the change can be applied.

Once you have got all the button templates referenced, you can easily change the look-and-feel of all the buttons just by changing the corresponding templates.

If you have several applications with the same look-and-feel you can copy the templates from one application to another. After copying you can adapt to the application, for example by translating the labels.

Happy APEXing ;-)

Wednesday, 24 January 2018

Dynamic Navigation Bar

In my current assignment I work on a large number of applications. These applications all share the same UI theme. Once logged in you can switch applications through an application menu. 
The navigation bar in all these applications is (or should be) the same. Until today each application had his own copy of the Desktop Navigation Bar which is in fact an Oracle APEX list. 
As the number of applications is approaching double digits you can imagine I would like one code base for the navigation bar content. 

In the Shared Components > User Interface Components > User Interface Details you can find the details of the Navigation Bar:

Here you see that the list on which the Navigation bar is based can be changed. The template can also be chosen, but the current template is sufficient for this moment. 

Unfortunately we cannot reference lists in other applications, only copying is possible. 
But we can define a dynamic list to populate the Navigation Bar. This opens the door to defining the list as a query or view in the database, which can be shared between applications. 
I could not find the definition of the list elements needed for the Navigation Bar list, so I had to do some experimenting myself. It seems that the meaning of the columns in the query result set is determined by their position. 
The following query results in a correct entry:

select '1'            as  lvl
     , 'Logout'       as  label
     , 'apex_authentication.logout?p_app_id=&p_session_id='  as  target
     , null           as  attr1
     , 'fa-sign-out'  as  icon_css_class
     , null           as  attr3
     , null           as  attr4
     , ''             as  badge
     , ''             as  list_item_css_classes
from   dual
where  nvl(v('APP_USER'),'nobody') != 'nobody'

In this query the label is the text displayed in the entry. 
The target is the URL to be linked to. 
The icon_css_class can contain a reference to a Font Awesome/Apex icon. If omitted no icon is shown. 
For some columns I did not find any use, but as the meaning is positional these columns are needed anyway. 
The entries are implemented as an HTML UL element (unordered list). The list_item_css_classes can contain CSS classes for the list item that contains the entry. These classes can for example be used to apply a color to the entry text and icon. 

You can build a query by connecting a number of selects from dual of the above form. But this results in a long messy query which is hard to maintain. Luckily a APEX list can also be based on a PL/SQL function returning a query. So the query can be composed in PL/SQL which enables more clean coding. A package is defined with procedures to add an navigation bar entry and to return an APEX url. 
The code to generate the query in PL/SQL looks like this: 

  function navigation_bar_query return varchar2 is
    l_target      varchar2(1000) := null;

    -- logout entry
    l_target := 'apex_authentication.logout'
         ( p_label                   =>  'Logout'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-sign-out'

    -- user entry
         ( p_label                   =>  initcap(v('APP_USER'))
         , p_icon_css_classes        =>  'fa-user'

    -- applications entry
    l_target := apex_url
                    ( p_app_id       =>  aut_pck.get_aut_app_id
                    , p_app_page_id  =>  'APP_MENU'
         ( p_label                   =>  'Applications'
         , p_target                  =>  l_target
         , p_icon_css_classes        =>  'fa-desktop'

    -- DEVELOPMENT entry
    if in_development_environment then
           ( p_label                   =>  'DEVELOPMENT'
           , p_icon_css_classes        =>  'fa-exclamation-circle'
           , p_list_item_css_classes   =>  'yellow'
    end if;


All the messy code is encapsulated in the procedures and functions. 
Two standard entries are defined: the logout entry and the display of the username. Notice that the username is displayed using initcap. It is also possible to display the user's real name here when it is available in the database. 

Next a link to the applications menu is supplied. 

The last entry is used to notify a user that he is working in the development environment. This entry is only shown when working in the development environment which can usually be determined in SQL, for example by retrieving the database SID or service. In this way the application can be deployed to other environments unchanged. 

You can define a dynamic list using this package: 

This list can be chosen in the User Interface attributes to represent the Navigation Bar. 

The resulting Navigation Bar looks like this: 

You can download the full code of the package here.  

Happy APEXing :-)

Friday, 22 December 2017

First impressions on mobile support in APEX 5.2EA

For APEX 5.2 the support of JQuery mobile to create mobile applications will be discontinued. The APEX development team have taken action to enable mobile development using the Universal Theme. In APEX 5.2EA you can already find the List View Region that will replace the equivalent component in JQuery Mobile.

The List View Region is defined with the same properties as in the Query Mobile version:

The region source contains a query and in the Attributes the role of the column is assigned.
After creating a page with a List View it looks like this.

So the data is present, but the formatting is not. So I read the release documentation again, found on the home page of APEX:

In the features document there is a paragraph about the List View Region.

Apparently we have to look into the Known issues. Here we find:

OK, that's easy. So the link is copied and pasted in the page's CSS file attribute. But alas, the file cannot be found :-(.
But it is visible that it is a JQuery mobile file. And of course that file can be found on the internet.
Indeed, a reference is found:

Unfortunately this does not work, because Chrome does not accept mixed content ( mixed being both http and https). Luckily we get a warning in the Browser Inspector, and the https equivalent is available, so we use:

Now our page looks like this:

Much better, isn't it? It can still be improved though:
- there is a bit of wasted space to be recovered
- the search item can be styled
- the divider can use a bit more emphasis

So a bit of CSS is added:

/* remove wasted white space */
.t-Body-contentInner {
    padding: 0;
html .ui-filterable + .ui-listview, html .ui-filterable.ui-listview {
    margin-top: 0em;
.ui-filterable {
    padding: 4px 12px;

/* style search item */
.ui-filterable input {
    border: 1px solid lightgrey;
    width: 100%;
    padding: 0px 12px;
    border-radius: 4px;

/* give list divider background color */
.ui-listview > .ui-li-divider {
    background-color: lightgrey;

And now the page looks like this:

I could not resist also styling the counter as a badge ;-).

My conclusion is that the APEX development team are on the right track to replace the JQuery Mobile functionality.

Happy Apexing!

Monday, 9 October 2017

About a plug-in providing native Apex functionality

This Saturday I published a plug-in on to create subheaders in a classic report. I got the idea while developing a long report and it seemed like a good idea to create a plugin for it to wrap and parameterize the JavaScript code. I searched the internet for the existence of such a plug-in and found nothing.
So I set out to create the plug-in. After finishing it I wrote a blogpost and added it to the plug-in repository on And I proudly tweeted about it.
Pretty soon I got a reply from Peter Raganitz: "Wouldn’t a control break do just that?".

I set out to check this and.. he is absolutely right. This functionality is available in Apex in a declarative way!

The only extra needed is a small CSS definition for the visuals of the headers:

.apex_report_break {
  1. background-colorlightgrey!important;
  2. font-weightbold;
  3. font-size12px;

Saturday, 7 October 2017

Adding Subheaders to reports the easy way

After publishing this plug-in I found out that this functionality is native in Apex. Read more details here.

For an assignment I was working on a page containing a list of dates with the amount of sunlight on that day. This produces a long list of dates with numbers which I think is not very accessible. I applied my Report2columns plug-in which already improved the layout, but I still was not satisfied:

It are still a lot of numbers with little structure. Especially the repetition of the month and year adds a lot of characters, but adds little meaning.

After some experimenting with bold fonts and grey colors I came up with the solution to use subheaders for the months. And suddenly the data made much more sense to me:

This was done by adding an extra column with the subheader text to the report. In a After Refresh  dynamic action a JavaScript function scans the table and adds a subheader at each location where the content of the subheader column differs from the previous row.

In addition to the sub headers the month and year indicators are displayed in grey. This way they have less effect  on the general picture, but they are still readable: you do not have to shift your attention to the sub header to know the month and year of the date. This effect was accomplished by adding the formatting in the query:

SELECT to_char(lgr_date,'dd')
||'<span style="color: lightgrey;">'
||to_char(lgr_date,'-mm-yyyy')||'</span>' AS thedate
FROM mytable

Plug-in for subheaders

I was so enthousiastic about the effect of the subheaders that I have created a plug-in to generate subheaders in a Classic report.

The Report Subheaders plug-in is very easy to use. All you have to do is:
  • import the plug-in ( you can download it from )
  • add a column to your query which contains you subheader content
  • create an After Refresh dynamic action on your report
  • add a true action with a call to the Report Subheaders plug-in
  • fill in the name of your subheader column in the Subheader Column attribute
  • if you want to have your table headings repeated after each subheader, set Repeat Subheaders to Yes
  • The default formatting of the subheaders can be changed by defining the class td.t-Report-cell.subheader.Note that the background-color has to be defined with !important.
You can see the plug-in in action here on my demo site:

Happy Apexing :-)

Sunday, 10 September 2017

ORA-00933 when editing a Apex report

Have you encountered this error before in working with Apex? I do regularly wen editing reports.
At first I have created a nice report. After getting the query right I make some columns invisible, change the header texts and do some formatting.
Then I run the report and it looks nice. No errors on the SQL.

Then I apply a few changes and suddenly the report will not show up anymore. Instead I get this ugly error message:

failed to parse SQL query:
ORA-00933: SQL command not properly ended

What happend? It took me some time to figure it out. In my case there was an order by in the report query. This means that none of the report columns may be sortable.
At first this was the case. But one column was changed from Hidden to Plain Text. And in this change the Sortable attribute was set to Yes:

And Apex gets confused when a report query has an order by and an item is also sortable. So the query Apex creates has an incorrect syntax, hence the error message.

Setting the sortable attribute of the item to No immediately solves the problem. The report executes again without any error message.

Happy Apexing...

Sunday, 3 September 2017

Adding a bit of Excel to tabular forms

When speaking to users they often ask me whether the editing of data can be like Excel. Using tabular forms is a long way from using Excel. It is hard to operate the tabular form just with the keyboard and copying of values needs to be done in a number of keystrokes.

With this in my mind I created a JavaScript function to perform a (single) copyDown action. The value of an item is copied to the item below and the focus is set to that item. When this can be executed using a key(combination) the user is able to do a fast copy down action.
After creating this function I realized it also can be used to move up and down in the tabular form using the arrow keys. And I decided to create a Plug-In around it.

The Plug-In can be called from a Page Load dynamic action. No settings are needed.
The actions will execute on any text, date, and select item in a tabular form. Checkboxes are not yet supported.
The available function keys are:

  • [Up] move up to previous row
  • [Down] move down to next row
  • [Ctrl] + [Down] copy content of current cell to cell below and set focus to this cell
  • [Ctrl] + [Up] copy content of current cell to cell above and set focus to this cell
  • [Ctrl] + [Shift] + [C] copy content of current cell to cell below and set focus to this cell ( needed for Select items because they have another function for [Down] )
I could have included the [Right] and [Left] arrow keys but then the editing of the cell content would be affected. These keys cannot be used anymore to navigate within the content. So the keys [Tab] and [Shift][Tab] can be used to go to the next and previous item in the row.

I think many of you can make your users even happier with their Apex application when implementing this Plug-In in your application.

You can see and experience the Plug-In at this demo page:

As always this plugin can be downloaded from

One last question: does anyone know how to access the state of checkboxes in an Universal Theme tabular form?

Happy Apexing