Create a Hierachical Tree form

Oracle Forms Hierarchical Tree widget is one of the more complex widgets available in Oracle Forms. The key to using this widget is to understand how to populate it with data. A Hierarchical Tree (HT) can be thought of as a Master and Detail or Parent and Child(ren) relationship.

An HT is made up of a root node, parent node and children node(s). Children nodes can be parents themselves. Think of it from the analogy of a Parent, Child and Grandchild. As of the date of this publication, I am not aware of any limitations on the number of node depths you can go with Oracle HT.

This “How To” document will use the sample HR.DEPARTMENTS and HR.EMPLOYEES tables as the source tables for the tree.

Each value in a tree is assigned a unique node_id. Node_IDs are assigned sequentially, in that as a value is added to the tree, it is assigned the next available ID. For example; If I load all of the rows from the HR.DEPARTMENTS table into a tree, the rows would be assigned as follows:

Now, when we add the employees in each department to the tree watch how NODE_IDs change.

Notice how the Node_ID is incremented and how the children are linked to the parent through the PARENT_NODE_ID.

All tree nodes must have a unique Parent and Child Node ID combination.

Now that you have a basic understanding of how the data loads into the tree, let’s build a form that uses a hierarchical tree. The following demo form is based on the sample Schema’s included with a standard installation of an Oracle database and uses the HR Schema and the DEPARTMENTS and EMPLOYEES tables mentioned previously. This demo form will demonstrate the following concepts:

Populate the tree
Programmatically expand and collapse a single tree node
Programmatically expand and collapse all tree nodes.
How to search for a value in the tree and how to search for subsequent instances of a specified search value.
How to select a value from a tree node and use this to filter records in a different data block.

Assumptions made by this document:

The attached sample form was built using Oracle Forms 10g R2 (10.1.2.0.2).
The concepts and sample code are Forms version generic and should work in Forms 6i and higher. You might have to make minor modifications to made it work with Forms 6i as I have not specifically created a Forms 6i version of this demo, but I have confirmed all of the Forms Built-ins used are present in Forms 6i.
That you have a basic understanding of Oracle Forms and have built and successfully run at least one form from the Oracle Forms development tool (Forms Builder).
With respects to item 3, your Oracle Forms installation is configured properly and you are able to run a form locally from the Forms Builder.
All steps are listed from the perspective of the Property Palette, in that I list all of the properties of each object rather than instruct you to draw an item on a canvas and make is so large, etc.
I have created and provided a Forms Program Unit (package) that simplifies using a hierarchical tree widget. You may use, modify or extending it to suite your needs. I’ve tried to encapsulate as much as possible into this package to simplify using hierarchical trees as much as possible. The methods I use in this sample use package variables instead of Global or Control block items as they are more efficient and offer greater flexibility. It is assumed that you are familiar with the use of Package variables.

Let’s Get Started

Start by opening the Oracle Forms builder, create a new Forms module and name it HIERACHY_TREE. Once you have a new forms module created and saved; complete the following steps:

1) Create a new Program Unit, name it TREE_CONTROL and select Package Spec as the Program Unit Type.

a) Add the following code to the package specification:

PACKAGE tree_control IS

-- Package Variables --

-- These Variables are used by supporting Triggers and

-- package methods.

-- --------------------------------------------------

-- The Block and Item Name of the Tree Control widget

v_item_name VARCHAR2(61);

-- The current and previously selected node's parent node ID

n_curr_parent_node Ftree.NODE;

n_prev_parent_node Ftree.NODE;

-- The current and previously select node's ID

n_curr_node Ftree.NODE := 0;

n_prev_node Ftree.NODE;

-- The current and previously selected node's label

v_curr_label VARCHAR2(60);

v_prev_label VARCHAR2(60);

-- The currnet and previously selected node's value

v_curr_value VARCHAR2(256);

v_prev_value VARCHAR2(256);

FUNCTION Create_RG (p_rg_name VARCHAR2 DEFAULT 'TREE_GROUP')

RETURN RecordGroup;

PROCEDURE ADD_RG_ROW (rg_id RECORDGROUP, n_state NUMBER, n_row NUMBER, n_level NUMBER, v_label VARCHAR2, v_icon VARCHAR2, v_data VARCHAR2);

PROCEDURE EXPAND_ALL (p_tree VARCHAR2);

PROCEDURE EXPAND_ONE (p_tree VARCHAR2);

PROCEDURE COLLAPSE_ALL (p_tree VARCHAR2);

PROCEDURE COLLAPSE_ONE (p_tree VARCHAR2);

PROCEDURE Node_Selected;

PROCEDURE Node_Activated;

PROCEDURE Find_Node(p_value VARCHAR2,

p_start_node NUMBER DEFAULT 0);

PROCEDURE CASE_SENS_FIND_NODE(p_value VARCHAR2,

p_start_node NUMBER DEFAULT 0);

END;

2) Create a new Program Unit, name it TREE_CONTROL and select Package Body as the program unit type.

a) Add the following code to the Package Body:

PACKAGE BODY tree_control IS

-- Create_RG

-- Creates an Instance of a Record Group

-- ---------------------------------------

FUNCTION Create_RG (p_rg_name VARCHAR2 DEFAULT 'TREE_GROUP') RETURN

RecordGroup IS

rg_id RecordGroup;

gc_id GroupColumn;

BEGIN

rg_id := Find_Group(p_rg_name);

-- Make sure the RG doesn't already exist.

IF ( NOT Id_NULL(rg_id) ) THEN

Delete_Group(rg_id);

END IF;

IF ( Id_NULL(rg_id) ) THEN

rg_id := Create_Group(p_rg_name);

-- Initial state.

gc_id := Add_Group_Column(rg_id, 'state', NUMBER_COLUMN);

-- Add Node Tree Dept

gc_id := Add_Group_Column(rg_id, 'dept', NUMBER_COLUMN);

-- Label

gc_id := Add_Group_Column(rg_id, 'label', CHAR_COLUMN,50);

-- Icon

gc_id := Add_Group_Column(rg_id, 'icon', CHAR_COLUMN,10);

-- Data

gc_id := Add_Group_Column(rg_id, 'data', CHAR_COLUMN,50);

END IF;

RETURN rg_id;

END Create_RG;

-- ADD_RECORD

-- Adds a record to the Record Group

-- ---------------------------------------

PROCEDURE ADD_RG_ROW ( rg_id RECORDGROUP,

n_state NUMBER,

n_row NUMBER, n_level NUMBER,

v_label VARCHAR2,

v_icon VARCHAR2,

v_data VARCHAR2)

AS

BEGIN

Add_Group_Row(rg_id, END_OF_GROUP);

Set_Group_Number_Cell('Tree_Group.state', n_row, n_state);

Set_Group_Number_Cell('Tree_Group.dept', n_row, n_level);

Set_Group_Char_Cell('Tree_Group.label', n_row, v_label);

Set_Group_Char_Cell('Tree_Group.icon', n_row, v_icon);

Set_Group_Char_Cell('Tree_Group.data', n_row, v_data);

END ADD_RG_ROW;

-- EXPAND_ALL

-- Starting from the ROOT node, this procedure will

-- Expand all nodes of a tree.

-- PARAMETERS:

-- p_tree - Name of the Tree Control object.

-- ---------------------------------------

PROCEDURE expand_all ( p_tree VARCHAR2 ) IS

n_node ftree.NODE;

i_tree ITEM;

v_state VARCHAR2(30);

BEGIN

-- Find the Tree Item

i_tree := Find_Item(p_tree);

-- Find the ROOT Node of the tree

n_node := Ftree.Find_Tree_Node(i_tree, '');

--LOOP through all of the nodes and expand each one

--if it is collapsed

<<expand_tree>>

WHILE NOT Ftree.ID_NULL(n_node) LOOP

v_state := Ftree.Get_Tree_Node_Property(i_tree, n_node, Ftree.NODE_STATE);

IF ( v_state = Ftree.COLLAPSED_NODE ) THEN

Ftree.Set_Tree_Node_Property(i_tree, n_node, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);

END IF;

n_node := Ftree.Find_Tree_Node(i_tree, '', Ftree.Find_Next, Ftree.NODE_LABEL, '',n_node);

END LOOP expand_tree;

END expand_all;

-- EXPAND_ONE

-- Starting from the ROOT node, this procedure will

-- Expand all nodes of a tree.

-- PARAMETERS:

-- p_tree - Name of the Tree Control object.

-- ---------------------------------------

PROCEDURE EXPAND_ONE (p_tree VARCHAR2) IS

v_state VARCHAR2(30);

i_tree ITEM;

BEGIN

-- Find the Tree Item

i_tree := Find_Item(p_tree);

v_state := Ftree.Get_Tree_Node_Property(i_tree, n_curr_node, Ftree.NODE_STATE);

IF ( v_state = Ftree.COLLAPSED_NODE ) THEN

Ftree.Set_Tree_Node_Property(i_tree, n_curr_node, Ftree.NODE_STATE, Ftree.EXPANDED_NODE);

END IF;

END EXPAND_ONE;

-- COLLAPSE_ALL

-- Starting from the ROOT node, this procedure will

-- Collapse all nodes of a tree.

-- PARAMETERS:

-- p_tree - Name of the Tree Control object.

-- ---------------------------------------

PROCEDURE collapse_all ( p_tree VARCHAR2 ) IS

n_node Ftree.NODE;

i_tree ITEM;

v_state VARCHAR2(30);

BEGIN

-- Find the Tree Item

i_tree := Find_Item(p_tree);

-- Find the root node

n_node := Ftree.Find_Tree_Node(i_Tree, '');

-- Loop through all of the nodes and collapse if expanded.

<<collapse_tree>>

WHILE ( NOT Ftree.ID_NULL(n_node) ) LOOP

v_state := Ftree.Get_Tree_Node_Property(i_tree, n_node, Ftree.NODE_STATE);

IF ( v_state = Ftree.EXPANDED_NODE ) THEN

Ftree.Set_Tree_Node_Property(i_tree, n_node, Ftree.NODE_STATE, Ftree.COLLAPSED_NODE);

END IF;

n_node := Ftree.Find_Tree_Node(i_tree, '', Ftree.FIND_NEXT, Ftree.NODE_LABEL, '', n_node);

END LOOP collapse_tree;

END collapse_all;

-- COLLAPSE_ONE

-- Starting from the ROOT node, this procedure will

-- Expand all nodes of a tree.

-- PARAMETERS:

-- p_tree - Name of the Tree Control object.

-- ---------------------------------------

PROCEDURE COLLAPSE_ONE (p_tree VARCHAR2) IS

v_state VARCHAR2(30);

i_tree ITEM;

BEGIN

-- Find the Tree Item

i_tree := Find_Item(p_tree);

v_state := Ftree.Get_Tree_Node_Property(i_tree, n_curr_node, Ftree.NODE_STATE);

IF ( v_state = Ftree.EXPANDED_NODE ) THEN

Ftree.Set_Tree_Node_Property(i_tree, n_curr_node, Ftree.NODE_STATE, Ftree.COLLAPSED_NODE);

END IF;

END COLLAPSE_ONE;

-- NODE_SELECTED

-- Used in When-Tree-Node-Selected trigger to capture

-- the Current Node, Current Parent Node, Current Node Label,

-- Prev Node, Prev Parent Node, and Prev Node Label.

-- --------------------------------------------

PROCEDURE Node_Selected IS

BEGIN

IF ( :SYSTEM.Trigger_Node_SELECTED = 'TRUE' ) THEN

n_curr_node := :SYSTEM.Trigger_Node;

n_curr_parent_node := Ftree.Get_Tree_Node_Parent( v_item_name, :SYSTEM.Trigger_Node);

v_curr_label := Ftree.Get_Tree_Node_Property( v_item_name,:SYSTEM.Trigger_Node, Ftree.NODE_LABEL);

ELSE

n_prev_node := :SYSTEM.Trigger_Node;

n_prev_parent_node := Ftree.Get_Tree_Node_Parent( v_item_name,:SYSTEM.Trigger_Node);

v_prev_label := Tree_Control.v_curr_label;

END IF;

END Node_Selected;

-- NODE_ACTIVATED

-- Used in the When-Tree-Node-Activated trigger

-- to capture the NODE_VALUE of the selected node.

-- -------------------------------------------

PROCEDURE Node_Activated IS

BEGIN

IF ( v_curr_value IS NOT NULL ) THEN

v_prev_value := v_curr_value;

END IF;

v_curr_value := Ftree.Get_Tree_Node_Property( v_item_name,n_curr_node, Ftree.NODE_VALUE);

END Node_Activated;

-- FIND_NODE

-- Called by a User defined button to search the

-- tree for a value. If a value is in the tree more than once,

-- calling the FIND_NODE a subsequent time will find the next

-- occurance of the value specified in P_VALUE.

-- Parameters:

-- p_value - the value you are searching for

-- p_start_node - defaults to 0 (zero). If specified,

-- the search will begin at this node

-- location, else it starts at the ROOT node.

-- -------------------------------------------

PROCEDURE find_node (p_value VARCHAR2,

p_start_node NUMBER DEFAULT 0) IS

htree ITEM;

found_node FTREE.node;

BEGIN

htree := Find_Item(Tree_Control.v_item_name);

IF ( p_start_node = 0 ) THEN

found_node := Ftree.Find_Tree_Node(htree ,p_value ,Ftree.FIND_NEXT ,Ftree.NODE_LABEL ,Ftree.ROOT_NODE ,Ftree.ROOT_NODE);

ELSE

found_node := Ftree.Find_Tree_Node(htree ,p_value ,Ftree.FIND_NEXT ,Ftree.NODE_LABEL ,Ftree.ROOT_NODE ,p_start_node);

END IF;

IF ( NOT Ftree.ID_NULL(found_node) ) THEN

Clear_Message;

Message('Value: "'||p_value||'" found');

n_curr_node := found_node;

Ftree.Set_Tree_Selection(htree, found_node, Ftree.SELECT_ON);

ELSE

Clear_Message;

Message('Value: "'||p_value||

'" not found. Please try again.');

END IF;

EXCEPTION

WHEN OTHERS THEN

Clear_Message;

Message('Value: "'||p_value||

'" not found. Please try again.');

Message(' ');

END Find_Node;

END;

3) Now that the TREE_CONTROL package has been created we need a tree widget to manipulate.

a) First we need to create a Canvas object to assign our tree widget too

i) Right-click on the Canvas and open the Property Palette and set the following properties:

(1) Name: TREE

(2) Canvas Type: Content (should be the default value)

(3) Window: WINDOW1 (should automatically be created with the new Forms Module)

(4) Width: 540

(5) Height: 324

b) Right-Click on the TREE Canvas in the Object Navigator and select the Layout Editor

i) Click on the Hierarchical Tree icon in the Layout Toolbar and then draw a tree widget anywhere on the TREE canvas. This will create a data block in addition to the tree item.

ii) Open the property palette for the tree item and set the following properties:

(1) Name: MY_TREE

(2) Canvas: Tree (should already be set)

(3) X Position: 15

(4) Y Position: 23

(5) Width: 205

(6) Height: 278

iii) Now, open the property palette for the block that was created and set the following properties:

(1) Name: MY_TREE_DATA

(2) Database Data Block: No

4) Now it is time to build a Record Group and use this to populate the Tree.

a) Create a Form level When-New-Form-Instance trigger and add the following code:

DECLARE

CURSOR c_dept IS

SELECT *

FROM hr.departments;

CURSOR c_emp ( n_dept NUMBER ) IS

SELECT *

FROM hr.employees

WHERE department_id = n_dept;

n_level NUMBER := 1;

n_row NUMBER := 1;

rg_id RecordGroup;

node Ftree.Node;

BEGIN

Tree_Control.v_item_name := 'MY_TREE_DATA.MY_TREE';

rg_id := Tree_Control.Create_RG;

<<parent>>

FOR r_dept IN c_dept LOOP

-- Add Parent Department_ID

Tree_Control.ADD_RG_ROW(rg_id, ftree.collapsed_node, n_row, n_level, r_dept.department_name, null, r_dept.department_id);

n_row := n_row + 1;

n_level := n_level + 1;

--Add Child Employee Records

<<child>>

FOR r_emp IN c_emp(r_dept.department_id) LOOP

Tree_Control.ADD_RG_ROW(rg_id, ftree.collapsed_node, n_row, n_level, r_emp.last_name, NULL, r_emp.employee_id);

n_row := n_row + 1;

END LOOP child;

n_level := n_level - 1;

END LOOP parent;

ftree.Add_Tree_Data(Tree_Control.v_item_name, ftree.ROOT_NODE, ftree.PARENT_OFFSET, ftree.LAST_CHILD, ftree.RECORD_GROUP, rg_id);

END;

5) At this point you should be able to run your form to confirm the Tree is populated. Your form should look similar to the following:

6) Now, let’s add the code to expand and collapse all tree nodes.

a) Create a new data block and set the following properties:

i) Name: CONTROL

ii) Database Data Block: No

b) Add two new items to the CONTROL block. These items are going to be the Expand All and Collapse All buttons.

c) Select the first item and set the following properties in the property palette:

i) Name: BTN_EXPAND_ALL

ii) Item Type: Push Button

iii) Label: >>

iv) Canvas: TREE

v) X Position: 2

vi) Y Position: 4

vii) Width: 18

viii) Height: 18

ix) Tooltip: Expand All Nodes

d) Select the second item added and set the following properties in the property palette:

i) Name: BTN_COLLAPSE_ALL

ii) Item Type: Push Button

iii) Label: <<

iv) Canvas: TREE

v) X Position: 214

vi) Y Position: 4

vii) Width: 18

viii) Height: 18

ix) Tooltip: Collapse All Nodes

e) Select the BTN_EXPAND_ALL item and add a When-Button-Pressed trigger with the following code:

Tree_Control.Expand_all(Tree_Control.v_item_name);

f) Select the BTN_COLLAPSE_ALL item and add a When-Button-Pressed trigger with the following code:

Tree_Control.Collapse_all(Tree_Control.v_item_name);

g) Let run the form and test your code. Your form should look similar to the following:

h) Now we will add the Expand One and Collapse One buttons.

i) Add two more items between BTN_EXPAND_ALL and BTN_COLLASPE_ALL buttons in the CONTROL block.

j) Select the new item that follows the BTN_EXPAND_ALL button and set the following properties in the property palette:

i) Name: BTN_EXPAND_ONE

ii) Item Type: Push Button

iii) Label: >

iv) Canvas: TREE

v) X Position: 21

vi) Y Position: 4

vii) Width: 18

viii) Height: 18

ix) Tooltip: Expand selected node

x) Add a When-Button Pressed trigger with the following code:

Tree_Control.EXPAND_ONE(Tree_Control.v_item_name);

k) Select the next newly added item and set the following properties in the property palette:

i) Name: BTN_COLLAPSE_ONE

ii) Item Type: Push Button

iii) Label: <

iv) Canvas: TREE

v) X Position: 195

vi) Y Position: 4

vii) Width: 18

viii) Height: 18

ix) Tooltip: Collapse selected node

x) Add a When-Button Pressed trigger with the following code:

Tree_Control.COLLAPSE_ONE(Tree_Control.v_item_name);

l) From the Object Navigator, select the MY_TREE_DATA block and expand the MY_TREE item and add the following triggers and code:

i) When-Tree-Node-Selected

Tree_Control.Node_Selected;

ii) When-Tree-Node-Activated

Tree_Control.Node_Activated;

m) Run the form and let’s test what we’ve added. Select a node of the tree and click the Expand One and Collapse One buttons. Your form should look similar to the following:

n) Now, let’s add the ability to search the tree. Note; Values in the tree are case sensitive with respects to how the data is stored in the table.

o) Click on the CONTROL.BTN_EXPAND_ONE item and add two new items.

p) Click on the first new item and set the following properties:

i) Name: FIND_NAME

ii) Item Type: Text Item (default value)

iii) Maximum Length: 60

iv) Canvas: TREE

v) X Position: 43

vi) Y Position: 6

vii) Width: 85

viii) Height: 14

ix) Tooltip: Enter a name to search for

q) Select the next new item and set the following properties:

i) Name: BTN_FIND

ii) Item Type: Push Button

iii) Label: Find

iv) Access Key: d

v) Canvas: TREE

vi) X Position: 129

vii) Y Position: 4

viii) Width: 30

ix) Height: 18

x) Tooltip: Click to search for the given name

r) Add a When-Button-Pressed trigger with the following code:

-- Find from Current selected node.

Tree_Control.find_node(:CONTROL.Find_Name, Tree_Control.n_curr_node);

Note: the comment in the code. The find function always starts at the currently select tree node. This enables the ability to search and find more than first instance of a given value.

s) Again, let’s run the form to test our changes.

t) In the search field, enter “Smith” and click the find button.

u) Your form should look similar to the following:

7) Now let’s add the final code to select a tree node value and perform an action based on the value. In this example, we will query the Departments and Employees tables.

a) Create two new data blocks using the Data Block Wizard for the HR.DEPARTMENTS and HR.EMPLOYEES tables.

i) For the Departments table, only display the DEPARTMENT_ID and DEPARTMENT_NAME columns.

ii) Allow the data block wizard to call the Layout Wizard and use a “Form” layout versus a “Tabular” layout.

iii) For the EMPLOYEES table, select all columns to be displayed.

iv) Allow the data block wizard to call the Layout Wizard and use a “Form” layout versus a “Tabular” layout.

v) Select the MY_TREE_DATA block, MY_TREE item and add the following triggers and code:

(1) When-Tree-Node-Activated

BEGIN

Tree_Control.Node_Activated;

IF ( Tree_Control.n_curr_parent_node = 0 ) THEN -- Dept Selected

Set_Block_Property('DEPARTMENTS',ONETIME_WHERE, 'DEPARTMENT_ID = '||TO_NUMBER(Tree_Control.v_curr_value));

Go_Block('DEPARTMENTS');

ELSE -- Employee selected.

Set_Block_Property('EMPLOYEES',ONETIME_WHERE, 'EMPLOYEE_ID = '|| TO_NUMBER(Tree_Control.v_curr_value));

Go_Block('EMPLOYEES');

END IF;

Execute_Query;

Go_Item('MY_TREE_DATA.MY_TREE');

END;

(2) When-Tree-Node-Selected

Tree_Control.Node_Selected;

vi) Now, let’s run the form and validate everything works correctly. Your form should look similar to the following:

vii) Double-clicking the Purchasing node will populate the Department region. Double-clicking the user Raphaely will populate the Employee region.

This ends this Forms Tree widget demonstration. This demonstration utilizes the many of the functions available to a Tree widget and should sufficiently familiarize you with the use of a tree widget. There are many other Forms Built-in available for the Tree widget. You should now be familiar enough with the use of a Forms Tree widget that you can figure out how to use the remaining Tree built-ins.