12 Mapping and Manipulating Data

This chapter describe how you can integrate data between source and target tables.

This chapter includes the following sections:

12.1 Limitations of Support

The following are limitations to the support of data mapping and manipulation.

  • Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects.

  • Some Oracle GoldenGate features and functionality do not support the use of data filtering and manipulation. Where applicable, this limitation is documented.

12.2 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 Section 4.5, "Specifying Object Names in Oracle GoldenGate Input" for instructions for specifying object names in these parameters.

12.3 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. When used, this file must be specified with the SOURCEDEFS or TARGETDEFS parameter. For more information about how to create a source-definitions or target-definitions file, see Chapter 13, "Associating Replicated Data with Metadata."

12.4 Deciding Where Data Mapping and Conversion Will Take Place

If the configuration you are planning involves a large amount of column mapping or data conversion, observe the following guidelines to determine which process or processes will perform these functions.

12.4.1 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 on the source system, on the target system, or on an intermediary system. To prevent the added overhead of this processing on the source system, you can configure the mapping and conversion to be performed on the target system 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. You can use one target-definitions file generated from the target tables, rather than having to manage an individual source-definitions file for each source database, which needs to be copied to the target each time the applications make layout changes.

For more information on which types of definitions files to use, and where, see Chapter 13, "Associating Replicated Data with Metadata."

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

12.5 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. These considerations encompass the following topics:

Section 12.5.1, "Conversion between Character Sets"

Section 12.5.2, "Preservation of Locale"

Section 12.5.3, "Support for Escape Sequences"

12.5.1 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 Appendix A, "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 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.


    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.

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 or a data pump.

If the trail is written by a version of Extract that is prior to version 11.2.1, the character set for character-type columns must be supplied to Replicat with the SOURCECHARSET parameter. This parameter also supplies a PASSTHRU option for preventing the conversion of character sets. For more information, see Reference for Oracle GoldenGate for Windows and UNIX.

12.5.2 Preservation of Locale

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

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


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.


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 sequence

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

12.6 Mapping Columns

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:

Section 12.6.2, "Configuring Table-level Column Mapping with COLMAP"

Section 12.6.3, "Configuring Global Column Mapping with COLMATCH"

Section 12.6.4, "Understanding Default Column Mapping"

Section 12.6.5, "Mapping Data Types from Column to Column"

Section 12.6.1, "Supporting Case and Special Characters in Column Names"

12.6.1 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 Section 4.5.7, "Differentiating Case-sensitive Column Names from Literals" for more information.

12.6.2 Configuring Table-level Column Mapping with COLMAP

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. 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 can be any of the following, allowing you to map the source column by name, so as to pass the source value exactly as recorded in the trail, or to transform the data before passing it to the target column:

  • 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 employ any of the Oracle GoldenGate column-conversion functions to transform data for the mapped columns, for example:

    @STREXT (COL1, 1, 3)

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 Section 4.5, "Specifying Object Names in Oracle GoldenGate Input" for more information about specifying names to Oracle GoldenGate.

See Section 12.5, "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, as shown in the following example.

  • Source table TCUSTMER1

  • Target table TCUSTMER2

  • Column layout, both tables:

    Column 1 = Cust
    Column 2 = Name
    Column 3 = City
    Column 4 = State
  • Primary key consiste of the Cust, Name, and City columns.

  • SQLEXEC query in the MAP statement:

    SQLEXEC (id mytest, query 'select city from TCUSTMER1 WHERE state = 'CA'', noparams, ERROR RAISE)
  • COLMAP statement in the MAP statement:

    COLMAP ( usedefaults, city = mytest.city )

This is the sequence of events that causes the error:

  1. INSERT statement inserts the following:

    INSERT into TCUSTMER1 values (Cust = '1234', Name = 'Ace', City = 'SF', State = 'CA');

    The SQLEXEC query returns the correct value, and the target table also has a value of SF for City and CA for State.

    mytest.city = 'SF'
  2. UPDATE statement changes City from SF to LA on the source. This does not succeed on the target. The SQLEXEC query looks up the City column in TCUSTMER1 and returns a value of LA. Based on the COLMAP clause, the before and after versions of City both are now LA. This generates SQL error 1403 when executing the target WHERE clause, because a value of LA does not exist for the City column in the target table. 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 (see Section, "Determining Whether COLMAP Requires a 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.

Table 12-1 Sample Column Mapping

Parameter statement Description

Maps the source table ACCTBL to the target table ACCTTAB.


Begins the COLMAP statement.


Maps source columns as-is when the target column names are identical.


Maps the source column CUST_NAME to the target column NAME.


Converts the transaction date from the source date columns to the target column TRANSACTION_DATE by using the @DATE column conversion function.

@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 Section 12.6.4, "Understanding Default Column Mapping" for more information about the rules followed by Oracle GoldenGate for default column mapping. Determining Whether COLMAP Requires a Data-definitions File

When using COLMAP, you might need to create a data-definitions file. To make this determination, you must consider whether the source and target column structures are identical, as defined by Oracle GoldenGate.

For source and target structures to be identical, they must:

  • be of the same database type, that is, all Oracle.

  • have the same character set and locale.

  • contain the same number of columns.

  • have identical column names (including case, white spaces, and quotes if applicable).

  • have identical data types.

  • have identical column lengths.

  • have the same column length semantics for character columns (bytes versus characters).

  • define all of the columns in the same order.

When using COLMAP for source and target tables that are not identical in structure, you must:

  • generate data definitions for the source tables, the target tables, or both, depending on the Oracle GoldenGate configuration and the databases that are being used.

  • transfer the definitions file to the system where they will be used.

  • use the SOURCEDEFS parameter to identify the definitions file for Replicat on a target system or use the TARGETDEFS parameter to identify the definitions file for Extract or a data pump on a source system or intermediary system.

When using COLMAP for source and target tables that are identical in structure, and you are only using COLMAP for other functions such as conversion, a source definitions file is not needed. When a definitions file is not being used, you must use the ASSUMETARGETDEFS parameter instead. See Reference for Oracle GoldenGate for Windows and UNIX for more information.

See Chapter 13, "Associating Replicated Data with Metadata" for more information about using a definitions file.

12.6.3 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 Sybase, MySQL, SQL Server, PostgreSQL, and Teradata, 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, DB2, and SQL/MX, 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.

See Section 4.5, "Specifying Object Names in Oracle GoldenGate Input" for more information about case-sensitivity support.


{NAMES target_column = source_column |
PREFIX prefix |
SUFFIX suffix |

Table 12-2 COLMATCH Options

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.

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.


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


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.

Table 12-3 COLMATCH Example Table: Source Database

ACCT Table ORD Table

Table 12-4 COLMATCH Example Table: Target Database


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:


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 Section 12.6.4, "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.

12.6.4 Understanding Default Column Mapping

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.

  • 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 Table 12-5.

Table 12-5 Defaults Values for Target Columns

Column Type Value


Zero (0)

Character or VARCHAR


Date or Datetime

Current date and time

Columns that can take a NULL value


12.6.5 Mapping Data Types from Column to Column

The following explains 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. See Reference for Oracle GoldenGate for Windows and UNIX 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. See Reference for Oracle GoldenGate for Windows and UNIX 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.

12.7 Selecting and Filtering Rows

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.

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


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_clause);

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

MAP source_table, TARGET target_table,
[, 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 Section 12.12, "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.



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.

Example 12-1 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.


Example 12-2 Calling the @STREQ Function

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


Example 12-3 Selecting Records

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


You can use the @RANGE function to divide the processing workload among multiple FILTER clauses, using separate TABLE or MAP statements. For example, the following splits the replication workload into two ranges (between two Replicat processes or two threads of a coordinated Replicat) based on the ID column of the source acct table.

Example 12-4 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 12-6, 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.

Table 12-6 Using Multiple FILTER Statements

Parameter file Description

Raises an exception for the specified error.


Starts the MAP statement.


Performs a query to retrieve the present value of the COUNT column whenever an update is encountered.


Uses a FILTER clause to select rows where the balance is greater than 15000.


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.


Designates an exceptions MAP statement. The REPERROR clause for error 9999 ensures that the exceptions map to TARGEXC will be executed.

12.7.2 Selecting Rows with a WHERE Clause

Use any of the elements in Table 12-7 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 12-7 Permissible WHERE Operators

Element Examples

Column names


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 Section 12.7.3, "Considerations for Selecting Rows with FILTER and WHERE".

Comparison operators

=, <>, >, <, >=, <=

Conjunctive operators


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 a FILTER clause or a user exit routine. See Section 17.3, "Using User Exits to Extend Oracle GoldenGate Capabilities" for more information.

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

TABLE table, WHERE (clause);
MAP source_table, TARGET target_table, WHERE (clause);

12.7.3 Considerations for Selecting Rows with FILTER and WHERE

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


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 except SQL/MX. 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:

  • 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 is NULL, and 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 not NULL.

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

12.8 Retrieving Before and After Values

For update operations, it can be advantageous to retrieve the before values of source columns: the values before the update occurred. 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));


    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

  1. Use the @BEFORE column conversion function with the name of the column for which you want a before value, as follows:

    @BEFORE (column_name)
  2. 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 Section, "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. For more information about @BEFORE and @AFTER, see Reference for Oracle GoldenGate for Windows and UNIX.

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

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




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.

12.11 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 12-8 Operation History for Table CUSTOMER

Sequence Operation ID BALANCE

















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

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

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

  3. 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 12-8 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
ID = ID,


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.


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:


@function (argument)

Table 12-9 Conversion Function Syntax

Syntax element Description


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.


A function argument.

Table 12-10 Function Arguments

Argument element Example

A numeric constant


A string literal enclosed within single quote marks


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


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.


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

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

    @DATE ('JTS',
    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. Omitting @COMPUTE

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


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


12.12.5 Manipulating Numbers and Character Strings

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

Table 12-11 Conversion Functions for Numbers and Characters

Purpose Conversion Function

Convert a binary or character string to a number.



Convert a number to a string.


Compare strings.



Concatenate strings.



Extract from a string.



Return the length of a string.


Substitute one string for another.


Convert a string to upper case.


Trim leading or trailing spaces, or both.




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:


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.


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


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.


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.


This conversion returns one of the following:


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


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.

12.13 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

12.13.1 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 2,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.


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


  • 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 (

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.

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


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.

Example 12-6 MAP Statement

MAP ora.oratest, TARGET ora.rpt,
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:

User tokens Values
:2011-01-24 17:08:59.000000