Configure Oracle E-Business Suite as a data pipeline source ​

Set up Oracle E-Business Suite (EBS) as a data pipeline source to extract and sync records from EBS database tables to your destination. The pipeline connects directly to the EBS database through an on-prem agent.

Use this guide to set up a database user, connect to EBS, configure your pipeline, and review sync behavior, data type handling, and limitations.

Features supported ​

The following features are supported when you use Oracle E-Business Suite as a pipeline source:

• On-prem connectivity: Connect to the EBS database through an on-prem group. Cloud connections aren't supported.
• Full sync and incremental sync: Sync all rows on each run, or sync only new and updated rows based on a change tracking column. Refer to Sync modes for more information.
• Table-level object selection: Select individual tables from any schema the database user can access.
• Primary key detection: Detect primary keys from database constraints and EBS table metadata. Refer to Primary keys for more information.
• Delete tracking: Mark records deleted at the source as deleted in the destination when a table runs in full sync mode.
• Schema drift detection and handling: Detect and apply schema changes automatically with Auto-sync new fields, or keep the schema fixed with Block new fields.
• Field-level data protection: Hash or replicate sensitive fields as is before the data reaches your destination.
• Configurable sync frequency: Schedule syncs on a time-based interval or with a cron expression. The minimum supported interval is 15 minutes.

Prerequisites ​

Connecting Oracle E-Business Suite as a data pipeline source requires:

• An Oracle EBS database reachable from your on-prem agent host
• An on-prem agent running version 31.0 or later. Refer to the on-prem agent documentation for setup steps.
• A database user with SELECT access to the EBS tables you plan to sync. Refer to Set up a database user for the required grants.
• The database host, port, service name, username, and password for your EBS database

Set up a database user ​

The database user for the pipeline must have SELECT on each table you plan to sync. Your database administrator can use an existing user or create a new user. Replace WORKATO in the following examples with the user's name:

Create the user and grant login access:

CREATE USER WORKATO IDENTIFIED BY <password>;
GRANT CONNECT TO WORKATO;

Grant SELECT on each table you plan to add as an object in your pipeline:

GRANT SELECT ON <schema>.<table_name> TO WORKATO;

The pipeline fails with the error Table '<SCHEMA>.<TABLE_NAME>' is not available. It may have been deleted, or your credentials lack permission to access it if the user doesn't have SELECT on a table you add.

Grant SELECT on the following EBS metadata tables in the APPLSYS schema:

GRANT SELECT ON APPLSYS.FND_TABLES TO WORKATO;
GRANT SELECT ON APPLSYS.FND_PRIMARY_KEYS TO WORKATO;
GRANT SELECT ON APPLSYS.FND_PRIMARY_KEY_COLUMNS TO WORKATO;
GRANT SELECT ON APPLSYS.FND_COLUMNS TO WORKATO;

Workato reads these tables to detect the primary key for tables that don't have a primary key, unique constraint, or unique index in the database. Tables without a database key fail to load with an Error running query on agent error if the user can't read these tables. Refer to Primary keys for more information.

Supported connection types ​

Oracle E-Business Suite pipelines support username and password authentication through an on-prem group.

Data pipelines use the Oracle E-Business Suite (Data Pipelines) connector, which reads directly from the EBS database. Connections you create with the Oracle E-Business Suite connector for recipes use Integrated SOA Gateway REST services and aren't available as a pipeline source. You must create a separate connection for your pipeline.

Connect to Oracle E-Business Suite ​

Complete the following steps to connect Oracle E-Business Suite as a data pipeline source:

Configure the pipeline ​

Complete the following steps to configure Oracle E-Business Suite as your data pipeline source:

[minute] [hour] [day of month] [month] [day of week]

0 8 * * *

Supported objects ​

Oracle E-Business Suite data pipelines sync data from tables in the connected EBS database. Workato lists every table the database user can access, across all schemas, when you click Add object. Each table appears as <SCHEMA>.<TABLE_NAME> and syncs as a separate table in your destination.

Only tables are listed. Views and synonyms aren't available as objects.

Primary keys ​

Workato identifies the primary key for each table in the following order:

• Database constraints: The table's enabled primary key or unique constraint, or a unique index.
• EBS metadata: The primary key registered for the table in the EBS FND_PRIMARY_KEYS metadata table. Workato can read this metadata only if the database user has access to the APPLSYS tables listed in Set up a database user.
• ROWID: Workato adds a ROWID column as the primary key when neither source explicitly defines a primary key. Refer to Tables keyed by ROWID for how this affects syncs.

Sync modes ​

Oracle E-Business Suite data pipelines support full sync and incremental sync. The sync mode is configured per table when you add it to your pipeline.

Full sync ​

A full sync reads the source table on each pipeline run. Use full sync for tables without a change tracking column. Full syncs of large EBS tables can take longer and use more resources than incremental syncs.

The rows a full sync reads depend on the pipeline start date in the When first started, this pipeline should pick up records from field:

• Tables with a change tracking column: Full syncs read only rows with a change tracking value on or after the start date. Rows with an earlier or null change tracking value don't sync, and aren't marked as deleted by delete tracking. Full syncs read every row if you leave the start date blank.
• Tables without a change tracking column: Full syncs read every row, regardless of the start date.

Incremental sync ​

An incremental sync reads only rows whose change tracking column changed since the previous run. The first run of an incremental table is a full sync. Workato checks for the following columns in this order and selects the first one that exists in the table as the default change tracking column:

• LAST_UPDATE_DATE
• UPDATED
• LAST_MODIFIED_DATE

You can select a different column in the Change tracking column field of the table's sync settings. Incremental syncs fail for tables that don't have one of the default columns and have no column selected. Select a change tracking column for these tables or switch them to full sync. Refer to Configure the pipeline for the sync settings step.

Incremental sync detects a changed row only when the change tracking column changes. Incremental syncs don't pick up rows updated without a new change tracking value, or rows with a null change tracking value.

Delete tracking ​

Delete tracking depends on the sync mode:

• Full sync: Workato marks records deleted at the source as deleted in the destination on the next full sync.
• Incremental sync: Deletes aren't detected, because each run reads only rows with a new change tracking value.

Schema and data type handling ​

Workato maps Oracle column types to destination types as follows:

Numeric columns sync as strings instead of decimals when the column doesn't have declared precision, a negative scale, a precision greater than 38, or a scale greater than the declared precision. Oracle DATE columns include a time of day that sync as timestamps.

Limitations ​

The following limitations apply when you use Oracle E-Business Suite as a data pipeline source:

Tables keyed by ROWID ​

Workato uses ROWID as the primary key for tables that don't have a primary key in the database or in EBS metadata. These tables show a ROWID field in the schema when you add them to your pipeline. A ROWID reflects where Oracle physically stores the row, so it can change when the row moves on disk, even if the row's contents don't change. Refer to the Oracle ROWID pseudocolumn documentation for more information.

A full sync can miss a row if the row's ROWID changes during the sync. The next run picks up the row if the table uses full sync, because each full sync reads the table again. Incremental syncs don't pick up the missed row unless the change tracking column changes. Run an object re-sync of the table to load the latest data into your destination.

Minimum sync frequency ​

The minimum supported sync interval is 15 minutes.