Oracle Publishing (on-prem)

Empower supports publishing datasets to on-premises or cloud-hosted Oracle databases via the Self-Hosted Integration Runtime (SHIR). Data is automatically transferred from your Empower delta lake when the underlying data is updated.

Prerequisites

1. An Oracle database instance accessible from the Self-Hosted Integration Runtime (SHIR).
2. A Self-Hosted Integration Runtime installed and registered in your Azure Data Factory environment.
3. Oracle credentials (host, port, service name, user ID, password) stored as a connection string in Azure Key Vault.
4. Data in your Empower delta lake to publish.
5. Advanced Options is toggled on for your account.

Steps

1. Add the Oracle Server as a Connection

1. In the Empower sidebar, click Connect.
2. Click + to add a new connection, navigate to Oracle or use the search bar.
3. Fill in the required fields:
   • Host (e.g. myoracleserver.example.com)
   • Port (defaults to 1521)
   • Service Name (e.g. ORCL or myservice.example.com)
   • User ID
   • Password
4. Click Save and Connect to save the new Oracle connection.

icon="⚙️" theme="default"

Self-Hosted Integration Runtime (SHIR)

Oracle publishing uses the SHIR to connect to databases that are not publicly accessible. Ensure the SHIR is installed on a machine that has network access to the target Oracle instance and is registered with your Azure Data Factory. The linked service (EMPOWER_ORACLE_LS) must be configured to use the SHIR.

2. Create a Publish Task

1. In the Empower sidebar, click Publish.
2. Click the + button to open the New Publish Task panel.
3. Fill in the following fields:
   • Publish Task Name: enter a descriptive name for the task.
   • Publish Method: select Publish to Target.
   • Connection: select the Oracle connection you created in the previous step.
   • Description (optional): add a short description (up to 100 characters).
4. Click Create to create the publish task.

3. Configure Publish Entities

Once the Publish Task is created, navigate to its Configuration tab to add the entities (tables) you want to publish:

1. Open the Publish Task you just created.
2. Click + New Record to add an entity.
3. Fill in the following fields:
   • Target Entity Name: the name of the table as it will be created/written in the target Oracle database (e.g. MY_TARGET_TABLE). Note: Oracle table and schema names are automatically converted to uppercase.
   • Source Schema: the source table's schema.
   • Source Entity Name: the source table's name.
   • Source Entity Filter (optional): adds a WHERE condition to filter the source data (e.g. account_type='Debit').
     1. The filter must be valid SQL (everything that could be part of a SQL WHERE clause, just without the preceding WHERE).
     2. Make sure to use fields that exist in the source table you are specifying.
     3. When a source filter is provided, the pipeline runs a Databricks notebook (Publish_Filter_To_Parquet) to write the filtered data as Parquet to a temporary ADLS path before the copy activity picks it up.
   • Source Catalog (optional): the source table's Unity Catalog. Only required if the source table is in a specific catalog.
4. Click Create to save the entity record.

ℹ️ New records default to inactive. You must activate the entity for it to be included in publish runs.

Entity Options

After creating an entity record, you can configure Entity Options to control how data is written to the target table. Options are stored as name / value pairs on each entity. To add an option, create an entry with the option name and its value for that entity.

Available Options:

Option Name	Values	Default	Description
oracle_table_mode	overwrite / append	overwrite	Controls write mode. overwrite replaces the data in the target table on each publish run. append adds new rows to the existing table without clearing it — no DDL is executed.
oracle_table_truncate	true / false	true	Controls how overwrite is performed. Only applies when oracle_table_mode is overwrite. When true, the pipeline attempts to create the table; if it already exists (ORA-00955) it truncates it instead. When false, the existing table is dropped and recreated from scratch.

Example — set an entity to append mode:

Name	Value
oracle_table_mode	append

Example — set an entity to drop-and-recreate on each run:

Name	Value
oracle_table_mode	overwrite
oracle_table_truncate	false

How the options work together:

oracle_table_mode	oracle_table_truncate	What Happens
overwrite (default)	true (default)	Attempt CREATE TABLE; if it already exists (ORA-00955), TRUNCATE TABLE instead. Then insert data.
overwrite	false	DROP TABLE (ignoring ORA-00942 if it doesn't exist), then CREATE TABLE. Then insert data. Useful if the schema has changed.
append	(ignored)	Insert data into the existing table as-is. No truncation, drop, or DDL is executed.

Default behavior (no options set): The target table is created if it does not exist, or truncated if it already exists, and then reloaded on each publish run.

icon="📌" theme="default">

Automatic DDL Generation

Unlike SQL Server publishing, Oracle publishing does not use ADF's autoCreate option. Instead, the pipeline runs a Databricks notebook (Oracle_DDL_Generator) that reads the Parquet schema from ADLS and generates an Oracle-typed DDL column list (e.g. COL1 VARCHAR2(255), COL2 NUMBER(10,2)). This generated DDL is used in the preCopyScript to create the table with the correct Oracle data types. No manual DDL or field_query configuration is required.

How It Works (Pipeline Flow)

The Oracle publish pipeline follows this execution path:

PUBLISH_MAIN_PL (Orchestrator)
    ↓
PUBLISH_TO_TARGET_PL (Target Router — routes by target_type)
    ↓ (target_type = "oracle")
PUBLISH_ORACLE_PL_ENTITY (Oracle Handler)
    ↓
ForEach Entity (batch count: 6, parallel)
    ├── GET_KEYVAULT_SECRET → retrieve connection string from Key Vault
    ├── Parse credentials (SET_SERVER_NAME, SET_USERNAME, SET_PASSWORD)
    ├── GET_DATABRICKS_POOL_ID → resolve Databricks instance pool
    ├── NB_GENERATE_ORACLE_DDL → Databricks notebook generates Oracle DDL from Parquet schema
    ├── IF_HAS_SOURCE_FILTER → optionally run Publish_Filter_To_Parquet notebook
    └── CPY_PUBLISH_ORACLE_DATA → ADF Copy from ADLS Parquet to Oracle table
         (preCopyScript: CREATE TABLE / TRUNCATE / DROP+CREATE based on options)

Data Source Path

The copy activity reads Parquet data from one of two ADLS locations depending on whether a source filter is set:

Scenario	Source Path
No filter	DELTA/{catalog}/{schema}/{table}/dl_iscurrent=true
With filter	RAW/PUBLISH/FILTERED/{publish_entity_id}

Key Configuration

Setting	Value
Write Batch Size	10,000 rows
Timeout	2 hours 30 minutes
Retry	0 (no automatic retries)
Type Conversion	Enabled (with data truncation allowed)
DDL Generation	Automatic via Databricks notebook

Oracle preCopyScript Logic

The preCopyScript uses PL/SQL blocks to handle table management:

Truncate mode (default):

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE SCHEMA.TABLE (col1 VARCHAR2(255), ...)';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE = -955 THEN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE SCHEMA.TABLE';
  ELSE RAISE;
  END IF;
END;

Drop + Recreate mode (oracle_table_truncate=false):

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE SCHEMA.TABLE';
  EXCEPTION WHEN OTHERS THEN
    IF SQLCODE = -942 THEN NULL;
    ELSE RAISE;
    END IF;
  END;
  EXECUTE IMMEDIATE 'CREATE TABLE SCHEMA.TABLE (col1 VARCHAR2(255), ...)';
END;

Key Differences: Oracle vs SQL Server Publishing

Aspect	SQL Server	Oracle
SHIR Required	Yes	Yes
Table Creation	ADF autoCreate (automatic)	Databricks notebook generates Oracle DDL
Schema/Table Names	Case-preserving	Automatically converted to UPPERCASE
Connection String Format	Server=...;Initial Catalog=...;User ID=...;Password=...	host=...;port=...;servicename=...;user id=...;password=...
Table Mode Option	sql_table_mode	oracle_table_mode
Table Truncate Option	sql_table_truncate	oracle_table_truncate
Linked Service	EMPOWER_SQLSERVER_LS	EMPOWER_ORACLE_LS
Dataset	SQLSERVER_PUBLISH_DS	ORACLE_PUBLISH_DS
Pipeline	PUBLISH_SQLSERVER_PL_ENTITY	PUBLISH_ORACLE_PL_ENTITY

Troubleshooting

Issue	Resolution
Connection timeout	Ensure the SHIR machine can reach the Oracle instance. Verify firewall rules and port access (default 1521).
Authentication failure	Verify the Key Vault secret contains the correct connection string format (host=...;port=...;servicename=...;user id=...;password=...).
ORA-00955 (table already exists)	This is handled automatically by the preCopyScript. If it persists, verify the user has CREATE TABLE and TRUNCATE TABLE privileges.
ORA-00942 (table does not exist)	This is handled automatically in drop mode. If you see this during data copy, ensure the DDL generation notebook ran successfully.
DDL generation failure	Check that the source Parquet data exists at the expected ADLS path and the Databricks cluster has access.
Data type mismatch	The Oracle_DDL_Generator notebook maps Spark/Parquet types to Oracle types. If a specific type mapping is wrong, check the notebook's type-mapping logic.
Filter not applied	Ensure the Source Entity Filter field contains valid SQL syntax without the WHERE keyword prefix.
Uppercase table names	Oracle identifiers are always uppercased by the pipeline. Ensure your downstream queries reference the correct casing.