Setting NLS_COMP and NLS_SORT

[bjallemad]

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

[Shalex]

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.

[bjallemad]

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

[Shalex]

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.

[bjallemad]

Any progress on this issue?

[Shalex]

The investigation is in progress. As soon as we have any results, we will let you know.

[shyam.pundkar]

Dear Support

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

Thanks
Shyam

[Shalex]

As a workaround, please create a handler of the OracleConnection.StateChange event and execute all needed commands when connection is opened.

[bjallemad]

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

[Shalex]

We are working on the implementation of this feature. We will notify you about the results as soon as any are available.

[Shalex]

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.

[cbiegner]

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...

[Shalex]

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