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 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;

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


سليم هليّل said...

Thank you for the useful tip.

Can you please share the code of aut_pck package?

Anonymous said...

The Logout URL is not working, cause the session id will not be rendered, so the url is only with app_id an session_id but without the "&p_session_id" string

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...

Add "#38;" after the '&' and it will work, case the &p_session_id string was rendered

Theresa said...

I think this is exactly what I'm looking for.

What theme are you using?
I'm using Universal Theme - 42

I can't seem to find a way to this path:

Shared Components > User Interface Components > User Interface Details

Under Shared Components I only see User Interface Attributes/Themes/Templates

Dick Dral said...

T G,

There was an error in the path. De right path is:

Shared Components > User Interface Attributes >User Interface > Desktop

Here you find the User Interface Details shown in the picture.

Good luck :-)

kcigiri said...

Arrived here looking for something as simple as V.


What is V? lol

Dick Dral said...

Well it is a bit out of context, but V is the PL/SQL function to aquire the value of an APEX item in session context.
So if you have the APEX item P100_ITEM and its value is stored in session state, in a PL/SQL (where you cannot use the bind variable :P100_ITEM) you can retrieve the value of P100_ITEM by calling v('P100_ITEM').

Good luck,

Jeroen said...

Hi Dick,

Thank you very much for your article.
This was exactly what I needed!

I put an extra function in my package which creates a dynamic navigation MENU with a cursor for loop which made it even more dynamic.

Thanks again.

Tiago said...

Hi Dick,

Thank you very much for sharing this solution, I was able to adapt to my scenario perfectly.