How-To Document

Based on Oracle Application Express (formerly HTML DB) version 1.5

After completing this How-To, you should be able to understand:

• How to create a new tree
• How to modify a tree
• How to include an existing tree on a page

The sections include:

Introduction

A tree is a hierarchical navigation or display mechanism. It is based upon a table that contains a hierarchical relationship. The tree is created as a query that specifies the relationship by identifying the ID and parent ID columns in a table or view. The tree definition also contains the starting point. A tree is displayed in a region on a page. Once a tree is created, it can be referenced by multiple regions.

This How-To will use an Employee table and the hierarchical Manager/Employee relationship within the table. The report created will display the hierarchy within the organization and allow for navigation to edit employees.

To use a tree purely for navigation, you need a navigation table containing the data to display along with a page number for each topic that requires navigation. To display the tree on every page of your application you create the tree on page 0 of your application. Alternately, you could create the tree on a menu page that can be easily navigated to by users.

Oracle HTML DB contains tree navigation. To view tree navigation for an application, navigate to the Page Definition. Select Page Reports in the left navigation page and then select Tree View. This displays all the objects on the current page in a hierarchical format with links to allow creation and editing. The image below is that report for page 200 in the Sample Application.

Image 1 - Tree View of Page

The discussion walks you through the process of creating a tree, modifying the defaulted tree, and then including that tree in another page. Also discussed are advanced tree concepts, such as displaying additional links. Next are the specific instructions to follow to create the objects used in this How-To in your own environment so that you can replicate this lesson. Lastly is the DDL for the objects used in this How-To.

For this exercise, the demonstration application Sample Application will be used as the base application. This application is included when a new workspace is provisioned.

Creating a Tree

When a tree is created, it can either be included on a new page or added to an existing page. The instructions below walk through the process of creating a new tree on a new page. For more information on creating trees, see chapter 8, "Building Application Components" in the Oracle HTML DB User's Guide.

To create the initial tree:

1. Click the Build icon in the upper right corner of Oracle HTML DB.
2. Under Available Applications, select Sample Application and click Go.
3. Select the Trees tab.
4. Click Create> to create a new tree.
5. Identify Page Attributes:
   1. Page - If you want the tree to be included on a new page, you enter a new page number. If you want the tree to be included on an existing page, you enter the existing page number. For this exercise, the tree will be created on a new page.
   2. Page Name - If the tree is to be included on a new page, this will become the name of the page. If the tree is to be included on an existing page, this value is not used.
   3. Region Template - The template for the region where the tree will be referenced.
   4. Region Name - The name of the new region created to reference the new tree. If the selected Region Template includes the title, this value will be displayed as the title.

   Image 2 - Identify Page Attributes

   

6. Identify Tabs - If the tree is to be included on a new page, identify the tab implementation for that page. If the tree is to be included in an existing page, the wizard skips this step.

7. Image 3 - Identify Tabs

   

8. Identify Tree Attributes:
   1. Tree Name - This will become the name of the tree. A Tree Definition is a standalone object. This separate definition allows a tree to be referenced by more than one region.
   2. Default Expanded Levels - This identifies how many levels to expand when the tree is first displayed. It can be any number between 1 and 100.
   3. Start Tree - The tree definition needs to know where to start the tree, this is known as the root node. A tree start identifies the specific row in the table to use as the root node. This value can be set by an Item with a Popup LOV, based on a Query, or a Static Value.
      1. An Item with a Popup LOV allows the user to determine the root node. In most cases, the LOV query would be based on the same table as the tree. This can work well for navigating large trees.
      2. A Query allows the root node to be dynamic. A good case for starting a tree with a query is where the query would select the only employee that has no parent. You must be careful with starting a tree with a query because the query should return one and only one row. If no rows are returned, the tree region will be blank. If more than one row is returned, the tree display will begin with the 1st record retrieved.
      3. If the data in the table is relatively static or predictable, you can start your tree with a static value. For this example, the tree will start will the static value of 1 because the head of the company's ID is 1.

   Image 4 - Identify Tree Attributes

   

9. Identify Tree Template - In addition to the region template, a look needs to be selected for the tree display. There are three included formats; Standard, with Folders, or with Information Icons. These are not templates that can be separately maintained like region templates can be. To achieve a look that is different from the default formats, select the format that is closest to the look you desire. Once the tree has been created, you can modify the look from within the tree definition.

10. Image 5 - Identify Tree Template

    

11. Identify Tree Start - This display will depend upon how you chose to start the tree back on the Tree Attributes page (step 7).
    1. If the tree start is an Item with a Popup LOV, you will be prompted for the LOV query. This will produce a displayed item, of type Popup LOV with the query provided as the LOV query.
    2. If the tree start is based on a Query, you will be prompted for the query. This will produce a hidden item whose value is determined by the provided query.
    3. If the tree start is a static value, you will be prompted for that value. This will produce a hidden item whose value is set to the provided value.

12. Image 6 - Identify Tree Start

    

13. Identify Expand/Collapse - There are three buttons that can be created to help users manipulate the tree. They are Collapse All, Expand All, and/or Reset Tree.
    1. Collapse All closes the tree down to display just the starting node.
    2. Expand all expands the tree to display all the levels and their associated values. If the tree is large, consider excluding this button, as it will have a big impact on performance as well as the display.
    3. Reset Tree brings the user back to the number of levels set for Default Expanded Levels in Step 7.

    Any or all of the buttons can be included within the region that will reference your tree. These buttons are part of the resulting region, not the Tree Definition. If the tree is referenced by another region, the buttons selected here will not be automatically created.

14. Image 7 - Identify Expand/Collapse

    

15. Identify Table/View Owner - Select the owner of the table or view that your tree will be based on. For this example, it is the owner of the Sample Application which was the default schema created when your Workspace was provisioned.

16. Image 8 - Identify Table/View Owner

    

17. Identify Table/View Name - Using the Popup LOV, select the table or view that your tree will be based on. Alternatively, you can type in the table or view name. For this example, it is HT_EMP.

18. Image 9 - Identify Table/View Name

    

19. Identify Query - You do not code the query. Instead, you identify the following:
    1. ID - The primary key column of the table that is then used as the foreign key reference.
    2. Parent ID - The foreign key column of the table that references the primary key column, thus forming the hierarchy.
    3. Leaf Node Text - Select the column to display. Although this is a singular selection, you can edit the resulting query after it is defaulted to concatenate in other columns.
    4. Link Option - If the tree is for informational purposes only, a Link Option is not necessary. If the tree will be used for navigation, select Existing Application Item.

20. Image 10 - Identify Query

    

21. Identify Link - If you selected the Link Option Existing Application Item, use this page to identify the page to navigate to and the item to which to pass the value. By default, the ID will be passed to the item you select. The Image below shows linking to the Form page created in How-To Layout a Form.

22. Image 11 - Identify Link

    

23. Identify Where and Order by - If the query should be restricted, or display in a specific order, this is where you would include the details. It is important to expand the Current Query region to review the query. This will help identify the order of the columns to assist in the order by if you use column order aliases (for example, 1, 2 desc).

    The image below shows the tree being restricted to the people in the SALES department and being ordered by EMP_LAST_NAME.

24. Image 12 - Identify Where and Order by

    

25. Tree Confirmation - This page appears so you can review your selections. To make changes before creating the tree, click <Previous to regress through the steps above to make the necessary modifications. To create the tree, click Finish.

26. Image 13 - Tree Confirmation

    

27. Success - Now the tree has been created along with a new page with a region that references the tree. At this time the page can either be run or edited. The image below is the page running. Notice that only 2 levels of the tree are open because that is what was set in Default Expanded Levels.

28. Image 14 - Running Defaulted Tree

    

Modifying a Tree - Page Changes

Once a tree has been created, it can be modified. The sections below detail the changes that can be made to the tree definition versus those that need to be made to the page displaying the tree.

Looking at Image 14 above, the display could be more attractive. Specifically, the buttons need to be modified to look like the application. The buttons are part of the page that was created. They were defaulted because of the selection to include them in Step 10 above, Identify Expand/Collapse. These buttons were created as HTML buttons and placed at the top of the region.

To modify the tree definition:

1. Click the Build icon in the upper right corner of Oracle HTML DB.
2. Under page list, select 901. Tree Page. The Page Definition will appear.

3. Image 15 - Page Definition

   

4. Select the first button, in this case COLLAPSE_ALL. The Button Definition page will appear.
5. Change the:
   1. Button Alignment to Right
   2. Button Style to Template Based Button
   3. Button Template to Opal Slices
   Alternatively, you can create images to use as buttons.

6. Image 16 - Button Definition

   

7. Click Apply Changes for this button and make the same modifications to the other two buttons.

Tree navigation is not always self-evident to users. To solve this, include a hint to explain how to navigate to the employee details from the tree. To create a hint:

1. From the Page Definition, select the Tree region. The Region Definition will appear. Notice that the Tree Source is displayed but not editable. You can use the included link to edit the Tree Definition from here, if necessary.
2. Scroll down to the Header and Footer Text and enter a hint.

3. Image 17 - Region Definition

   

4. Click Apply Changes for this region.

You can change the Start Tree from the Page Definition. The Tree Definition points to the item that stores the starting point of the tree. The item itself exists on the page created for the tree and the value of the item is calculated on that page. As mentioned earlier, if the Start Tree was an Item with a Popup LOV, the resulting item will be displayed and will contain a locally defined LOV. If the Start Tree was based on a Query, the resulting item will be hidden and the Item Source would be set to SQL Query with the provided query as the Source value. In this example, the Start Tree was a Static Value. The resulting item, P901_TREE_ROOT, is hidden and the Item Source is statically assigned to 1.

Image 18 - Start Tree Item Source

With the changes described above, the page will now look like the Image below.

Image 19 - After Page Changes

Modifying a Tree - Tree Definition

Looking at Image 19 above, only the last name is displayed. More details can be displayed by editing the Tree Definition. The Tree Definition can be edited from Trees under the Application tab or by selecting the T to the left of the region referencing the tree from the Page Definition.

To modify a Tree Definition:

1. Click the Build icon in the upper right corner of Oracle HTML DB.
2. Under page list, select 901. Tree Page. The Page Definition will appear.
3. Select the T next to the Tree region. The Tree Definition will appear.
4. The first item in the select statement is the ID Column. The second is the Parent ID Column. The third is the value displayed. The fourth is the link.

   The first four items in the select list are used to display the tree. The fifth and sixth items, a1 and a2, are used for substitution strings. You can use these items to select additional columns from the table, or to select static values that they can be referenced in the tree template. They would be referenced as #a1# and #a2#, respectively.

   Change the third item to include the Employee's First Name.

5. Image 20 - Tree Definition

   

6. Additionally, you can modify the following:
   1. Max Levels - Make a selection to specify the number of levels initially opened when the tree displays.
   2. Application Item - Make a selection to specify the item that stores the starting point for the query.
   3. Where Clause - Change the Where Clause by editing the Query. The Where Clause could be removed or could be modified to only retrieve employees in a department set by a new item that could be created.
   4. Order By - Change the Order By by editing the Query.

The Tree Definition contains a number of additional sections including Before and After Tree HTML, Static Tree Node Templates, Dynamic Expand and Contract Tree Node Templates, Node Text Templates, and Hyper Text Link Templates. This information defaults from your tree template selection. You can change the look of a tree by changing these attributes.

With the change to the displayed item, the page will now look like the Image below. In this display, Expand All has been used to display all the data.

Image 21 - After Tree Change

Referencing an Existing Tree

A tree can be referenced by more than one region. It is supported, but not suggested because when you reference a tree more than once, the page that contains the Start Tree controls the display on all pages (recall that the Start Tree item is specified in the Tree Definition). Also, when you contract the tree on one page, it contracts on all. This can be very confusing to users. To display a tree on multiple pages, it is recommended, as discussed in the Introduction, to create it on Page 0. If there are specific pages where it should not display, these can be coded into a Conditional Display or a Security Scheme that would then be applied to the region on Page 0.

If you create a tree and then remove the region or page that references it, the tree will still exist. You can reference this tree in a new region but you must be careful with the Start Tree. If the page has been deleted then the Start Tree item will have also been deleted. A new item must be created and then added to the Tree Definition.

To reference an existing tree from a new page:

1. Click the Build icon in the upper right corner of Oracle HTML DB.
2. Under Available Applications, select Sample Application, and click Go.
3. Under page list, select 901. Tree Page. The Page Definition will appear.
4. Under Page, click Create.
   1. Identify Page Attributes - Enter page a new page number. For this example, 902.
   2. Identify Page Name and Title - For this example, name the page Second Tree Reference.
   3. Identify Tabs - Leave the default for No Tabs.
   4. Select Finish.
5. Select Edit Page.
6. Under Regions, click Create.
   1. Select Other.
   2. Select Tree.
   3. Identify Region Attributes:
      1. Title - The name of the new region created to reference the tree.
      2. Region Template - The template for the region where the tree will be referenced.
      3. Display Point - Where the new region will be displayed on the page. The Display Points are determined by the page level template, which is an attribute of the page. If there is no page level template, Oracle HTML DB uses the default page level template defined in the Application Definition. You can view specific positions by selecting the flashlight icon to the right of the Display Point list.
      4. Sequence - The order in which the regions will display.
      5. Column - The Column in which the region will display.

   4. Image 22 - Region Details for Referenced Tree

      

   5. Identify Page Region Source - Select the existing Tree. At this point, you can create the region, or you can continue and add a conditional display.

   6. Image 23 - Source for Referenced Tree

      

   7. Click Create Region.

If the page were run now, the tree would be blank. This is because the Start Tree item will have no value. To make the tree display on this page, a Before Header Computation needs to be created that will set the Start Tree item, P901_TREE_ROOT, to 1. With that in place, the tree would now display.

Advanced Tree Concepts

This section will be added at a later date.

Instructions for creating HT_EMP table

To create the HT_EMP table and associated objects:

1. Click the SQL icon in the upper right corner of Oracle HTML DB.
2. Select the Scripts tab.
3. Click Create.
4. Name the Script HT_EMP and click Next>.
5. Paste in the DDL from the next section.
6. Click Create Script.
7. Click Run in the row adjacent to your HT_EMP script.
8. Select Parse As to be the schema owner that was initially provisioned with your workspace.
9. Click Run Script.

This procedure creates a table with insert and update triggers, a sequence to populate the primary key and will insert several rows into the table.

DDL for HT_EMP

The following is the code to replicate the table used in the example. If this table already exists from another How-To, you must delete both the HT_EMP table and the HT_EMP_SEQ sequence.

create table ht_emp (
   emp_id                 number       primary key,
   emp_first_name         varchar2(30) not null,
   emp_middle_initial     varchar2(1),
   emp_last_name          varchar2(45) not null,
   emp_part_or_full_time  varchar2(1)  not null check (emp_part_or_full_time in ('P','F')),
   emp_salary             number,
   emp_dept               varchar2(20) check (emp_dept in ('SALES','ACCOUNTING','MANUFACTURING','HR')),
   emp_hiredate           date,
   emp_manager            number       references ht_emp,
   emp_special_info       varchar2(2000),
   emp_telecommute        varchar2(1) check (emp_telecommute in ('Y')),
   rec_create_date        date         not null,
   rec_update_date        date)
/

insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (1,'Larry','R','CEO','F',
    100000,'SALES',sysdate,null,'cell phone number is xxx.xxx.xxxx
home phone is yyy.yyy.yyyy',null,
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (2,'Joe',null,'Manager','F',
    80000,'SALES',sysdate,1,'cell phone number is 123.456.7777',null,
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (3,'Jane','I','Boss','F',
    80000,'SALES',sysdate,1,null,null,
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (4,'Scott','N','Tiger','F',
    70000,'SALES',sysdate,1,'Tech Guru','Y',
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (5,'Sally',null,'Sales','F',
    60000,'SALES',sysdate,2,null,null,
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (6,'Joe','M','Software','F',
    60000,'SALES',sysdate,2,null,null,
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (7,'Peter','O','Phone','P',
    40000,'SALES',sysdate,6,'Assistant to Joe',null,
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (8,'Anita','A','Hardware','F',
    80000,'SALES',sysdate,1,null,'Y',
    sysdate)
/
commit
/
insert into ht_emp
   (emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_full_time, 
    emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info, emp_telecommute,
    rec_create_date)
values
   (9,'Mike','Q','Resource','F',
    70000,'HR',sysdate,1,'Head of HR',null,
    sysdate)
/
commit
/

create sequence ht_emp_seq
   start with 20
/

create or replace trigger bi_ht_emp
      before insert on ht_emp
      for each row
   begin
      select ht_emp_seq.nextval
        into :new.emp_id
        from dual;
      :new.rec_create_date := sysdate;
   end;
/

create or replace trigger bu_ht_emp
      before update on ht_emp
      for each row
   begin
      :new.rec_update_date := sysdate;
   end;
/