We needed a solution that made text matching in SQL queries case insensitive. Some Googling we found we could do this:
1: alter session set nls_comp=ansi;
2: alter session set nls_sort=binary_ci;
3:
4: select * from casetest where "field" = 'tom'
If you look at the execution plan (shown below), you’ll see the SQL that actually gets run.
It’s important to note that because if you want to use an index (as you see above) you’ll need to add a function index (shown below). A standard index on the field column won’t get used.
1: create index ix_casetestfunc on casetest (NLSSORT("field", 'NLS_SORT=BINARY_CI'))
So, how do you set that up in code (.Net)? You can use the OracleGlobalization class (see here for more info)… This class lets you alter the session without needing to run the alter session sql…
Now, this is great but I had a few problems… Firstly, I kept getting an error when I tried to set my changes to OracleGlobalization (by calling SetThreadInfo – was getting the same error with connection.SetSessionInfo). The error I got was "ORA-12705: Cannot access NLS data files or invalid environment specified". I was getting this error when I ran the following OracleGlobalization.SetThreadInfo(OracleGlobalization.GetThreadInfo()); – which shouldn’t happen because all the data should be valid, I was just given it! A jump into reflector to have a look what was going on didn’t help as it pretty much delegates off to a Win32 call straight away… So I had a look at all the parameters on the instance of OracleGlobalization and cross referenced them with values in the database to find that the value for ISOCurrency is wrong it was “UNITED “. So I made an educated guess and set it to UNITED KINGDOM in the debugger and it worked!
Next problem I found was that the code below made no difference to my queries… I guessed based on the API that by calling SetThreadInfo all my connections created on that thread would get my modified OracleGlobalization instance… No, I was wrong – in fact – nothing gets it. If you call GetThreadInfo immediately after calling SetThreadInfo you get the original instance…
1: var data = OracleGlobalization.GetThreadInfo();
2: data.ISOCurrency = "UNITED KINGDOM";
3: data.Sort = "BINARY_CI";
4: data.Comparison = "ANSI";
5: OracleGlobalization.SetThreadInfo(data);
The code below works, which caused significant elation I might add!
1: using (var con = new OracleConnection("..."))
2: {
3: con.Open();
4: var data = con.GetSessionInfo();
5: data.ISOCurrency = "UNITED KINGDOM";
6: data.Sort = "BINARY_CI";
7: data.Comparison = "ANSI";
8: con.SetSessionInfo(data);
9: using (var cmd = con.CreateCommand())
10: {
11: cmd.CommandText = "select * from casetest where \"field\" = 'tom'";
12:
13: using (var reader = cmd.ExecuteReader())
14: {
15: while (reader.Read())
16: {
17: Console.WriteLine(reader[0]);
18: }
19: }
20: }
21: }
I hope this story saves someone else spending a lot of money in a swear box. Not sure if the issues I had are environmental (I’m running Oracle Database 10g Express Edition Release 10.2.0.1. on a Windows 7 box – although I tested it on a Vista box too with the same problems). As always, if anyone knows a better way of solving this problem please let me know.