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.

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.

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.

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.

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.

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

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

Use any of the elements in Table 9-4 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.

PRODUCT_AMT

-123, 5500.123

'AUTO', 'Ca'

AND, OR

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.

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

TABLE table, WHERE (clause);

MAP source_table, TARGET target_table, WHERE (clause);

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.

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

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.

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.

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