Tree Queries
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page Tree Queries
(Created page with "Hierarchical or tree data can be obtained from an Oracle database table. In this example, we are using the role_role_privs view. This view gives the roles that are granted ...") |
|||
| Line 1: | Line 1: | ||
| - | Hierarchical or tree data can be obtained from an [[Oracle | + | ==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' | ||
connect by prior granted_role=role | 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 | ||
| + | |||
</pre> | </pre> | ||
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 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 the | + | 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. | ||
