Setting NLS_COMP and NLS_SORT

Setting NLS_COMP and NLS_SORT

Postby bjallemad » Mon 23 Sep 2013 08:12

Hi,

Is there any support in Devart to set NLS_COMP and NLS_SORT for getting case insensitive searches?

We would like to set these to something like:
NLS_COMP=LINGUISTIC
NLS_SORT=BINARY_CI

I have tried to set these on every connection open, but that will issue two extra sql commands for each query:
1. Open connection
2. ALTER SESSION SET NLS_COMP=LINGUISTIC
3. ALTER SESSION SET NLS_SORT=BINARY_CI
4. Execute query

This solution would work really nice if I could tell if the actual connection is pooled or not, since I would only need to do this once for each pooled connection I guess?

Or is there any other way of doing this?

Regards
Andreas
bjallemad
 
Posts: 4
Joined: Mon 23 Sep 2013 08:01

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Wed 25 Sep 2013 11:47

bjallemad wrote:I have tried to set these on every connection open, but that will issue two extra sql commands for each query:
1. Open connection
2. ALTER SESSION SET NLS_COMP=LINGUISTIC
3. ALTER SESSION SET NLS_SORT=BINARY_CI
4. Execute query

Why do you create a separate connection for each your query? Please follow your 1,2,3 steps only once and execute all your queries using this single connection object.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Setting NLS_COMP and NLS_SORT

Postby bjallemad » Mon 07 Oct 2013 09:54

Hi,

Well, this might work in an old style client/server application, but we are developing a web application with thousands of simultaneous users making request all the time.

In this environment we are only servicing request by request, and we don't keep any reference to the connections between calls (we are using your connection pool I guess!!!).

So is there any solution to this in a server environment?

Regards
Andreas
bjallemad
 
Posts: 4
Joined: Mon 23 Sep 2013 08:01

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Wed 09 Oct 2013 13:11

The connection string of OracleConnection includes the Initialization Command parameter for specifying a database-specific command that should be executed immediately after establishing the connection. But currently only one command can be executed via this parameter. We are investigating the possibility of implementing the functionality of using multiple commands in Initialization Command. We will post here about the result as soon as possible.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Setting NLS_COMP and NLS_SORT

Postby bjallemad » Wed 04 Dec 2013 13:28

Any progress on this issue?
bjallemad
 
Posts: 4
Joined: Mon 23 Sep 2013 08:01

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Thu 05 Dec 2013 14:49

The investigation is in progress. As soon as we have any results, we will let you know.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Setting NLS_COMP and NLS_SORT

Postby shyam.pundkar » Tue 08 Apr 2014 03:59

Dear Support

We are facing the same problem
Any progress on this issue?

Thanks
Shyam
shyam.pundkar
 
Posts: 3
Joined: Mon 07 Apr 2014 12:09

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Tue 08 Apr 2014 14:37

As a workaround, please create a handler of the OracleConnection.StateChange event and execute all needed commands when connection is opened.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Setting NLS_COMP and NLS_SORT

Postby bjallemad » Fri 25 Apr 2014 07:49

Well, that doesn't do much difference since that event is called every time we do cn.Open, and not when the underlying connection is actually opened the first time.

The two extra commands will still be executed for every client web-request!

Regards
Andreas
bjallemad
 
Posts: 4
Joined: Mon 23 Sep 2013 08:01

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Thu 01 May 2014 09:52

We are working on the implementation of this feature. We will notify you about the results as soon as any are available.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Tue 26 Apr 2016 17:09

The Run Once Command connection string parameter, which specifies a command or several commands to execute when the connection is opened the first time and not executed when the connection is taken from the pool, is added in the newest (9.0) version of dotConnect for Oracle: http://forums.devart.com/viewtopic.php?f=1&t=33571.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Setting NLS_COMP and NLS_SORT

Postby cbiegner » Thu 24 Nov 2016 15:56

Any Example for the RUn Once Command available? I can't get it to work with

Code: Select all
SET NLS_COMP=ANSI;ALTER SESSION SET NLS_LANGUAGE=GERMAN;


set...
cbiegner
 
Posts: 1
Joined: Thu 24 Nov 2016 15:52

Re: Setting NLS_COMP and NLS_SORT

Postby Shalex » Fri 25 Nov 2016 10:23

Try this way:
Code: Select all
    //var monitor = new OracleMonitor() { IsActive = true };

    using (var conn = new OracleConnection()) {

        OracleConnectionStringBuilder sb = new OracleConnectionStringBuilder();
        sb.RunOnceCommand = "BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=ANSI'; EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE=GERMAN'; END;";
        sb.Server = "orcl1120";
        sb.UserId = "scott";
        sb.Password = "tiger";

        conn.ConnectionString = sb.ConnectionString;
        conn.Open();
    }
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle