Generate for Oracle

#### Search Results

Search

# Generate for Oracle

This document covers the following topics:

- Common Parameters for All Oracle Types

- Oracle Table/View

## Common Parameters for All Oracle Types

The following parameters can be set in all Oracle object types.

Parameters
Oracle Handler | The Oracle handler, the object will be implemented in. The value is preset with the value specified in the user or system profile.
List SQL statements |
Y | List the generated Oracle SQL statements.
Protocol saved in member | SQL statements generated for the implementation of the database can be saved in a Natural source member (protocol) on the FDIC file. A member name for the protocol is only required when the Oracle object is generated for the first time. SQL statements generated in subsequent generation runs are concatenated to the protocol. The protocol can be displayed using the Display function of the Administration Implemented object-type menu.
Protocol saved in library | Library where the protocol is to be saved.

## Oracle Table/View

The Generate Oracle Table / View function:

- Generates Oracle Table(s)/View(s)/Index(es).

- Generates check expressions. A check expression that differs from the documentation in Predict is dropped and recreated.

- Modifies existing Oracle Table/Indexes, replaces Oracle Views/Indexes or

- Purges indexes from Oracle tables if no longer documented with a corresponding Predict file object of file type OT (Oracle Table).

The following topics are covered below:

- Calling the Function: Generate Oracle Table/View

- Parameters for Oracle Table/View

- Generate Oracle Table/View in Batch Mode

- Sample File Definition for Oracle Table/View

- Sample Output for Oracle Table/View

### Calling the Function: Generate Oracle Table/View

The Generate Oracle Table/View screen is displayed with function code G and object code OF in a Predict main menu, or with the command `GENERATE ORACLE-TABLE`.

    16:27:39                *****  P R E D I C T  *****                  2017-07-31
    Plan   0              - Generate Oracle Table / View -

    File ID ..................* PREDICT-OT
    Oracle Handler ...........* ORAC-TEST-XYZ                    DBnr=221

    Add table / view .......... N (Y,N)      Replace view .............. N (Y,N)
    Add index ................. N (Y,N)
    Update table .............. Y (Y,N)
    Update index .............. N (Y,N)      Comment on ................ N (Y,N)
    Purge index ............... N (Y,N)

    List SQL statements ....... Y (Y,N)

    Protocol saved in member .. *
                   in library . ORALIB

    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 Table/View

Values for fields which have been locked by your data dictionary administrator cannot be overwritten. These fields are skipped when positioning the cursor with the TAB key. See Generation Defaults.

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

Presetting
The parameter below can be changed in the Modify Oracle Table/View Defaults screen. See Generation Defaults.
Count of saved actions | Number (0-99) of generation actions which are stored in the protocol specified under Protocol saved in member/library. If 99 is entered, all generation actions are stored. This is the default setting at installation.
Parameters
File ID | ID of the Predict file object from which the Oracle table/view is to be generated. Enter an asterisk to display a selection screen. Alternatively, use an asterisk as a wildcard. For example, enter "A*" to display IDs beginning with A only. Only Files of type OT or OV can be used.
Add table/view |
Y | A new Oracle table/view is created. If the Oracle table already exists, this parameter must be N.
Replace view |
Y | If the Oracle view already exists, the Oracle statements `DROP VIEW` and `COMMIT` are generated first, followed by the `CREATE VIEW` statement.
Add index | If Oracle indexes are defined for the Oracle table in Predict, set this option to Y to generate the `CREATE INDEX` statements.
Update table |
Y | If the Oracle table/view is already implemented, commands are generated to adapt the Oracle table/view to the documentation (if differences were found). This includes the generation of necessary `ALTER TABLE DROP COLUMN` statements for fields that are no longer documented.
Update index |
Y | If the Oracle index is already implemented, update commands are generated to adapt the Oracle index to the documentation (if differences were found). Updating Oracle indexes is only possible for differences of the following attributes: PARTIAL, BUFFERPOOL, FLASH_CACHE, MAXSIZE, FREELISTS, FREELISTS GROUPS, OPTIMAL, COMPRESS, PCTFREE, PCTUSED, INITIAL, NEXT, MAXEXTENTS, MINEXTENTS, PCTINCREASE, LOGGING, INITRANS. If any other attributes differ, Update index and Purge index must be set to Y. Predict will then update the index by issuing the statements `DROP INDEX`, `COMMIT` and `CREATE INDEX`.
Purge index |
Y | A `DROP INDEX` and a `COMMIT` statement are generated if a Oracle index exists and no corresponding index is defined in Predict.
Comment on |
Y | The first eight abstract lines of Predict field and file objects are used as Oracle comments for the columns and the table (SQL statement `COMMENT ON`). Note Comment on is set to Y, existing comments are generally overwritten even if no abstract is specified in Predict.

### Generate Oracle Table/View in Batch Mode

Command: `GENERATE ORACLE-TABLE`

Enter parameters on next line in positional or keyword form. File ID is obligatory, all other parameters are optional.

Field | Keyword | Position
--- | --- | ---
File ID | FILE-ID | 1
Add table/view | ADD-TABLE | 2
Add index | ADD-INDEX | 3
Update table | UPD-TABLE | 4
Update index | UPD-INDEX | 5
Purge index | PURGE-INDEX | 6
Replace view | REPLACE | 7
List Oracle statements | LIST | 8
Protocol saved in member | MEM | 9
Protocol saved in library | LIB | 10
Comment on | COMMENT | 11
Oracle Handler | SQL-SERVER | 12

If a parameter is not specified, the default value is taken.

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=*

### Sample File Definition for Oracle Table/View

The following file definition was used for generating the sample output:

    File ID ............ EXAM-GEN_TABLE
    Type ............... Oracle table
    -------------------------------------------------------------------------------

    Cnt  Ty L Field ID                         F  Cs  Length D DB N Df

       1    1 EMPNO                            A        6.0  P AA R
              Field is defined as unique
       2    1 FIRSTNAME                        AV      12.0    AB U
       3    1 MIDINIT                          A        1.0    AC U
       4    1 LASTNAME                         AV      15.0    AD U
       5    1 WORKDEPT                         A        4.0  F AE R Y
       6    1 PHONENO                          A        4.0    AF R Y
       7    1 HIREDATE                         DT              AG R Y
       8    1 JOB                              A        8.0    AH R Y
       9    1 EDLEVEL                          I        2.0    AI R Y
      10    1 SEX                              A        1.0    AJ R Y
      11    1 BIRTHDATE                        DT              AK R Y
      12    1 SALARY                           PS       7.2    AL R Y
      13    1 BONUS                            PS       7.2    AM R Y

    *** End of report ***

### Sample Output for Oracle Table/View

    File ID .. EXAM-GEN_TABLE

    CREATE TABLE EXAM.GEN_TABLE
       (EMPNO                           CHAR(6)
           NOT NULL,
        FIRSTNAME                       VARCHAR2(12),
        MIDINIT                         CHAR(1),
        LASTNAME                        VARCHAR2(15),
        WORKDEPT                        CHAR(4)
           DEFAULT ' '
           NOT NULL,
        PHONENO                         CHAR(4)
           DEFAULT ' '
           NOT NULL,
        HIREDATE                        DATE
           DEFAULT SYSDATE
           NOT NULL,
        JOB                             CHAR(8)
           DEFAULT ' '
           NOT NULL,
        EDLEVEL                         SMALLINT
           DEFAULT 0
           NOT NULL,
        SEX                             CHAR(1)
           DEFAULT ' '
           NOT NULL,
        BIRTHDATE                       DATE
           DEFAULT SYSDATE
           NOT NULL,
        SALARY                          DECIMAL(9, 2)
           DEFAULT 0
           NOT NULL,
        BONUS                           DECIMAL(9, 2)
           DEFAULT 0
           NOT NULL)
        ORGANIZATION HEAP
        PCTFREE 10
        PCTUSED 40
        INITRANS 1
        TABLESPACE USERS
        STORAGE ( INITIAL 5M
                  NEXT 5
                  MINEXTENTS 1
           NOT NULL)
        ORGANIZATION HEAP
        PCTFREE 10
        PCTUSED 40
        INITRANS 1
        TABLESPACE USERS
        STORAGE ( INITIAL 5M
                  NEXT 5
                  MINEXTENTS 1
                  MAXEXTENTS 2
                  PCTINCREASE 50
                  BUFFER_POOL DEFAULT
                  FLASH_CACHE DEFAULT )
        ROW STORE COMPRESS BASIC
        INMEMORY
        MEMCOMPRESS FOR DML
        PRIORITY HIGH
        DISTRIBUTE AUTO
        DUPLICATE ALL
       INDEXING ON
       PARTITION BY SYSTEM PARTITIONS 3
       RESULT_CACHE ( MODE FORCE )
    CREATE UNIQUE INDEX EXAM.EMPNO
       ON EXAM.GEN_TABLE
          (EMPNO                            ASC)
       PCTFREE 12
       PCTUSED 2
       INITRANS 33
        STORAGE ( FREELISTS 1
                  FREELIST GROUPS 3
                  BUFFER_POOL DEFAULT
                  FLASH_CACHE DEFAULT )
        FILESYSTEM_LIKE_LOGGING
        TABLESPACE TAB
       VISIBLE
       INDEXING PARTIAL
       COMPRESS 23
    ALTER TABLE EXAM.GEN_TABLE ADD
        PRIMARY KEY
                      ( EMPNO )
    CREATE INDEX EXAM.WORKDEPT
       VISIBLE
       INDEXING PARTIAL
       COMPRESS 23
    ALTER TABLE EXAM.GEN_TABLE ADD
        PRIMARY KEY
                      ( EMPNO )
    CREATE INDEX EXAM.WORKDEPT
       ON EXAM.GEN_TABLE
          (WORKDEPT                         ASC)
       PCTFREE 12
       PCTUSED 2
       INITRANS 33
        STORAGE ( FREELISTS 1
                  FREELIST GROUPS 3
                  BUFFER_POOL DEFAULT
                  FLASH_CACHE DEFAULT )
        FILESYSTEM_LIKE_LOGGING
        TABLESPACE TAB
       VISIBLE
       INDEXING PARTIAL
       COMPRESS 23

     DIC1800 Summary:    13 Field(s) processed.

### On this page

- Common Parameters for All Oracle Types
- Oracle Table/View