RE: Case insensitive searches

  • From: Herald ten Dam <Herald.ten.Dam@xxxxxxxxxxxxxxx>
  • To: "rjjanuary@xxxxxxxxxxxxxxxx" <rjjanuary@xxxxxxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 18 Mar 2014 08:12:34 +0000

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@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] namens Ryan 
January [rjjanuary@xxxxxxxxxxxxxxxx]
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..
------------------------------------------------------------------

--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: