Re: Case insensitive searches

From: Mark Bobak <>
Date: Fri, 21 Mar 2014 19:51:43 +0000
Message-ID: <>

Good oneŠI was not aware of that.

On 3/21/14, 3:07 PM, "Ric Van Dyke" <> wrote:

>There is one (at least) major drawback of using virtual columns.
>Inserts must be done using column reference not position (see below). So
>adding this on COULD be a serious problem IF inserts are done without the
>column names.
> 2 ( col1 NUMBER
> 3 , col2 NUMBER
> 5 );
> *
>ERROR at line 1:
>ORA-00947: not enough values
>SQL> INSERT INTO vc VALUES (10, 20, 30);
>INSERT INTO vc VALUES (10, 20, 30)
>ERROR at line 1:
>ORA-54013: INSERT operation disallowed on virtual columns
>SQL> INSERT INTO vc (col1, col2) VALUES (10, 20);
>-----Original Message-----
>[] On Behalf Of Mark W. Farnham
>Sent: Tuesday, March 18, 2014 10:49 AM
>To:; 'Martin Klier';;
>Cc: 'oracle-l'
>Subject: RE: Case insensitive searches
>Another take (not implying that previous suggestions will not work) on
>this is to add the virtual column of upper(<original column>). IF that
>column becomes a frequent primary filter the pros and cons of indexing
>the virtual column (which will then de facto be a function based index)
>can be considered, but the sorting semantics can be directly displayed
>side by side <original column> and upper(<original column>) to answer
>questions that come up, and it is effective as a secondary filter, group
>by, and order by column reference.
>I have not tested constraints on virtual columns, but I certainly *hope*
>they work the way it seems natural to presume, that a unique constraint
>on upper(<original column>) would prevent the simultaneous commitment of
>both 'Ryan' and 'ryan' in the <original> column.
>Excusing the pun, whether this is a useful solution for a specific case
>will depend on the details of the case.
>-----Original Message-----
>From: []
>On Behalf Of Ryan January
>Sent: Tuesday, March 18, 2014 9:12 AM
>To: Martin Klier;;;
>Cc: oracle-l
>Subject: Re: Case insensitive searches
>Thank you for all the responses so far.
>Stéphane: Function based indexes were the first solution that came to
>Especially considering that we'd possibly require them to support the NLS
>changes, similar to what Martin is suggesting. Unfortunately we felt it
>wasn't a good fit for a few reasons. Mainly, the varchar fields are a
>secondary search criteria. Table access would generally be through
>existing indexes on integer columns. You make a very good point about
>constraints and insuring uniqueness. I don't believe we'll run into
>issues here but it is something I'll have to keep in mind.
>Internationalization was our main reason to avoid storing varchar values
>case insensitive within the database. We do support multiple languages
>and want to avoid that situation if at all possible.
>Herald: We have considered Oracle text in the past, however decided
>against it due it's inability to perform partial word searches. The words
>'App' and 'Apple' would bring back very different search results.
>I'll read back through the Oracle text docs today to see if we overlooked
>anything or if it's been extended since we last reviewed it.
>Even if it isn't a fit here, I can think of a few other areas it may be
>Rajendra: We had not considered regex. The reason we were included
>within the discussion was to make a general sweeping change in an attempt
>to reduce the impact on the application code base. Ultimately that's why
>we began to consider the NLS settings. This is something we may look at
>if it comes to modifying the queries within the application.
>Norm: This is what I've been considering as a next step. The main
>complaint started out that a user felt the search results were
>incorrectly sorted, they hadn't yet realized the searches were case
>sensitive. After we explained it was working as expected they also
>brought up the need for case insensitive searching. (eg: Ryan = ryan)
>Given that most table access will be through existing indexes I'm
>thinking we propose this change as at least a temporary solution. My
>worry is that going down this road will cause us to eventually modify the
>queries to allow case insensitive compares as well.
>Martin: I'm glad to see someone who has had success with this approach.
>When we started seeing this issue so soon I felt we may be wondering down
>a path rarely traveled. I'm at least willing to put a little additional
>time in the NLS settings as a solution. As you've mentioned, we're
>setting these params at the session level and only for the few app users
>who require it.
>Unfortunately the query that surfaced this issue was of a moderately
>complex view which involves a few sys owned objects. We're still trying
>to narrow down the table access which
>is actually causing the problem. Since the bug referenced is
>specifically sort/nosort operations within the execution plan I expected
>this to be an issue with the index itself. If the issue ends up being
>with these sys object access methods I'm not sure where we'll go next.
>This is something I'll be digging into momentarily.
>Did you remove/replace all the existing varchar indexes or additionally
>supplement with the NLS specific indexes? Do any of your applications
>interact with any oracle owned tables/views? If so, how do you approach
>those situations?
>On 03/18/2014 07:28 AM, Martin Klier wrote:
>> Hi Ryan,
>> we are running more than 50 projects on 11.2.x with NLS_SORT set to
>> We do two things:
>> * both parameters are only set on session level for the app users and
>> NOT for SYSTEM or the dictionary owner.
>> * all VARCHAR field indexes are changed to function based:
>> NLSSORT(MY_TEXT_FIELD, 'NLS_SORT=BINARY_CI') Because this function is
>> what Oracle wraps around any string search when the mentioned
>> parameters are set.
>> Hope this helps
>> Martin Klier
>> Am 17.03.2014 23:02, schrieb Ryan January:
>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 Fri Mar 21 2014 - 20:51:43 CET

Original text of this message