case insensitive searching

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
csadler
Posts: 2
Joined: Fri 12 Sep 2008 14:18

case insensitive searching

Post by csadler » Fri 12 Sep 2008 14:25

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

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 15 Sep 2008 11:02

Please refer to the SQL documentation. We don't support such functionality.

slaxman
Posts: 51
Joined: Wed 16 Sep 2009 20:09
Location: United States

is this still the case in v5.70?

Post by slaxman » Fri 05 Aug 2011 19:59

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?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 08 Aug 2011 16:07

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.

gustavp
Posts: 10
Joined: Wed 13 Jul 2011 08:44

Post by gustavp » Tue 09 Aug 2011 11:44

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

Post Reply