Thanks for the suggestion - this way I don't get the error message anymore, although it doesn't return yet what is needed.
There are possibly more then one root node, so starting with 'admin' is not sufficient. Adding a 'start with privilege in (select ...)' clause that contains all such root nodes is possible.
The problem remains though that the inner query (the one with the CONTAINS) does not return an extra row for the leaf node, so the joined rows from acs_privileges do not appear in the correct order.
This is what your query returns
TREE_LEVEL PRIVILEGE CHILD_PRIVILEGE
---------- -------------------- --------------------
1 one_top_level sub_level
2 sub_level sub_sub_level
another_top_level
one_top_level
This is from a test schema with testing data in it, that contains this:
one_top_level
|
+- sub_level
|
+ sub_sub_level
another_top_level
I modified your query to fit the testing data:
select
tree.tree_level,
tree.privilege,
p.privilege as child_privilege
from
test_acs_privileges p,
(select
level as tree_level,
privilege,
child_privilege
from
test_acs_privilege_hierarchy
connect by privilege = prior child_privilege
start with privilege in ('one_top_level', 'another_top_level')
) tree
where
tree.child_privilege(+) = p.privilege
order by tree_level,tree.privilege
