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 Attributes > User Interface Desktop 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;

  begin

    -----------------------------------------------------    

    -- logout entry

    l_target := 'apex_authentication.logout'

                ||'?p_app_id='||v('APP_ID')

                ||'&p_session_id='||v('APP_SESSION');

    add_navigation_bar_entry

         ( p_label                   =>  'Logout'

         , p_target                  =>  l_target

         , p_icon_css_classes        =>  'fa-sign-out'

         );

    -----------------------------------------------------    

    -- user entry

    add_navigation_bar_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'

                    );

    add_navigation_bar_entry

         ( p_label                   =>  'Applications'

         , p_target                  =>  l_target

         , p_icon_css_classes        =>  'fa-desktop'

         );

    -----------------------------------------------------    

    -- DEVELOPMENT entry

    if in_development_environment then

      add_navigation_bar_entry

           ( p_label                   =>  'DEVELOPMENT'

           , p_icon_css_classes        =>  'fa-exclamation-circle'

           , p_list_item_css_classes   =>  'yellow'

           );

    end if;

  end;

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 :-)