Case insensitive searches in Oracle databases

Behavior:
If the storage of index entries for a DocuWare file cabinet has been defined as case sensitive, i.e. the "Add index entries in upper case" option is disabled, you cannot run case insensitive searches, when using Oracle databases. If for example the index entry "Huber" has been given to a document, it will not be found if you search for "huber". This is because the default search setting in Oracle is case sensitive.

Solution:
Important: This adaption changes the collation DocuWare uses for it's database queries. Since this prevents DocuWare from using your existing database indexes, you will have to recreate them in the collation used below.

In order to run case insensitive searches, when using Oracle databases, the suffix "_CI" must be added at the end of the dynamic parameters, that control the "National language sort" (NLS). This changes the default BINARY NLS to case insensitive through the use of "BINARY_CI". This applies to Oracle databases from version10gR2.

For DocuWare you need to make changes to the following directories:

DocuWare 6.x

• …\DocuWare\Authentication Server

• …\DocuWare\Workflow Server

• …\DocuWare\Content Server

• ...\DocuWare\Workflow Engine

• ...\DocuWare\Web\Platform\bin

• ...\DocuWare\Web\Settings\bin

DocuWare 7.x

• …\DocuWare\Authentication Server

• ...\DocuWare\Web\Platform\bin

• ...\DocuWare\Web\Settings\bin

• ...\DocuWare\BackgroundProcessService

Adapt the file "DocuWare.DAL.dll.config" in each of these directories by adding or commenting in the <Parameters> tag as follows:

DocuWare 6.x - 7.2
…
<dataProvider name="OracleDBClient" checkVersion="true"
commandType="DocuWare.DAL.DataAccess.OracleDB.OracleBuilderDBCommand, DocuWare.DAL.OracleDB" dataAdapterType="DocuWare.DAL.DataAccess.OracleDB.OracleBuilderAdapter, DocuWare.DAL.OracleDB"
connectionType="Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess, Version=2.112.1.2, Culture=neutral, PublicKeyToken=89b483f429c47342"
parameterType="Oracle.DataAccess.Client.OracleParameter, Oracle.DataAccess, Version=2.112.1.2, Culture=neutral, PublicKeyToken=89b483f429c47342"
parameterDbType="Oracle.DataAccess.Client.OracleDbType, Oracle.DataAccess, Version=2.112.1.2, Culture=neutral, PublicKeyToken=89b483f429c47342"
parameterDbTypeProperty="OracleDbType"
commandBuilderType="Oracle.DataAccess.Client.OracleCommandBuilder, Oracle.DataAccess, Version=2.112.1.2, Culture=neutral, PublicKeyToken=89b483f429c47342"
sqlBuilderType="DocuWare.DAL.DataAccess.OracleDB.OracleBuilder, DocuWare.DAL.OracleDB">
<Parameters>
<System.String DictionaryEntryKey="NLS">BINARY_CI</System.String>
</Parameters>
</dataProvider>
…

DocuWare 7.3 - 7.x
...
<dataProvider name="OracleDBClient"
checkVersion="true"
commandType="DocuWare.DAL.DataAccess.OracleDB.OracleBuilderDBCommand, DocuWare.DAL.OracleDB"
dataAdapterType="DocuWare.DAL.DataAccess.OracleDB.OracleBuilderAdapter, DocuWare.DAL.OracleDB"
connectionType="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"
parameterType="Oracle.ManagedDataAccess.Client.OracleParameter, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"
parameterDbType="Oracle.ManagedDataAccess.Client.OracleDbType, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"
parameterDbTypeProperty="OracleDbType"
commandBuilderType="Oracle.ManagedDataAccess.Client.OracleCommandBuilder, Oracle.ManagedDataAccess, Version=4.122.18.3, Culture=neutral, PublicKeyToken=89b483f429c47342"
sqlBuilderType="DocuWare.DAL.DataAccess.OracleDB.OracleBuilder, DocuWare.DAL.OracleDB">
<Parameters>
<System.String DictionaryEntryKey="NLS">BINARY_CI</System.String>
<System.String DictionaryEntryKey="NLS_COMP">LINGUISTIC</System.String>
</Parameters>
</dataProvider>
...
 

One more adaption to the "DAL.dll.config" file is needed to make wildcard searches case insensitive as well.
Adapt the following directories:

• …\DocuWare\Content Server (DocuWare 6.x)

• ...\DocuWare\Web\Platform\bin

• ...\DocuWare\Web\Settings\bin

Add the LikeIgnoreCase="True" parameter to line 3.
The altered line should now look like this:
…
<dataSettings LikeIgnoreCase="True" AutoTempTableListSize="...
...

This does not fully work in DocuWare 6.5, as select lists will still be case sensitive after these adaptions.
We recommend an upgrade to DocuWare 6.7 (HotfixPack 52) or higher.

KBA is applicable to On-premise Organizations ONLY.