Question

Can HVR be used to replicate case-sensitive table or column names?

Environment

Local Data Processing

Answer

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 Explore/Table Select.

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