Tree Queries
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Tree Queries
| Line 1: | Line 1: | ||
| - | Hierarchical or tree data can be obtained from an [[Oracle Database]] table using | + | ==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. | 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. | ||
| Line 7: | Line 9: | ||
<pre> | <pre> | ||
select role | select role | ||
| - | ,lpad(' ',level*4,' ')||granted_role | + | ,lpad(' ',(level-1)*4,' ')||granted_role |
from role_role_privs | from role_role_privs | ||
start with role='DBA' | start with role='DBA' | ||
| Line 13: | Line 15: | ||
; | ; | ||
| - | ROLE LPAD('',LEVEL*4,'')||GRANTED_ROLE | + | ROLE LPAD('',(LEVEL-1)*4,'')||GRANTED_ROLE |
| - | ------------------------------ --------------------------------------------- | + | ------------------------------ --------------------------------------------------- |
| - | DBA | + | DBA DATAPUMP_EXP_FULL_DATABASE |
| - | DATAPUMP_EXP_FULL_DATABASE | + | DATAPUMP_EXP_FULL_DATABASE EXP_FULL_DATABASE |
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE | EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
EXP_FULL_DATABASE SELECT_CATALOG_ROLE | EXP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| - | DBA | + | 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 | IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE | ||
EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | ||
IMP_FULL_DATABASE SELECT_CATALOG_ROLE | IMP_FULL_DATABASE SELECT_CATALOG_ROLE | ||
SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| - | DBA | + | DBA DELETE_CATALOG_ROLE |
| - | DBA | + | DBA EXECUTE_CATALOG_ROLE |
| - | + | EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE | |
| - | DBA | + | 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 | SELECT_CATALOG_ROLE HS_ADMIN_SELECT_ROLE | ||
| - | DBA | + | DBA GATHER_SYSTEM_STATISTICS |
| - | DBA | + | DBA IMP_FULL_DATABASE |
| - | DBA | + | 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 | ||
</pre> | </pre> | ||
| Line 62: | Line 64: | ||
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. | 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: | ||
| + | <pre> | ||
| + | lpad(' ',(level-1)*4,' ')||granted_role | ||
| + | </pre> | ||
| + | |||
| + | "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. | ||
