COLMATCH

Valid For

Extract and Replicat

Description

Use the COLMATCH parameter to create global rules for column mapping. COLMATCH rules apply to all TABLE or MAP statements that follow the COLMATCH statement. Global rules can be turned off for subsequent TABLE or MAP entries with the RESET option.

With COLMATCH, you can map between tables that are similar in structure but 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 a COLMAP clause in individual TABLE or MAP statements.

With COLMATCH, you can:

  • Map explicitly based on column names.

  • Ignore name prefixes or suffixes.

Either COLMATCH or a COLMAP clause of a TABLE or MAP statement is required when mapping differently named source and target columns.

See Administering Oracle GoldenGate for Windows and UNIX for more information about mapping columns.

Default

None

Syntax

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

Specifies the name of a target and source column, for example CUSTOMER_CODE and CUST_CODE. If the database requires double quotes to enforce case-sensitivity, specify the column name that way. For example: NAMES "ABC" = "ABC2". For other case-sensitive databases, specify the column name as it is stored in the database, for example: NAMES ABC = abc.

PREFIX prefix | SUFFIX suffix

Specifies a column name prefix or suffix to ignore. If the database requires double quotes to enforce case-sensitivity, specify the prefix or suffix that way if it is case-sensitive. For other case-sensitive databases, specify the prefix or suffix as it is stored in the database

For example, to map a target column named "ORDER_ID" to a source column named "P_ORDER_ID", specify:

COLMATCH PREFIX "P_"

To map a target column named "CUST_CODE_K" to a source column named CUST_CODE, specify:

COLMATCH SUFFIX "_K"
RESET

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

Examples

Example 1   
COLMATCH NAMES "CUSTOMER_CODE" = "CUST_CODE"
Example 2   
COLMATCH NAMES Customer_Code = "Cust_Code"
Example 3   
COLMATCH PREFIX P_
Example 4   
COLMATCH SUFFIX _K
Example 5   
COLMATCH RESET