Testing and Transforming Data

Data testing and transformation can be performed by either Extract or Replicat and is implemented by using the Oracle GoldenGate built-in column-conversion functions within a COLMAP clause of a TABLE or MAP statement. With these conversion functions, you can:

Transform dates.
Test for the presence of column values.
Perform arithmetic operations.
Manipulate numbers and character strings.
Handle null, invalid, and missing data.
Perform tests.

This chapter provides an overview of some of the Oracle GoldenGate functions related to data manipulation. For the complete reference, see Reference for Oracle GoldenGate for Windows and UNIX.

If you need to use logic beyond that which is supplied by the Oracle GoldenGate functions, you can call your own functions by implementing Oracle GoldenGate user exits. See Using User Exits to Extend Oracle GoldenGate Capabilities for more information about user exits.

Oracle GoldenGate conversion functions take the following general syntax:

Syntax

@function (argument)

Table 8-6 Conversion Function Syntax

Syntax element	Description
@`function`	The Oracle GoldenGate function name. Function names have the prefix `@`, as in `@COMPUTE` or `@DATE`. A space between the function name and the open-parenthesis before the input argument is optional.
`argument`	A function argument.

Table 8-7 Function Arguments

Argument element	Example
A numeric constant	`123`
A string literal enclosed within single quote marks	'ABCD'
The name of a source column	`PHONE_NO or phone_no, or "Phone_No"` or Phone_no Depends on whether the database is case-insensitive, is case-sensitive and requires quote marks to enforce the case, or is case-sensitive and does not require quotes.
An arithmetic expression	`COL2 * 100`
A comparison expression	`((COL3 > 100) AND (COL4 > 0))`
Other Oracle GoldenGate functions	`AMOUNT = @IF (@COLTEST (AMT, MISSING, INVALID), 0, AMT)`