Enumerating a hierarchy?

I have a hierarchical data structure represented in Oracle (8i, 8.1.7.0.0), and I've been playing with hierarchical queries using CONNECT BY and START WITH, but they don't quite cover what I want.

In particular, I'd like to have a query that gave me the full enumeration of the containment of an object, i.e., its "owner" (e.g., manager), the owner's owner, the owner's owner's owner, and so on, all the way up to the top-level owner.

Using the "emp" SQL example table in the Oracle manual (Oracle8 Server SQL Reference), I'd like a query that, when written (something like this):

	select
		empno, mgr_no
	from
		group_enumeration
	where
		empno = 7369;

gave:

	7369	7902
	7369	7566
	7369	7839

where "group_enumeration" was some table/view (PL/SQL?) that did the enumeration.

I've seen that Oracle 9i has something called "SYS_CONNECT_BY_PATH" that looks like it does what I want, except it's 9i, and it comes back as a delimited string, rather than a set of records.

Any help would be appreciated.

Thanks,
Brad Received on Mon Mar 03 2003 - 12:12:17 CST