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:
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.
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.