Incorporating Oracle Tables/Views

#### Search Results

Search

# Incorporating Oracle Tables/Views

This document covers the following topics:

- Prerequisites for Oracle Tables/Views

- Selecting Oracle Tables/Views

- Incorporation Functions for Oracle Tables/Views

- Incorporating Oracle Tables and Views in Batch Mode

## Prerequisites for Oracle Tables/Views

For Oracle tables and views, a Predict object documenting the Oracle database containing the tables/views must exist.

Note
See Concepts of Incorporation for basic information on how to use incorporation functions.

## Selecting Oracle Tables/Views

The Incorporate Oracle Tables/Views screen is called with function code I and object code OF in a Predict main menu or with the command `INCORPORATE TABLES`.

    17:26:25                *****  P R E D I C T  *****                  2017-07-25
    Plan   0            - Incorporate Oracle Tables/Views -

    Table / View name .....
    Oracle Handler .......* XYZ-ORA1                         DBnr=17

    Select options
      Tables or Views ....*
      Creator name ........

    Processing option
      Connect automatic ... N (Y,N)

    Update when connecting
      File attributes ..... N (Y/N)

    Command ===>
    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
          Help  Next  Stop  Last  LnkEl Flip  Print Impl  AdmFi             Main

Common parameters which are not included in the table below are described under Common Parameters for All Oracle Types.

Parameters
Table / view name | The name in Oracle of the tables and views from which the data dictionary objects are to be created. Asterisk notation is allowed.
Tables or views |
Limit the function to files of one type. Valid values:
T | Oracle tables
V | Oracle views
blank | All types
Creator name | The Oracle "creator" of the tables and views from which the data dictionary objects are to be created. Asterisk notation is allowed.
Connect automatic | Y - Oracle table/views and the Predict object will be connected by the Compare function (code T) if no differences were detected between the field lists of the two objects.
File attributes | Y - Attributes of the Predict file object and, for files of type E, the subselect specification of the SQL create statement are to be updated. The triggers linked to the file via association "Has TR" are also updated.

Files which meet the given selection criteria and are not already connected to a Predict file object are then listed.

Columns in the List
Creator | Creator in Oracle.
Table/view name | Name of the table/view.
T |
Type of the Oracle file:
T | Oracle table
V | Oracle view
Doc. exists |
Y | Predict object exists for the table/view.
Remark | Any of the following type-specific remarks or blank. For type-independent remarks see Remarks which apply to all Object Types.
Master missing | The specified Oracle view cannot be incorporated as a data dictionary object until a dictionary object for a related DB2 table or view has been created.
Invalid file-type | The type of the specified Oracle table or view does not match the type of the corresponding Predict object: the file type of the data dictionary object is either not OT (Oracle table) or not OV (Oracle view).

The tables/views in the list can then be processed by entering a function code in the Cmd column. The functions T, C and I apply.

## Incorporation Functions for Oracle Tables/Views

Valid functions:

- Compare - Code T

- Connect - Code C

- Display Masters - Code M

- Incorporate - Code I

Note
Corresponding Oracle tables/views and Predict file objects of type OT and OV are identified by Oracle table/view name.

### Compare - Code T

Compares Oracle table/views and corresponding Predict file object. Differences between field lists and attributes of the tables/views are reported.

If no differences between the field lists are found and Connect automatic is set to Y, the Oracle tables/view and the Predict file object are connected.

### Connect - Code C

Connects Oracle tables/views to Predict file object and

- copies current information from the Oracle catalog to the dictionary object;

- writes to the generation log of the Predict file object that a corresponding implemented table/view existed when the connection was established.

### Display Masters - Code M

Oracle databases and tables/views a table/view is related to must be documented in Predict before the table/view can be incorporated. This function finds all objects that are not documented. These can then be incorporated before the table/view is incorporated.

### Incorporate - Code I

#### Scope of the Incorporation

- One Predict file object is created for each table or view. The ID of the new file object consists of the creator name and Oracle name of the table or view joined by a hyphen.

- One field object is created for each field in the table or view. The ID assigned to each new field object will be the name of the field in Oracle. For each view that is incorporated, the appropriate parts of the subselect specification in the SQL statement CREATE will be copied to the file and field objects created.

- One Predict file object with file type IV (Intermediate view) is created for each subselect clause in the from clause of a view definition. For each column in the select list of subselect a Predict field object is created. For each intermediate view that is created, the appropriate parts of the subselect specification in the SQL statement CREATE will be copied to the file and field objects created.

- One Predict field object (type SP) for each composite index, partitioning key and cluster column is created.

- One Predict field object (type SP) for each composite foreign key is created if there is not already a Predict field object for a composite index built from the same columns.

- One Predict relationship object is created for each foreign key.

- A verification is added and linked to the corresponding field for each check constraint that is defined for one column.

- Attributes of the corresponding file object are updated for each table check constraint (check expression for more than one column).

#### Calling the Function

Specify the following parameters in an additional input screen before executing the function.

Incorporation Options
Comments as abstract |
Y | String constants assigned with COMMENT ON are to be incorporated as the abstract of the corresponding Predict object: Comments assigned to the table or view are to be copied to the file object. Comments assigned to a field of the table or view are to be copied to the field object.
List incorporated code |
Y | The values of all attributes incorporated for the table or view are to be listed.
Document as table |
Y | For views only. This incorporation option can be used if you are not interested in the derivation of a view from the underlying base table or if the used query involves the usage of compiled SQL scalar or table functions.

## Incorporating Oracle Tables and Views in Batch Mode

Command: `INCORPORATE ORACLE-TABLE`

Enter parameters on the next line in positional or keyword form.

Field | Keyword | Position
--- | --- | ---
Table/View name | TABLE | 1
Tables or Views | TYPE | 2
Creator name | CREATOR | 3
From DBID | DBNR | 4
Incorporate comments | COMMENT | 5
List incorporated code | LIST | 6
Incorporate comments | ABSTRACT | 7
Function | SUB-FUNCTION | 8
Oracle Handler | SQL-SERVER | 9
Connect automatic | AUTO-CONNECT | 10
Update file attributes | UPD-FILE | 11
Document as table | AS-TABLE | 12

Note
The keyword ABSTRACT can be used as a synonym for COMMENT.

If you explicitly set SQL-SERVER to blank in order to use the default server, another keyword must be specified after the SQL-SERVER keyword. If it is not possible to specify another keyword, the SQL-SERVER keyword must be followed by "D1=*" as shown below:

    SQL-SERVER= ,D1=*

#### Example

To incorporate the table "TDEPT", code the command:

    INCORPORATE ORACLE-TABLE
    TABLE=TDEPT,TYPE=T,,,SUB-FUNCTION=I

or

    INCORPORATE ORACLE-TABLE
    TDEPT,T,,,I

The example above uses the Natural parameters `ID=,` and `IA==`.

### On this page

- Prerequisites for Oracle Tables/Views
- Selecting Oracle Tables/Views
- Incorporation Functions for Oracle Tables/Views
- Incorporating Oracle Tables and Views in Batch Mode