Re: Problem with Case Sensitivity in Oracle?

Bob Chow (bchow_at_rogers.com) wrote:
: Hello all,
 

: I am running into a problem with case sensitivity of Oracle. As you know,
: Oracle is sensitive to upper or lower case character strings. To accommodate
: this, I upshift the data inputted from the user in my application as well as
: using the Oracle UPPER function in a Select statement. This way , I
: ensure the I am comparing 2 strings with the same Case. The trouble with this
: is that, when one manipulates a column using a function such as UPPER, the
: Optimizer will suppress use of the index associated with that column, causing a
: performance problem. (I use PowerBuilder as the application development
: environment).
 

: Does anyone know of any trick or function that tells Oracle in my DML
: statement to ignore the case or any other workaround to this problem. Thanks
: in advance.
 

: Example:
 

: Select contactnumber,contactname, companyname
: from contactdirectory where
: contactnumber > 100 and UPPER (contactname) like UPPER ('ted rogers') and
: UPPER (companyname) like UPPER ('rogers');
 

: BC

If you are executing SQL or PL/SQL statements, the way to turn the case sensitivity on or off is setting SQLC[ASE] to lower or to upper. Please notice that this could only be achieved through SQL*PLUS, that is, you could not set SQLC[ASE] this way in PRO*C, for example.

A reveiew of the SQL*PLUS manual will further explain this.

I hope this answers your question.

Ramy. Received on Thu Mar 03 1994 - 14:39:41 CET