CONNECT_BY_ROOT with XMLAGG

This is a post about Oracle hierarchical queries. The other day I was after a solution which would display the hierarchy in the execution of some PL/SQL procedures. That is, there was  a main procedure which would call several dozens of other procedures in a predefined order. This order was pre-recorded in a table. What I was after was the ancestral relationship in the order of executions. To demonstrate simply, say there is table called steps, which looks like this:




MAIN_ID                DEPENDANT_ID           
---------------------- ---------------------- 
1                      2
1                      3   
2                      4
2                      3

Process MAIN_ID 1 depends on the execution of 2 and 3 first and then 2 depends on the execution of 4 and 3. Can we execute 1 after we execute 2 and 3?

Well the table says YES!. But how about ancestry? We can clearly see that for process 2 to execute process 4 must execute first! Wouldn't it be nicer, if we could show this ancestry relationship via an SQL query straight out of this table? An SQL query which would show us all dependants of 1 and 2, including parents, granddads etc...

So the results we are after could look like this:

MAIN_ID                DEPENDS_ONTHIS
---------------------- --------------
1                      2,4,3  
2                      3,4 

Here is the query to obtain such hierarchical relationships out of this table by using the CONNECT_BY_ROOT, Oracle 10g operator and some XMLAGG function magic for hierarchical queries:

select main_id, rtrim(xmlagg(xmlelement(d, dependant_id||',')).extract ('//text()'),',') depends_onthis 
from 
(
SELECT  distinct connect_by_root main_id main_id,
    dependant_id
  FROM steps
    CONNECT BY prior dependant_id=main_id
  ORDER BY 1,2
)
group by main_id
order by main_id

MAIN_ID                DEPENDS_ONTHIS
---------------------- --------------
1                      2,4,3  
2                      3,4 

I found this new Operator in Oracle 10g amusing and cool. Here is the Oracle documentation:

And here is the test table scripts if you want to run the above test case for yourself:


drop table steps;

create table steps
(
main_id number,
dependant_id number
);

insert into steps values (1,2);
insert into steps values (1,3);
insert into steps values (2,4);
insert into steps values (2,3);