Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Duplicates same dates in table + Informatica

SweAnderlineJul 10 2018 — edited Jul 10 2018

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)

pastedImage_6.png

A PK on column TZ succeeds!

Thanks in advance / Arman

This post has been answered by Solomon Yakobson on Jul 10 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 7 2018
Added on Jul 10 2018
4 comments
346 views