Data Express - Error message ORA-00955 using case insensitive schema names in the Oracle Extension

Problem:

In Oracle, schema names are always case insensitive and are stored as uppercase. You can access a table by specifying the owner with any case. In Data Express, however, the target schema name is case sensitive in the Oracle Extension. The reason is that if you have the “Always Drop Table” parameter set to “Y”, the process will access the Oracle system tables to verify if the table exists. Thus the schema name will be passed in the SQL “WHERE” clause where, by default, Oracle is case sensitive. The result would be that the table does not exist (although it actually does), the extension will try to create it and the error message  ‘’ORA-00955: name is already used by an existing object’’ will appear.

Resolution:

Oracle can be modified so that it will ignore the case sensitivity in the SQL “WHERE” clauses. This can be done by altering the session, using the predefined scripts pre_ConnectionAlias.sql and post_ConnectionAlias.sql related to the target data stores.  See “Data Store SQL Scripts” chapter in the “Getting Started with Distributed Data Stores” document. Before starting the Oracle Extension, you need to insert in the pre_TargetConnectionAlias.sql file the following commands:

ALTER SESSION SET NLS_COMP=LINGUISTIC

ALTER SESSION SET NLS_SORT=BINARY_CI