There is a 4-parts tutorial on tree component, but it does not concern handling of a "real" tree, with multiple sub-nodes of different depth. How one can populate with single query , say, such a tree on employees table (employee_id vs manager_id), with leaves having the icons different from nodes, with expandable nodes and non-expandable leaves? Do we need to re-populate every nodes children in 'on click' event?

asked 23 Oct '12, 08:23

Stan12's gravatar image

accept rate: 0%

Hi Stan,

In Formspider, it's not possible to populate the tree with a single hierarchical query. Instead, you can define a query which populates child nodes of an expanded node if it's not expanded before. Therefore, you need to populate a single node only once. Here is a sample procedure which populates child nodes of the expanded node only if it's necessary;

procedure expand is
  v_expandedNodeKey_tx varchar2(4000);
  -- get expanded nodekey
  v_expandedNodeKey_tx := api_tree.getexpandednodekey('panel.tree');
  -- populate its child nodes if this node is not expanded before
  if api_treenode.isprevexpanded('panel.tree', v_expandedNodeKey_tx) = 'N' then
    api_datasource.setBindVar('datasource.bindvar', v_expandedNodeKey_tx);
    api_treenode.populatechildren('panel.tree', v_expandedNodeKey_tx);
  end if;

You can make leaves non-expandable using the nodeStateColumn attribute of the tree component. In the api_treenode package, there are three constants named LEAF, COLLAPSED and EXPANDED which can be used as the nodeState value.

You can also assign different icons to different nodes using the iconColumn attribute of the tree component.

An example query on employees table is as follows;

select * 
from (
      select e.first_name || ' ' || e.last_name as fullName
            ,decode(connect_by_isleaf, 1, tree_demo.LEAF, tree_demo.COLLAPSED) as nodeState
            ,decode(connect_by_isleaf, 1, 'apps/yourApp/images/employeeIcon.png', 'apps/yourApp/images/managerIcon.png') as icon
      from employees e
      start with e.manager_id = :manager_id
        or (e.manager_id is null and :manager_id is null)
      connect by e.manager_id = prior e.employee_id and level <=2
where manager_id = :manager_id
  or (manager_id is null and :manager_id is null)

Note that tree_demo is simple package holding references to api_treenode constants;

  function collapsed return varchar2;
  function leaf return varchar2;
function leaf return varchar2 is
  return api_treenode.LEAF;

function collapsed return varchar2 is
  return api_treenode.COLLAPSED;

The icon value 'apps/yourApp/images/employeeIcon.png' is an example path of the icon file existing in a folder which should be created under the "apps" folder of your middle-tier. In this example the icon file is placed under the "yourApp/images" folder.

In your panel XML, you can declare your tree as;

<tree name="tree" displayColumn="FULLNAME" nodeKeyColumn="EMPLOYEE_ID" dataSource="tree1" nodeStateColumn="NODESTATE"  iconColumn="ICON">

You can set the nodeState of a specific node in run-time using the api_treenode.setState API. Additionally, you can set the icon of a specific node through the api_treenode.setIcon API.

Hope this helps, Ibrahim


answered 23 Oct '12, 10:56

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
accept rate: 25%

edited 23 Oct '12, 10:57

In Oracle Forms this takes single query

     SELECT -1                           NODE_STATE,
         LEVEL                           NODE_DEPTH,
         LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,
         NULL                            NODE_ICON,
         EMPLOYEE_ID                     NODE_VALUE    

and single line of code


In Apex it takes just 11 clicks, no code.

Here it takes I'd say half an hour of coding.

I am considering FormSpider as an alternative to Forms and/or Apex, so far this does not look inspiring :(


answered 23 Oct '12, 14:32

Stan12's gravatar image

accept rate: 0%

edited 23 Oct '12, 14:50

I understand how you feel. I felt the same way initially about the tree. However, after using it for a while, i realized that it is actually not that different from the Forms tree. If you look carefully to the code you wrote to the Q&A page and the Formspider example Ibrahim wrote, the query differs only in that Formspider one is only slightly different. I think it has one bind var and two additional restrictions. The code seems to be longer because Ibrahim wanted to show you an additional API. It could be just two lines.

We really had some really complicated trees to support when we started to build the component. We simply had to build this tree first because not every tree can be easily written with a connect by SQL query. Sometimes many tables are involved and even some code to determine the child nodes. Hiding all the logic behind a view and using a connect by on this view was also not the best option for various reasons, including performance.

So we created a tree component that allows us to create trees of any complexity easily and render them fast. When we were done, we asked ourselves: Do we want to add direct support for a connect by tree or is this good enough and we can move to other much needed features?

We compared the two ways and decided to move on to other features. Here is our reasoning: You usually have 1 at most 2 tree components in an application. They are usually complex enough that a simple connect by support is not good enough and you have to do more anyway. Besides, in cases where the tree is simple, saving a potential 30 minutes will not effect the total completion time of a project that lasts 1-2 months. So, the return on the investment was really small.

Our thought was that being able to do a tree with 11 clicks or having connect by support looks good in a demo but in real life where Formspider makes a difference, our tree shines. Try to build the tree component for the Airforce Recruiting System with just 11 clicks and you will want to throw yourself from the nearest building. :-)

So this was our reasoning back in the days. It served us well so far. These days the framework is a lot more mature though. Maybe it is time to add connect by support. :-)


answered 23 Oct '12, 23:11

Yalim%20K.%20Gerger's gravatar image

Yalim K. Gerger ♦♦
accept rate: 20%

edited 23 Oct '12, 23:12

Hi, I create a tree from a hierarchical table as Ibrahim wrote. In read-only mode it works fine, but when I tried to make it editable starts the problems. With this method each time click in a node the datasource is reloaded, losing the previous data. Is there any way to append the data in the datasource rather replace it? Regards


answered 18 Feb '13, 09:11

Jorge%20Alonso's gravatar image

Jorge Alonso
accept rate: 0%

Hi Jorge,

The tree's datasource reloads every time the api_datasource.populateChildren API is used, therefore we don't recommend accessing or editing the tree data through the tree's datasource. To edit the data of the tree, you can create a separate datasource which retrieves and holds the tree node(s) -row(s)- to be manipulated.

Best Regards,

(18 Feb '13, 10:07) Ibrahim Sand... ♦♦
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported



Asked: 23 Oct '12, 08:23

Seen: 11,243 times

Last updated: 18 Feb '13, 10:07

© Copyright Gerger 2017