My Oracle Support Banner

Master Note for Oracle GoldenGate for Filtering and Transformation Data (Doc ID 1450495.1)

Last updated on MARCH 12, 2021

Applies to:

Middleware > Data Integration > GoldenGate
Information in this document applies to any platform.

Purpose

This note provides background and troubleshooting information applicable to Oracle GoldenGate data transformation. Its main purpose as a master note is to bring together reference material in a form which allows a clear understanding of what is required in the configuration as well as to where to look when there are issues. Its focus is setup and understanding relevant parameters setting which are required to integrate data between source and target tables to obtain the format required by target tables and applications. It will include: differences in table names and column names, differences in table definitions, data transformation and data differences. The samples are based on Oracle Server database but apply to all databases where Oracle GoldenGate is certified. 

Scope

This document is intended for anyone with an interest in understanding data and column transformation and mapping. Where samples are provided for reference to DBAs and Application developers. It is likely these will require modification in view of the fact that the examples presented are for illustration purposes. In order to have a better insight into how the examples work, please ensure you have read and understood the information below.

Whilst every care has been taken to ensure that the details are correct and accurate, code should be throroughly tested to verify it is fit for intended purpose.

Please note, the approach adopted in relation to knowledge has been to improve Oracle's product documentation. Thus, relevant content in relation to data transformation is now found in the primary references guides for Oracle GoldenGate; if possible, please refer to the lastest documentation documentation library specific to your platform and database, eg:

Oracle® GoldenGate Windows and UNIX Administrator’s Guide

Oracle® GoldenGateOracle Installation and Setup Guide

Oracle® GoldenGate Windows and UNIX Reference Guide

Details

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
Purpose
Scope
Details
 Mapping and Transforming Columns
 Mapping between dissimilar databases
 Colmap
 Colmatch
 Defgen Utility
 Filtering Columns
 Filtering Partitions in Extract
 Filtering DML Statements
 Filtering Users
 Filtering DDL
 Filtering Rows
 Where Clause
 Filter Clause
 @Range
 @Case, @Eval,@ If
 Filtering with macros
 Data Transformation
 Column Conversion Functions
 Retrieve dates and times, perform computations on them, and convert them.: @DATE,  @DATEDIFF, and  @DATENOW
 Convert a binary or character string to a number. @NUMBIN ,@NUMSTR
 Convert a number to a string.  @STRNUM
 Compare strings:  @STRCMP, @STRNCMP 
 Concatenate strings: @STRCAT, @STRNCAT
 Extract from  a string:  @STREXT, @STRFIND
 Return the length of a string:  @STRLEN
 Case chasing: @STRUP.
 Another samples
 DML Conversion Parameters
 User Tokens
 @GETENV
 User Exits
 SQLEXEC
 Auditing With Goldengate
 How to transform a single source record in multiple target records
 11.2 Enhancements
 Column Conversion Functions
 Character-Set conversion
 Transformations in Streams  vs Transformations in Golden Gate:
 Transform Schema Name
 Renaming  a column
 Filtering rows and columns
 Filtering  Specific DML Operations on the Source
References

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