Re: Oracle case insensitive ?

You can not force ORACLE to be case insensitive. What you could do is to force character data in your ORACLE tables to either upper or lower case. To do that you need to create before insert or update for each row triggers and do something like:

   :new.column := UPPER(:new.column); (or LOWER)

Now instead of WHERE Upper(kuku_id) = Upper('abc') you can write WHERE kuku_id = Upper('abc') and ORACLE will use index on kuku_id column (if you have one). Similar way instead of SELECT * FROM ABC WHERE ABC_ID LIKE 'a%' you will use SELECT * FROM ABC WHERE ABC_ID LIKE UPPER('a%'). Note that ORACLE will use index on LIKE only if your pattern does not start with a wild card.
Also, if your data is really case insensitive, 'ABC', 'abc', 'AbC' mean the same to you. Although if you will insert these values you will end up with 3 different rows. Having the above described trigger will force ORACLE to treat all these strings as same one string. At the same time you should realize that there is a price for having such triggers. They will not affect your selects, but will increase insert and update time.

Solomon.Yakobson_at_entex.com

In article <3316C127.25E4_at_astea.co.il>,
  leo_at_astea.co.il wrote:
>
> This is a multi-part message in MIME format.
> ------------6A3C1D8E7D260
> Content-Transfer-Encoding: 7bit
> Content-Type: text/plain; charset=us-ascii
>
> Is there a way to define Oracle database to be case insensitive?
>
> We are looking for a way to retrieve case insensitive information from
> Oracle database different from using a function in where clause, e.g.
> WHERE Upper(kuku_id) = Upper('abc').
>
> Regarding the best of our knowledge such functions prevent using of
> indexes. We have a number of requests from our customers to support
> case insensitive retrieve.
> Example: SELECT * FROM ABC WHERE ABC_ID LIKE 'a%' should return records
> for both cases abc_id beginning by lowercase 'a' and abc_id beginning by
> UPPERCASE 'A'.
> Environment: Oracle Server 7.3 for Sun Solaris Unix.
> Our customers have various specter of Oracle configurations from Oracle
> 7.0 on Novell Netware to Oracle 7.3 on HP/9000,
> AIS, NT and Sun Solaris. Please advise if it is a configurable option.
> Thanks in advance.
>
> Leonid Borisov
> Project Leader
> Astea International
> ------------6A3C1D8E7D260
> Content-Transfer-Encoding: 7bit
> Content-Description: Address Book Card for Leonid Borisov
> Content-Disposition: inline; filename="nsmail3J.TMP"
> Content-Type: text/x-vCard; charset=us-ascii; name="nsmail3J.TMP"
>
> BEGIN:VCARD
> FN:Leonid Borisov
> N:Borisov;Leonid
> ORG:Astea Israel
> EMAIL;INTERNET:leo_at_astea.co.il
> TITLE:Project leader
> X-NAV-HTML:F
> END:VCARD
>
> ------------6A3C1D8E7D260--

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet