Hello
This is a question regarding date transformation between Informatica and Oracle.
Informatica 9.6.1 HF1
Oracle 11.2 g
Hello
We are getting duplicates when we insert the same date as TIMESTAMP date from SQL+ / Toad and what Informatica inserts.
The date is the same to the naked eye, but Oracle treats it as two different dates
Our date columns in the database are TIMESTAMP(9)
Here is example)
Row 1: 2017/06/29 00:00:00,000000000
Row 2: 2017/06/29 00:00:00,000000000
To the naked eye - complete the same, but if you put a primary key on this, you will not get PK violation.
If we now print these values to hex
RAWTOHEX
Row1: 7875061D01010100000000
Row2: 7875061D010101
The difference is that Row 1 came from Informatica.
Row 2 came from INSERT statement from my own oracle client.
I have tried to reproduce the extra 0's at the end from my oracle client, I tried all possibilities, it cant be done.
TO_DATE / TO_TIMESTAMP / TO_TIMESTAMP_TZ etc etc...
So what it is that integration is doing to get these extra 0's ? and how I can reproduce it from my oracle client.
Root question: How can I reproduce the date transformation Informatica is sending to Oracle and get the extra 0s at the end?
Here the 3 rows from top are inserted via my Oracle client (SQL+, Toad)
The last row came from Informatica. (notice the difference in the RAWTOHEX conversion)
A PK on column TZ succeeds!
Thanks in advance / Arman