Howard J. Rogers wrote:
> "Roberto Leo" <roberto.leo_at_exm.it> wrote in message
> news:bj72dn$8l$1_at_newsread.albacom.net...
>
>>Oracle can be case insensitive ? What Options Should I set to be case
>>insensitive in the Oracle Database ?
>>
>>Thanks Rob.
>>
>
>
> No, Rob, it can't be case insensitive, because we live in a case sensitive
> world.
>
> Alright, there are ways and means of achieving something similar, but there
> is no 'CASE_INSENSITIVE=TRUE' init.ora parameter or anything like that.
>
> For example, if you have a query which says select * from emp where ename =
> 'Bob', and you wish it to be case insensitive, you can issue it instead as
> select * from emp where upper(ename)='BOB'. That, of course, will provoke a
> full table scan, even with an index on the ename field, because the index
> contains "Bob", not "BOB".
>
> So in 8i, they invented function-based indexes. Create index blah on emp
> upper(ename). So now you have an index which *does* contain an entry for
> "BOB", and the earlier query is thus able to make use of index access whilst
> doing its case insensitive search.
>
> In earlier versions, one tended to add an extra set of columns to the table,
> and create triggers which, on an insert or update, inserted an upper case
> version of the relevant data into those extra columns.
>
> So a piecemeal approach is possible, but no quick fix.
>
> Regards
> HJR
>
>
>
We just ran in to this with a new app coming online later this year,
tens of thousands of records are being converted from an AS400 system
and populating oracle tables, as you can guess, they are in various
states of case sensitivity, ie. whatever the user felt like entering at
the time. Our users wanted case insensitive searches. So the vendors way
to have their next release do this was to create a table called
ignore_case that I'll populate with which owner-table-column will need
to be searched with case insensitive searches and create function based
indexes on said columns. The app on startup will then load the contents
of this table into memory so that any queries on said clumns will then
be case insensitive.
cheers
Received on Thu Sep 04 2003 - 14:17:13 CDT
