Oracle Troubleshooting Hub


This article contains three sections: general guidance on potential pain points for the Oracle data source, a list of troubleshooting articles by symptom, and a list of Oracle bugs that have been patched out of our software.  We hope that this article will prove to be a one-stop resource for problems related to the Oracle data source, but if you do not see your problem addressed here, please submit a ticket and we will do our best to assist you.

For guidance on the Oracle data export, please see the guide here.


General Guidance

Connecting to an Oracle data source can some times be difficult for two primary reasons: extra software is required to allow SQL Server and Oracle to communicate, and Oracle and SQL Server data types do not always match up.

The Oracle Data Provider

The extra software needed to connect SQL Server and Oracle is called the Oracle Data Provider (ODP), and you can find our guide to installing it here.  The ODP software can present difficulties because a mistake during the installation process can cause the data source not to work correctly, no matter how it is later configured. 

If you are having issue getting your Oracle data source set up for the first time, a problem during the ODP installation process is by far the most probable cause.  Checking to ensure the ODP and your TX DWA software are using the same bit version and reinstalling the ODP will often resolve these first-time setup woes.

Data Type Clashes

The other common problem with the Oracle data source occurs because Oracle data types are often larger than the data types used by TX DWA's default settings, especially text data types and date-time data types. Selecting Force Unicode Conversion and converting dates to in-range values, re-synchronizing, and re-deploying will resolve these problems quickly.


Problems by Symptom:

I can't set up the Oracle data source

I can't synchronize the Oracle data source

I can't synchronize or deploy Oracle data source tables

I can't deploy Oracle data source tables

I can't execute Oracle data source tables


List of Patched Bugs:


  • ZenDesk Ticket Reference Number
  • Description of the problem
  • Version in which the problem was first identified
  • Version in which the problem was patched



Problem: trying to deploy a table with a CLOB datatype field from an Oracle database with AL32UTF8 results in a Unicode conversion error.

Detected: 16.5.1

Fixed: 16.8.4



Problem: manual prefixes overwrite the owner in staging table names


Fixed: 16.1.5/6



Problem: synching a changed Oracle data source gives the error “Failed to compare two elements in an array.”

Detected: 15.12.1

Fixed: 15.12.4 (or earlier)



Problem: Oracle connection gives the error “Column 'TABLE_SCEHMA' does not belong to table Views.”

Detected: 15.10.3

Fixed:  16.2.1



Problem: CLOB and TIMESTAMP fields do not show up during data source synchronization

Detected: 14.5.13

Fixed: 16.2.1



Problem: Querying a table with a field based on Oracle TIMESTAMP gives the error “Object must implement IConvertable.” and the query continues to run in the background.  The TIMESTAMP-based field shows up in the query as “timestamp_*bin*_”.

Detected: 14.5.4

Fixed: 14.5.9



Problem: doing an incremental load of an Oracle table can give the following OLE DB error:


An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E07 Description: "ORA-01722: invalid number".

An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01722: invalid number".

Data Source <DataSource> failed the pre-execute phase and returned error code 0xC0202009.


Detected: 14.2.6

Fixed: 14.5.4



Problem: Setting an Oracle global database to force Unicode conversion gives the following OLE DB error:


An OLE DB record is available.  Source: "Microsoft OLE DB Service Components. " Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors.   Check each OLE DB status value if available. No work was done.".

The AquireConnection method call to the connection manager “Data Source” failed with error code 0xC0202009. 

Component “OLE DB Source” (1) failed validation and returned error code 0xC020801C.


Detected 14.0.3

Fixed: 14.2.1



Problem: Testing an existing Oracle data source turns all associated staging tables red.

Detected: 14.1.11

Fixed: 14.5.0



Problem: More than two incremental selection rules on an Oracle data source won’t work

Detected: 14.06.64

Fixed:  Jul – Aug 2014, probably 12.9.9



Problem: Adding an additional data source for Oracle gives error “ORA-00942: table or view does not exist” even if the databases are identical.

Detected: 14.0.3

Fixed: 14.5.0



Problem: NULL values in Oracle data source incremental fields result in records not being loaded

Detected: 14.0.3

Fixed: 14.0.6+



Problem: Duplicate versions of columns appear in the view of the Oracle data source

Detected: 4.5.37

Fixed: 12.4.8



Problem: When using the "Convert out of range dates to SQL server min/max date" feature on a Oracle data source, the time-part of any Date fields are lost.


Fixed: 4.5.42

Was this article helpful?
0 out of 0 found this helpful


Please sign in to leave a comment.