-3

I am tring to set case insensitive parameters like bellow commands 

 ALTER SESSION SET NLS_COMP=LINGUISTIC;
 ALTER SESSION SET NLS_SORT=BINARY_CI;

and 

ALTER SYSTEM SET NLS_COMP=LINGUISTIC;
ALTER SYSTEM SET NLS_SORT=BINARY_CI;

but when It doesnt work. Can someone help me with this problem?

I type the first one ALTER SESSION it works only on SQL Developer but only in first time. When I open a new connection it doesnt work.

I type second one, ALTER SESSION SET NLS_COMP=LINGUISTIC, in settings shows NLS_COMP=LINGUISTIC; but when open new connection it doesnt work again. I mean when I search with lower case like

SELECT * 
FROM users 
WHERE username like 'conan'

and my username is CONAN it cannt find

A. Zalonis
  • 1,599
  • 6
  • 26
  • 41
  • What do you mean by "doesn't work"? – Mat Oct 18 '14 at 13:46
  • 3
    You're only altering the session, so if you open a new session it's not going to work, no... – Ben Oct 18 '14 at 14:26
  • 1
    There seems to be a little bit confusion between session and connection. A connection is actual connection (typically a socket over TCP/IP) between the client and the server. It is used by the client to communicate with the server, and by the server to respond to the client's requests. A session is the process (or thread) on the server that has been created for that connection, to service the client. This reads the client's request from the connection, executes/services the request, and then respond to the request to the client via the connection. – Lalit Kumar B Oct 18 '14 at 16:15

1 Answers1

2

ALTER SESSION SET NLS_COMP=LINGUISTIC;

ALTER SESSION SET NLS_SORT=BINARY_CI;

That statement will only affect your current session, when you open you a new session, the previous alter session in previous session won't affect the new session. If you want to make all the sessions to be case-insensitive, then use a AFTER LOGON TRIGGER and issue the same statements using dynamic sql.

For example,

CREATE OR REPLACE TRIGGER case_insensitive_trg
AFTER LOGON ON SCHEMA
BEGIN
  execute immediate 'alter session set NLS_SORT=BINARY_CI';
  execute immediate 'alter session set NLS_COMP=LINGUISTIC';
END;
/

For detailed information on case-insensitive sorts and compares, please read Oracle – Case Insensitive Sorts & Compares

Regarding, system level,

ALTER SYSTEM SET NLS_COMP=LINGUISTIC SCOPE SPFILE;
ALTER SYSTEM SET NLS_SORT=BINARY_AI SCOPE SPFILE;

You can certainly do whatever you want, of course it is your database. But, I won't recommend to do such a thing at system level. It is an overkill at system level, and unnecessary welcome to performance issues.

Update : The confusion between a session and a connection

A connection is actual connection (typically a socket over TCP/IP) between the client and the server. It is used by the client to communicate with the server, and by the server to respond to the client's requests.

A session is the process (or thread) on the server that has been created for that connection, to service the client. This reads the client's request from the connection, executes/services the request, and then respond to the request to the client via the connection.

So, a session reads and respond back the client's request via the connection.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • I came to something strange while reading the documentation for [`ALTER SESSION`](http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_2012.htm). It saids _"Use the ALTER SESSION statement to set or modify any of the conditions or parameters that affect your **connection** to the database."_ As of myself, I though about the *connection* as being the physical pathway to the DB, and the *session* as being established over a connection to query the DB. For _shared server_ you might have several sessions over one connection. So does ALTER SESSION alter _session_ or _connection_? – Sylvain Leroux Oct 18 '14 at 15:01
  • As per my experience with it, which I also shared here http://lalitkumarb.wordpress.com/2014/01/22/oracle-case-insensitive-sorts-compares/, it will alter the current session executing the alter session statement. If a new session is opened, it won't be affected. – Lalit Kumar B Oct 18 '14 at 15:05
  • I was almost certain of that too, but by using the word "connection" istead of "session", the documentation inserted a little bit of doubt in my mind. – Sylvain Leroux Oct 18 '14 at 15:17
  • The connection word in SQL Developer is even more confusing and developers find it synonymous with session. I have edited my answer and made my statement more explicit. – Lalit Kumar B Oct 18 '14 at 15:51
  • 1
    To put in clear words, A connection is actual connection (typically a socket over TCP/IP) between the client and the server. It is used by the client to communicate with the server, and by the server to respond to the client's requests. A session is the process (or thread) on the server that has been created for that connection, to service the client. This reads the client's request from the connection, executes/services the request, and then respond to the request to the client via the connection. – Lalit Kumar B Oct 18 '14 at 16:15