Re: Case insensitivity in frontend queries (2nd Posting)

Starous,

Thanks! nice workaround, for the time being it will serve my purpose at least for searching...This is useful as long as you know upfront which columns are used for searching by the user. (can't very well use ALL columns like this, it would double the size of any table) and I do not have much influence
on the SQL generated by the frontend, so I can't tackle it from that end.

Raj Gabrielse

Starous wrote:

> >
> > Is it possible to make Oracle case insensitive? So that a user of our
> > non-oracle frontend can enter selection criteria without having to worry
> > about case?
> >
> In this case I'm using new column deriwated from value of column which
> have to be case insensitive. In trigger on update or insert of
> this column I fill value to new column as
>
> :new.insensitivecolumn := UPPER ( :new.casesensitivecolumn );
>
> Then where clause in SELECT statement is like this
>
> SELECT casesensitivecolumn
> FROM ..
> WHERE insensitivecolumn = UPPER ( value );
>
> This solution has no affect on performance, it's only disk space eater
>
> > I'm not interrested in constructions or functions that somehow convert
> > entered criteria or data to either all lower/upper *before* it get's
> > send to Oracle. They are of no use, since data in the database needs to
> > be both lower/upper case, plus I don't wan't to suffer the penalty of
> > indices not being used as a result of functions applied on indexed
> > columns.
> >
> > The solution I'm after would have to be global for the database
> > instance, I'm therefore looking for a method to be applied at a lower
> > level. Since the instance is dedicated for our application (no other
> > application will ever be allowed to access it other than thru our
> > frontend), we do not have to take effects into account for other
> > applications that *do* require case sensitivity.
>
> I'm thinking global solution of this problem doesn't exist.
>
> Bye
> Starous
Received on Mon Mar 02 1998 - 00:00:00 CST