This document covers the following topics:
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 |
|
||
| 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. | ||
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:
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 |
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. |
||
| Add table/view |
|
||
| Replace view |
|
||
| 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 |
|
||
| Update index |
|
||
| Purge index |
|
||
| Comment on |
|
||
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=*
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 ***
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.