Use the COLMAP option of the MAP and TABLE parameters to:
● explicitly map source columns to target columns that have different names.
● specify default column mapping when an explicit column mapping is not needed.
COLMAP provides instructions for selecting, mapping, translating, and moving data from a source column into a target column. Within a COLMAP statement, you can employ any of the Oracle GoldenGate column-conversion functions to transform data for the mapped columns.
Mapping columns
Specifying data definitions
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, i.e. 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.
See “Associating replicated data with metadata” on page 172.
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 the Oracle GoldenGate Windows and UNIX Reference Guide.
COLMAP availability
The COLMAP option is available with the following parameters:
Syntax TABLE <table spec>, TARGET <table spec>,
COLMAP ([USEDEFAULTS, ] <target column> = <source expression>);
Or...
MAP <table spec>, TARGET <table spec>,
COLMAP ([USEDEFAULTS, ] <target column> = <source expression>);
Extract Replicat
TABLE MAP
Mapping columns
Example 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.
Table 14 TABLE and MAP arguments
Argument Description
TARGET <table spec> The target owner and table. Always required for MAP. Required for TABLE if COLMAP is used.
<target column> The name of the target column to which you are mapping data. For information about supporting case-sensitivity or special characters in column names, see “Supporting case and special characters in column names” on page 144.
<source expression> Can be any of the following to represent the data that is to be mapped:
◆ Numeric constant, such as 123
◆ String constant enclosed within double quotes, such as “ABCD”. Single quotes, such as ’ABCD’, can be used if the USEANSISQLQUOTES parameter is used in the GLOBALS file.
◆ The name of a source column, such as ORD_DATE. To use double quotes for a column name that is case-sensitive or contains special characters, such as "Ord Date", use the USEANSISQLQUOTES parameter in the GLOBALS file.
◆ An expression using an Oracle GoldenGate column-conversion function, such as:
@STREXT (COL1, 1, 3)
A direct name-to-name mapping, without conversion of any sort, is the only column mapping that is supported when the source and target character sets are different. For information about supporting case-sensitivity or special characters in column names, see “Supporting case and special characters in column names” on page 144.
USEDEFAULTS Applies default mapping rules to map source and target columns automatically if they have the same name. USEDEFAULTS eliminates the need to map every target column explicitly, whether or not the source column has the same name. Transformation of data types is automatic based on the data-definitions file that was created with the defgen utility.
Use an explicit map or USEDEFAULTS, but not both for the same set of columns.
For more information about default column mapping, see “Using default column mapping” on page 150.
For more information about TABLE and MAP, see the Oracle GoldenGate Windows and UNIX Reference Guide.
Mapping columns
● 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.