Is Recursive Retrieval Possible using SQL or Procedures

[amolik]

Hi

I have a Table with following columns

create table topology(
nid number(19) primary key,
ipaddress varchar2(15),
macaddress varchar2(15),
status varchar2(10),
parentid number(19) references topology(nid));

There is a self reference in the table.
I have Stored Data in a topological tree structure.
There is a single record at Top Level for which the parent id is null . Call it as owner.
This Owner has 2 children (Thats the parent id of this is nid of owner) Now these 2 children have 3 to 4 children each and further those children can have further.
The level of tree structure can be much bigger and its dynamic. There can be records adding in between tree strucure.
My Problem is I want a query or function which will return the sequential tree structure of an
element which I input. If I input a owner's id then it should retrieve all the hierarchy of children below that element and to whatever level it goes.

Amol

[pipo]

you should use something like (level, connect by, prior and start with are reserved keywords) :

SELECT level,parentid
FROM topology
start with parentid is null
CONNECT BY parentid = PRIOR nid

[csathya]

I have similar type of question but its different. Is there any way to get the top level node using the intermediate child level node?

[Shestakov]

Pipo wrote to you RIGHT statment, because he changed direction in CONNECT BY .. PRIOR :

from child --> parent
to parent --> child