RE: Case insensitive searches
Date: Tue, 18 Mar 2014 08:12:34 +0000
Message-ID: <d17d50fb350c4671b07180c9232b1f63_at_THNMS015.TheHumanNetwork.local>
Hi,
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:
http://docs.oracle.com/cd/E11882_01/text.112/e24435/toc.htm
http://docs.oracle.com/cd/E11882_01/text.112/e24436/toc.htm
Herald ten Dam
Superconsult
Van: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] namens Ryan January [rjjanuary_at_multiservice.com] Verzonden: maandag 17 maart 2014 23:02
Aan: oracle-l
Onderwerp: Case insensitive searches
RHEL 5.10 / Oracle 11.2.0.3
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 11.2.0.4 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.
Thanks,
Ryan
--
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..
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Mar 18 2014 - 09:12:34 CET