4/19/2018 Oracle Forms – Javabean
Oracle Forms
Handle hierarchical trees
Home page
1. Purpose
This is an article to show how to handle a hierachical tree in an Oracle Forms
application.
The goal is to build a tree menu with the possibility of enabling the database roles.
There are 2 ways to populate a hierarchical tree in Forms:
§ Using a record group / query
§ Building the tree with the Add_Tree_Node() built-in
Because we want to handle a database stored menu, this sample use the first option with a
record group populated by a database table.
https://sheikyerbouti.developpez.com/tree/tree.htm 1/6
4/19/2018 Oracle Forms – Javabean
This table contains the corresponding menu options.
2. The database objects
This example needs 2 tables to handle the menu options and their corresponding roles:
1. The table that contains the menu options : MENU
CREATE TABLE MENU
(
ID NUMBER(5), -- Unique identifiant
LABEL VARCHAR2(128 BYTE), -- Tree label
ICON VARCHAR2(40 BYTE), -- Icon name
MASTER NUMBER(5), -- Parent ID
STATUS NUMBER(1) DEFAULT 1, -- Initial status of the node
VALUE VARCHAR2(128 BYTE) -- Dialog name to call
)
/
ICON contains the icon name (without extension) attached to the node.
STATUS can take one of the three possible values:
§ 0 normal (Not expandable)
§ 1 expanded
§ -1 collapsed
2. The table that contains the corresponding roles : MENU_ROLES
CREATE TABLE MENU_ROLES
(
ID NUMBER(5), -- Menu identifiant
ROLE VARCHAR2(30 BYTE) -- Role name
)
/
If you want to enable the database roles in the tree menu, you have to execute the following
steps :
§ Create the necessary roles in the database
CREATE ROLE ROLE_MENU1
CREATE ROLE ROLE_MENU2
...
§ Grant the roles to the users
GRANT ROLE_MENU1 to user1
GRANT ROLE_MENU1 to user2
...
This sample needs the ROLE_MENU1 and ROLE_MENU2 roles enabled.
Here is the content of the tables after the provided script is executed:
Table : MENU
ID LABEL ICON MASTER STATUS VALUE
1 Menu1 mainmenu 1
https://sheikyerbouti.developpez.com/tree/tree.htm 2/6
4/19/2018 Oracle Forms – Javabean
2 Menu1 Option 1 optionmenu 1 1 Dialog11
3 Menu1 Option 2 optionmenu 1 1 Dialog12
4 Menu1 Option 3 optionmenu 1 1
5 Menu1 Opt 3 Sub Opt 3 suboptionmenu 4 1 Dialog131
6 Menu2 mainmenu -1
7 Menu2 Option1 optionmenu 6 1 Dialog21
Table MENU_ROLES :
ID ROLE
1 ROLE_MENU1
2 ROLE_MENU1
3 ROLE_MENU1
4 ROLE_MENU1
5 ROLE_MENU1
6 ROLE_MENU2
7 ROLE_MENU2
3. How to populate the hierarchical tree
The Forms tree needs five column to work correctly:
SELECT STATUS, LEVEL, LABEL, ICON, VALUE
FROM MENU
Four of them come from the table:
STATUS that indicate the initiate status of the node
LABEL that is the visible label of the node
ICON that contains the (16x16 pixels) icon name of the node (can be NULL)
VALUE that contains the value of the node
LEVEL, wich is a “CONNECT BY” specific pseudo-column specifies the depth at
which the individual node appears.
The tree is populated with a record group...
HTREE := FIND_ITEM('BL_TREE.MENU');
V_IGNORE := POPULATE_GROUP('RG_TREE');
FTREE.SET_TREE_PROPERTY(HTREE, FTREE.RECORD_GROUP,'RG_TREE');
... , itself populated with a CONNECT BY query.
SELECT STATUS, LEVEL, LABEL, ICON, VALUE
FROM MENU
CONNECT BY PRIOR ID = MASTER
START WITH MASTER IS NULL
Two buttons are added to show how to expand or collapse all the nodes of the tree:
https://sheikyerbouti.developpez.com/tree/tree.htm 3/6
4/19/2018 Oracle Forms – Javabean
A single click populate the “Node selected” display item.
A double click populate the “Node activated” display item and call the corresponding screen:
https://sheikyerbouti.developpez.com/tree/tree.htm 4/6
4/19/2018 Oracle Forms – Javabean
A radio button allows to take into account the database roles.
For this purpose, the tree use another record group (RG_TREE_ROLES) that filters the
query.
SELECT m.STATUS, LEVEL, m.LABEL, m.ICON, m.VALUE
FROM MENU m
WHERE EXISTS
(
Select ROLE From MENU_ROLES
Where ID = m.ID
And ROLE IN (Select granted_role From user_role_privs)
)
CONNECT BY PRIOR ID = MASTER
START WITH MASTER IS NULL
4. The sample dialog
Ø Download the tree.zip file
Ø Unzip the tree.zip file
Ø Run the tree_install.sql script under Sql*Plus or Sql Developer.
https://sheikyerbouti.developpez.com/tree/tree.htm 5/6
4/19/2018 Oracle Forms – Javabean
Ø Create the 2 roles ROLE_MENU1 and ROLE_MENU2.
Ø Assign ROLE_MENU1 to one user and both roles to another user.
Ø Copy the 4 icons of the /icons directory in your icons directory
Ø Open the tree.fmb module (Oracle Forms 10.1.2)
Ø Compile all and run the module
https://sheikyerbouti.developpez.com/tree/tree.htm 6/6
