How to display a tree structure from an Oracle table (recursive search)

I have a simple table that is used to build up a tree structure in an application. Then there are related objects loacted in a diferent table. In the application I can see the tree similar to a directory structure. Now, I want to be able to examine the tree from SQL in order to find large objects. I can do this in a query and get the objects ordered by size. The problem I have is that witnh my query, I'm not able to determine in what path these objects are located.

The table I have has the fields: NODE, NAME, CHILDNODE where NODE and CHILDNODE are integer ID numbers and NAME is a varchar2.

If I do something like:
select * from ossdnodes start with inode=1 connect by childinode=inode

Open in new window

I will get the first level of the tree structure. But I would like to be able to get all in one go and then bring in some other information about the objects like size and owner from another table.

I think I need to use the WITH statement but I haven't been able to figure this out.

The desired output would be something like:

PATH                                               OBJECT
------------------------                        --------
LEVEL1 > LEVEL2 > LEVEL3          NAME

Open in new window


(Formatting is not critical but a path separator looks nicer.)

Please just let me know if the question is not clear.
Sharp2bAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sharp2bAuthor Commented:
I can find several examples on the internet where it looks like the above select should work but the output I get is just:
NODE,CHILDNODE,NAME
1,2,home
1,3,tmp
1,4,Applications

But there are plenty of data below both "home" and "Applications" in my table. Here I just see the first level of the hierarchy and I would like to see the entire structure.
Sharp2bAuthor Commented:
OK, I had some progress now that I figured out that I need to use the keyword PRIOR like below.

The question that remains is how I can now get the names for each level in the hierarchy to show for each item.


SELECT name from ossdnodes start with inode = 1 connect by prior childinode=inode order siblings by inode;

Open in new window

sdstuberCommented:
what version of the database?  connect by prior can work,  if you want to use a recursive-with, you need 11gR2
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Sharp2bAuthor Commented:
Sorry, due to network changes I had to go offline for a while. I should have mentioned the version, this is still Oracle 10.2. Thanks for pointing out that WITH is new in Oracle 11R2, then I can give that one up.

The SQL I gave above with ...CONNECT BY PRIOR... seem to do the trick but I still have not figured out how to include the path, i.e. the names of the previous parents for each item. I would like to find out how to concatenate these names into a string to present as well.
sdstuberCommented:
WITH isn't new to 11gR2,  but it "recursive-WITH" is.


as for showing the path,  try this...


SELECT name, SYS_CONNECT_BY_PATH(name,'>') from ossdnodes start with inode = 1 connect by prior childinode=inode order siblings by inode;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sharp2bAuthor Commented:
Hey, cool, it works!

That was something completely new to me. (Well, I don't have much experience with these recursive queries.) Strange I didn't see anything like this when I tried to google for a solution. Thanks a lot!
Sharp2bAuthor Commented:
You just made my life a little bit easier and my report readable.
sdstuberCommented:
glad I could help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.