Question Re: Oracle case sensitivity in Technology Q&A

Oracle case sensitivity

Former Member — Fri, 17 May 2013 12:55:02 GMT

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

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 13:16:00 GMT

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

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 13:46:47 GMT

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

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 13:50:23 GMT

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');

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 14:02:17 GMT

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

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 15:34:33 GMT

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

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 15:53:25 GMT

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

Re: Oracle case sensitivity

Former Member — Fri, 17 May 2013 18:00:45 GMT

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.

Re: Oracle case sensitivity

Former Member — Mon, 20 May 2013 09:34:18 GMT

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.

Re: Oracle case sensitivity

Former Member — Mon, 20 May 2013 10:50:40 GMT

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.

Re: Oracle case sensitivity

Former Member — Mon, 20 May 2013 19:54:07 GMT

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.