We are implementing hierarchy in our app using the adjacency model. We have been using CONNECT BY PRIOR to retrieve the leaf nodes and traverse through the hierarchy.

We need to develop a SQL function that can be database agnostic. But first we want to do that with Oracle. Can you give me pointers regarding a recursive function to traverse through the hierarchy?

    Requires Free Membership to View

My table structure is very simple:

Node ID Parent ID <Node Attributes1-5>...

Your response is highly appreciated.

If you’re already on Oracle, why do you wish to be database-agnostic? Once you choose a database platform, you should leverage its unique capabilities to your fullest advantage.

If you have SQL2000 in-house, then you already know it doesn’t support hierarchical queries. You have to simulate them procedurally.

The least database-dependent method I can think of is to do an iterative scan, instead of using the hierarchy clauses (start with, connect by). This requires you to keep some housekeeping variables on hand. You can still do it recursively. At least then the algorithm you develop can be readily translated between PL/SQL and T-SQL (or whatever procedural database language you’re using). Your function will be calling itself, so it needs a reliable method of terminating the recursion.



This was first published in January 2010

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: