Invalid column names for the target

Owned by Copy Space for Confluence
Jan 22, 2017
1 min read

Although it's a bad practice, Oracle allows the columns to be named arbitrarily using the double-quote syntax. With this, the names can include keywords, lowercase or unicode characters.

A similar problem occurs when replicating to non-Oracle targets - some of the valid names in Oracle are keywords in other database systems and thus can cause issues for the APPLY process.

  • For lowercase names: as the SQL is case insensitive and APPLY does not use double quotes, just create the column name in uppercase on the target
  • For unicode: not supported
  • For spaces in names: not supported (although spaces at the beginning or end of column names are ignored)
  • For keywords: a global rename function is implemented. This APPLY_COLUMN_NAMES_MAP variable specifies a global rule renaming all columns in all replicated tables with a specified name to a different name.


Syntax:

set *.APPLY_COLUMN_NAMES_MAP coumn1=newcolumn1[,column2=newcolumn2[,...]]

 

Example:

set *.APPLY_COLUMN_NAMES_MAP COMMENT=COMMENT_COLUMN

APPLY process requires a restart to implement this mapping.

 

After restart, APPLY process log will show column mapping details as follow.

2016/06/06 05:34:37 INFO> Column translate: [COMMENT]=>[COMMENT_COLUMN]