Tree Queries - Revision history

WikiSysop at 18:10, 28 November 2015

2015-11-28T18:10:26Z

← Older revision		Revision as of 18:10, 28 November 2015
Line 71:		Line 71:

	"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.		"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.

WikiSysop at 23:30, 10 December 2011

2011-12-10T23:30:52Z

← Older revision		Revision as of 23:30, 10 December 2011
Line 66:		Line 66:

	We have padded the granted role with spaces to show the depth within the treem using the expression:		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		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 that many spaces to the beginning of the 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.

WikiSysop at 22:44, 28 November 2011

2011-11-28T22:44:58Z

← Older revision		Revision as of 22:44, 28 November 2011
Line 1:		Line 1:
-	Hierarchical or tree data can be obtained from an [[Oracle Database]] table using ~~a hierarchical queries and 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.		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 DATAPUMP_EXP_FULL_DATABASE	+	DBA DATAPUMP_EXP_FULL_DATABASE
-	DATAPUMP_EXP_FULL_DATABASE ~~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		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 ~~GATHER_SYSTEM_STATISTICS~~	+	DBA DATAPUMP_IMP_FULL_DATABASE
-	~~DBA~~ 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 ~~JAVA_ADMIN~~	+	DBA DELETE_CATALOG_ROLE
-	DBA ~~JAVA_DEPLOY~~	+	DBA EXECUTE_CATALOG_ROLE
-	~~DBA OLAP_DBA~~	+	EXECUTE_CATALOG_ROLE HS_ADMIN_EXECUTE_ROLE
-	DBA ~~OLAP_XS_ADMIN~~	+	DBA EXP_FULL_DATABASE
-	~~DBA SCHEDULER_ADMIN~~	+	EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE
-	~~DBA~~ SELECT_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 WM_ADMIN_ROLE	+	DBA GATHER_SYSTEM_STATISTICS
-	DBA XDBADMIN	+	DBA IMP_FULL_DATABASE
-	DBA XDB_SET_INVOKER	+	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:
		+
		+	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 that many spaces to the beginning of the granted_role.

WikiSysop at 22:13, 28 November 2011

2011-11-28T22:13:21Z

← Older revision		Revision as of 22:13, 28 November 2011
Line 1:		Line 1:
-	Hierarchical or tree data can be obtained from an [[Oracle ~~database~~]] table.	+	Hierarchical or tree data can be obtained from an [[Oracle Database]] table using a hierarchical queries and tree 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 12:		Line 12:
	connect by prior granted_role=role		connect by prior granted_role=role
	;		;
		+
		+	ROLE LPAD('',LEVEL*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 parent~~ role.	+	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.

WikiSysop: 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 ..."

2011-11-27T22:13:54Z

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 ..."

New page

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 (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:

<pre>
select role
,lpad(' ',level*4,' ')||granted_role
from role_role_privs
start with role='DBA'
connect by prior granted_role=role
;
</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 then lists those rows with a granted_role same as the parent role.