My Oracle Support Banner

Oracle Goldengate Cannot Extract From Raw Data With Function @Getval (Doc ID 1334096.1)

Last updated on APRIL 03, 2019

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 <schema name>.<table name>, TARGET <schema name>.<table name>, SQLEXEC (ID <label>, QUERY " select utl_raw.CAST_TO_VARCHAR2(utl_raw.substr(:r1, 5, 6)) <Column name3> from dual ", PARAMS (r1 = <Column name2>_r1)), COLMAP (USEDEFAULTS, <Column name3>_no=@getval(<label>.<Column name3>)); Fails

This works:

Table In Source DB:

SQL> desc <table name>
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
<Column name1>                                           VARCHAR2(2 CHAR)
<Column name2>                                         VARCHAR2(10 CHAR)

Table In Target DB:

SQL> desc <table name>
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
<Column name1>                                           VARCHAR2(2)
<Column name2>                                         VARCHAR2(10)
<Column name3>                                          VARCHAR2(6)

Replicat Code:

MAP <schema name>.<table name>, TARGET <schema name>.<table name>, &
SQLEXEC (ID <label>, &
QUERY " select substr(:r1, 5, 6) <Column name3> from dual ", &
PARAMS (r1 = <Column name2>)), &
COLMAP (USEDEFAULTS, <Column name3>=@getval(<label>.<Column name3>));

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 <table name>;

<Column name1>    <Column name2>    <Column name3>
--------------------     --------------------    -------------------
01                          ABCDEFGHIJ             EFGHIJ

 

Changes

Use of a RAW column instead of a CHAR of VARCHAR

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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