Best Practices For Oracle JDBC Drivers To Be Used With ODI (Doc ID 423733.1)

Last updated on JULY 03, 2018

Applies to:

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

Purpose

The purpose of this document is to:

Help you in the identification and resolution of problems that are encountered when using Oracle JDBC Drivers with Oracle Data Integrator (ODI).
Provide a response to the question "which is faster between the THIN and OCI drivers?".

Scope

Oracle recommends the use of the most recent version of its JDBC Driver.

However, problems may be signaled during their use with Oracle Data Integrator (ODI). These problems are due to an inappropriate version of JDBC Driver.

Case 1. The Network Adapter could not establish the connection

When opening an Integration Interface, the following message is displayed:

17002 : null : java.sql.SQLException: IO exception: The Network Adapter could not establish the connection

Note that the ODI Work Repository is stored in an Oracle instance.

Case 2. ORA-00600

When reverse engineering an Oracle Model, the following message is displayed:

ORA-00600 : internal error code, arguments: [[ttcgcshnd-1],[0],[],[],[]

Case 3. No standard reverse engineering

It is impossible to execute a standard reverse engineering operation on an Oracle Model.

Case 4. NULL values are not allowed

Cannot insert NULL data.

Column is nullable, but a bug in Oracle JDBC Driver 9.2.0.x generates following error message:

java.lang.NullPointerException
at oracle.jdbc.dbaccess.DBData.clearItem(DBData.java:431)
at oracle.jdbc.dbaccess.DBDataSetImpl.clearItem(DBDataSetImpl.java:3528)
at oracle.jdbc.driver.OraclePreparedStatement.checkBindTypes

Case 5. Date fields lose precision

When transiting through the Oracle JDBC Driver 9.2.0.x or 10.1.0.x, Date fields lose precision: hours, minutes and seconds are lost.

Case 6a. Issues related to TIMESTAMP data type

The following error occurs when running an Integration Interface that inserts into a timestamp column from a timestamp column or when doing a to_timestamp() transformation:

com.sunopsis.sql.a: Parse Exception : value:oracle.sql.TIMESTAMP@9d4e7f pattern:yyyy-MM-dd hh:mm:ss.SSSSSSS at byte:01

Case 6b. Issues related to TIMESTAMP data type (Microsoft SQLServer timestamp to Oracle timestamp)

The following error occurs when running an Integration Interface that inserts into an Oracle timestamp column from a Microsoft SQLServer timestamp column:
com.sunopsis.sql.SnpsQueryException: Parse Exception : value:[B@115e01c2 pattern:yyyy-MM-dd at byte:01

Case 7. Timestamp fields lose precision

When using one of versions 9.2.0.x and 10.x of the Oracle JDBC Driver and "oracle.jdbc.V8Compatible=true" Java property is set, Timestamp fields loose "miliseconds" precision. More precisely:

If JDBC "oracle.jdbc.V8Compatible=true" property is used:

DATE fields are correct, but

TIMESTAMP fields loose milisecond precision (as they are managed as ordinary DATE fields...)

If JDBC "oracle.jdbc.V8Compatible=true" property is not used:

DATE fields loose hour, minute and seconds precision (refer to Case 5 above), but

TIMESTAMP fields are correct

Case 8. Missing IN or OUT parameter at index

When starting ODI Journalization on Oracle 10g Database, the following message is displayed at the "Create journalizing trigger" step:

java.sql.SQLException: Parameter IN or OUT missing in the index:: 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)

Case 9. IO exception with Oracle 10.2.0.3 Database

When attempting to execute ODI Objects which address an Oracle 10.2.0.3 Database, the following error is signaled:

java.sql.SQLException: Io exception: Got minus one from a read call
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)

Case 10. Can not access a member of class oracle.jdbc.driver.OraclePreparedStatementWrapper with modifiers "public"

If Oracle Data Integrator (ODI) uses Jython-based code and version 11g of Oracle JDBC Driver, following message may be observed:

java.lang.IllegalAccessException: java.lang.IllegalAccessException: Class org.python.core.PyReflectedFunction can not access a member of class oracle.jdbc.driver.OraclePreparedStatementWrapper with modifiers "public"

Case 11. java.lang.IllegalArgumentException when loading a Date value of 01/01/1900

The following message is shown when and ODI Integration Interface loads a Date values of 01/01/1900 from Oracle 9.2.0.8 to Oracle 10.2.0.4 using version 10.1.0.5 of the Oracle JDBC driver:

java.lang.IllegalArgumentException
at sun.util.calendar.ZoneInfo.getOffset(ZoneInfo.java:346)
at oracle.jdbc.driver.DateTimeCommonAccessor.zoneOffset(DateTimeCommonAccessor.java:375)

No error is thrown when using the same JDBC driver to load this Date from Oracle 10g to Oracle10g.

Case 12. Wrong reverse engineering of the length of CHAR and VARCHAR2 datatypes

The Oracle JDBC driver reverse engineers CHAR and VARCHAR2 data types with a length four times larger than the expected one. For example, a VARCHAR2 (10) is reversed engineered as VARCHAR2(40), which is not correct.

This bug is responsible or a wrong reverse of the length of CHAR and VARCHAR datatypes