Can HVR be used to replicate case-sensitive table or column names?
Yes - The action to use if at least one of the tables in the replication setup (channel) has a case-sensitive name or column name is LocationProperties /CaseSensitiveNames. This action is particularly important against Oracle Databases when by default Oracle will assume table and column names are case insensitive unless quoted.
The action LocationProperties /CaseSensitiveNames will ensure that table and column names will be quoted. Please note that the action LocationProperties /CaseSensitiveNames:
- Always applies to all tables in the channel.
- Is required before tables are added to the channel, since by default HVR will assume that table and column names are case-insensitive. If tables were already added (through the Table Explore/Table Select function in the GUI) then tables will have to be removed and re-added to the channel.
You may not realize that a table uses a case-sensitive name or column name until you see errors like:
F_JD0006: A table named [.] does not appear in the DBMS catalogs during Table
- ORA-00942: table or view does not exist during a Refresh.
- ORA-00904 "": invalid identifier, e.g. during a Refresh. This may be an indication that the column reported in uppercase may in fact be in mixed case in the database.