Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Hierarchical SQL: lack of CONNECT_BY_ROOT feature in Oracle <=9i
Hey Rob, great! Works like a charm!
So my new query is:
SELECT t.rootuser, s.PRIVILEGE
FROM (SELECT DISTINCT rootuser, granted_role
FROM (SELECT REPLACE (SYS_CONNECT_BY_PATH (DECODE (LEVEL, 1, r.grantee ), '~' ), '~' ) AS rootuser, grantee, granted_role FROM dba_role_privs r CONNECT BY r.grantee = PRIOR r.granted_role) rp, dba_users u WHERE u.username = rp.rootuser) t, dba_sys_privs s
FROM dba_sys_privs
WHERE grantee IN (SELECT username
FROM dba_users)
It is technically possible to also rewrite SYS_CONNECT_BY_PATH (new feature in Oracle 9i), to make my (new) SQL running on Oracle 8i?
Thanks,
Ronny
Received on Tue Jul 17 2007 - 04:23:37 CDT