In this tutorial, I am giving an example to create a dynamic menu using Oracle Apex Tree. I will use the home page of Oracle Apex application to add a tree menu so that whenever a user logged in, he can see the menu first.

I am using Oracle Apex version 19.1 to demonstrate this example.

Oracle Apex Tree Example

Follow these steps to create a menu using Tree region in Oracle Apex:

  1. Create a table TREE_MENU to store menu data, as shown in the below example:
Create table tree_menu (
   parent_node integer primary key,
   child_node integer,
   menu_desc varchar2(50),
   page_no integer
);

You can add more fields to it depending on your requirement, but these fields are mandatory for a menu.

  1. Then I inserted some data into TREE_MENU table to produce the menu. The following is the data example:

Data example for a tree table in Oracle Apex.

Note: In the above screenshot, the hyphen (-) is a null value so do not insert a hyphen in it just leave null that column. By looking at this data and the tree menu produced with this data, you will have the idea of tree data structure. Then you will be able to modify and create new data easily for your tree menu.

  1. Now in Oracle Apex, open the Home page in the page designer.
  2. Then create a Region and set the type of that region to Tree.
  3. In the Source section, change the type to SQL Query and add the following query to it:
select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
        level,
        menu_desc as title,
        decode(page_no, null, 'fa-bars', 'fa-file') as icon,
        child_node as value,
        menu_desc as tooltip,
        'f?p=&APP_ID.:'||"PAGE_NO"||':&APP_SESSION.' as link 
   from (select menu_desc, 
parent_node, 
child_node, 
page_no
from TREE_MENU)
  start with child_node is null
connect by prior parent_node = child_node
order siblings by parent_node

The following is the screenshot for the above settings:

Data source setting for the Tree in Oracle Apex.
Tree – Data Source Setting
  1. Then click on the Attributes under the Tree and set the following properties as shown in the below screenshot:

Oracle Apex Tree Attributes.

Note: Do not miss any setting above and first change the Hierarchy property to Not Computed.

Now your Tree menu is ready, and it will have the following output:Oracle Apex Tree Example.

When the user will click on the node Employee Form it will open the employee form, because in this application page number 2 is the employee form and I configured that page number into the TREE_MENU table. You have to specify page numbers according to your application page numbers.

See also:

Leave a Reply

This Post Has 10 Comments

  1. Sakul Gupta

    Hi Vinish, I followed the above example and added a Tree region in one of the APEX application, it works fine as intended, but I am getting the following error when I run the advisor check.
    “Column Level does not exist in Region Source SQL statement”. I am getting these errors for TITLE, STATUS and VALUE columns. I do see these are included in the source SQL.
    Any insight, how to fix these errors.

    1. Vinish Kapoor

      Have you specified the settings for 6th step for the attributes?

      Because the SQL query is having all these columns, you need to define for the attributes too.

  2. Umer Farooq

    Dear Sir, How can i navigate next page in same window

    1. Vinish Kapoor

      In the process tab, you will find a branch node, there create a branch to call another page.

      Create a button to submit page. Make that branch that button specific.

      At runtime it will navigate to that page in same window.

  3. Imrul Kayes

    Dear Sir, I have a problem on tree region. when i run pages, if there are more then two parent id value this value inside root. why display root run time??

    1. Vinish Kapoor

      The parent node should be unique, if it is duplicated, then it will be an issue.

    2. Imrl Kayes

      Thak you so much sir. I will try it.

  4. PJPJPJPJ

    For the icons to show (20.1) you need to add ‘t-Icon fa’ ie ‘t-Icon fa fa-file’

    and thanks for the help

  5. Marion

    What is the expected behavior when the user clicks on EMPLOYEE or DEPARTMENT (neither of which have page numbers assigned)? Thank you

  6. Robert Síbek

    Hi Vinish,

    I’ve followed your tutorial, but the result is just empty region as you can see on the picture.

    Do you have any advice how to fix that?

    https://ibb.co/y4N4ytc