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.
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
- Testing the connection to a new Oracle data source causes the Oracle data source window to close and an error message about AddEditDatasourceOracleCommand to appear.
- Testing the connection to a new Oracle data source results in an error message about OracleConnectionStringBuilder.
- Testing the connection to a new Oracle data source connection results in an error message about Oracle.DataAccess.
- Testing an Oracle global database connection results in a logon error message.
I can't synchronize the Oracle data source
- Reading objects from an Oracle data source takes a very long time, or returns an incomplete or empty list of tables.
- Reading objects from the Oracle Data Source returns no values, and the specified owner name has the correct permissions.
- Reading objects from an Oracle data source results in an error message about Oracle drivers, but Test Connection to the Oracle data source works.
I can't synchronize or deploy Oracle data source tables
- Reading objects from an Oracle data source results in an SSIS error message, but Test Connection to the Oracle data source works.
I can't deploy Oracle data source tables
- Deploying an Oracle table results in an error message about Unicode conversion.
- Deploying an Oracle table results an error message about the DefaultCodePage property.
- After changing the tnsnames file, deploying an oracle table results in a PRIMEOUTPUTFAILED error.
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.
Problem: manual prefixes overwrite the owner in staging table names
Problem: synching a changed Oracle data source gives the error “Failed to compare two elements in an array.”
Fixed: 15.12.4 (or earlier)
Problem: ADO.net Oracle connection gives the error “Column 'TABLE_SCEHMA' does not belong to table Views.”
Problem: CLOB and TIMESTAMP fields do not show up during data source synchronization
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*_”.
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.
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.
Problem: Testing an existing Oracle data source turns all associated staging tables red.
Problem: More than two incremental selection rules on an Oracle data source won’t work
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.
Problem: NULL values in Oracle data source incremental fields result in records not being loaded
Problem: Duplicate versions of columns appear in the view of the Oracle data source
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.