Oracle GoldenGate Cannot Extract from Raw Data with function @getval (Doc ID 1334096.1)

Last updated on JANUARY 13, 2017

Applies to:

Oracle GoldenGate - Version 5.0.0 and later
Information in this document applies to any platform.

Symptoms

Data extracted from a RAW column with @getval is either NULL or nonsense.

The same extraction from a VARCHAR column is successful.
MAP GGUSER.T7276_RECUR_FU, TARGET gguser.t7276_recur_fu, SQLEXEC (ID lookup, QUERY " select utl_raw.CAST_TO_VARCHAR2(u
tl_raw.substr(:r1, 5, 6)) rfuorder from dual ", PARAMS (r1 = r7276_data_r1)), COLMAP (USEDEFAULTS, rfu_order_no=@getval(
lookup.rfuorder)); Fails

This works:
Table In Source DB:

SQL> desc rfu_test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TYPE_REC                                           VARCHAR2(2 CHAR)
R7276_DATA                                         VARCHAR2(10 CHAR)

Table In Target DB:

SQL> desc rfu_test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TYPE_REC                                           VARCHAR2(2)
R7276_DATA                                         VARCHAR2(10)
RFU_ORDER                                          VARCHAR2(6)

Replicat Code:

MAP gguser.rfu_test, TARGET gguser.rfu_test, &
SQLEXEC (ID lookup, &
QUERY " select substr(:r1, 5, 6) rfuorder from dual ", &
PARAMS (r1 = r7276_data)), &
COLMAP (USEDEFAULTS, rfu_order=@getval(lookup.rfuorder));

Test it by inserting a record into the source where the values are :   '01','ABCDEFGHIJ'

Here are the working results in the Target:

SQL> select * from rfu_test;

TY    R7276_DATA    RFU_OR
--     ----------            ------
01   ABCDEFGHIJ     EFGHIJ

Changes

Use of a RAW column instead of a CHAR of VARCHAR

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms