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

The following sample shows the column mapping using the COLMAP option of the MAP and TABLE parameters. It describes the mapping of the source table ACCTBL to the target table ACCTTAB.

MAP SALES.ACCTBL, TARGET SALES.ACCTTAB,
                COLMAP (   USEDEFAULTS,
                           NAME = CUST_NAME,
                           TRANSACTION_DATE = @DATE ('YYYY-MM-DD', 'YY',YEAR, 'MM', MONTH, 'DD', DAY),
                           AREA_CODE = @STREXT (PHONE_NO, 1, 3),
                           PHONE_PREFIX = @STREXT (PHONE_NO, 4, 6),
                           PHONE_NUMBER = @STREXT (PHONE_NO, 7, 10)
)
;

Table 8-2 Sample Column Mapping

Parameter statement	Description
`COLMAP`	Begins the `COLMAP` statement.
`USEDEFAULTS,`	Maps source columns as-is when the target column names are identical.
`NAME = CUST_NAME,`	Maps the source column `CUST_NAME` to the target column `NAME`.
`TRANSACTION_DATE = @DATE ('YYYY-MM-DD', 'YY', YEAR, 'MM', MONTH, 'DD', DAY),`	Converts the transaction date from the source date columns to the target column `TRANSACTION_DATE` by using the `@DATE` column conversion function.
`AREA_CODE = @STREXT (PHONE_NO, 1, 3), PHONE_PREFIX = @STREXT (PHONE_NO, 4, 6), PHONE_NUMBER = @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 Understanding Default Column Mapping for more information about the rules followed by Oracle GoldenGate for default column mapping.