Follow

Oracle Troubleshooting Hub

Introduction

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:

Format:

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

 

#8816

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

 

#7769

Problem: manual prefixes overwrite the owner in staging table names

Detected: 15.12.1.64

Fixed: 16.1.5/6

 

#7700

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)

 

#7312

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

Detected: 15.10.3

Fixed:  16.2.1

 

#7008 

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

Detected: 14.5.13

Fixed: 16.2.1

 

#6018:

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

 

#5810

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

 

#5427

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

 

#4977

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

Detected: 14.1.11

Fixed: 14.5.0

 

#4870

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

 

#4771

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

 

#4204

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

Detected: 14.0.3

Fixed: 14.0.6+

 

#957

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

Detected: 4.5.37

Fixed: 12.4.8

 

#UNK

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.

Detected:

Fixed: 4.5.42

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.