Case insensitive searches
- From: Ryan January <rjjanuary@xxxxxxxxxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 17 Mar 2014 17:02:44 -0500
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
Other related posts:
