Comparing Oracle Tables/Views

#### Search Results

Search

# Comparing Oracle Tables/Views

This document covers the following topics:

- Prerequisites and Restrictions for Oracle Tables/Views

- Calling the Function: Compare DD Objects with Oracle Files

- Parameters for Oracle Tables/Views

- Comparing Oracle Tables and Views in Batch Mode

## Prerequisites and Restrictions for Oracle Tables/Views

Oracle must be installed. See the section Oracle in the Predict and Other Systems documentation.

See also General Prerequisites for Comparison Functions.

## Calling the Function: Compare DD Objects with Oracle Files

The Compare DD Objects with Oracle Files screen is called with code C and object code OF in any Predict main menu or with the command `COMPARE ORACLE-TABLE`.

    16:35:48                *****  P R E D I C T  *****                  2017-07-31
    Plan   0          - Compare DD Objects with Oracle Files -       Profile SYSTEM

    File ID ........... PREDICT-OT                       Files of type ..*
    Contained in DA ...
    Oracle Handler ...* ORA-TEST-XYZ                    DBnr=221

    Check options                       List options
      File attributes .... Y (Y/N)        Files .........................* D
      Fields ............. Y (Y/N)        Fields ........................* D
                                          Members using differences .....* N
    Update options
      File attributes .... N (Y/N)
      Field sequences .... N (Y/N)
      Add fields ......... N (Y/N)
      Purge fields ....... N (Y/N)
      Field attributes ... N (Y/N)

    Restrictions ........*   Profile SYSTEM ,empty        Save set ....... 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 SelFi Prof  Main

## Parameters for Oracle Tables/Views

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

Parameters
Selection criteria
File ID | The ID of the Predict file object or objects which are to be compared with the connected Oracle tables or views. Asterisk notation can be used.
Files of type | Limit the function to files of the specified type.
Contained in DA | Limit the function to tables and views which reside in the specified database. Asterisk notation can be used.
Check options
If check options are not set, compare functions will not reset the Modified after generation flag. See Resetting the Modified after generation Flag for more information.
File attributes |
Y | Attributes of the file are to be compared with the implementation of the file in Oracle.
Fields |
Y | The field list of the file object is to be compared with the Oracle table or view.
List options
Files |
How attributes of the Predict file object are to be displayed.
Y | All file attributes.
N | No file attributes.
D | Different: only file attributes whose Predict values differ from the implementation.
Fields |
How fields linked to the Predict file object are to be displayed.
Y | All fields.
N | No fields.
D | Different: only fields whose Predict definitions differ from the Oracle table or view.
Members using differences |
Determines the amount of information to be displayed on members using fields that have been compared and whose Predict definition differs from the Oracle table or view.
Y | Display the names and types of all members.
N | Do not display members.
C | Count the number of members per library.
Update options
File attributes |
Y | Attributes of the Predict file object and, for files of type OV, the subselect specification of the SQL create statement are to be updated.
Field sequence |
Y | The field list is to be rearranged to match the sequence of the fields in the Oracle table or view.
Add fields |
Y | Fields in the Oracle table or view missing in the field list are to be inserted in the field list.
Purge fields |
Y | Fields that are missing in the Oracle table or view are to be removed from the field list.
Field attributes |
Y | Attributes of the fields linked to the Predict file object are to be changed to match the Oracle table or view. For files of type OV, the attributes that are to be updated include the subselect specification of the SQL create statement for each field. Comments of fields are not updated.
Save set |
Y | Program types and names reported by the function are saved in a set. See also Saving the Result of Comparison Funtions in Sets.

## Comparing Oracle Tables and Views in Batch Mode

Command: `COMPARE ORACLE-TABLE`

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

Field | Keyword | Position
--- | --- | ---
File ID | FILE-ID | 1
Files of type | TYPE | 2
Contained in DA | DB | 3
Check options
File attributes | CHECK-FILE | 4
Fields | CHECK-FIELD | 5
List options
Files | LIST-FILE | 6
Fields | LIST-FIELD | 7
Members using differences | LIST-PROGRAM | 8
Save set | SAVE-SET | 9
Update options
File attributes | UPD-FILE | 10
Field sequences | UPD-SEQUENCE | 11
Add fields | ADD-FIELD | 12
Purge fields | PURGE-FIELD | 13
Fields attributes | UPD-FIELD | 14
Oracle Handler | SQL-SERVER | 15

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 compare all files of type OT or OV with prefix "TEST" and display all fields with a different definition, code the command:

    COMPARE ORACLE-TABLE
    FILE-ID=TEST*,LIST-FIELD=D

or

    COMPARE ORACLE-TABLE
    TEST*,,,,,D

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

### On this page

- Prerequisites and Restrictions for Oracle Tables/Views
- Calling the Function: Compare DD Objects with Oracle Files
- Parameters for Oracle Tables/Views
- Comparing Oracle Tables and Views in Batch Mode