APEX Tree based on Pipelined Table Function

Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve that.
It is also possible to create a Pipelined Table Function which can be used a the basis of the APEX Tree. In this blogpost I will show you how to do that.
The first that we will need are some Type definitions, starting with the Object Type:

create or replace type tree_ot as object
(status  number
,lvl     number
,title   varchar2(255)
,icon    varchar2(255)
,value   varchar2(255)
,tooltip varchar2(255)
,link    varchar2(255)
)
/

Currently I decided to include all the attributes as they appear with the regular hierarchical query syntax that APEX Tree requires. I choose the second attribute to be called "lvl" because "level" is a reserved word.
The status and level attributes need some clarification. The status is used to determine where in the APEX Tree the record is shown. When the status is zero (0) it means that it is a leaf node. Status one (1) means that it is a root node. Status minus one (-1) is everything in between the root and the leaf. In the last case the level determines where the row must be shown.
This information can be deduced from the sample query that Oracle provides:

case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status

Because we are going to create a Pipelined Table Function, we will also need a Nested Table Type:

create or replace type tree_tt as table of tree_ot
/

Now that all the Type definitions are in place, we can create a Pipelined Table Function.
The first example shows the departments as toplevel with their employees listed underneath.

create or replace function f_tree
   return tree_tt pipelined
is
   l_retval tree_ot := tree_ot (null, null, null, null, null, null, null);
begin
   for r_dept in (select 1 status
                        ,1 lvl
                        ,d.dname
                        ,d.deptno
                    from dept d
   )
    loop
         l_retval.status  := r_dept.status;
         l_retval.lvl     := r_dept.lvl;
         l_retval.title   := r_dept.dname;
         l_retval.icon    := null;
         l_retval.value   := r_dept.dname;
         l_retval.tooltip := 'Department: '||r_dept.deptno;
         l_retval.link    := null;
         pipe row(l_retval);
       for r_emp in (select 0 status
                           ,2 lvl
                           ,e.empno
                           ,e.ename 
                       from emp e
                      where e.deptno = r_dept.deptno
                      order by e.ename)
       loop
         l_retval.status  := r_emp.status;
         l_retval.lvl     := r_emp.lvl;
         l_retval.title   := r_emp.ename;
         l_retval.icon    := null;
         l_retval.value   := r_emp.empno;
         l_retval.tooltip := 'Employee: '||to_char (r_emp.empno);
         l_retval.link    := null;
         pipe row(l_retval);
   end loop;
   end loop;
   return;
end f_tree;​​​

The second example shows the Departments as toplevel, followed by the employees who are Managers, and finally the employees working for that manager.

create or replace function f_tree_mgr
   return tree_tt pipelined
is
   l_retval tree_ot := tree_ot (null, null, null, null, null, null, null);
begin
   for r_dept in (select 1 status
                        ,1 lvl
                        ,d.dname
                        ,d.deptno
                    from dept d
   )
    loop
         l_retval.status  := r_dept.status;
         l_retval.lvl     := r_dept.lvl;
         l_retval.title   := r_dept.dname;
         l_retval.icon    := null;
         l_retval.value   := r_dept.dname;
         l_retval.tooltip := 'Department: '||r_dept.deptno;
         l_retval.link    := null;
         pipe row(l_retval);
       for r_mgr in (select -1 status
                           ,2 lvl
                           ,m.empno
                           ,m.ename
                       from emp m
                      where m.deptno = r_dept.deptno
                        and m.job = 'MANAGER')
       loop        
          l_retval.status  := r_mgr.status;
          l_retval.lvl     := r_mgr.lvl;
          l_retval.title   := r_mgr.ename;
          l_retval.icon    := null;
          l_retval.value   := r_mgr.empno;
          l_retval.tooltip := 'Manager: '||to_char (r_mgr.empno);
          l_retval.link    := null;
          pipe row(l_retval);
         for r_emp in (select 0 status
                             ,3 lvl
                             ,e.empno
                             ,e.ename 
                         from emp e
                        where e.mgr = r_mgr.empno
                        order by e.ename)
         loop
           l_retval.status  := r_emp.status;
           l_retval.lvl     := r_emp.lvl;
           l_retval.title   := r_emp.ename;
           l_retval.icon    := null;
           l_retval.value   := r_emp.empno;
           l_retval.tooltip := 'Employee: '||to_char (r_emp.empno);
           l_retval.link    := null;
           pipe row(l_retval);
         end loop;
      end loop;
   end loop;
   return;
end f_tree_mgr;​

Links

Demo
Oracle docs: Pipelined Table Functions
Oracle Base on Pipelined Table Functions