description: keywords: —

Test and Transform 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:

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.

Oracle GoldenGate conversion functions take the following general syntax:

Syntax

@function (argument)

Table 7 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 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, phone_no, "Phone_No", or Phone_no, depending on whether the database is case-insensitive, case-sensitive and requires quote marks to enforce case, or 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)

Handling Column Names and Literals in Functions

By default, literal strings must be enclosed in single quotes in a column-conversion function. Case-sensitive column names must be enclosed within double quotes if required by the database, or otherwise entered in the case in which they are stored in the database.

Using the Appropriate Function

Use the appropriate function for the type of column that is being manipulated or evaluated. For example, numeric functions can be used only to compare numeric values. To compare character values, use one of the Oracle GoldenGate character-comparison functions. LOB columns cannot be used in conversion functions.

This statement would fail because it uses @IF, which is a numerical function, to compare string values.

@IF (SR_AREA = 'Help Desk', 'TRUE', 'FALSE')

The following statement would succeed because it compares a numeric value.

@IF (SR_AREA = 20, 'TRUE', 'FALSE')

See Manipulating Numbers and Character Strings for more information.

Note: Errors in argument parsing sometimes are not detected until records are processed. Verify syntax before starting processes.

Transforming Dates

Use the @DATE, @DATEDIF, and @DATENOW functions to retrieve dates and times, perform computations on them, and convert them.

This example computes the time that an order is filled

Example 9-9 Computing Time

ORDER_FILLED = @DATE (
    'YYYY-MM-DD HH:MI:SS',
    'JTS',
    @DATE ('JTS',
    'YYMMDDHHMISS',
    ORDER_TAKEN_TIME) +
    ORDER_MINUTES * 60 * 1000000)

Performing Arithmetic Operations

To return the result of an arithmetic expression, use the @COMPUTE function. The value returned from the function is in the form of a string. Arithmetic expressions can be combinations of the following elements.

@COMPUTE ( (COL1 > 0) AND (COL2 < 3) )

Omitting @COMPUTE

The @COMPUTE keyword is not required when an expression is passed as a function argument.

@STRNUM ((AMOUNT1 + AMOUNT2), LEFT)

The following expression returns the same result as the previous one:

@STRNUM ((@COMPUTE (AMOUNT1 + AMOUNT2), LEFT)

Manipulating Numbers and Character Strings

To convert numbers and character strings, Oracle GoldenGate supplies the following functions:

Table 9 Conversion Functions for Numbers and Characters

Purpose Conversion Function
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
Substitute one string for another. @STRSUB
Convert a string to upper case. @STRUP
Trim leading or trailing spaces, or both. @STRLTRIM
@STRRTRIM
@STRTRIM

Handling Null, Invalid, and Missing Data

When column data is missing, invalid, or null, an Oracle GoldenGate conversion function returns a corresponding value.

If BALANCE is 1000, but AMOUNT is NULL, the following expression returns NULL:

NEW_BALANCE = @COMPUTE (BALANCE + AMOUNT)

These exception conditions render the entire calculation invalid. To ensure a successful conversion, use the @COLSTAT, @COLTEST and @IF functions to test for, and override, the exception condition.

Using @COLSTAT

Use the @COLSTAT function to return an indicator to Extract or Replicat that a column is missing, null, or invalid. The indicator can be used as part of a larger manipulation formula that uses additional conversion functions.

The following example returns a NULL into target column ITEM.

ITEM = @COLSTAT (NULL)

The following @IF calculation uses @COLSTAT to return NULL to the target column if PRICE and QUANTITY are less than zero.

ORDER_TOTAL = PRICE * QUANTITY, @IF ((PRICE < 0) AND (QUANTITY < 0), @COLSTAT (NULL))

Using @COLTEST

Use the @COLTEST function to check for the following conditions:

The following example checks whether the AMOUNT column is present and NULL and whether it is present but invalid.

@COLTEST (AMOUNT, NULL, INVALID)

Using @IF

Use the @IF function to return one of two values based on a condition. Use it with the @COLSTAT and @COLTEST functions to begin a conditional argument that tests for one or more exception conditions and then directs processing based on the results of the test.

NEW_BALANCE = @IF (@COLTEST (BALANCE, NULL, INVALID) OR
@COLTEST (AMOUNT, NULL, INVALID), @COLSTAT (NULL), BALANCE + AMOUNT)

This conversion returns one of the following:

Performing Tests

The @CASE, @VALONEOF, and @EVAL functions provide additional methods for performing tests on data before manipulating or mapping it.

Using @CASE

Use @CASE to select a value depending on a series of value tests.

@CASE (PRODUCT_CODE, 'CAR', 'A car', 'TRUCK', 'A truck')

This example returns the following:

Using @VALONEOF

Use @VALONEOF to compare a column or string to a list of values.

@IF (@VALONEOF (STATE, 'CA', 'NY'), 'COAST', 'MIDDLE')

In this example, if STATE is CA or NY, the expression returns COAST, which is the response returned by @IF when the value is non-zero (meaning TRUE).

Using @EVAL

Use @EVAL to select a value based on a series of independent conditional tests.

@EVAL (AMOUNT > 10000, 'high amount', AMOUNT > 5000, 'somewhat high')

This example returns the following: