Oracle objects can be documented in Predict and generation, incorporation, comparison and administration functions can be applied to them.
Note:
To use functions of Predict that support Oracle in a Linux, UNIX or
Windows environment, Entire Access in a Linux, UNIX or Windows environment must
be installed.
This document covers the following topics:
The following topics are covered below:
Oracle tables, views, columns, indexes and referential constraints can be documented in Predict.
The following table gives an overview of how different Oracle objects are documented.
Documenting Oracle objects is described in the sections below.
| Oracle Object | Documented in Predict with |
|---|---|
| Table / view | File object of type OT or OV |
| Table check constraint | Attribute of file object (type OT) |
| Column check constraint | Verification object |
| Index | Field attributes. |
| Column | Field objects. |
You are able to document several implementations of Oracle objects in different servers. An Oracle server is documented as a database object with Type O (Oracle handler).
With the means of Oracle handlers, it is possible to document one logical database design and to assign different physical database implementations to Oracle objects. The object types affected are File and Field. For objects of these types, the maintenance function Physical attributes is available to assign an individual setting of physical attributes to an object in a specific server.
There is one specific server named <Unassigned>, that is always available and must not be documented. This server is used for all objects not assigned to a documented Oracle handler.
When a Oracle object is added, it has a set of physical attributes in the <Unassigned>. This set of attributes can be modified but can not be deleted. In the Add, Copy and Modify object function, you can edit attributes of the logical database design as well as attributes of the physical database design in one preferred server. You can specify your preferred server in the maintenance options of your profile. If your preferred server is not <Unassigned>, the physical attributes for <Unassigned> will be taken from the general defaults when an object is added.
There are general defaults for Oracle objects such as Table and Index. You may adapt the defaults to meet the requirements of your enterprise. These default values will be preset and may be modified when an object is added.
As indicated above, it is possible to define an index as partitioned in one server and as not partitioned in a second server.
Predict offers the support of Oracle also on Windows and UNIX platforms. On Windows and UNIX platforms, Entire Access is required for access to Oracle.
When a connection to a server is established, a user ID and password may be required.
Tables are documented as files of type OT. Views are documented as files of type OV.
Note:
If a table is partitioned, the number of partitions must be
documented as an attribute of the file.
The documentation of views is supported by an additional editor to
specify the part of the subselect clause starting from the first
FROM clause.
The selection clause of the subselect clause is documented by the
specification of the field list of the view. The specified list of tables/views
in the first FROM clause of the subselect clause is generated by
Predict and will be updated if a field from an additional table/view is added
to the view. Correlation names can be added to the tables and views in the list
(using editor functions). The remaining part of the subselect clause is left
unchanged.
The expression used to define Oracle fields can contain complex expressions. Fields that are defined not only by a single column name but use either a constant or a more complex expression are called derived fields. A special editor is provided for specifying the expression of derived fields.
In the field expression and in the subselect clause, comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
Oracle offers the possibility to use subqueries and joined tables
in the FROM clause of the view definitions. This functionality is
also supported under Predict.
You can define in the subquery of a view whether the
FROM clause is made up of an EXCEPT, EXCEPT
ALL, INNER, INTERSECT, INTERSECT
ALL, LEFT OUTER, RIGHT OUTER, FULL OUTER
JOIN, UNION or UNION ALL. Use the subquery
editor to add and modify joined views.
Predict has a file of type IV (Intermediate View) that enables you
to use subselects in the FROM clause of a view definition. Files
of type IV have a field list to show the selection clause and a subquery to
show the search condition. Just like views, files of type IV can have a Join
type. They can be used as master files for views and files of type IV. When a
view is generated that has a file of type IV as a master file, a
SELECT partial statement is generated into the FROM
clause. Files of type IV are not in the Oracle catalog. Nevertheless, the same
naming standards are valid for files of type IV as for tables and views. See
Naming Conventions
for Oracle for further information.
When using Incorporate and Compare, files of type IV are created
in addition to the view in order to represent subselects in the
FROM clause. Files of type IV are implemented as file objects in
Predict, in order to ensure the consistency of the field definitions via
rippling.
The maximum number of master files for a view or file of type IV is 100 in Predict.
Oracle supports so-called archive tables. An archive table is a table that stores older rows from another table.
Oracle can automatically store rows that are deleted from a table in an associated archive table.
Referential constraints are documented as file relations of type R (referential constraint). The relation is established between a unique constraint and a foreign key. Unique constraint and foreign key can belong to the same or to different tables.
Oracle columns are documented as field objects.
LOBs are represented as the field format LO. The character set determines whether it is a BLOB, CLOB or a DBCLOB. The lengths of these fields can be declared in the following units: bytes, kilobytes, megabytes or gigabytes.
ROWID fields are documented with fieldtype QN. Their format is A. The field maintenance ensures that only one ROWID field per table exists.
It is possible to define an identity property for numeric fields. The contents of these fields can be generated by Oracle. This is an easy way to create a primary key.
Oracle indexes are documented with field objects as follows:
Field attributes
index name
definition of index
index attributes
If the index consists of only one column, the field documenting the column is marked as a descriptor with descriptor type D, P or F.
If the index consists of multiple columns, it is documented as a field with field type SP (superfield) and descriptor type D, P or F. The descriptor types have the following meaning:
D
Field is an index.
F
Field is a foreign key and an index.
P
Field is a primary key. This always implies that the field
is also a unique index.
Unique constraints are documented as follows:
If the unique constraint applies to only one column, the field documenting the column is marked U in column Unique option.
If the unique constraint applies to multiple columns, it is documented as a field of type SP (superfield) with descriptor type D, F or P, and U in column Unique option. The descriptor types have the following meaning:
D
Field is a unique index.
F
Field is a foreign key with unique index.
P
Field is a primary key. This always implies that the field
has a unique constraint.
Foreign keys are documented as follows:
If the foreign key consists of only one column, the field documenting the foreign key is marked as a descriptor with descriptor type F or E.
If the foreign key consists of multiple columns, it is documented as a field with field type SP (superfield) and descriptor type F or E. The descriptor type F means the field is a foreign key and an index. E means the field is a foreign key without an index.
Partitioning keys are documented as field objects in Predict as follows:
If the partitioning key consists of only one column, the field documenting the partitioning key is marked by specification of the partitioning schema in the DBMS extensions
If the partitioning key consists of multiple columns, it is documented as a field with field type SP (superfield) and is marked by specification of the partitioning schema in the DBMS extensions
If the partitioning key consists of only one column, the field documenting the partitioning key is marked by specification of the partitioning schema in the DBMS extensions.
If the partitioning key consists of multiple columns, it is documented as a field with field type SP (superfield) and is marked by specification of the partitioning schema in the DBMS extensions.
Cluster columns are documented as field objects in Predict as follows:
To define a field as a cluster column, enter Y in the field Cluster column of the corresponding file object.
Check expressions for single columns are documented with verifications of status SQL. The check expression is stored as the rule of the verification.
Check expressions can be edited with the Predict Rule Editor.
Comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
A table check expression is a check expression that applies to more than one column. A table check expression is an attribute of a file.
To edit table check expressions, enter Y in the field Check expression of the corresponding file object.
Comment lines (lines starting with * or **) and remarks within a line (starting with /*) are allowed.
Oracle naming conventions must be observed when creating or maintaining Predict objects for Oracle. The following rules apply:
Valid identifiers are from 2 to 32 characters long, must start with an alpha character (A - Z) and may be followed by either an alpha, US national character (#, $, @), a digit or underscore character. Identifiers must comply with these rules if Predict maintenance and generation functions are to be applied.
Concatenated names must not consist of more than 32 characters including the hyphen. See the following paragraphs for details.
Hyphens in names are treated as follows:
When a table/view is generated from a Predict file object, the hyphen will be transformed into a period (.).
Because hyphens are used as delimiters, only one hyphen can occur in the file ID.
The hyphen can be used as a minus sign or negative sign in the field expression or the subselect clause and must then be preceded by a blank.
Column names must not contain a hyphen.
Notes:
FLIP C and FLIP
T to edit the field names and their derivation in full
length.
Contraint names can consist of up to 32 characters.
Correlation names can be defined in the subselect clause of a view. If a correlation name is defined for a table/view in the subselect clause, all references (in field expressions as well as in the field editor of the view) to columns of the table/view must be qualified with the correlation name. If no correlation name is defined for a table/view in the subselect clause, all references to columns of the table/view must be fully qualified with creator-tablename-columnname (for example: SYSORACLE-SYSCOLUMNS-COLNAME).
Correlation names can consist of up to 18 characters.
Index names consist of the creator name and the actual name of the index concatenated by a hyphen (used as qualification character).
Creator name and actual name of the index each can consist of up to 32 characters, but a concatenation of both (including the hyphen as qualification character) must not exceed a length of 32 characters.
Table/View names consist of the creator name and the actual name of the table/view concatenated by a hyphen (used as qualification character).
Creator name and actual name of the table/view each can consist of up to 32 characters, but a concatenation of both (including the hyphen as qualification character) must not exceed a length of 32 characters.
Column names can consist of up to 32 characters.
With Oracle, special characters can be used in identifiers of tables and views. Identifiers that contain special characters have to be delimited (usually with single or double quotes) and are therefore called delimited identifiers.
Oracle tables and views with delimited identifiers can be incorporated. They can then be renamed with Predict maintenance functions and retrieval functions can be applied to them. It is strongly advisable to rename delimited identifiers for the following reasons:
The only Predict functions that can be applied without restriction to objects with delimited identifiers are Incorporate and Rename.
If identifiers contain special characters such as blank or asterisk, results of retrieval functions are unpredictable.
Views can only be generated if the subselect clause and the column expressions do not contain references to delimited identifiers enclosed by quotation marks.
As the SQL escape character Predict uses quotation marks (") and as the SQL string delimiter apostrophes (') are used. The Predict Incorporate function converts other escape characters or string delimiters to quotation marks (") and apostrophes (').
Generation, Incorporation and Comparison are subject to Oracle security mechanisms. The following mechanisms apply:
To perform the Generate function and administration functions Purge and Refresh, the user must have the appropriate privileges within Oracle.
To perform Incorporation and Comparison functions, the user must have SELECT privilege on nearly all catalog tables.
The SELECT privilege in Oracle is the minimum prerequisite for incorporation of Oracle tables/Views (see GRANT (TABLE or VIEW PRIVILEGES) in your Oracle documentation.
To use the SQL statements generated by Predict, the corresponding Oracle privileges are also required.
Oracle objects can be generated from Predict documentation objects.
| Oracle table/view | Columns, indexes and referential constraints are
automatically included when generating Oracle tables and views.
Command: |
All objects are generated by first generating the SQL statements that are necessary to implement the object and then executing these statements. An additional confirmation is requested before an Oracle object is actually implemented.
The generated SQL statements can be saved in a protocol.
If a generation function is executed for an object that is already implemented, the existing Oracle object can be updated.
See respective sections in Generation in the External Objects in Predict documentation for more information.
The incorporation functions create Predict documentation objects for tables/views (including columns, indexes and referential constraints) from the system catalog.
See the section Incorporation in the External Objects in Predict documentation.
The comparison functions list differences between the current implementation in Oracle and the corresponding documentation. The documentation can be updated to match the implementation.
See the section Comparison in the External Objects in Predict documentation.
Functions for administrating Oracle objects are provided to display, purge or refresh Oracle objects that have been implemented from Predict documentation.
The Display function lists generation protocols.
The Purge function drops a table/view physically in Oracle.
The Refresh function deletes all data in an implemented table but keeps the table structure.
See the section Administration of External Objects in the External Objects in Predict documentation.