Case insensitive searches

From: Ryan January <>
Date: Mon, 17 Mar 2014 17:02:44 -0500
Message-ID: <>

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 Mon Mar 17 2014 - 23:02:44 CET

Original text of this message