Best Practices for Oracle JDBC Drivers to be Used with ODI Including Resolutions for Issues Encountered Due to an Inappropriate Version of JDBC Driver
(Doc ID 423733.1)
Last updated on JANUARY 15, 2020
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:
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
Details
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!