--- title: Oracle Data Mart description: Oracle Data Mart generates a Mart layer for an Oracle database from the objects of linked projects, as SQL*Plus scripts with PL/SQL loader packages. --- [Skip to content](https://knowledge.bigenius-x.com/oracle-data-mart#main-content) [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal?hsLang=en) [![TRI\_Logo\_biGENIUS\_2020\_RGB\_s](https://knowledge.bigenius-x.com/hs-fs/hubfs/Marketing/TRI_Logo_biGENIUS_2020_RGB_s.png?width=150&height=42&name=TRI_Logo_biGENIUS_2020_RGB_s.png)](https://www.bigenius-x.com/) Open main navigation Close main navigation - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new) - [Go to Customer Portal](https://knowledge.bigenius-x.com/customer-portal) How can we help you? - There are no suggestions because the search field is empty. 1. [biGENIUS-X Knowledge Base](https://knowledge.bigenius-x.com/?hsLang=en) 2. [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en) 3. [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) # Oracle Data Mart **Applies to** Platform: Oracle Architecture: Data Mart Oracle Data Mart generates a Mart layer for an Oracle database from the objects of linked projects, as SQL\*Plus scripts with PL/SQL loader packages. Select it in Project Settings, where Generator Configuration shows `Oracle DataMart`, when your data mart runs on Oracle. This article lists what the configuration supports and where it differs from [Microsoft SQL Server Data Mart](https://knowledge.bigenius-x.com/microsoft-sql-server-data-mart?hsLang=en). ### Scope and output - Platform: Oracle. The configuration is available since Generator 2.0. - Layers: Mart, and the layers of the imported linked projects. Dataflow Modeling shows the imported objects in the Linked column in Silver and the Mart layer as the Datamart column in Gold. See [Understand the Layering concept](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en). - Output: a SQL\*Plus script for the Mart layer, PL/SQL loader packages, the load control scripts and the PowerShell 7 scripts that deploy and load, described in [Oracle - Artifacts](https://knowledge.bigenius-x.com/oracle-artifacts?hsLang=en). ### Prerequisites - One Oracle user for the Mart layer and one for load control (with Native Load Control). The user name is also the schema name of the layer. - PowerShell 7 and SQL\*Plus on the machine that deploys. - The privileges listed in [Oracle - Target environment](https://knowledge.bigenius-x.com/oracle-target-environment?hsLang=en). The placeholders for the users, schemas and connections are listed in [Oracle - Placeholder values](https://knowledge.bigenius-x.com/oracle-placeholder-values?hsLang=en). ### Sources and linked projects The configuration reads only model objects of linked projects, for example of a Data Vault or Data Store project. It has no discovery of its own and does not offer Database Links for Source Systems. [Import a linked project](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) before you model the Mart layer. You can also [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en). [Create GRANTs for Imported Objects](https://knowledge.bigenius-x.com/create-grants-for-imported-objects-project?hsLang=en) decides whether the deployment grants the imported objects to the Mart user. When it is on, the deployment also connects as the users of the linked layers. ### Supported model objects - [Mart Dimension](https://knowledge.bigenius-x.com/mart-dimension?hsLang=en) - [Mart Fact](https://knowledge.bigenius-x.com/mart-fact?hsLang=en) - [Mart Composite](https://knowledge.bigenius-x.com/mart-composite?hsLang=en) - [Mart Hierarchy Flatten](https://knowledge.bigenius-x.com/mart-hierarchy-flatten?hsLang=en) - [Mart Out](https://knowledge.bigenius-x.com/mart-out?hsLang=en), since Generator 2.2 ### 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, and business keys get a unique constraint. [Foreign Key Mode](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en) on Mart Facts offers `None`, `Create Disabled` and `Create Enforced`; the default is `Create Disabled`. - Indexes: the deployment creates an index on the referencing columns of each relationship, unless the primary key or a unique key has the same columns. On Mart Facts it is a bitmap index, otherwise a B-tree index. [Index management](https://knowledge.bigenius-x.com/add-an-index?hsLang=en) 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)](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en). - Wide objects: [Hash Group Size](https://knowledge.bigenius-x.com/hash-group-size-model-object?hsLang=en) splits the row hash of Mart Dimensions into groups. - Identity values: the surrogate keys of Mart Dimensions and identity columns such as `BG_ID` are `NUMBER GENERATED BY DEFAULT AS IDENTITY`. - Grants: the Mart layer grants `EXECUTE` on its loader packages to the load-control user. - Loading: [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) 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)](https://knowledge.bigenius-x.com/invalid-data-type-configuration-business-rule?hsLang=en). 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](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en): `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](https://knowledge.bigenius-x.com/hash-algorithm-project?hsLang=en). - [Hash Casing](https://knowledge.bigenius-x.com/hash-casing-project?hsLang=en): `None`; also `Upper` and `Lower` - [Hash Concatenation Separator](https://knowledge.bigenius-x.com/hash-concatenation-separator-project?hsLang=en): `_` - [Hash Null Placeholder](https://knowledge.bigenius-x.com/hash-null-placeholder-project?hsLang=en): `_NULL_` - [Hash Trim](https://knowledge.bigenius-x.com/hash-trim-project?hsLang=en): off - [Begin of Universe](https://knowledge.bigenius-x.com/begin-of-universe-project?hsLang=en): `19000101` - [End of Universe](https://knowledge.bigenius-x.com/end-of-universe-project?hsLang=en): `99991231` - [Persist Validity](https://knowledge.bigenius-x.com/persist-validity-project?hsLang=en): off - [Create Result Views](https://knowledge.bigenius-x.com/create-result-views-project?hsLang=en): on - [Target Object Comment Type](https://knowledge.bigenius-x.com/target-object-comment-type-project?hsLang=en): `None` - [Artifact Output Mode](https://knowledge.bigenius-x.com/artifact-output-mode-project?hsLang=en): `Layer Composites` - [Deployment Options](https://knowledge.bigenius-x.com/deployment-options-project?hsLang=en): `Generate Drop Table Statements` - [Configure LoadControl](https://knowledge.bigenius-x.com/configure-loadcontrol-project?hsLang=en): on - [Deploy LoadControl](https://knowledge.bigenius-x.com/deploy-loadcontrol-project?hsLang=en): on - [LoadControl Options](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en): `Native Load Control`; also `Airflow` - [Create GRANTs for Imported Objects](https://knowledge.bigenius-x.com/create-grants-for-imported-objects-project?hsLang=en): on ### Platform differences The table compares the configuration with Microsoft SQL Server Data Mart 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 | 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`, `BG_MartDimension_Identity`, `BG_MartFact_ForeignKey_Identity` - Flags: `BG_IsDeleted` ### Example A Mart Dimension `common_pos` with the business key term `POS_ID` generates this table. Its surrogate key `COMMON_POS_SK` is an identity column. In the excerpt, `` stands for the placeholder of the Mart schema, and short lines are joined: ``` CREATE TABLE .DM_MD_COMMON_POS ( BG_SOURCESYSTEM VARCHAR2(255) ,BG_LOADTIMESTAMP TIMESTAMP NOT NULL ,COMMON_POS_SK NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL ,POS_ID NUMBER NOT NULL ,CASHBOX_NO NUMBER NOT NULL ,BRANCH_ID NUMBER NOT NULL ,HUB_HK RAW(20) NOT NULL ,BG_ROWHASHSCD1 RAW(20) NOT NULL ) ; ALTER TABLE .DM_MD_COMMON_POS ADD CONSTRAINT DM_MD_COMMON_POS_PK PRIMARY KEY (COMMON_POS_SK) RELY USING INDEX ( CREATE UNIQUE INDEX .DM_MD_COMMON_POS_PK ON .DM_MD_COMMON_POS (COMMON_POS_SK) ); ALTER TABLE .DM_MD_COMMON_POS ADD CONSTRAINT DM_MD_COMMON_POS_UK UNIQUE (POS_ID) USING INDEX ( CREATE UNIQUE INDEX .DM_MD_COMMON_POS_UK ON .DM_MD_COMMON_POS (POS_ID) ); ``` On Microsoft SQL Server, `BG_SOURCESYSTEM` and `BG_LOADTIMESTAMP` get the types `nvarchar(255)` and `datetimeoffset`. ### Release differences Project Settings shows the Generator Version of your project. - Generator 2.0: first release of the configuration. - Generator 2.2: Mart Out; the project properties Persist Validity, Create Result Views, Hash Casing, Hash Concatenation Separator, Hash Null Placeholder and Hash Trim; the model object property Increment Management Type on Mart Facts; 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 properties Hash Group Size and Custom Singleton Values. - Fixes for Oracle are listed in [Generators Release - 2.3.0](https://knowledge.bigenius-x.com/generators-release-2.3.0?hsLang=en), [Generators Release - 2.4.0](https://knowledge.bigenius-x.com/generators-release-2.4.0?hsLang=en) and [Generators Release - 2.4.1](https://knowledge.bigenius-x.com/generators-release-2.4.1?hsLang=en). ### Related articles - Previous: [Import a Project to be used as a Linked Project](https://knowledge.bigenius-x.com/import-a-project-to-be-used-as-a-linked-project?hsLang=en) - Oracle: [Target environment](https://knowledge.bigenius-x.com/oracle-target-environment?hsLang=en) - Next: [Generate artifacts](https://knowledge.bigenius-x.com/generate-artifacts?hsLang=en), [Replace the placeholders in the artifacts](https://knowledge.bigenius-x.com/replace-the-placeholders-in-the-artifacts?hsLang=en) - Oracle: [Artifacts](https://knowledge.bigenius-x.com/oracle-artifacts?hsLang=en), [Deployment](https://knowledge.bigenius-x.com/oracle-deployment?hsLang=en), [Load data - Native Load Control](https://knowledge.bigenius-x.com/oracle-load-data-native-load-control?hsLang=en), [Load data - Apache Airflow Load Control](https://knowledge.bigenius-x.com/oracle-load-data-apache-airflow-load-control?hsLang=en) - Reference: [Foreign Key Mode (Model Object)](https://knowledge.bigenius-x.com/foreign-key-mode-model-object?hsLang=en), [Partition Clause and table storage (Model Object)](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en), [Hash Group Size (Model Object)](https://knowledge.bigenius-x.com/hash-group-size-model-object?hsLang=en), [Custom Singleton Values (Model Object)](https://knowledge.bigenius-x.com/custom-singleton-values-model-object?hsLang=en), [Create GRANTs for Imported Objects (Project)](https://knowledge.bigenius-x.com/create-grants-for-imported-objects-project?hsLang=en), [LoadControl Options (Project)](https://knowledge.bigenius-x.com/loadcontrol-options-project?hsLang=en) - Oracle: [Placeholder values](https://knowledge.bigenius-x.com/oracle-placeholder-values?hsLang=en) - See also: [Available Generator Configurations](https://knowledge.bigenius-x.com/available-generator-configurations?hsLang=en), [Understand the Generator Configuration](https://knowledge.bigenius-x.com/understand-the-generator-configuration?hsLang=en), [Use Linked Projects](https://knowledge.bigenius-x.com/use-linked-projects?hsLang=en), [Microsoft SQL Server Data Mart](https://knowledge.bigenius-x.com/microsoft-sql-server-data-mart?hsLang=en) - Oracle: [Stage](https://knowledge.bigenius-x.com/oracle-stage?hsLang=en), [Data Store](https://knowledge.bigenius-x.com/oracle-data-store?hsLang=en), [Data Vault](https://knowledge.bigenius-x.com/oracle-data-vault?hsLang=en), [Data Vault and Mart](https://knowledge.bigenius-x.com/oracle-data-vault-and-mart?hsLang=en) - [Getting started](https://knowledge.bigenius-x.com/getting-started?hsLang=en#main-content) - [Guidelines](https://knowledge.bigenius-x.com/getting-started?hsLang=en#guidelines) - [Start working](https://knowledge.bigenius-x.com/getting-started?hsLang=en#start-working) - [Account](https://knowledge.bigenius-x.com/getting-started?hsLang=en#account) - [General overview](https://knowledge.bigenius-x.com/general-overview?hsLang=en) - [Artificial Intelligence](https://knowledge.bigenius-x.com/artificial-intelligence?hsLang=en) - [Application Modules](https://knowledge.bigenius-x.com/application-modules?hsLang=en#main-content) - [Administration](https://knowledge.bigenius-x.com/application-modules?hsLang=en#administration) - [Solutions](https://knowledge.bigenius-x.com/application-modules?hsLang=en#solutions) - [Global Features](https://knowledge.bigenius-x.com/application-modules?hsLang=en#global-features) - [Projects](https://knowledge.bigenius-x.com/application-modules?hsLang=en#projects) - [Branches](https://knowledge.bigenius-x.com/application-modules?hsLang=en#branches) - [Data Connections](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-connections) - [Dataflow Modeling - Overview](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-overview) - [Dataflow Modeling - Wizard Steps](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-wizard-steps) - [Dataflow Modeling - Terms](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-terms) - [Dataflow Modeling - Term Mapping](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-term-mapping) - [Dataflow Modeling - Relationships](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-relationships) - [Dataflow Modeling - Data Quality](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-data-quality) - [Dataflow Modeling - Indexes](https://knowledge.bigenius-x.com/application-modules?hsLang=en#dataflow-modeling-indexes) - [Relationship Modeling](https://knowledge.bigenius-x.com/application-modules?hsLang=en#relationship-modeling) - [Generate Artifacts](https://knowledge.bigenius-x.com/application-modules?hsLang=en#generate-artifacts) - [Project Settings](https://knowledge.bigenius-x.com/application-modules?hsLang=en#project-settings) - [Data Marketplace](https://knowledge.bigenius-x.com/application-modules?hsLang=en#data-marketplace) - [Generators](https://knowledge.bigenius-x.com/generators?hsLang=en#main-content) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Databricks dbt](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks-dbt) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Snowflake dbt](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake-dbt) - [Artifacts](https://knowledge.bigenius-x.com/generators?hsLang=en#artifacts) - [Replace Placeholders](https://knowledge.bigenius-x.com/generators?hsLang=en#replace-placeholders) - [Target solution environment](https://knowledge.bigenius-x.com/generators?hsLang=en#target-solution-environment) - [Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment) - [Deployment with an Azure DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azure-devops-pipeline) - [Delta Deployment](https://knowledge.bigenius-x.com/generators?hsLang=en#delta-deployment) - [Load control environment](https://knowledge.bigenius-x.com/generators?hsLang=en#load-control-environment) - [Load data with a native load control](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-a-native-load-control) - [Load data with Apache Airflow](https://knowledge.bigenius-x.com/generators?hsLang=en#load-data-with-apache-airflow) - [Model Object Type](https://knowledge.bigenius-x.com/generators?hsLang=en#model-object-type) - [Properties](https://knowledge.bigenius-x.com/generators?hsLang=en#properties) - [Default Terms](https://knowledge.bigenius-x.com/generators?hsLang=en#default-terms) - [Discovery application](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#main-content) - [Discovery configurations](https://knowledge.bigenius-x.com/discovery-application?hsLang=en#discovery-configurations) - [Best practices](https://knowledge.bigenius-x.com/best-practices?hsLang=en#main-content) - [Modeling Approaches](https://knowledge.bigenius-x.com/best-practices?hsLang=en#modeling-approaches) - [Use Cases](https://knowledge.bigenius-x.com/best-practices?hsLang=en#use-cases) - [Business Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#business-rules) - [Data Quality Rules](https://knowledge.bigenius-x.com/best-practices?hsLang=en#data-quality-rules) - [FAQs](https://knowledge.bigenius-x.com/faqs?hsLang=en) - [Product Release Notes](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#main-content) - [SaaS Application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#saas-application) - [Discovery application](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#discovery-application) - [Generators](https://knowledge.bigenius-x.com/product-release-notes?hsLang=en#generators) - [Legal Documents](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#main-content) - [Current legal docs](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#current-legal-docs) - [Software Product and Limits](https://knowledge.bigenius-x.com/legal-documents?hsLang=en#software-product-and-limits) - [Contact us](https://knowledge.bigenius-x.com/kb-tickets/new?hsLang=en) - [Customer Portal](https://knowledge.bigenius-x.com/customer-portal) - [Sign in](https://knowledge.bigenius-x.com/_hcms/mem/login) [![biGENIUS logo](https://knowledge.bigenius-x.com/hubfs/TRI_Logo_biGENIUS_2020_RGB-1.svg "biGENIUS logo")](https://www.bigenius-x.com) All rights reserved © 2025, biGENIUS AG