Losing timestamp in ODI when using ojdbc14.jar

Uli Bethke Oracle Data Integrator (ODI)

The ojdbc14.jar version of the JDBC driver is losing timestamp information when returning DATE datatypes from Oracle. As per Oracle JDBC driver FAQ this is a known issue. As a workaround it is suggested to set the oracle.jdbc.V8Compatible JDBC parameter to true. You can set this parameter in ODI when defining a Topology:

odi_jdbc

However, a better solution for this is to use a 1.5 JDK in combination with the 11.1 ojdbc5.jar driver. This is supported for both Oracle 10G and 11G. Refer to note 807235.1 on Metalink for  information which JDBC and JVM/JDK versions work together.

In a first step stop all ODI agents and download the 1.5 JDK from the SUN website. Next install and point the ODI_JAVA_HOME environment variable to point to the JDK, e.g. E:Program Filessunjdkjdk.

Next go to the ODI drivers folder and rename the ojdbc14.jar to ojdbc14.jar.bak. We now need to download the 11.1 driver from the Oracle website. Once downloaded put the ojdbc5.jar into the ODI drivers folder.

We are now ready to test this. Log into the ODI Designer module. Expand the Projects folder and right click Variables and select Insert Variable. Give the variable a name and change to the Refreshing tab. From the Schema dropdown select an Oracle Schema. In the Select Query textbox type the following:

SELECT sysdate FROM dual

Click the Refresh button and change to the History tab. Next click the Refresh icon and you should get timestamp information.

odi_jdbc_timestamp

One more thing: You will need to re-install your agents as windows services.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns

Jython

The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O'Reilly Scripting)

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.