Goldengate Converting Timestamp(0) With Timezone Incorrectly. (Doc ID 2660784.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

Replicat applying incorrect date for column type TIMESTAMP WITH TIME ZONE

Table Structure....

CREATE TABLE <SCHEMA>.<TABLE>
( "KEY" NUMBER(38,0) NOT NULL ENABLE PRIMARY KEY,
  "COL_TMS_TZ" TIMESTAMP (0) WITH TIME ZONE,                  <===== Issue in replicating
  "TMS" TIMESTAMP);

DML...

insert into <SCHEMA>.<TABLE> (KEY,COL_TMS_TZ,TMS) 

          values(1,to_timestamp_tz ( '01-JAN-1901 01.00.00 AM AMERICA/LOS_ANGELES' , 'DD-MON-YYYY HH.MI.SSXFF AM TZR' ),to_date('01-JAN-1901 01.00.00','DD-MON-YYYY HH.MI.SS'));

from source...
SQL> select KEY,COL_TMS_TZ,TMS from <SCHEMA>.<TABLE>;

KEY  COL_TMS_TZ                                                          TMS
----  -----------------------------------------------------    -------------------------------
  1   01-JAN-01 01.00.00 AM AMERICA/LOS_ANGELES     01-JAN-01 01.00.00.000000 AM

from target...
SQL> select KEY,COL_TMS_TZ,TMS from <SCHEMA>.<TABLE>;

KEY COL_TMS_TZ                                                        TMS
---- -----------------------------------------------------   -------------------------------

  1  06-FEB-37 07.28.16 AM AMERICA/LOS_ANGELES   01-JAN-01   01.00.00.000000 AM <=== PROBLEM with COL_TMS_TZ value

In this case the issue happened with ogg v12.2.x and ogg v18.x

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.