RE: Case insensitive searches

From: Herald ten Dam <>
Date: Tue, 18 Mar 2014 08:12:34 +0000
Message-ID: <d17d50fb350c4671b07180c9232b1f63_at_THNMS015.TheHumanNetwork.local>


for case insensitive searches I always tend to use ORACLE TEXT. It is build for it. You can adjust it to the needs for example the use of diacritics. I can translate diacritics to base letter so it makes the search easier. The only disadvantage is that most of the people don't know about it and it has another syntax. But try it and you'll be amazed what it can.

Documentation about Oracle Text:

Herald ten Dam

Van: [] namens Ryan January [] Verzonden: maandag 17 maart 2014 23:02
Aan: oracle-l
Onderwerp: Case insensitive searches

RHEL 5.10 / Oracle

This seems to be one of those classic situations that everyone runs across at one time or another. We've been approached to assist in making an existing in-house application's queries case insensitive. Many things were discussed but the first option we're testing is NLS_SORT and NLS_COMP changes. The settings remain default at the database level. We've configured a login trigger to set NLS_SORT to BINARY_CI and NLS_COMP to LINGUISTIC for sessions initiated by a particular user. Less than a full day into testing we've already consistently run into ORA 600's via Bug #15936924 (MOS 15936924.8). While it's sad to admit, I really wasn't surprised. I expected to run into issues, but not this soon in, and not this severe.
Where it goes from unfortunate to infuriating is that the planned fix is to be included in 12.2 with no work around listed. The doc also mentions as being affected so there's no end in sight.

In light of this I think we need to take a step back and see how others have approached these situations.
Having no plans to attempt a resolution on any announced time frame leads me to believe Oracle simply doesn't have many users covering this code path. Are these non-standard NLS settings really that out of the norm? Has anyone else run across similar issues in the past? Did you ever find a work around?

If you haven't run into this issue, how are you handling case insensitivity mandates? Do you handle this in the app stack outside the database? Do you change the data model and only store a standard case inside the database? Do nothing outside of avert your eyes when you see sql containing upper() on both sides of a predicate?

Any insight you can provide would be appreciated.



This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email..


-- Received on Tue Mar 18 2014 - 09:12:34 CET

Original text of this message