--- title: Oracle - Stage description: Oracle - Stage generates a Stage layer for an Oracle database as SQL*Plus scripts with PL/SQL loader packages. --- [Skip to content](https://knowledge.bigenius-x.com/oracle-stage#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 - Stage **Applies to** Platform: Oracle Architecture: Stage Oracle - Stage generates a Stage layer for an Oracle database as SQL\*Plus scripts with PL/SQL loader packages. Select it in Project Settings, where Generator Configuration shows `Oracle Stage`, when your Stage layer runs on Oracle. This article lists what the configuration supports and where it differs from [Microsoft SQL Server - Stage](https://knowledge.bigenius-x.com/microsoft-sql-server-stage-2.0?hsLang=en). ### Scope and output - Platform: Oracle. The configuration is available since Generator 2.0. - Layers: Source and Stage. Dataflow Modeling shows Stage in the Bronze column. See [Understand the Layering concept](https://knowledge.bigenius-x.com/understand-the-layering-concept?hsLang=en). - Output: a SQL\*Plus script for the Stage 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 Stage 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), for example `CREATE DATABASE LINK` for the Stage user. 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 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](https://knowledge.bigenius-x.com/database-links-for-source-systems-project?hsLang=en). To create source model objects from an Oracle schema, see [Discover an Oracle database](https://knowledge.bigenius-x.com/discover-an-oracle-database?hsLang=en). You can [export this project as a linked project](https://knowledge.bigenius-x.com/export-a-project-to-be-used-as-linked-project?hsLang=en). The configuration cannot import linked projects. ### Supported model objects - [Source](https://knowledge.bigenius-x.com/create-a-source?hsLang=en) - [Stage](https://knowledge.bigenius-x.com/create-a-stage?hsLang=en) ### 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. - Indexes: [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 Stage are described in [Partition Clause and table storage (Model Object)](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en). - 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 The configuration offers these project properties, with their defaults: - [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 - [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` - [Database Links for Source Systems](https://knowledge.bigenius-x.com/database-links-for-source-systems-project?hsLang=en): `Create Database Links` - [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 - Stage 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` | | Index types | B-tree, bitmap | rowstore, columnstore | | Begin of Universe | not available | available | Database Links for Source Systems, Create GRANTs for Imported Objects, 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 Stage `CUSTOMER` reads the table `CUSTOMER` of the Oracle source system `BFM Mssql`. With Database Links for Source Systems set to `Create Database Links`, it generates this table and source view. In the excerpt, `` and `` stand for the placeholders of the two schemas, and eight columns of the view are left out: ``` CREATE TABLE .STG_ST_CUSTOMER ( BG_LOADTIMESTAMP TIMESTAMP NOT NULL ,BG_SOURCESYSTEM VARCHAR2(255) ,CUSTOMER_ID NUMBER NOT NULL ,CUSTOMER VARCHAR2(100) NOT NULL ,CUSTOMER_TYPE VARCHAR2(100) NOT NULL ,CUSTOMER_SINCE DATE ,ADDRESS_LINE1 VARCHAR2(100) ,ADDRESS_LINE2 VARCHAR2(100) ,ADDRESS_LINE3 VARCHAR2(100) ,ZIP_CODE VARCHAR2(20) ,COUNTRY VARCHAR2(50) ,COUNTRY_ISO_CODE VARCHAR2(2) ,BU VARCHAR2(2) NOT NULL ) ; CREATE OR REPLACE VIEW .STG_ST_CUSTOMER_SOURCE AS SELECT CAST(NULL AS TIMESTAMP) AS BG_LOADTIMESTAMP ,CAST(NULL AS VARCHAR2(255)) AS BG_SOURCESYSTEM ,S1.CUSTOMER_ID AS CUSTOMER_ID ,S1.CUSTOMER AS CUSTOMER ... ,S1.BU AS BU FROM .CUSTOMER@BFM_Mssql_DBL S1 ; ``` The view reads the source table through the database link `BFM_Mssql_DBL`, which the Stage script creates for the source system. On Microsoft SQL Server, `BG_LOADTIMESTAMP` and `BG_SOURCESYSTEM` get the types `datetimeoffset` and `nvarchar(255)`. ### Release differences Project Settings shows the Generator Version of your project. - Generator 2.0: first release of the configuration. - Generator 2.0.1: index management, deactivation of model objects, Customize Default Terms, custom code before and after templates, template preview and the export as a linked project. - 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 property Increment Management Type; 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)`. - 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: - 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: [Partition Clause and table storage (Model Object)](https://knowledge.bigenius-x.com/partition-clause-and-table-storage-model-object?hsLang=en), [Database Links for Source Systems (Project)](https://knowledge.bigenius-x.com/database-links-for-source-systems-project?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), [Source Systems per Generator Configurations](https://knowledge.bigenius-x.com/source-systems-per-generator-configurations?hsLang=en), [Microsoft SQL Server - Stage](https://knowledge.bigenius-x.com/microsoft-sql-server-stage-2.0?hsLang=en) - Oracle: [Data Store](https://knowledge.bigenius-x.com/oracle-data-store?hsLang=en), [DataVault](https://knowledge.bigenius-x.com/oracle-datavault?hsLang=en), [Datamart](https://knowledge.bigenius-x.com/oracle-datamart?hsLang=en), [DataVault and Mart](https://knowledge.bigenius-x.com/oracle-datavault-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) - [Fabric Warehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-warehouse) - [Fabric Lakehouse](https://knowledge.bigenius-x.com/generators?hsLang=en#fabric-lakehouse) - [Databricks](https://knowledge.bigenius-x.com/generators?hsLang=en#databricks) - [Snowflake](https://knowledge.bigenius-x.com/generators?hsLang=en#snowflake) - [Microsoft SQL Server](https://knowledge.bigenius-x.com/generators?hsLang=en#microsoft-sql-server) - [Oracle](https://knowledge.bigenius-x.com/generators?hsLang=en#oracle) - [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 Azuze DevOps pipeline](https://knowledge.bigenius-x.com/generators?hsLang=en#deployment-with-an-azuze-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