Oracle - Data Store

Applies to
Platform: Oracle
Architecture: Data Store

Oracle - Data Store generates a Stage and a Store layer for an Oracle database as SQL*Plus scripts with PL/SQL loader packages. Select it in Project Settings, where Generator Configuration shows Oracle DataStore, when your Data Store runs on Oracle. This article lists what the configuration supports and where it differs from Microsoft SQL Server - Data Store.

Scope and output

Platform: Oracle. The configuration is available since Generator 2.0.
Layers: Source, Stage and Store. Dataflow Modeling shows the Stage and Data Store columns in Bronze. See Understand the Layering concept.
Output: one SQL*Plus script per layer, PL/SQL loader packages, the load control scripts and the PowerShell 7 scripts that deploy and load, described in Oracle - Artifacts.

Prerequisites

One Oracle user for each layer, here Stage and Store, and one for load control (with Native Load Control). Each user name is also the schema name of its layer.
PowerShell 7 and SQL*Plus on the machine that deploys.
The privileges listed in Oracle - Target environment, for example CREATE DATABASE LINK for the Stage user.
For model objects imported from a linked project, with Create GRANTs for Imported Objects on: the user name and password of each linked layer's user. The deployment connects as that user to grant the objects.

The placeholders for the users, schemas and connections are listed in Oracle - Placeholder values.

Sources and linked projects

The source system has the type Oracle. Stage views read its tables through a database link or directly, as set in Database Links for Source Systems. To create source model objects from an Oracle schema, see Discover an Oracle database.

You can import a linked project as a source and export this project as a linked project. Create GRANTs for Imported Objects decides whether the deployment grants the imported objects to the layer users.

Supported model objects

Capabilities and limitations

Names: generated names are unquoted and in capitals. Characters other than letters, digits, _, $ and # become _, and names are cut to 128 characters.
Keys: primary keys are created with RELY and a unique index.
Indexes: index management offers B-Tree Index and Bitmap Index and the option Create as local Index.
Table storage: the partition, compression, tablespace and statistics properties of a model object are described in Partition Clause and table storage (Model Object).
Wide objects: Hash Group Size splits the row hash of Store Entities into groups.
Data quality: data quality rules are available, see Understand Data Quality rules overview.
Grants: the Stage layer grants ALL with GRANT OPTION on its tables and views to the Store user. Each layer grants EXECUTE on its loader packages to the load-control user.
Loading: LoadControl Options offers Native Load Control and Airflow.
Data types: the platform data types CHAR(CHAR), CHAR(BYTE), VARCHAR2(CHAR) and VARCHAR2(BYTE) write the length semantics, for example VARCHAR2(100 CHAR). CHAR and VARCHAR2 write none, so the database setting NLS_LENGTH_SEMANTICS applies. TIMESTAMP WITH TIME ZONE with precision 3 becomes TIMESTAMP(3) WITH TIME ZONE.
Data type validation: a length, precision or scale that does not fit the Oracle data type gets a warning, see Invalid data type configuration (Business Rule). The generator writes the configured value: VARCHAR2(5000) fails with ORA-00910 at deployment on a database with MAX_STRING_SIZE = STANDARD.

Project properties

Project Settings shows these properties of the configuration, with their defaults:

Hash Algorithm: SHA1; also MD5, SHA256, SHA384 and SHA512

With SHA256, SHA384 or SHA512, the hash Default Terms stay RAW(20). Set their data type to the result length of the algorithm, see Hash Algorithm.

Hash Casing: None; also Upper and Lower
Hash Concatenation Separator: _
Hash Null Placeholder: _NULL_
Hash Trim: off
Begin of Universe: 19000101
End of Universe: 99991231
Persist Validity: off
Create Result Views: on
Target Object Comment Type: None
Artifact Output Mode: Layer Composites
Deployment Options: Generate Drop Table Statements
Configure LoadControl: on
Deploy LoadControl: on
LoadControl Options: Native Load Control; also Airflow
Database Links for Source Systems: Create Database Links
Create GRANTs for Imported Objects: on

Platform differences

The table compares the configuration with Microsoft SQL Server - Data Store of the same release:

Item	Oracle	Microsoft SQL Server
Timestamp Default Terms	`TIMESTAMP`	`datetimeoffset`
Hash Default Terms	`RAW(20)`	`binary(20)`
Text Default Terms	`VARCHAR2`	`nvarchar`
Number Default Terms	`NUMBER`	`int` or `bigint`
Flag Default Terms	`NUMBER(1, 0)`	`bit`
Hash Algorithm values besides `SHA1` and `MD5`	`SHA256`, `SHA384`, `SHA512`	`SHA2_256`
Index types	B-tree, bitmap	rowstore, columnstore

Database Links for Source Systems, Create GRANTs for Imported Objects, Hash Group Size, Create as local Index and the table storage properties exist only on Oracle. Included Columns of an index exists only on Microsoft SQL Server.

These Default Terms belong to each group; text Default Terms have the same lengths on both platforms:

Timestamps: BG_LoadTimestamp, BG_ValidFromTimestamp, BG_ValidToTimestamp, BG_UpdateTimestamp, BG_EffectiveTimestamp
Row hashes: BG_RowHash, BG_RowHashScd1, BG_RowHashScd2
Text: BG_SourceSystem, BG_DataflowName, BG_DataflowSetName, BG_ErrorDescription, BG_IncrementLoadStatus
Numbers: BG_ID, BG_ErrorCount, BG_DataQuality_ID
Flags: BG_IsDeleted

Example

A Store Entity named 1st dd-fullset scd2 keeps the history of a customer source. Three of its terms use Oracle-specific types: CUSTOMER is VARCHAR2(CHAR) with length 100, CUSTOMER_TYPE is CHAR(BYTE) with length 10, and CUSTOMER_SINCE is TIMESTAMP WITH TIME ZONE with precision 3. The model object name becomes 1ST_DD_FULLSET_SCD2 in the table name. In the excerpt, <store schema> stands for the placeholder of the Store schema, and short lines are joined:

CREATE TABLE <store schema>.DS_SE_1ST_DD_FULLSET_SCD2 (
     BG_LOADTIMESTAMP TIMESTAMP NOT NULL
    ,BG_UPDATETIMESTAMP TIMESTAMP NOT NULL
    ,BG_SOURCESYSTEM VARCHAR2(255)
    ,BG_VALIDFROMTIMESTAMP TIMESTAMP NOT NULL
    ,BG_ISDELETED NUMBER(1, 0) NOT NULL
    ,BG_ROWHASHSCD2 RAW(20) NOT NULL
    ,CUSTOMER_ID NUMBER NOT NULL
    ,CUSTOMER VARCHAR2(100 CHAR) NOT NULL
    ,CUSTOMER_TYPE CHAR(10 BYTE) NOT NULL
    ,CUSTOMER_SINCE TIMESTAMP(3) WITH TIME ZONE
    ,ADDRESS_LINE1 VARCHAR2(100)
    ,ADDRESS_LINE2 VARCHAR2(100)
    ,ADDRESS_LINE3 VARCHAR2(100)
    ,ZIP_CODE VARCHAR2(20)
    ,COUNTRY VARCHAR2(50)
    ,COUNTRY_ISO_CODE VARCHAR2(2)
)
;
ALTER TABLE <store schema>.DS_SE_1ST_DD_FULLSET_SCD2
ADD CONSTRAINT DS_SE_1ST_DD_FULLSET_SCD2_PK
PRIMARY KEY (BG_VALIDFROMTIMESTAMP, CUSTOMER_ID)
RELY
USING INDEX (
    CREATE UNIQUE INDEX <store schema>.DS_SE_1ST_DD_FULLSET_SCD2_PK
    ON <store schema>.DS_SE_1ST_DD_FULLSET_SCD2
    (BG_VALIDFROMTIMESTAMP, CUSTOMER_ID)
);

After deployment, USER_TAB_COLUMNS shows CHAR_USED C for CUSTOMER and B for CUSTOMER_TYPE. The other text columns follow NLS_LENGTH_SEMANTICS of the database.

Release differences

Project Settings shows the Generator Version of your project.

Generator 2.0: first release of the configuration.
Generator 2.2: the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder and Hash Trim; the model object properties Increment Management Type on Stages, Delete Detection Partial Operator and Delete Detection Partial Terms; the platform data types with length semantics and the data type validation; the replacement of characters in generated names. Up to Generator 2.0.1, precision 3 was written as TIMESTAMP(3 WITH TIME ZONE).
Generator 2.3: the model object property Hash Group Size.
Fixes for Oracle are listed in Generators Release - 2.3.0, Generators Release - 2.4.0 and Generators Release - 2.4.1.