Oracle APEX - Dynamic Navigation Menus

 In this post I explain you how to Change default navigation menu to Dynamic menu in oracle APEX.

Step 1

Create a Table. 

 CREATE TABLE BLOG_DYNAMIC_MENU(BDM_MENU_ID     VARCHAR2(25),
  BDM_MENU_DESC     VARCHAR2(50),
  BDM_PAGE_NO     NUMBER(15),
  BDM_PAR_MENU_ID     VARCHAR2(25),
  BDM_SEQ_NO     NUMBER(5),
  BDM_ICON     VARCHAR2(100),
  BDM_VISIBLE     VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  CONSTRAINT BDM_PK PRIMARY KEY (BDM_MENU_ID));

Step 2

Insert records to table.

INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('HOME', 'Home', 1, NULL, 1, 'fa-home', 'Y');
 
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('MENU_1', 'Menu 1', 2, NULL, 2, 'fa-wheelchair', 'Y');
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU1_1', 'Sub Menu 1.1', 3, 'MENU_1', 1, 'fa-hospital-o', 'Y');
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU1_2', 'Sub Menu 1.2', 4, 'MENU_1', 2, 'fa-heartbeat', 'Y');   
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('MENU_2', 'Menu 2', 5, NULL, 3, 'fa-gear', 'Y');
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU2_1', 'Sub Menu 2.1', 6, 'MENU_2', 1, 'fa-refresh', 'Y');  
              
INSERT INTO blog_dynamic_menu(bdm_menu_id, bdm_menu_desc, bdm_page_no, bdm_par_menu_id, bdm_seq_no, bdm_icon, bdm_visible)
               VALUES ('SUB_MENU2_2', 'Sub Menu 2.2', 7, 'MENU_2', 2, 'fa-spinner', 'N');

Step 3

Write SQL Query using connect by.

  SELECT LEVEL,
         bdm_menu_desc label,
         DECODE(bdm_page_no, NULL, NULL, ('f?p=&APP_ID.:'||bdm_page_no||':&SESSION.::&DEBUG.::::')) target,
         NULL is_current,
         bdm_icon image,
         NULL
    FROM blog_dynamic_menu
   WHERE bdm_visible = 'Y'
   START WITH bdm_par_menu_id IS NULL
 CONNECT BY bdm_par_menu_id = PRIOR bdm_menu_id
 ORDER SIBLINGS BY bdm_seq_no ASC

Step 4

Click below link to view Video tutorial to change Static Navigation to Dynamic Navigation Menu.

https://youtu.be/xHnNIYTmMDY

Thanks & Regards

R. Kalaiyarasan

+91 9944809554