When loading the data for ODI 10g and using the Oracle 10g driver , For columns with "DATE" data type, only the date is captured and the hours, minutes , second are marked as 00.00.00 or Not shown at all.
In oracle the data will be without hour , minutes and Seconds.
To capture this enable a jdbc property called oracle.jdbc.v8compatible , under the Data Server of the Oracle
Now check for the data of Date columns, hours, minutes and second will be captured by rerunning the Interfaces again.
Issue with enabling this option
There is a known issue related to Enabling this option
When using Timestamp as datatype, Milliseconds are suppressed with zeroes i.e. all the data will be 01-jul-09 03:06:23.000 for correct data (source) 01-jul-09 03:06:23.234.
2. To solve this issue, make a duplicate of the above Data server where you have enabled the v8compatible option to be true. Delete the option or mark it as False in it .
Link your Physical and Logical Scheme to the new (Duplicate) Server and Re Run the interfaces. Look for the Data Now all the Milliseconds will be captured with exact precision
[ Note : There is known issues with ODI 3.4 and 3.5 where the writable Timestamp is entered wrong as ‘ TIMESTAMP( %L ) ‘ , as when ODI create work table it creates as TIMESTAMP (11) which would fail the interface and show an error .
The solution is remove the ( % L ) so the correct writable Data type Syntax is
The other best solution for oracle.jdbc.V8Compatible is to use oracle 11g JDBC drivers.