SQL> 
SQL> /*
DOC>
DOC>First, the definitions - as I understand them to be...
DOC>
DOC>- DAG (Directed Acyclic Graph): is a graph whose edge has a direction associated with them.
DOC>  All the paths in the graph will not form a closed loop, or cycle - hence the name Acyclic.
DOC>
DOC>      7  8    9
DOC>      \  /\  /\
DOC>       \/  \/  \
DOC>        6  5    \
DOC>         \/ \    \
DOC>          4  3  10
DOC>           \/ \/
DOC>            1  2
DOC>
DOC>  This would be a DAG if the nodes on higher level are the parents of the lower level nodes.
DOC>
DOC>- Tree: is a special kind of DAG. The paths, or the branches, of the gaph will not intertwine.
DOC>          1
DOC>         /\
DOC>        2  3
DOC>       /\   \
DOC>      4  5   6
DOC>     /\
DOC>    7  8
DOC>
DOC>
DOC>ORACLE "connect by" will give you all the possible path in a graph - be it a DAG or a tree.
DOC>There are situations where "connect by" may not be efficient in dealing with a DAG. For
DOC>example, finding all the parents of a particular node in a graph. When there are many possible
DOC>paths to a each and every node in a graph, things can quickly get out of hand as ORACLE traverses
DOC>the entire graph and try to identify all the poassible paths for you.
DOC>
DOC>This function will resolve the problem by identifying parent or child nodes one level at a time.
DOC>The function assumes the data is correct and the graph will not form a close loop.
DOC>
DOC>*/
SQL> 
SQL> 
SQL> Create or replace type num_tab_type as table of number;
  2  /
Create or replace type num_tab_type as table of number;
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents 


SQL> -- Node_type: 0 (default) to find all parent nodes.
SQL> -- Node_type: 1 to find all child nodes.
SQL> Create or replace function node_list(rootnode in number, node_type in number default 0)
  2  return  num_tab_type as
  3  x num_tab_type:=num_tab_type();
  4  y num_tab_type:=num_tab_type();
  5  nodelist num_tab_type:=num_tab_type();
  6  icount number:=0;
  7  begin
  8  x.extend;
  9  x(1):=rootnode;
 10  
 11  if node_type=0 then
 12  loop
 13  select distinct parent_id bulk collect into y from hq_tab
 14    where child_id in (select t.column_value from table(x) t);
 15  --  where child_id in (select t.column_value from table(cast(x as num_tab_type)) t);
 16  exit when SQL%NOTFOUND;
 17    nodelist.extend(y.count);
 18    for i in y.first..y.last loop
 19        icount := icount+1;
 20        nodelist(icount) :=y(i);
 21    end loop;
 22  x:=y;
 23  end loop;
 24  end if;
 25  
 26  if node_type=1 then
 27  loop
 28  select distinct child_id bulk collect into y from hq_tab
 29    where parent_id in (select t.column_value from table(x) t);
 30  --  where parent_id in (select t.column_value from table(cast(x as num_tab_type)) t);
 31  exit when SQL%NOTFOUND;
 32    nodelist.extend(y.count);
 33    for i in y.first..y.last loop
 34        icount := icount+1;
 35        nodelist(icount) :=y(i);
 36    end loop;
 37  x:=y;
 38  end loop;
 39  end if;
 40  
 41  return nodelist;
 42  end;
 43  /

Function created.

SQL> 
SQL> -- All parents of node 7
SQL> select * from table(cast(node_list(7) as num_tab_type));

COLUMN_VALUE                                                                    
------------                                                                    
           4                                                                    
           2                                                                    
           1                                                                    
                                                                                

SQL> -- With 9i, no need to use 'CAST'
SQL> select * from table(node_list(7));

COLUMN_VALUE                                                                    
------------                                                                    
           4                                                                    
           2                                                                    
           1                                                                    
                                                                                

SQL> -- All children of node 2
SQL> select * from table(node_list(2,1));

COLUMN_VALUE                                                                    
------------                                                                    
           4                                                                    
           5                                                                    
           7                                                                    
           8                                                                    

SQL> 
SQL> spool off