CMS - PL/SQL Reference Guide

PL/SQL Reference Guide

This document provides some more detailed information about the objects and links that are stored in the Analysis Service. Other technical information may also be available.

Object Types

Objects detected by the analyzer are summarized in the following table:


Icon	Object Type	Parent Object
	Collection Nested Table Type	Schema
	Collection Varray	Schema
	Cursor	Package Header
	Database Link	Schema
	Function	Schema, Package
	Function (Private)	Function
	Instance	-
	Index, Foreign Key Constraint, Primary Key Constraint, Check Constraint and Unique Key Constraint	Schema
	Object Type (Incomplete, Body and Header)	Schema
	Package Body	Schema
	Package Header	Schema
	Procedure	Schema, Package
	Procedure (Private)	Procedure
	Schema	Instance
	Schema (Unknown)	Instance
	Sequence	Schema
	Synonym (Public/Private)	Schema
	Oracle Table	Schema
	Oracle Table Column	Table
	Trigger	Schema
	Unknown Object	Schema
	View (Normal and Materialized)	Schema
	View Column	View

Object Properties

All object properties that are supported by the current release of the SQL Analyzer are described in the following table:


Object type	Property	Comment
Oracle bitmap index	version	RDBMS Version
Oracle check table constraint	version	RDBMS Version
Oracle collection nested table type	version	RDBMS Version
Oracle collection varray type	version	RDBMS Version
Oracle Database Event trigger	isInvalid	object is Invalid
Oracle Database Event trigger	onDbRoleChange	in a Data Guard configuration the trigger is fired whenever a role change occurs from standby to primary or from primary to standby
Oracle Database Event trigger	onLogoff	the trigger is fired whenever a client application logs off the database
Oracle Database Event trigger	onLogon	the trigger is fired whenever a client application logs onto the database
Oracle Database Event trigger	onServerError	the trigger is fired whenever a server error message is logged
Oracle Database Event trigger	onShutdown	the trigger is fired whenever an instance of the database is shut down
Oracle Database Event trigger	onStartup	the trigger is fired whenever the database is opened
Oracle Database Event trigger	onSuspend	the trigger is fired whenever a server error causes a transaction to be suspended
Oracle Database Event trigger	version	RDBMS Version
Oracle database link	connectionDescriptor	connection descriptor
Oracle database link	domain	domain
Oracle database link	version	RDBMS Version
Oracle DDL trigger	isInvalid	object is Invalid
Oracle DDL trigger	onAlter	the trigger is fired whenever an ALTER statement modifies a database object in the data dictionary
Oracle DDL trigger	onAnalyze	the trigger is fired whenever the database collects or deletes statistics or validates the structure of a database object
Oracle DDL trigger	onAssociateStatistics	the trigger is fired whenever the database associates a statistics type with a database object
Oracle DDL trigger	onAudit	the trigger is fired whenever the database tracks the occurrence of a SQL statement or tracks operations on a schema object
Oracle DDL trigger	onComment	the trigger is fired whenever a comment on a database object is added to the data dictionary
Oracle DDL trigger	onCreate	the trigger is fired whenever a CREATE statement adds a new database object to the data dictionary
Oracle DDL trigger	onDDL	the trigger is fired whenever any of the preceding DDL statements is issued
Oracle DDL trigger	onDisassociateStatistics	the trigger is fired whenever the database disassociates a statistics type from a database object
Oracle DDL trigger	onDrop	the trigger is fired whenever a DROP statement removes a database object from the data dictionary
Oracle DDL trigger	onGrant	the trigger is fired whenever a user grants system privileges or roles or object privileges to another user or to a role
Oracle DDL trigger	onNoAudit	the trigger is fired whenever a NOAUDIT statement instructs the database to stop tracking a SQL statement or operations on a schema object
Oracle DDL trigger	onRename	the trigger is fired whenever a RENAME statement changes the name of a database object
Oracle DDL trigger	onRevoke	the trigger is fired whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role
Oracle DDL trigger	onTruncate	the trigger is fired whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics
Oracle DDL trigger	version	RDBMS Version
Oracle DML trigger	isInvalid	object is Invalid
Oracle DML trigger	version	RDBMS Version
Oracle foreign key table constraint	version	RDBMS Version
Oracle function	aggregate	AGGREGATE indicator
Oracle function	isInvalid	object is Invalid
Oracle function	parallelEnable	PARALLEL_ENABLE declaration
Oracle function	pipelined	PIPELINED indicator
Oracle function	version	RDBMS Version
Oracle function based index	version	RDBMS Version
Oracle incomplete type	version	RDBMS Version
Oracle index	version	RDBMS Version
Oracle materialized view	isInvalid	object is Invalid
Oracle materialized view	isPrimaryKey	is a primary key materialized view
Oracle materialized view	version	RDBMS Version
Oracle object type	version	RDBMS Version
Oracle object type body	version	RDBMS Version
Oracle package body	isInvalid	object is Invalid
Oracle package body	version	RDBMS Version
Oracle package header	isInvalid	object is Invalid
Oracle package header	version	RDBMS Version
Oracle primary key table constraint	version	RDBMS Version
Oracle procedure	aggregate	AGGREGATE indicator
Oracle procedure	isInvalid	object is Invalid
Oracle procedure	parallelEnable	PARALLEL_ENABLE declaration
Oracle procedure	pipelined	PIPELINED indicator
Oracle procedure	version	RDBMS Version
Oracle relational table	version	RDBMS Version
Oracle sequence	version	RDBMS Version
Oracle standalone stored function	aggregate	AGGREGATE indicator
Oracle standalone stored function	isInvalid	object is Invalid
Oracle standalone stored function	parallelEnable	PARALLEL_ENABLE declaration
Oracle standalone stored function	pipelined	PIPELINED indicator
Oracle standalone stored function	version	RDBMS Version
Oracle standalone stored procedure	aggregate	AGGREGATE indicator
Oracle standalone stored procedure	isInvalid	object is Invalid
Oracle standalone stored procedure	parallelEnable	PARALLEL_ENABLE declaration
Oracle standalone stored procedure	pipelined	PIPELINED indicator
Oracle standalone stored procedure	version	RDBMS Version
Oracle synonym	dbLink	Synonym dblink
Oracle synonym	version	RDBMS Version
Oracle table column	version	RDBMS Version
Oracle unique table constraint	version	RDBMS Version
Oracle view	isInvalid	object is Invalid
Oracle view	version	RDBMS Version
Oracle view column	version	RDBMS Version
PL/SQL Cursor	version	RDBMS Version
SQL Instance	DBMSType	RDBMS Type
SQL Instance	version	RDBMS Version
SQL Machine	version	RDBMS Version
SQL Schema	version	RDBMS Version

Link Types

Links created by the analyzer are summarized in the following table:


Called/Parent (X) → Caller/Child (Y) ↓	area (instance)	container (schema)	table	table column	index (simple, bitmap, function based)	view	materialized view	view column	unique constraint	PK	FK	CHECK	sequence	trigger DML	function	procedure	function (subprogram)	procedure (subprogram)	package header	package body	function declaration (package header)	function implementation (package body)	procedure declaration (package header)	procedure implementation (package body)	←Called/Parent (X) ↓Caller/Child (Y)
area (instance)																									area (instance)
container (schema)	Belong																								container (schema)
table		Belong	Refer Integrity (FK –> PK)											Fire: Before Insert/Update/Delete, After Insert/Update/Delete, For each row Insert/Update/Delete and For all row Insert/Update/Delete											table
table column			Belong	Refer Integrity (FK –> PK)																					table column
index (simple, bitmap, function based)		Belong	Rely on	Rely on*)		Rely on	Rely on																		index (simple, bitmap, function based)
view		Belong	Use (select)	Access (read)		Use (select)	Use (select)	Access (read)						Fire: Instead of Insert/Update/Delete, For each row Insert/Update/Delete and For all row Insert/Update/Delete											view
materialized view		Belong	Use (select)	Access (read)		Use (select)	Use (select)	Access (read)						Fire: Before Insert/Update/Delete, After Insert/Update/Delete, For each row Insert/Update/Delete and For all row Insert/Update/Delete											materialized view
view column						Belong	Belong																		view column
Unique constraint			Belong	Rely on																					Unique constraint
PK			Belong	Rely on*)																					PK
FK			Belong	Rely on*)						Refer Integrity (FK –> PK)															FK
CHECK			Belong	Rely on																					CHECK
sequence		Belong																							sequence
trigger DML		Belong	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)							Call	Call	Call	Call			Call		Call		trigger DML
function		Belong	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)							Call	Call	Call	Call			Call		Call		function
procedure		Belong	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)							Call	Call	Call	Call			Call		Call		procedure
function (subprogram)			Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)							Belong, Call	Belong, Call					Belong, Call	Belong	Belong, Call	Belong	function (subprogram)
procedure (subprogram)			Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)							Belong, Call	Belong, Call					Belong, Call	Belong	Belong, Call	Belong	procedure (subprogram)
package header		Belong	Use (Select)	Access (read)		Use (select)	Use (select)	Access (read)																	package header
package body		Belong	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access (read)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access (read)							Call	Call			Rely on		Call		Call		package body
function declaration (package header)															Call	Call			Belong						function declaration (package header)
function implementation (package body)			Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)									Call	Call		Belong	Prototype				function implementation (package body)
procedure declaration (package header)															Call	Call	Call	Call	Belong						procedure declaration (package header)
procedure implementation (package body)			Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)									Call	Call		Belong			Prototype		procedure implementation (package body)
cursor package declaration (package header)																			Belong						cursor package declaration (package header)
object type header		Belong																							object type header
object type body		Belong																							object type body
object type incomplete		Belong																							object type incomplete
object type collection varray		Belong																							object type collection varray
object type collection table		Belong																							object type collection table
DDL trigger		Belong	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)																	DDL trigger
Event trigger		Belong	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)		Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Use: Use (Select), Use (Insert), Use (Update), Use (Delete)	Access: Access (read), Access (write)																	Event trigger
synonym		Belong	Rely On			Rely On																			synonym
database link		Belong																							database link