Oracle

#### Search Results

Search

# Oracle

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 or Windows environment, Entire Access in a Linux or Windows environment must be installed.

This document covers the following topics:

- Documenting Oracle in Predict

- Naming Conventions for Oracle

- Generating, Incorporating and Comparing Oracle Objects

- Administrating Implemented Oracle Objects

## Documenting Oracle in Predict

The following topics are covered below:

- General Information

- Documenting Oracle Tables and Views

- Documenting Referential Constraints

- Documenting Other Oracle Objects

### General Information

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.

#### Documentation of multiple server

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.

#### Generation, Administration, Incorporation and Comparison

As indicated above, it is possible to define an index as partitioned in one server and as not partitioned in a second server.

#### Support of Oracle in Natural for Windows and Natural for Linux

Predict offers the support of Oracle also on Windows and Linux platforms. On Windows and Linux 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.

### Documenting Oracle Tables and Views

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.

#### Subselect Clauses and Expressions in Field Definitions

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.

#### Archive Tables

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.

### Documenting Referential Constraints

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.

### Documenting Other Oracle Objects

#### Oracle Columns

Oracle columns are documented as field objects.

#### Columns With Format LOB

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.

#### Columns With Format ROWID

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

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

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

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

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

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.

#### Column Check Expressions

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.

#### Table Check Expression

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.

## Naming Conventions for Oracle

Oracle naming conventions must be observed when creating or maintaining Predict objects for Oracle. The following rules apply:

- General Rules

- Constraint Name

- Correlation Name

- Index Name

- Table/View Name

- Column Name

- Delimited Identifier

### General Rules

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:

1. Incorporation functions accept object and creator names as selection criteria according to the restrictions mentioned here and in the following sections. Oracle objects with a name longer than the described ones are not offered for incorporation.
2. Names that apply to the restrictions mentioned here and in the following sections but can not be shown in full length are indicated by ">" as last character.
3. For views use the editor commands `FLIP C` and `FLIP T` to edit the field names and their derivation in full length.

### Constraint Name

Contraint names can consist of up to 32 characters.

### Correlation Name

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 Name

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 Name

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 Name

Column names can consist of up to 32 characters.

### Delimited Identifier

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 (').

## Generating, Incorporating and Comparing Oracle Objects

### Prerequisites

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.

### Generation

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: `GENERATE ORACLE-TABLE`

#### Rules Applying when Generating Oracle Objects

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

### Incorporation

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.

### Comparison

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.

## Administrating Implemented Oracle Objects

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.

### On this page

- Documenting Oracle in Predict
- Naming Conventions for Oracle
- Generating, Incorporating and Comparing Oracle Objects
- Administrating Implemented Oracle Objects