Case Insensitive Searches?

We have a horrible canned application running on PCs hitting an oracle database.
It does almost all of its X-ref integrity, indexing and sorting outside the database. It is built to support Various "Databases" from text files on up, so everything is written to the lowest level.

In any event, we can not change the Qrys the application is generating. The user Wants to be able to enter mixed case names into the database and find them in a case insensitive manner.

Is this possible without changing the SQL calls being made or messing with the data on its way into or out of the database?

I only way I know for sure is to use one of the Character sets in the database that does not have lower case letter and therefore force everything to a known case. Not a solution.

In the past it was easy enough to fix the SQL code. That is not an option in this case.

NLS_SORT appears to just modify ORDER BY clauses, not case sensitivity in searches.

This software package is EXTREMELY sensitive to any changes to its data. We were working on adding some triggers. The user added data into a column we were trying to indentify from the application.
I went and found the data in the database. He added "Hello Chip" I changed it to "Hello Dale". When he tried to read the data from the app is crashed.

Is there any way to force an oracle instance to do case insentive searches by default?

As a related but seperate issue from the one currently at hand:

That does bring up another issue, even "Fixing" the SQL code by using an UPPER() or LOWER() will cause problems. In my understanding this will force a full table scan for every search, BAD IMHO.
Another "Fix" is to have a Search Name Columns forced to a known case and printing name columns in Mixed Case "Shane McCarren".

That brings up another issue, there is a function to force the initial letters of words to Upper Case, The above name kills that solution.

We either ignore the issue or use what in retrospect are hacks, What is the real answer?

Marc Received on Tue Jul 28 1998 - 12:14:30 CDT