Creating a Tree Structure in Oracle APEX Using an Internal Table

In this blog post, I’ll guide you through the process of creating a dynamic tree structure in Oracle Application Express (APEX) using data from the internal table APEX_APPLICATION_FILES. This method is ideal for visualizing hierarchies within your APEX applications in an intuitive and organized way.

 

---

Preparation

To build the tree structure, we’ll use the APEX_APPLICATION_FILES table, which stores standard files and MIME types. In our setup:

• File names will represent root nodes.
• MIME types will serve as child nodes under their respective root nodes.

 

---

SQL Query for Tree Structure

The following SQL query creates the tree structure by leveraging hierarchical queries with CONNECT BY and START WITH clauses:

 

SQL

xxxxxxxxxx

49

 

1

sql

2

Code kopieren

3

WITH data AS (

4

    SELECT 

5

        'V' AS link_type, 

6

        FILENAME AS ID,

7

        NULL AS parent,   

8

        FILENAME AS Name,

9

        ID AS V_ID

10

    FROM 

11

        APEX_APPLICATION_FILES

12

    UNION ALL

13

    SELECT 

14

        'K' AS link_type,

15

        FILENAME || '-' || MIME_TYPE AS ID,

16

        FILENAME AS parent,  

17

        MIME_TYPE AS Name,

18

        ID AS V_ID

19

    FROM 

20

        APEX_APPLICATION_FILES

21

)

22

SELECT 

23

    CASE 

24

        WHEN connect_by_isleaf = 1 THEN 0 

25

        WHEN level = 1 THEN 1 

26

        ELSE -1 

27

    END AS status,

28

    level,

29

    Name AS title,

30

    CASE 

31

        WHEN link_type = 'V' THEN NULL -- No icon for root nodes

32

        WHEN link_type = 'K' THEN 'fa fa-search' -- Icon for child nodes

33

        ELSE NULL

34

    END AS icon,

35

    ID AS value,

36

    'View' AS tooltip,

37

    CASE 

38

        WHEN link_type = 'K' THEN 

39

            apex_util.prepare_url('f?p=' || :app_id || ':1:' || :app_session || '::NO::P1_KATG_ID:' || V_ID)

40

        ELSE NULL 

41

    END AS link

42

FROM 

43

    data

44

START WITH 

45

    parent IS NULL

46

CONNECT BY PRIOR 

47

    ID = parent

48

ORDER SIBLINGS BY 

49

    title;

 

---

Explanation of the Query

• Data Source:
• The APEX_APPLICATION_FILES table is used as the data source to create hierarchical relationships.
• Hierarchical Query:
• The START WITH clause identifies the root nodes (files with no parent).
• The CONNECT BY PRIOR clause links parent nodes to their respective child nodes.
• Node Attributes:
• status: Determines whether a node is a leaf (0), a root (1), or an intermediate node (-1).
• icon: Displays icons for child nodes (e.g., fa fa-search), while root nodes have no icons.
• link: Generates dynamic URLs for child nodes using apex_util.prepare_url.
• Custom Sorting:
• The ORDER SIBLINGS BY clause ensures sibling nodes are sorted alphabetically by their titles.

 

---

Steps to Implement the Tree Structure in APEX

• Create a Region:
• In Oracle APEX, create a new region of type Tree.
• Add the SQL Query:
• Paste the provided SQL query into the SQL Query field of the region.
• Adjust Application Parameters:
• Replace :app_id, :app_session, and other bind variables with the appropriate values for your application.
• Preview the Tree:
• Save the changes and run the page to see your dynamic tree structure in action.

 

---

Benefits of This Approach

• Dynamic Hierarchies: Automatically generate hierarchical views based on the data in your internal tables.
• Improved Usability: The tree structure allows users to navigate complex data relationships effortlessly.
• Customizable: Easily adapt the query to include additional attributes, icons, or tooltips.

 

---

Conclusion

Creating a tree structure in Oracle APEX using the APEX_APPLICATION_FILES table is a powerful way to visualize hierarchies. By combining SQL hierarchical queries and APEX features, you can deliver a seamless and interactive user experience. Try implementing this in your next project and unlock new possibilities for dynamic data visualization.

 {fullWidth}