Press "Enter" to skip to content

Oracle Apex Tree Example

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:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Sakul Gupta
Sakul Gupta
2 days ago

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.