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?
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.
Have a question for Dan Clamage? Send an e-mail to email@example.com
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
Expert Dan Clamage explains how to use SQL SELECT and SQL UNION ALL statements to sort and visualize a set of sales figures. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading