Dynamic Navigation Menu in Oracle APEX

In APEX, Navigation Menu is basically a list component. You can create two types of lists: static and dynamic. In a Static List, entries are set up during design time in the APEX builder by using Create List Wizard and selecting Static List Type. On the other hand, a Dynamic List is created based on a SQL query or a PL/SQL function returning a SQL query that runs at runtime.

In this blog, I will explain how to create a dynamic list for the side navigation menu.

Create a table to store the menu entries. I created the table dynamic_nav_bar to hold the menu entries.

-- table
 CREATE TABLE dynamic_nav_bar 
   ( menu_id            NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY, 
     parent_menu_id     NUMBER, 
     seq_id             NUMBER, 
     menu_level         NUMBER, 
     name               VARCHAR2(100) NOT NULL ENABLE, 
     icon               VARCHAR2(50), 
     icon_attributes    VARCHAR2(200), 
     icon_alt_attribute VARCHAR2(200), 
     target_type        VARCHAR2(50), 
     page_id            NUMBER, 
     request            VARCHAR2(100), 
     clear_cache        VARCHAR2(100), 
     set_items          VARCHAR2(500), 
     set_item_values    VARCHAR2(500), 
     url_target         VARCHAR2(500), 
     currnet_pages      VARCHAR2(200), 
     attribute1         VARCHAR2(1000), 
     attribute2         VARCHAR2(1000), 
     attribute3         VARCHAR2(1000), 
     attribute4         VARCHAR2(1000), 
     attribute5         VARCHAR2(1000), 
     attribute6         VARCHAR2(1000), 
     attribute7         VARCHAR2(1000), 
     attribute8         VARCHAR2(1000), 
     attribute9         VARCHAR2(1000), 
     attribute10        VARCHAR2(1000), 
     is_active          VARCHAR2(1)   DEFAULT ON NULL 'Y' NOT NULL ENABLE, 
     created            TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE, 
     created_by         VARCHAR2(255) NOT NULL ENABLE, 
     updated            TIMESTAMP (6) WITH LOCAL TIME ZONE NOT NULL ENABLE, 
     updated_by         VARCHAR2(255) NOT NULL ENABLE, 
     CONSTRAINT dynamic_nav_bar_ck  CHECK (target_type in ('PAGE','URL','#')) ENABLE, 
     CONSTRAINT dynamic_nav_bar_ck1 CHECK (is_active in ('Y','N')) ENABLE, 
     CONSTRAINT dynamic_nav_bar_pk  PRIMARY KEY (MENU_ID),
     CONSTRAINT dynamic_nav_bar_fk  FOREIGN KEY (parent_menu_id) REFERENCES dynamic_nav_bar (menu_id) 
     ON DELETE CASCADE ENABLE
   );
/
-- index
CREATE INDEX dynamic_nav_bar_idx 
     ON dynamic_nav_bar (parent_menu_id);
/
-- trigger
CREATE OR REPLACE TRIGGER dynamic_nav_bar_biu 
    before insert or update 
    on dynamic_nav_bar 
    for each row 
 BEGIN
    if inserting then 
        :new.created := sysdate; 
        :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user); 
    end if; 
    :new.updated := sysdate; 
    :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user); 
END dynamic_nav_bar_biu;

Insert records for the menu entries. For this blog, I am using the Customers sample application that comes with APEX and have inserted the menu entries into this table.

Task 2 : Create SQL query for Dynamic List

In APEX, we can create a dynamic list using a SQL query or a function that returns a SQL query. In this blog, I will explain how to create a dynamic list using a SQL query. Detailed documentation about Dynamic Lists can be found here.

select m.menu_level lavel,
       m.name  label,
       case
          when m.target_type = 'PAGE' then
             apex_util.prepare_url(
                        p_url  => 'f?p='
                                   || :app_id
                                   || ':'
                                   || m.page_id
                                   || ':'
                                   || :app_session
                                   || ':'
                                   || m.request
                                   || '::'
                                   || m.clear_cache
                                   || ':'
                                   || m.set_items
                                   || ':'
                                   || m.set_item_values,
                p_checksum_type => 'SESSION')
          when m.target_type = 'URL' then
             m.url_target
          else
             '#'
       end target,
       null is_current,
       m.icon icon_name,
       m.icon_attributes image_attributes,
       m.icon_alt_attribute image_alt_text,
       m.attribute1,
       m.attribute2,
       m.attribute3,
       m.attribute4,
       m.attribute5,
       m.attribute6,
       m.attribute7,
       m.attribute8,
       m.attribute9,
       m.attribute10
  from dynamic_nav_bar m
 where m.is_active = 'Y'
   start with m.parent_menu_id is null
   connect by prior m.menu_id = m.parent_menu_id
   order siblings by m.seq_id;

Task 3: Create Dynamic List in APEX

To create a dynamic list, navigate to Shared Components > Lists from your application home page in APEX builder.

Click Create

The Create List wizard popup will open. Enter the name, select Dynamic for the type, and click Next.

Select SQL Query from the Query Source Type drop-down, enter the query we created in the previous step in the SQL Query section and click Next.

Select Do not create list region(s) from Create List Regions? drop-down and click Create List.

A new list has been created.

To configure your app's navigation menu, go to Shared Components > User Interface Attributes.

From the Navigation Menu List drop-down select the list that we created in previous step and click Apply Changes.

And that’s it! Run your app to see the Dynamic Navigation Menu in action.

Conclusion

In summary, Creating a Dynamic Navigation Menu is straightforward and significantly improves your application's flexibility. It's particularly helpful in multi-tenant applications, where you need to create different menu entries for each tenant.