Oracle case sensitivity

[Former Member]

My application (WizSource Source Control) comes in PB10 and PB10.5 versions and is designed to work with various databases.

I have come across a problem with Oracle which appears to be caused by queries being case sensitive.

I am pretty sure there is a session command I can use to change it to case insensitive but what exactly the command is, I don't know.

I would appreciate it if an Oracle guru could give an example.

Thanks,

Roland

[Former Member · Accepted solution]

Case sensitive with respect to what?

Object names:  remove any quotation marks around object references.  Quotation marks means do a case senstive comparision.

Where clause:  Put UPPER or LOWER on both parts of the operation.

Sort:  Add order by nlssort(<insert column name here>,'NLS_SORT=BINARY_CI');

[Former Member · Accepted solution]

It isn't a problem with the table/column names. I don't have quotes around the table/column names.

The problem is with the data not matching in the where clause. I don't care about sort order.

This page mentions some alter session commands:

http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle

[Former Member · Accepted solution]

LOWER seems to be a function found on Microsoft, Sybase and Oracle so I think I'll try adding that to the WHERE clause.

[Former Member · Accepted solution]

Yes, like i PowerBuilder ... adding a Lower ( ) or Upper ( ) method around your data will resolve your usage issues.

[Former Member · Accepted solution]

The only issue with using LOWER or UPPER is that the optimiser will not use indexes if you LOWER the data element. If it is just a data scan then it does not matter.

[Former Member · Accepted solution]

Once my one Oracle customer verifies that LOWER fixes the problem, I think I'll back the change out and change it so that the data is always lower. I'll create a fix process for them to lower case their data. That will simplify my code and will have no impact on performance.

[Former Member · Accepted solution]

you can use functions (lower) in indexes in oracle.  The real problem with that approach is that the other databases that the app supports dont' support that.  So, you can speed up oracle to get around the oracle issue, but the other databases would do table scans.