Sql query to get records associated to a node in tree

I have seen few guys querying regarding this kind of stuff in orkut communities.
I found this has a solution to resolve.
Query is like
Example :-


It is very similar to chain marketing.
The below sql functions help us to get the associated child nodes for the given node.
Step1:
I have created a table saying “tree”.

CREATE TABLE TREE (
PRNT NUMBER,
CHLD NUMBER);
Test data :


Step 2:
My sql function
CREATE OR REPLACE FUNCTION Getchilds(argC1 IN VARCHAR)
RETURN VARCHAR IS
CURSOR c1 IS SELECT chld FROM TREE WHERE prnt=argC1;
tmpChld VARCHAR(10);
tmpReturn VARCHAR(100);
tmpSubChilds VARCHAR2(100);
BEGIN
OPEN c1;
tmpReturn:=NULL;
LOOP
FETCH c1 INTO tmpChld;
EXIT WHEN c1%NOTFOUND;
IF(tmpReturn IS NULL) THEN
tmpReturn:=tmpChld;
ELSE
tmpReturn:=tmpReturn||','||tmpChld;
END IF;
tmpSubChilds:=Getchilds(tmpChld);
IF(tmpSubChilds IS NOT NULL) THEN
tmpReturn:=tmpReturn||','||tmpSubChilds;
END IF;
END LOOP;
RETURN tmpReturn;
END;
/


I am using recursion technique here to get the Childs associated to the current node.


Output:-
Sql Query

select Getchilds(1) as Childs from dual


select Getchilds(2) as Childs from dual