12 Mapping and Manipulating Data
12.12 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 Section 17.3, "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)
Note: This is not representative of a complete parameter file for an Oracle GoldenGate process. Also note that these examples represent a case-insensitive database.
Table 12–9 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.
12.12.1 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.
12.12.2 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 Section 12.12.5, "Manipulating Numbers and Character Strings" for more information.
12.12.3 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 12–5 Computing Time ORDER_FILLED = @DATE (
Table 12–10 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)
Note: Errors in argument parsing sometimes are not detected until records are processed. Verify syntax before starting processes.
’YYYY-MM-DD HH:MI:SS’, ’JTS’,
@DATE (’JTS’, ’YYMMDDHHMISS’, ORDER_TAKEN_TIME) +
ORDER_MINUTES * 60 * 1000000)
12.12.4 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.
■ Numbers
■ The names of columns that contain numbers
■ Functions that return numbers
■ Arithmetic operators:
– + (plus) – - (minus) – * (multiply) – / (divide) – \ (remainder)
■ Comparison operators:
– > (greater than)
– >= (greater than or equal) – < (less than)
– <= (less than or equal) – = (equal)
– <> (not equal)
Results that are derived from comparisons can be zero (indicating FALSE) or non-zero (indicating TRUE).
■ Parentheses (for grouping results in the expression)
■ The conjunction operators AND, OR. Oracle GoldenGate only evaluates the necessary part of a conjunction expression. Once a statement is FALSE, the rest of the expression is ignored. This can be valuable when evaluating fields that may be missing or null. For example, if the value of COL1 is 25 and the value of COL2 is 10, then the following are possible:
@COMPUTE ( (COL1 > 0) AND (COL2 < 3) ) returns 0.
@COMPUTE ( (COL1 < 0) AND (COL2 < 3) ) returns 0. COL2 < 3 is never evaluated.
@COMPUTE ((COL1 + COL2)/5) returns 7.
12.12.4.1 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)
12.12.5 Manipulating Numbers and Character Strings
To convert numbers and character strings, Oracle GoldenGate supplies the following functions:
12.12.6 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.
12.12.6.1 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)
Table 12–11 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
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))
12.12.6.2 Using @COLTEST
Use the @COLTEST function to check for the following conditions:
■ PRESENT tests whether a column is present and not null.
■ NULL tests whether a column is present and null.
■ MISSING tests whether a column is not present.
■ INVALID tests whether a column is present but contains invalid data.
The following example checks whether the AMOUNT column is present and NULL and whether it is present but invalid.
@COLTEST (AMOUNT, NULL, INVALID)
12.12.6.3 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:
■ NULL when BALANCE or AMOUNT is NULL or INVALID
■ MISSING when either column is missing
■ The sum of the columns.
12.12.7 Performing Tests
The @CASE, @VALONEOF, and @EVAL functions provide additional methods for performing tests on data before manipulating or mapping it.
12.12.7.1 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:
■ A car if PRODUCT_CODE is CAR
■ A truck if PRODUCT_CODE is TRUCK
■ A FIELD_MISSING indication if PRODUCT_CODE fits neither of the other conditions
12.12.7.2 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).
12.12.7.3 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:
■ high amount if AMOUNT is greater than 10000
■ somewhat high if AMOUNT is greater than 5000, and less than or equal to 10000, (unless the prior condition was satisfied)
■ A FIELD_MISSING indication if neither condition is satisfied.