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. 

[Former Member]

I have these notes that i made for making Oracle case insensitve:

ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE SPFILE;

ALTER SYSTEM SET NLS_SORT=BINARY_AI SCOPE SPFILE;

also, make your indexes case insensitive too, like this:

create index index_name on table_name (NLSSORT (column_name, 'NLS_SORT=BINARY_CI') );

==============================

the performance of my app dropped to a such a degree that i didn't implement these changes.

[Former Member]

Roland

The case sensitivity usually occurs because you have the element in quotes, remove the quotes and it will be case insensitive. Elements in oracle are automatically up-shifted when they are created in the database unless you wrap them in quotes, in which case you can have mixed case.

If you want to email directly with the SQL I'll give you the altered syntax.

Cheers

David

[Former Member]

Hi Roland;

  Many DBMS's - not just Oracle - can be installed with or without case sensitivity. Normally, the DBA can change that on the DBMS server for either the server or the individual database (depending on the vendor).

AFAIK: There is no way in PL/SQL to change this at run-time.  

Regards ... Chris