My Oracle Support Banner

How To Load Data Directly from the Source to the Target Without Creating Any Temporary Table in an ODI 12c Mapping or ODI 11g Integration Interface (Doc ID 762723.1)

Last updated on OCTOBER 23, 2023

Applies to:

Oracle Data Integrator - Version and later
Oracle Data Integrator on Marketplace - Version 1.0.2 and later
Information in this document applies to any platform.


How to force Oracle Data Integrator (ODI) to load data into a target Table / Datastore without creating C$, I$, E$ temporary objects?

Before providing the answer to above question, it is useful to understand why and when ODI is creating temporary tables. The descriptions below apply to standard cases.

C$ Collect tables

A C$_ table is created by an LKM in order to transfer data from a source server to the staging area - which, by default, is on the target server.

This will allow ODI to join data sets coming from heterogeneous source data servers. For example, if as sources you are using an Excel spreadsheet and an Oracle table to load a Teradata database, ODI will retrieve the content of the Oracle table and the Excel file into 2 collect tables, both hosted on the staging area. This will allow ODI to join the data coming from Excel and Oracle.

If the 12c Mapping or 11g Integration Interface contains several source Datastores which are joined on the Source, the C$_ table will contain the result of the join operation. If the join is set to be executed on the staging area, ODI will create two distinct C$_ tables.

Notice that when the source Datastores are on the same server as the target Datastore or same server as the staging area, there is no LKM to set at the Mapping / Integration Interface level.

I$ Integration tables

Most of the time, the I$_ tables are created by the IKMs whenever there is a need for updating a target table or running a flow control.

If this is not required, the creation of the I$_ table is of no use since ODI can retrieve the data directly from the collect tables. The structure of the I$_ table is to have an almost identical structure as the target table in order to contain the data that ODI is about to insert or to use to update the target table.

E$ Error tables

From a 12c Mapping or 11g Integration Interface, E$_ tables are created and populated by the CKM if the IKM option called Flow Control is set to yes.

So, only under the following conditions:

    1. Only one source Datastore, not journalized
    2. Loading strategy = Truncate/Insert or basic Insert (no updates or deletes)
    3. No flow nor static control is required

In a situation where the C$, I$, E$ tables are of no use... or the user lacks privileges on the staging area... it would be beneficial to directly load data into the target table, without creating any of above mentioned temporary objects. 

How to achieve this goal?

The article applies to all releases of ODI like the current 12C as well as to previous releases.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.