Hi
Oracle 10g, C# 3.5
I'm trying to set case insensitive searches base on what I have found on the web and tested in toad:
_command = new OracleCommand("", _connection);
_command.CommandText = "alter session set nls_comp = ANSI";
_command.ExecuteNonQuery();
_command.CommandText = "alter session set nls_sort = BINARY_CI";
_command.ExecuteNonQuery();
This does not seem to work.
Any pointers would really be appreciated!
Thanks
Chris
case insensitive searching
is this still the case in v5.70?
we would like to change the NLS_COMP and NLS_SORT settings at session level? do you have an API support in v5.70? if not, can we prepend the setting change scripts as part of the same connection simulating a session level change?
We do not provide API for changing NLS_COMP and NLS_SORT. Try setting these initial parameters at the session level via OracleCommand. Be aware that Direct mode does not support NLS conversion on client side.
Wouldn't it be easier to create a functional index in your DB (not even required, but faster for searching), and use the UPPER() function in you query?
some pseudo code to explain:
string somestring = "somestring";
string query = "select * from sometable where upper(somecolumn) = :bind";
Bindings bind = db.bind(":bind",somestring.ToUpper());
db.prepare(query, bind);
ResultSet restult = db.select();
some pseudo code to explain:
string somestring = "somestring";
string query = "select * from sometable where upper(somecolumn) = :bind";
Bindings bind = db.bind(":bind",somestring.ToUpper());
db.prepare(query, bind);
ResultSet restult = db.select();