Creating Oracle APEX Dynamic Menu via oracle table-CodePudding

I am trying to add dynamic menu in Oracle APEX through oracle table using sql query. It will have n levels of menu (Menu will have sub-menu, sub menu will have further menu under it), should not be restricted to any number of nodes.

I have already created the dynamic navigation menu bar with oracle table and hierarchical query but the sequence is not coming correct. Could you please help me with below to achieve this -

Table structure
Sample table data
SQL Query

I am attaching a sample snapshot to show what I am trying to achieve.

Nested Menu Sample

Please Note - I am not looking for dynamic menu which is fully Javascript driven.

CodePudding user response：

To create a dynamic navigation menu in Oracle APEX with multiple levels of sub-menus, you can use a hierarchical query to retrieve the data from the database and then use the APEX tree structure to display the data. Here's an example of how you could do this:

First, create a hierarchical query to retrieve the data from the database, ordered by the LEVEL and SEQ columns:

SELECT id, parent_id, label, level
FROM your_table
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER BY level, seq;

Then, in your APEX page, create a tree region and specify the query that you created above as the source for the tree.

In the tree attributes, set the Root Node Level to the highest level of your hierarchy (for example, if your hierarchy has 3 levels, set this to 3).

Then, in the Tree node template, use the level column to display the sub-menu items at the correct level. Here's an example of how you could do this:

<li>
  <a href="#LINK#">#LABEL#</a>
  <apex:if level="2">
    <ul>
      <li><a href="#LINK#">#LABEL#</a></li>
    </ul>
  </apex:if>
</li>

In this example, we are using the apex:if tag to only display the sub-menu items at level 2 in the hierarchy. You can adjust this code to display sub-menu items at any level in your hierarchy.

CodePudding user response：

To create a dynamic menu in Oracle APEX using an Oracle table and apply a hierarchical query to retrieve the data from the table and then use it to populate the menu items. A common approach is to have a parent-child relationship in the table, where each row has a foreign key reference to its parent row.

Here is an example of a table structure that you can use:

CREATE TABLE menu_items (
    id        NUMBER PRIMARY KEY, // uniquely identifies each menu item
    parent_id NUMBER,             // references the `id` of the parent menu item
    title     VARCHAR2(255),      
    link      VARCHAR2(255),      
    sequence  NUMBER              // specifies the order of displaying menu items 
);

Let's populate this table with sample data:

INSERT INTO menu_items (id, parent_id, title, link, sequence) VALUES (1, NULL, 'Home', '/home', 1);
INSERT INTO menu_items (id, parent_id, title, link, sequence) VALUES (2, NULL, 'About', '/about', 2);
INSERT INTO menu_items (id, parent_id, title, link, sequence) VALUES (3, 2, 'Our Team', '/about/team', 1);
INSERT INTO menu_items (id, parent_id, title, link, sequence) VALUES (4, 2, 'Our History', '/about/history', 2);
INSERT INTO menu_items (id, parent_id, title, link, sequence) VALUES (5, NULL, 'Contact', '/contact', 3);

This will create a top-level menu with three items: "Home", "About", and "Contact". The "About" menu will have two sub-items: "Our Team" and "Our History".

To retrieve the data from this table and use it to populate the menu in Oracle APEX, you can use a hierarchical query:

SELECT
    LEVEL     AS "Level",
    id        AS "ID",
    parent_id AS "Parent ID",
    title     AS "Title",
    link      AS "Link",
    sequence  AS "Sequence"
FROM menu_items
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY sequence;

This query uses the CONNECT BY and START WITH clauses to traverse the hierarchy of menu items and the ORDER SIBLINGS BY clause to order the menu items at each level by the sequence column.

Use the result of this query to populate the menu in Oracle APEX. For example, you can use the LEVEL column to determine the level of each menu item and create the necessary indentation in the menu, and use the title and link columns to display the text and URL for each menu item.