Need an algorithm to process nodes in a tree recursively

[Dinesh]

Hi,

I have two tables 'master' and 'child', the master is the master table
for all nodes in all trees. To get children of any node, we need to go
to the 'child' table to get the nodeid of the children. The master has
about 40,000 such trees with about 400 nodes in each tree.
The input to me is the 'Root Node'/'First Node' of a tree. I need to
traverse thru all the child nodes starting from 'Root Node' ( and
process it ) and then populate my destination table 'Tree'. As an
example, here is the table structure with data for a single tree.

I will very much appreciate if anyone can give me an algorithm or
pl/sql code to do this.

SOURCE TABLES
--------------

MASTER
------
Node_id, childid, childcount
1,10,3
2,13,2
3,0,0
4,0,0
5,0,0
6,0,0

CHILD
-----
childid, nodeid, nextchildid
10,2,11
11,3,12
12,4,0
13,5,14
14,6,0

Now, Node 1 has 3 children. To get the node ids of children, We need
to traverse the CHILD table starting at childid till nextchildid
becomes 0 to get all children of node 1.

DESTINATION TABLE
-------------------

Tree
-----
Nodeid, childnodeid
1,2
1,3
1,4
2,5
2,6
3,0
4,0
5,0
6,0