1. Oracle GoldenGate Classic Architecture Documentation
  2. Administer
  3. Data Management
  4. Mapping and Manipulating Data
  5. Mapping Columns Using TABLE and MAP
  6. Configuring Table-level Column Mapping with COLMAP
  7. Specifying the Columns to be Mapped in the COLMAP Clause

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.