Mapping and Manipulating Data

Parameters that Control Mapping and Data Integration

All data selection, mapping, and manipulation that Oracle GoldenGate performs is accomplished by using one or more options of the TABLE and MAP parameters.

Use TABLE in the Extract parameter file.
Use MAP in the Replicat parameter file.

TABLE and MAP specify the database objects that are affected by the other parameters in the parameter file. See Specifying Object Names in Oracle GoldenGate Input for instructions for specifying object names in these parameters.

Mapping between Dissimilar Databases

Mapping and conversion between tables that have different data structures requires either a source-definitions file, a target-definitions file, or in some cases both. Mapping between dissimilar databases is controlled by the self-describing trails, and mapping is done by column name, regardless of the data type for the source or target column.

If you don't want automatic mapping based on the self-describing trails or want backward compatibility then you can use SOURCEDEFS or TARGETDEFS.

Mapping and Conversion on NonStop Systems

If you are mapping or converting data from a Windows or UNIX system to a NonStop Enscribe target, the mapping or conversion must be performed on the Windows or UNIX source system. Replicat for NonStop cannot convert three-part or two-part SQL table names and data types to the three-part file names that are used for the Enscribe platform. Extract can format the trail data with Enscribe names and target data types.

Mapping and Conversion on Windows and UNIX Systems

When Oracle GoldenGate is operating only on Windows-based and UNIX-based systems, column mapping and conversion can be performed in the Extract process, or in the Replicat process. To prevent the added overhead of this processing on the Extract process, you can configure the mapping and conversion to be performed on the Replicat process or on an intermediary system.

In the case where there are multiple sources and one target, it might be more efficient to perform the mapping and conversion on the source.

Globalization Considerations when Mapping Data

When planning to map and convert data between databases and platforms, take into consideration what is supported or not supported by Oracle GoldenGate in terms of globalization.

Conversion between Character Sets

Oracle GoldenGate converts between source and target character sets if they are different, so that object names and column data are compared, mapped, and manipulated properly from one database to another. See Supported Character Sets, for a list of supported character sets.

To ensure accurate character representation from one database to another, the following must be true:

The character set of the target database must be a superset or equivalent of the character set of the source database. Equivalent means not equal, but having the same set of characters. For example, Shift-JIS and EUC-JP technically are not completely equal, but have the same characters in most cases.
If your client applications use different character sets, the database character set must also be a superset or equivalent of the character sets of the client applications.
In many databases, including Oracle, it is possible to force a character into a database that is not part of the Character Set. Oracle GoldenGate considers this as an invalid value, and may not map this character correctly when replicating data. For these types of situations you can use the REPLACEBADCHAR parameter as described in the Parameters and Functions Reference for Oracle GoldenGate.

In this configuration, every character is represented when converting from a client or source character set to the local database character set.

A Replicat process can support conversion from one source character set to one target character set.

Database Object Names

Oracle GoldenGate processes catalog, schema, table and column names in their native language as determined by the character set encoding of the source and target databases. This support preserves single-byte and multibyte names, symbols, accent characters, and case-sensitivity with locale taken into account where available, at all levels of the database hierarchy.

Column Data

Oracle GoldenGate supports the conversion of column data between character sets when the data is contained in the following column types:

Character-type columns: CHAR/VARCHAR/CLOB to CHAR/VARCHAR/CLOB of another character set; and CHAR/VARCHAR/CLOB to and from NCHAR/NVARCHAR/NCLOB.
Columns that contain string-based numbers and date-time data. Conversions of these columns is performed between z/OS EBCDIC and non-z/OS ASCII data. Conversion is not performed between ASCII and ASCII versions of this data, nor between EBCDIC and EBCDIC versions, because the data are compatible in these cases.

Note:

Oracle GoldenGate supports timestamp data from 0001-01-03 00:00:00 to 9999-12-31 23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. A value of zero month, zero day field, or an all zero date value isn't supported. For example, values such as 0000-00-00 00:00:00, or any date value that includes a zero month or zero day field isn't supported.

Character-set conversion for column data is limited to a direct mapping of a source column and a target column in the COLMAP or USEDEFAULTS clauses of the Replicat MAP parameter. A direct mapping is a name-to-name mapping without the use of a stored procedure or column-conversion function. Replicat performs the character-set conversion. No conversion is performed by Extract.

Preservation of Locale

Oracle GoldenGate takes the locale of the database into account when comparing case-insensitive object names. See Supported Locales for a list of supported locales.

Support for Escape Sequences

Oracle GoldenGate supports the use of an escape sequence to represent a string column, literal text, or object name in the parameter file. You can use an escape sequence if the operating system does not support the required character, such as a control character, or for any other purpose that requires a character that cannot be used in a parameter file.

An escape sequence can be used anywhere in the parameter file, but is particularly useful in the following elements within a TABLE or MAP statement:

An object name
WHERE clause
COLMAP clause to assign a Unicode character to a Unicode column, or to assign a native-encoded character to a column.
Oracle GoldenGate column conversion functions within a COLMAP clause.

Oracle GoldenGate supports the following types of escape sequence:

\uFFFF Unicode escape sequence. Any UNICODE code point can be used except surrogate pairs.
\377 Octal escape sequence
\xFF Hexadecimal escape sequence

The following rules apply:

If used for mapping of an object name in TABLE or MAP, no restriction apply. For example, the following TABLE specification is valid:
```
TABLE schema."\u3000ABC";
```
If used with a column-mapping function, any code point can be used, but only for an NCHAR/NVARCHAR column. For an CHAR/VARCHAR column, the code point is limited to the equivalent of 7-bit ASCII.
The source and target data types must be identical (for example, NCHAR to NCHAR ).
Begin each escape sequence with a reverse solidus (code point U+005C), followed by the character code point. (A solidus is more commonly known as the backslash symbol.) Use the escape sequence, instead of the actual character, within your input string in the parameter statement or column-conversion function.

Note:

To specify an actual backslash in the parameter file, specify a double backslash. For example, the following finds a backslash in COL1: @STRFIND (COL1, '\\' ).

To Use the \uFFFF Unicode Escape Sequence

The \uFFFF Unicode escape sequence must begin with a lowercase u, followed by exactly four hexadecimal digits.
Supported ranges are as follows:
- 0 to 9 (U+0030 to U+0039)
- A to F (U+0041 to U+0046)
- a to f (U+0061 to U+0066)

\u20ac is the Unicode escape sequence for the Euro currency sign.

Note:

For reliable cross-platform support, use the Unicode escape sequence. Octal and hexadecimal escape sequences are not standardized on different operating systems.

To Use the \377 Octal Escape Sequence

Must contain exactly three octal digits.
Supported ranges:
- Range for first digit is 0 to 3 (U+0030 to U+0033)
- Range for second and third digits is 0 to 7 (U+0030 to U+0037)
  
  \200 is the octal escape sequence for the Euro currency sign on Microsoft Windows

To Use the \xFF Hexadecimal Escape Eequence

Must begin with a lowercase x followed by exactly two hexadecimal digits.
Supported ranges:
- 0 to 9 (U+0030 to U+0039)
- A to F (U+0041 to U+0046)
- a to f (U+0061 to U+0066)

\x80 is the hexadecimal escape sequence for the Euro currency sign on Microsoft Windows 1252 Latin1 code page.

Mapping Columns Using TABLE and MAP

Oracle GoldenGate provides for column mapping at the table level and at the global level. Default column mapping is also provided in the absence of explicit column mapping rules.

This section contains the following guidelines for mapping columns:

Supporting Case and Special Characters in Column Names

By default, Oracle GoldenGate follows SQL-92 rules for specifying column names and literals. In Oracle GoldenGate parameter files, conversion functions, user exits, and commands, case-sensitive column names must be enclosed within double quotes if double quotes are required by the database to enforce case-sensitivity. For other case-sensitive databases that do not require quotes, case-sensitive column names must be specified as they are stored in the database. Literals must be enclosed within single quotes. See Differentiating Case-Sensitive Column Names from Literals for more information.

Configuring Table-level Column Mapping with COLMAP

If you are using self-describing trails then any column on the source object is mapped to the same column name on the target object. You only need to manage column names that are different between source and target or if you need to transform a column.

However, if not using self-describing trails then the default mapping is done by column order and not the column name. So column 1 on the source will be mapped to column 1 on the target, column 2 to column 2 and so on.

Use the COLMAP option of the MAP and TABLE parameters to:

map individual source columns to target columns that have different names.
specify default column mapping when an explicit column mapping is not needed.
Provide instructions for selecting, mapping, translating, and moving data from a source column into a target column.

Using USEDEFAULTS to Enable Default Column Mapping

You can use the USEDEFAULTS option of COLMAP to specify automatic default column mapping for any corresponding source and target columns that have identical names. USEDEFAULTS can save you time by eliminating the need to map every target column explicitly.

Default mapping causes Oracle GoldenGate to map those columns and, if required, translate the data types based on the data-definitions file. Do not specify default mapping for columns that are mapped already with an explicit mapping statement.

The following example of a column mapping illustrates the use of both default and explicit column mapping for a source table ACCTBL and a target table ACCTTAB. Most columns are the same in both tables, except for the following differences:

The source table has a CUST_NAME column, whereas the target table has a NAME column.
A ten-digit PHONE_NO column in the source table corresponds to separate AREA_CODE, PHONE_PREFIX, and PHONE_NUMBER columns in the target table.
Separate YY, MM, and DD columns in the source table correspond to a single TRANSACTION_DATE column in the target table.

To address those differences, USEDEFAULTS is used to map the similar columns automatically, while explicit mapping and conversion functions are used for dissimilar columns.

The following sample shows the column mapping using the COLMAP option of the MAP and TABLE parameters. It describes the mapping of the source table ACCTBL to the target table ACCTTAB.

MAP SALES.ACCTBL, TARGET SALES.ACCTTAB,
                COLMAP (   USEDEFAULTS,
                           NAME = CUST_NAME,
                           TRANSACTION_DATE = @DATE ('YYYY-MM-DD', 'YY',YEAR, 'MM', MONTH, 'DD', DAY),
                           AREA_CODE = @STREXT (PHONE_NO, 1, 3),
                           PHONE_PREFIX = @STREXT (PHONE_NO, 4, 6),
                           PHONE_NUMBER = @STREXT (PHONE_NO, 7, 10)
)
;

Table 9-3 Sample Column Mapping

Parameter statement	Description
`COLMAP`	Begins the `COLMAP` statement.
`USEDEFAULTS,`	Maps source columns as-is when the target column names are identical.
`NAME = CUST_NAME,`	Maps the source column `CUST_NAME` to the target column `NAME`.
`TRANSACTION_DATE = @DATE ('YYYY-MM-DD', 'YY', YEAR, 'MM', MONTH, 'DD', DAY),`	Converts the transaction date from the source date columns to the target column `TRANSACTION_DATE` by using the `@DATE` column conversion function.
`AREA_CODE = @STREXT (PHONE_NO, 1, 3), PHONE_PREFIX = @STREXT (PHONE_NO, 4, 6), PHONE_NUMBER = @STREXT (PHONE_NO, 7, 10)) ;`	Converts the source column `PHONE_NO` into the separate target columns of `AREA_CODE`, `PHONE_PREFIX`, and `PHONE_NUMBER` by using the `@STREXT` column conversion function.

See Understanding Default Column Mapping for more information about the rules followed by Oracle GoldenGate for default column mapping.

Specifying the Columns to be Mapped in the COLMAP Clause

The COLMAP syntax is the following:

COLMAP ([USEDEFAULTS, ] target_column = source_expression)

In this syntax, target_column is the name of the target column and source_expression. Some examples of source_expressions are:

The name of a source column, such as ORD_DATE.
Numeric constant, such as 123.
String constant enclosed within single quotes, such as 'ABCD'.
An expression using an Oracle GoldenGate column-conversion function. Within a COLMAP statement, you can use any of the Oracle GoldenGate column-conversion functions to transform data for the mapped columns, for example:
```
@STREXT (COL1, 1, 3)
```
Here's an example of using BEFORE column_name: BEFORE ORD_DATE
Here's an example of using AFTER column_name : AFTER ORD_DATE. This is the default option if a column name is listed.

If the column mapping involves case-sensitive columns from different database types, specify each column as it is stored in the database.

If the database requires double quotes to enforce case-sensitivity, specify the case-sensitive column name within double quotes.
If the database is case-sensitive without requiring double quotes, specify the column name as it is stored in the database.

The following shows a mapping between a target column in an Oracle database and a source column in a case-sensitive SQL Server database.

COLMAP ("ColA" = ColA)

See Specifying Object Names in Oracle GoldenGate Input for more information about specifying names to Oracle GoldenGate.

See Globalization Considerations when Mapping Data for globalization considerations when mapping source and target columns in databases that have different character sets and locales.

Avoid using COLMAP to map a value to a key column (which causes the operation to become a primary key update), The WHERE clause that Oracle GoldenGate uses to locate the target row will not use the correct before image of the key column. Instead, it will use the after image. This will cause errors if you are using any functions based on that key column, such as a SQLEXEC statement.

Column Mapping Limitations

Here are the column mapping limitations:

LOB columns cannot be used in FILTER, WHERE columns, or as a source_expression in a COLMAP statement. LOB columns are BLOB, CLOB, NCLOB, XMLType, User-Defined Data Types, Nested Tables, VARRAYs and other special data types.
If the source column contains more than 4000 bytes, it cannot be used in transformation routines, as the value is stored in the trail as an LOB record. For example a VARCHAR2(4000 CHAR) in Oracle and the Japanese character set is stored as 3 bytes for each character. This implies that the column could be 12000 bytes long and Oracle GoldenGate would store this value as an LOB field.
The full SQL statement that Oracle GoldenGate would execute would exceed 4MB in size. For example, if you have a table with thousands of VARCHAR2(4000) columns and you want to put 4000 bytes in each one, this could cause the total SQL statement that Oracle GoldenGate is going to execute to exceed the maximum size of 4MB.

Configuring Global Column Mapping with COLMATCH

Use the COLMATCH parameter to create global rules for column mapping. With COLMATCH, you can map between similarly structured tables that have different column names for the same sets of data. COLMATCH provides a more convenient way to map columns of this type than does using table-level mapping with a COLMAP clause in individual TABLE or MAP statements.

Case-sensitivity is supported as follows:

For MySQL, SQL Server if the database is case-sensitive, COLMATCH looks for an exact case and name match regardless of whether or not a name is specified in quotes.
For Oracle Database and Db2 databases, where names can be either case-sensitive or case-insensitive in the same database and double quotes are required to show case-sensitivity, COLMATCH requires an exact case and name match when a name is in quotes in the database.

Syntax

COLMATCH
{NAMES target_column = source_column |
PREFIX prefix |
SUFFIX suffix |
RESET}

Argument Description

Argument	Description
`NAMES target_column = source_column`	Maps based on column names. Put double quotes around the column name if it is case-sensitive and the database requires quotes to enforce case-sensitivity. For these database types, an unquoted column name is treated as case-insensitive by Oracle GoldenGate. For databases that support case-sensitivity without requiring quotes, specify the column name as it is stored in the database. If the `COLMATCH` is between columns in different database types, make certain the names reflect the appropriate case representation for each one. For example, the following specifies a case-sensitive target column name `"aBc`" in an Oracle Database and a case-sensitive source column name `aBc` in a case-sensitive SQL Server database. `COLMATCH NAMES "aBc" = aBc`
`PREFIX prefix \| SUFFIX suffix`	Ignores the specified name prefix or suffix. Put double quotes around the prefix or suffix if the database requires quotes to enforce case-sensitivity, for example "`P_"`. For those database types, an unquoted prefix or suffix is treated as case-insensitive. For databases that support case-sensitivity without requiring quotes, specify the prefix or suffix as it is stored in the database. For example, `P_` specifies a capital `P` prefix. The following example specifies a case-insensitive prefix to ignore. The target column name `P_ABC` is mapped to source column name `ABC`, and target column name `P_abc` is mapped to source column name `abc`. COLMATCH PREFIX p_ The following example specifies a case-sensitive `suffix` to ignore. The target column name `ABC_k` is mapped to the source column name `ABC`, and the target column name "`abc_k`" is mapped to the source column name "abc". SUFFIX "_k"
`RESET`	Turns off previously defined `COLMATCH` rules for subsequent `TABLE` or `MAP` statements.

NAMES target_column = source_column

Maps based on column names.

Put double quotes around the column name if it is case-sensitive and the database requires quotes to enforce case-sensitivity. For these database types, an unquoted column name is treated as case-insensitive by Oracle GoldenGate.

For databases that support case-sensitivity without requiring quotes, specify the column name as it is stored in the database.

If the COLMATCH is between columns in different database types, make certain the names reflect the appropriate case representation for each one. For example, the following specifies a case-sensitive target column name "aBc" in an Oracle Database and a case-sensitive source column name aBc in a case-sensitive SQL Server database.

COLMATCH NAMES "aBc" = aBc

PREFIX prefix | SUFFIX suffix

Ignores the specified name prefix or suffix.

Put double quotes around the prefix or suffix if the database requires quotes to enforce case-sensitivity, for example "P_". For those database types, an unquoted prefix or suffix is treated as case-insensitive.

For databases that support case-sensitivity without requiring quotes, specify the prefix or suffix as it is stored in the database. For example, P_ specifies a capital P prefix.

The following example specifies a case-insensitive prefix to ignore. The target column name P_ABC is mapped to source column name ABC, and target column name P_abc is mapped to source column name abc.

COLMATCH PREFIX p_

The following example specifies a case-sensitive suffix to ignore. The target column name ABC_k is mapped to the source column name ABC, and the target column name "abc_k" is mapped to the source column name "abc".

SUFFIX "_k"

RESET

Turns off previously defined COLMATCH rules for subsequent TABLE or MAP statements.

The following example illustrates when to use COLMATCH. The source and target tables are identical except for slightly different table and column names.The database is case-insensitive.

ACCT Table	ORD Table
`CUST_CODE CUST_NAME CUST_ADDR PHONE S_REP S_REPCODE`	`CUST_CODE CUST_NAME ORDER_ID ORDER_AMT S_REP S_REPCODE`

ACCOUNT Table	ORDER Table
`CUSTOMER_CODE CUSTOMER_NAME CUSTOMER_ADDRESS PHONE REP REPCODE`	`CUSTOMER_CODE CUSTOMER_NAME ORDER_ID ORDER_AMT REP REPCODE`

To map the source columns to the target columns in this example, as well as to handle subsequent maps for other tables, the syntax is:

COLMATCH NAMES CUSTOMER_CODE = CUST_CODE
COLMATCH NAMES CUSTOMER_NAME = CUST_NAME
COLMATCH NAMES CUSTOMER_ADDRESS = CUST_ADDR
COLMATCH PREFIX S_
MAP SALES.ACCT, TARGET SALES.ACCOUNT, COLMAP (USEDEFAULTS);
MAP SALE.ORD, TARGET SALES.ORDER, COLMAP (USEDEFAULTS);
COLMATCH RESET
MAP SALES.REG, TARGET SALE.REG;
MAP SALES.PRICE, TARGET SALES.PRICE;

Based on the rules in the example, the following occurs:

Data is mapped from the CUST_CODE columns in the source ACCT and ORD tables to the CUSTOMER_CODE columns in the target ACCOUNT and ORDER tables.
The S_ prefix will be ignored.
Columns with the same names, such as the PHONE and ORDER_AMT columns, are automatically mapped by means of USEDEFAULTS without requiring explicit rules. See Understanding Default Column Mapping for more information.
The previous global column mapping is turned off for the tables REG and PRICE. Source and target columns in those tables are automatically mapped because all of the names are identical.

Understanding Default Column Mapping

For self-describing trails, if an explicit column mapping does not exist, either by using COLMATCH or COLMAP, Oracle GoldenGate maps source and target columns by default according to the following rules.

This doesn't apply if you are using SOURCEDEFS or TARGETDEFS.

If a source column is found whose name and case exactly match those of the target column, the two are mapped.
If no case match is found, fallback name mapping is used. Fallback mapping performs a case-insensitive target table mapping to find a name match. Inexact column name matching is applied using upper cased names. This behavior is controlled by the GLOBALS parameter NAMEMATCHIGNORECASE. You can disable fallback name matching with the NAMEMATCHEXACT parameter, or you can keep it enabled but with a warning message by using the NAMEMATCHNOWARNING parameter.
Target columns that do not correspond to any source column take default values determined by the database.

If the default mapping cannot be performed, the target column defaults to one of the values shown in the following table.

Column Type	Value
Numeric	Zero (0)
Character or `VARCHAR`	Spaces
Date or Datetime	Current date and time
Columns that can take a `NULL` value	Null

Data Type Conversions

Learn about how Oracle GoldenGate maps data types.

Numeric Columns

Numeric columns are converted to match the type and scale of the target column. If the scale of the target column is smaller than that of the source, the number is truncated on the right. If the scale of the target column is larger than that of the source, the number is padded with zeros on the right.

You can specify a substitution value for invalid numeric data encountered when mapping number columns by using the REPLACEBADNUM parameter for more information.

Character-type Columns

Character-type columns can accept character-based data types such as VARCHAR, numeric in string form, date and time in string form, and string literals. If the scale of the target column is smaller than that of the source, the column is truncated on the right. If the scale of the target column is larger than that of the source, the column is padded with spaces on the right.

Literals must be enclosed within single quotes.

You can control the response of the Oracle GoldenGate process when a valid code point does not exist for either the source or target character set when mapping character columns by using the REPLACEBADCHAR parameter for more information.

Datetime Columns

Datetime (DATE, TIME, and TIMESTAMP) columns can accept datetime and character columns, as well as string literals. Literals must be enclosed within single quotes. To map a character column to a datetime column, make certain it conforms to the Oracle GoldenGate external SQL format of YYYY-MM-DD HH:MI:SS.FFFFFF.

Oracle GoldenGate supports timestamp data from 0001-01-03 00:00:00 to 9999-12-31 23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the timezone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

Required precision varies according to the data type and target platform. If the scale of the target column is smaller than that of the source, data is truncated on the right. If the scale of the target column is larger than that of the source, the column is extended on the right with the values for the current date and time.

Selecting and Filtering Rows

Filtering can only be performed on columns that are available to Oracle GoldenGate. In the TRANLOG Extract Oracle GoldenGate has access to all columns that are present in the redo logs and in the database. If the columns are not in the redo logs, they must be explicitly fetched (using FETCHCOLS) to be able to filter them. In the Extract pump and in the Replicat, the columns must be available in the trail file. Because of this, any column that you want to use in a FILTER or WHERE clause must be explicitly logged using ADD TRANDATA COLS, and you have to retain the default of LOGALLSUPCOLS.

To filter out or select rows for extraction or replication, use the FILTER and WHERE clauses of the TABLE and MAP parameters.

The FILTER clause offers you more functionality than the WHERE clause because you can employ any of the Oracle GoldenGate column conversion functions, whereas the WHERE clause accepts basic WHERE operators.

Selecting Rows with a FILTER Clause

Use a FILTER clause to select rows based on a numeric value by using basic operators or one or more Oracle GoldenGate column-conversion functions.

Note:

To filter a column based on a string, use one of the Oracle GoldenGate string functions or use a WHERE clause.

The syntax for FILTER in a TABLE statement is as follows:

TABLE source_table,
, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
, filter_clause);

The syntax for FILTER in a MAP statement is as follows and includes an error-handling option.

MAP source_table, TARGET target_table,
, FILTER (
[, ON INSERT | ON UPDATE| ON DELETE]
[, IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE]
[, RAISEERROR error_number]
, filter_clause);

Valid FILTER clause elements are the following:

An Oracle GoldenGate column-conversion function. These functions are built into Oracle GoldenGate so that you can perform tests, manipulate data, retrieve values, and so forth. See Testing and Transforming Data for more information about Oracle GoldenGate conversion functions.
Numbers
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 derived from comparisons can be zero (indicating FALSE) or non-zero (indicating TRUE).
Parentheses (for grouping results in the expression)
Conjunction operators: AND, OR

Use the following FILTER options to specify which SQL operations a filter clause affects. Any of these options can be combined.

ON INSERT | ON UPDATE | ON DELETE IGNORE INSERT | IGNORE UPDATE | IGNORE DELETE

Use the RAISEERROR option of FILTER in the MAP parameter to generate a user-defined error when the filter fails. This option is useful when you need to trigger an event in response to the failure.

Use the @RANGE function within a FILTER clause to distribute the processing workload among multiple MAP or TABLE statements.

Here's a sample:

REPERROR (9999, EXCEPTION)
MAP OWNER.SRCTAB, TARGET OWNER.TARGTAB,
                   SQLEXEC (ID CHECK, ON UPDATE, QUERY ' SELECT COUNT FROM TARGTAB WHERE PKCOL = :P1 ', PARAMS (P1 = PKCOL)),
                   FILTER (BALANCE > 15000),
                   FILTER (ON UPDATE, @BEFORE (COUNT) = CHECK.COUNT)
;
MAP OWNER.SRCTAB, TARGET OWNER.TARGEXC,
EXCEPTIONSONLY, 
COLMAP (   USEDEFAULTS,
ERRTYPE = 'UPDATE FILTER FAILED'
)
;

Table 9-4 Using Multiple FILTER Statements

Parameter file	Description
`REPERROR (9999, EXCEPTION)`	Raises an exception for the specified error.
`MAP OWNER.SRCTAB, TARGET OWNER.TARGTAB,`	Starts the `MAP` statement.
`SQLEXEC (ID CHECK, ON UPDATE, QUERY ' SELECT COUNT FROM TARGTAB ' 'WHERE PKCOL = :P1 ', PARAMS (P1 = PKCOL)),`	Performs a query to retrieve the present value of the `COUNT` column whenever an update is encountered. There is a `BEFOREFILTER` option also that allows the query or stored procedure to be executed prior to processing the `FILTER` clause. This allows values from the `SQLEXEC` portion to be used inside the `FILTER` at runtime.
`FILTER (BALANCE > 15000),`	Uses a `FILTER` clause to select rows where the balance is greater than 15000.
`FILTER (ON UPDATE, @BEFORE (COUNT) = CHECK.COUNT)`	Uses another `FILTER` clause to ensure that the value of the source `COUNT` column before an update matches the value in the target column before applying the target update.
`;`	The semicolon concludes the `MAP` statement.
`MAP OWNER.SRCTAB, TARGET OWNER.TARGEXC, EXCEPTIONSONLY, COLMAP (USEDEFAULTS, ERRTYPE = 'UPDATE FILTER FAILED');`	Designates an exceptions `MAP` statement. The `REPERROR` clause for error 9999 ensures that the exceptions map to `TARGEXC` will be executed.

Example 9-5 Calling the @COMPUTE Function

The following example calls the @COMPUTE function to extract records in which the price multiplied by the amount exceeds 10,000.

MAP SALES.TCUSTORD, TARGET SALES.TORD,
FILTER (@COMPUTE (PRODUCT_PRICE * PRODUCT_AMOUNT) > 10000);

Example 9-6 Calling the @STREQ Function

The following uses the @STREQ function to extract records where the value of a character column is 'JOE'.

TABLE ACCT.TCUSTORD, FILTER (@STREQ ("Name", 'joe') > 0);

Example 9-7 Selecting Records

The following selects records in which the AMOUNT column is greater than 50 and executes the filter on UPDATE and DELETE operations.

TABLE ACT.TCUSTORD, FILTER (ON UPDATE, ON DELETE, AMOUNT > 50);

Example 9-8 Using the @RANGE Function

(Replicat group 1 parameter file)

MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (1, 2, ID));

(Replicat group 2 parameter file)

MAP sales.acct, TARGET sales.acct, FILTER (@RANGE (2, 2, ID));

You can combine several FILTER clauses in one MAP or TABLE statement, as shown in Table 9-4, which shows part of a Replicat parameter file. Oracle GoldenGate executes the filters in the order listed, until one fails or until all are passed. If one filter fails, they all fail.

Selecting Rows with a WHERE Clause

Use any of the elements in Table 9-5 in a WHERE clause to select or exclude rows (or both) based on a conditional statement. Each WHERE clause must be enclosed within parentheses. Literals must be enclosed within single quotes.

Table 9-5 Permissible WHERE Operators

Element	Examples
Column names	`PRODUCT_AMT`
Numeric values	`-123, 5500.123`
Literal strings	`'AUTO', 'Ca'`
Built-in column tests	`@NULL`, `@PRESENT`, `@ABSENT` (column is null, present or absent in the row). These tests are built into Oracle GoldenGate. See Considerations for Selecting Rows with FILTER and WHERE.
Comparison operators	=, <>, >, <, >=, <`=`
Conjunctive operators	`AND, OR`
Grouping parentheses	Use open and close parentheses ( ) for logical grouping of multiple elements.

Oracle GoldenGate does not support FILTER for columns that have a multi-byte character set or a character set that is incompatible with the character set of the local operating system.

Arithmetic operators and floating-point data types are not supported by WHERE. To use more complex selection conditions, use aFILTER clause or a user exit routine.

The syntax for WHERE is identical in the TABLE and MAP statements:

TABLE table, WHERE (clause);

MAP source_table, TARGET target_table, WHERE (clause);

Considerations for Selecting Rows with FILTER and WHERE

The following suggestions can help you create a successful selection clause.

Note:

The examples in this section assume a case-insensitive database.

Ensuring Data Availability for Filters

If the database only logs values for changed columns to the transaction log, there can be errors if any of the unchanged columns are referenced by selection criteria. Oracle GoldenGate ignores such row operations, outputs them to the discard file, and issues a warning.

To avoid missing-column errors, create your selection conditions as follows:

Use only primary-key columns as selection criteria, if possible.
Make required column values available by enabling supplemental logging for those columns. Alternatively, you can use the FETCHCOLS or FETCHCOLSEXCEPT option of the TABLE parameter. These options are valid for all supported databases. They query the database to fetch the values if they are not present in the log. To retrieve the values before the FILTER or WHERE clause is executed, include the FETCHBEFOREFILTER option in the TABLE statement before the FILTER or WHERE clause. For example:
```
TABLE DEMO.PEOPLE, FETCHBEFOREFILTER, FETCHCOLS (age), FILTER (age > 50);
```
Test for a column's presence first, then for the column's value. To test for a column's presence, use the following syntax.
```
column_name {= | <>} {@PRESENT | @ABSENT}
```
The following example returns all records when the amount column is over 10,000 and does not cause a record to be discarded when amount is absent.
```
WHERE (amount = @PRESENT AND amount > 10000)
```

Comparing Column Values

To ensure that elements used in a comparison match, compare appropriate column types:

Character columns to literal strings.
Numeric columns to numeric values, which can include a sign and decimal point.
Date and time columns to literal strings, using the format in which the column is retrieved by the application.

Testing for NULL Values

To evaluate columns for NULL values, use the following syntax.

column {= | <>} @NULL

The following returns TRUE if the column value is NULL, and thereby replicates the row. It returns FALSE for all other cases (including a column missing from the record).

WHERE (amount = @NULL)

The following returns TRUE only if the column is present in the record and is not NULL.

WHERE (amount = @PRESENT AND amount <> @NULL)

Note:

If a value in the trail contains more than 4000 bytes then the @NULL function will return TRUE.

Retrieving Before and After Values

For update and delete operations, it can be useful to retrieve the BEFORE values of the source columns (the values before the update occurred). For inserts, all column values are considered AFTER images.

These values are stored in the trail and can be used in filters and column mappings. For example, you can:

Retrieve the before image of a row as part of a column-mapping specification in an exceptions MAP statement, and map those values to an exceptions table for use in testing or troubleshooting conflict resolution routines.
Perform delta calculations. For example, if a table has a Balance column, you can calculate the net result of a particular transaction by subtracting the original balance from the new balance, as in the following example:
```
MAP "owner"."src", TARGET "owner"."targ",
COLMAP (PK1 = PK1, delta = balance – @BEFORE (balance));
```
Note:

The previous example indicates a case-sensitive database such as Oracle. The table names are in quote marks to reflect case-sensitivity.

To Reference the Before Value

Use the @BEFORE column conversion function with the name of the column for which you want a before value, as follows:
```
@BEFORE (column_name)
```
Use the GETUPDATEBEFORES parameter in the Extract parameter file to capture before images from the transaction record, or use it in the Replicat parameter file to use the before image in a column mapping or filter. If using the Conflict Resolution and Detection (CDR) feature, you can use the GETBEFORECOLS option of TABLE. To use these parameters, all columns must be present in the transaction log. If the database only logs the values of columns that changed, using the @BEFORE function may result in a "column missing" condition and the column map is executed as if the column were not in the record. See Ensuring Data Availability for Filters to ensure that column values are available.

Oracle GoldenGate also provides the @AFTER function to retrieve after values when needed for filtering, for use in conversion functions, or other purposes. See @BEFORE and @AFTER in the Parameters and Functions Reference for Oracle GoldenGate.

Selecting Columns

To control which columns of a source table are extracted by Oracle GoldenGate, use the COLS and COLSEXCEPT options of the TABLE parameter. Use COLS to select columns for extraction, and use COLSEXCEPT to select all columns except those designated by COLSEXCEPT.

Restricting the columns that are extracted can be useful when a target table does not contain the same columns as the source table, or when the columns contain sensitive information, such as a personal identification number or other proprietary business information.

Selecting and Converting SQL Operations

By default, Oracle GoldenGate captures and applies INSERT, UPDATE, and DELETE operations. You can use the following parameters in the Extract or Replicat parameter file to control which kind of operations are processed, such as only inserts or only inserts and updates.

GETINSERTS | IGNOREINSERTS

GETUPDATES | IGNOREUPDATES

GETDELETES | IGNOREDELETES

You can convert one type of SQL operation to another by using the following parameters in the Replicat parameter file:

Use INSERTUPDATES to convert source update operations to inserts into the target table. This is useful for maintaining a transaction history on that table. The transaction log record must contain all of the column values of the table, not just changed values. Some databases do not log full row values to their transaction log, but only values that changed.
Use INSERTDELETES to convert all source delete operations to inserts into the target table. This is useful for retaining a history of all records that were ever in the source database.
Use UPDATEDELETES to convert source deletes to updates on the target.

Using Transaction History

Oracle GoldenGate enables you to retain a history of changes made to a target record and to map information about the operation that caused each change. This history can be useful for creating a transaction-based reporting system that contains a separate record for every operation performed on a table, as opposed to containing only the most recent version of each record.

For example, the following series of operations made to a target table named CUSTOMER would leave no trace of the ID of Dave. The last operation deletes the record, so there is no way to find out Dave's account history or his ending balance.

Table 9-6 Operation History for Table CUSTOMER

Sequence	Operation	ID	BALANCE
`1`	`Insert`	`Dave`	`1000`
`2`	`Update`	`Dave`	`900`
`3`	`Update`	`Dave`	`1250`
`4`	`Delete`	`Dave`	`1250`

Retaining this history as a series of records can be useful in many ways. For example, you can generate the net effect of transactions.

To Implement Transaction Reporting

To prepare Extract to capture before values, use the GETUPDATEBEFORES parameter in the Extract parameter file. A before value (or before image) is the existing value of a column before an update is performed. Before images enable Oracle GoldenGate to create the transaction record.
To prepare Replicat to post all operations as inserts, use the INSERTALLRECORDS parameter in the Replicat parameter file. Each operation on a table becomes a new record in that table.
To map the transaction history, use the return values of the GGHEADER option of the @GETENV column conversion function. Include the conversion function as the source expression in a COLMAP statement in the TABLE or MAP parameter.

Using the sample series of transactions shown in Table 9-6 the following parameter configurations can be created to generate a more transaction-oriented view of customers, rather than the latest state of the database.

Process Parameter statements

Process	Parameter statements
Extract	`GETUPDATEBEFORES TABLE ACCOUNT.CUSTOMER;`
Replicat	`INSERTALLRECORDS MAP SALES.CUSTOMER, TARGET SALES.CUSTHIST, COLMAP (TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), BEFORE_AFTER = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'), OP_TYPE = @GETENV ('GGHEADER', 'OPTYPE'), ID = ID, BALANCE = BALANCE);`

Extract

GETUPDATEBEFORES
TABLE ACCOUNT.CUSTOMER;

Replicat

INSERTALLRECORDS
MAP SALES.CUSTOMER, TARGET SALES.CUSTHIST,
COLMAP (TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
BEFORE_AFTER = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'),
OP_TYPE = @GETENV ('GGHEADER', 'OPTYPE'),
ID = ID,
BALANCE = BALANCE);

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.

This configuration makes possible queries such as the following, which returns the net sum of each transaction along with the time of the transaction and the customer ID.

SELECT AFTER.ID, AFTER.TS, AFTER.BALANCE - BEFORE.BALANCE
FROM CUSTHIST AFTER, CUSTHIST BEFORE
WHERE AFTER.ID = BEFORE.ID AND AFTER.TS = BEFORE.TS AND
AFTER.BEFORE_AFTER = 'A' AND BEFORE.BEFORE_AFTER = 'B';

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.

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 9-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 9-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 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)`

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.

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.
```

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-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:

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)

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.

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:

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

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:

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.

Using Tokens

You can capture and store data within the user token area of a trail record header. Token data can be retrieved and used in many ways to customize the way that Oracle GoldenGate delivers information.

For example, you can use token data in:

Column maps
Stored procedures called by a SQLEXEC statement
User exits
Macros

Defining Tokens

To use tokens, you define the token name and associate it with data. The data can be any valid character data or values retrieved from Oracle GoldenGate column-conversion functions.

The token area in the record header permits up to 16,000 bytes of data. Token names, the length of the data, and the data itself must fit into that space.

To define a token, use the TOKENS option of the TABLE parameter in the Extract parameter file.

Syntax

TABLE table_spec, TOKENS (token_name = token_data [, ...]);

Where:

table_spec is the name of the source table. A container or catalog name, if applicable, and an owner name must precede the table name.
token_name is a name of your choice for the token. It can be any number of alphanumeric characters and is not case-sensitive.
token_data is a character string of up to 2000 bytes. The data can be either a string that is enclosed within single quotes or the result of an Oracle GoldenGate column-conversion function. The character set of token data is not converted. The token must be in the character set of the source database for Extract and in the character set of the target database for Replicat. In the trail file, user tokens are stored in UTF-8.

TABLE ora.oratest, TOKENS (
TK-OSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME'),
TK-GROUP = @GETENV ('GGENVIRONMENT' , 'GROUPNAME')
TK-HOST =  @GETENV('GGENVIRONMENT' , 'HOSTNAME'));

As shown in this example, the Oracle GoldenGate @GETENV function is an effective way to populate token data. This function provides several options for capturing environment information that can be mapped to tokens and then used on the target system for column mapping.

Using Token Data in Target Tables

To map token data to a target table, use the @TOKEN column-conversion function in the source expression of a COLMAP clause in a Replicat MAP statement. The @TOKEN function provides the name of the token to map. The COLMAP syntax with @TOKEN is:

Syntax

COLMAP (target_column = @TOKEN ('token_name'))

The following MAP statement maps target columns host, gg_group, and so forth to tokens tk-host, tk-group, and so forth. Note that the arguments must be enclosed within single quotes.

User tokens	Values
tk-host	:sysA
tk-group	:extora
tk-osuser	:jad
tk-domain	:admin
tk-ba_ind	:B
tk-commit_ts	:2011-01-24 17:08:59.000000
tk-pos	:3604496
tk-rba	:4058
tk-table	:oratest
tk-optype	:insert

Example 9-10 MAP Statement

MAP ora.oratest, TARGET ora.rpt,
COLMAP (USEDEFAULTS,
host = @token ('tk-host'),
gg_group = @token ('tk-group'),
osuser= @token ('tk-osuser'),
domain = @token ('tk-domain'),
ba_ind= @token ('tk-ba_ind'),
commit_ts = @token ('tk-commit_ts'),
pos = @token ('tk-pos'),
rba = @token ('tk-rba'),
tablename = @token ('tk-table'),
optype = @token ('tk-optype'));

The tokens in this example will look similar to the following within the record header in the trail: