plsql oracle parent child

Question

I have a parent-child relationship in an Oracle 9i database-table

like:

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

I have an absolute parent (e.g. child 1) and i need a csv list or resultset of all childs of this parent.

Answer 1

We just moved off Oracle but I wrote this procedure for you in SQL Server (they should be very similar minus the CURSOR declarations).

CREATE PROCEDURE ShowRelationships @parent AS int AS PRINT 'Parent = ' + CAST(@parent AS varchar(3))

DECLARE @child AS int; DECLARE cur_children CURSOR FOR SELECT child FROM PCREL WHERE parent = @parent;

OPEN cur_children; FETCH NEXT FROM cur_children INTO @child;

IF (@child IS NULL) BEGIN PRINT CAST(@parent AS varchar(3)) + ' has no children...'; END

WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Child = ' + CAST(@child AS varchar(3))

FETCH NEXT FROM cur_children INTO @child; END

CLOSE cur_children; DEALLOCATE cur_children;

SELECT TOP 1 @child = child FROM PCREL WHERE parent = @parent;

EXECUTE ShowRelationships @child;

GO

Answer 2

Using SYS_CONNECY_BY_PATH will give you the whole hierarchy comma separated:

SELECT SYS_CONNECT_BY_PATH(parent, ',') "PATH" 
  FROM table 
 START WITH child = 1 
CONNECT BY PRIOR child = parent;

Further options here