Oracle Case Insenesitive

Hi Michel,

Yellowfin just passes the query onto Oracle, and then, inside Oracle the default behaviour of LIKE and the other comparison operators, = etc is case-sensitive. So you could say that Yellowfin is at the mercy of the Oracle case sensitive/insensitive settings.

However, there are a few different ways that you can change how your Oracle behaves with regards to case sensitive/insensitive searching. There is lots of information out there in the internet about this, and just for an example I submit to you the following Stack Overflow post:

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

I hope that helps, please let us know how you get on with this.

regards

David

Hi David,

The query runs fine in our Oracle client.  We have made the Oracle db insensitive, and with the query the Michel provided earlier, it works.

We took the query directly from Yellowfin, but within YF the exact same query returns no values/records.

Our question is why does it work fine in the Oracle client but not in YF.

Thanks,

Angelo

From: Big Dave [mailto:support@yellowfin.bi]

Sent: Monday, October 16, 2017 9:24 PM

To: Angelo Rigakis <arigakis@i-sight.com>

Subject: Re: Oracle Case Insenesitive [#5489]

Hi Angelo,

could you please tell me:

1) exactly how you made your Oracle DB case insensitive (e.g. in the initialization file, or setting NLS_SORT & NLS_COMP via Windows registry, or as environment variables, or using ALTER SESSION etc.)

2) what your Oracle client is (e.g. DBVisualizer, Toad, SQuirreL etc.)

3) where your Oracle is (e.g. on the Oracle database server, or a remote machine)

When you tell me this information then I can go about replicating the issue you are experiencing.

thanks,

David

Hi Dave;

the IT depart. confirm that they modify the init.ora - oracle init file.

le me know if you need more info.

Have a great day

- what your Oracleclient is (e.g. DBVisualizer, Toad, SQuirreL etc.)

Oracle SQL developer version 17.2.0.188 client on the pc

build 188.1159.

-where your Oracle is (e.g. on the Oracle database server, or a remote machine

it reply:

It's Oracle server 11.2 Server installed on remote machine

Yellow Fin and oracle share the same server.

Hi Michel and Angelo,

I have been researching this quite a bit, and what I've found is that if you set the NLS_COMP and NLS_SORT parameters (for case-insensitivity) in the initialization parameter file to specify a default session NLS environment, then these settings have no effect on the client side; they control only the server's behavior. (taken from this Oracle documentation)

Apparently you have to specially set your client to be case-insensitive, and one way to do this is to run the following queries at the beginning of a session:

ALTER SESSION SET NLS_SORT=BINARY_CI;
ALTER SESSION SET NLS_COMP=LINGUISTIC;

I tested this out and it worked for me. I have attached a short video that demonstrates this, in the beginning you will see that I open SQLPlus on the Oracle Server and by default it is case-insensitive. I search for a report called 'test' by filtering on a reportname = 'TEST' and the report is found. This case-insensitive behaviour is because I have set the relevant NLS parameters in the initialization startup file on the server.

Then I try 2 different clients, one is SQLDeveloper, the other is DBVisualizer, both connected to the same schema on that Oracle server, both of them are case-sensitive until I run the above queries.

So what I've done is to raise an enhancement request for a Yellowfin user to be able to configure a Yellowfin Oracle Data Source to be case-insensitive, then YF would automatically run those queries for the session.

One final point - I noticed in this forum post that there are 2 other ways to achieve making an Oracle client case-insensitive, one is by creating a trigger and the other is by JVM startup options. You may like to try them out!

Also, with your permission I would like to convert this Ticket into an Idea, that's just how we work here, we can link an Idea to our JIRA Enhancement Requests. Please let me know if that's OK by you.

regards,

David