Goldengate - Problems converting bit datatypes from sybase, MS Sql and My-Sql database to Oracle Number data type.

Recently encountered an issue where the client was taking data from MS-SQL Server to Oracle 11g Database. Both source and target environments were running the latest Goldengate Version 12.


Columns with Bit Data Type are not converted and sent to Oracle database. i.e. the corresponding column in Oracle db table is blank.


Goldengate Processes (Extracts and Replicats) don't give any kind of errors and their status is shown  as Running.


Symptoms -


 When Bit data type is used in sqlserver source, and the replicat site maps the BIT to char or number, the bit value will not show in target site.


Cause -


Bit values used to be treated as ASCII prior to 11.1. Now they are treated as binary, so a pre-11.1 trail with a bit value of 0 applied to a target with an 11.1 or later replicat will treat the ASCII value of 0x30 as 1 since it is not  0x00


NOTE - Although Oracle support lists the problem with Goldengate Ver 11. The issue was duplicated in Goldengate Ver 12 also.


Solution -


Apply @NUMTOBIN while converting and writing data on the target column.


e.g


MAP scott.emp,  target ora.emp,
COLMAP (usedefaults, source_bit_column_name = @NUMBIN (target_bit_column_name);