Tree Queries

Getting Hierarchical Data

Hierarchical or tree data can be obtained from an Oracle Database table using "connect by" queries.

In this example, we are using the role_role_privs view. This view gives the roles that are granted (role_granted) to another role (role). A hierarchy of roles can be built up by roles that contain roles being granted to yet another role.

The following query gives this heirarchy for the DBA role:

select role
,lpad(' ',(level-1)*4,' ')||granted_role
from role_role_privs
start with role='DBA'
connect by prior granted_role=role
;

ROLE                           LPAD('',(LEVEL-1)*4,'')||GRANTED_ROLE
------------------------------ ---------------------------------------------------
DBA                            DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE         EXP_FULL_DATABASE
EXP_FULL_DATABASE                      EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE                       HS_ADMIN_EXECUTE_ROLE
EXP_FULL_DATABASE                      SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE                        HS_ADMIN_SELECT_ROLE
DBA                            DATAPUMP_IMP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE         EXP_FULL_DATABASE
EXP_FULL_DATABASE                      EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE                       HS_ADMIN_EXECUTE_ROLE
EXP_FULL_DATABASE                      SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE                        HS_ADMIN_SELECT_ROLE
DATAPUMP_IMP_FULL_DATABASE         IMP_FULL_DATABASE
IMP_FULL_DATABASE                      EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE                       HS_ADMIN_EXECUTE_ROLE
IMP_FULL_DATABASE                      SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE                        HS_ADMIN_SELECT_ROLE
DBA                            DELETE_CATALOG_ROLE
DBA                            EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE               HS_ADMIN_EXECUTE_ROLE
DBA                            EXP_FULL_DATABASE
EXP_FULL_DATABASE                  EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE                   HS_ADMIN_EXECUTE_ROLE
EXP_FULL_DATABASE                  SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE                    HS_ADMIN_SELECT_ROLE
DBA                            GATHER_SYSTEM_STATISTICS
DBA                            IMP_FULL_DATABASE
IMP_FULL_DATABASE                  EXECUTE_CATALOG_ROLE
EXECUTE_CATALOG_ROLE                   HS_ADMIN_EXECUTE_ROLE
IMP_FULL_DATABASE                  SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE                    HS_ADMIN_SELECT_ROLE
DBA                            JAVA_ADMIN
DBA                            JAVA_DEPLOY
DBA                            OLAP_DBA
DBA                            OLAP_XS_ADMIN
DBA                            SCHEDULER_ADMIN
DBA                            SELECT_CATALOG_ROLE
SELECT_CATALOG_ROLE                HS_ADMIN_SELECT_ROLE
DBA                            WM_ADMIN_ROLE
DBA                            XDBADMIN
DBA                            XDB_SET_INVOKER

The query selects role and granted role from the table. It treats those rows where the role is DBA as the top nodes of the hierarchy. It follows the hierarchy by using the connect by prior clause.

The query then lists those rows with a granted_role same as this row's role, in turn listing the rows below each of these rows.

We have padded the granted role with spaces to show the depth within the treem using the expression:

lpad(' ',(level-1)*4,' ')||granted_role

"level" is the depth in the tree (counts from one, so we took one off), and multiplied by 4 so that the indentations are visible, and used lpad to add that many spaces to the beginning of the granted_role.

See also:

Direct Reports - this uses the recursive SQL method for displaying a tree.