My Oracle Support Banner

How to set Case Insensitive Sorts BINARY_AI or CI for APIs that cannot use ALTER SESSION (Doc ID 753771.1)

Last updated on JULY 25, 2022

Applies to:

Oracle Database - Standard Edition - Version 10.2.0.1 to 11.1.0.8 [Release 10.2 to 11.1]
Information in this document applies to any platform.
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

While using the new AI ( accent insensitive ) or CI (case insensitve) Sorting options for 10.2, you find that you are unable to set the values for your non-SQLPLUS API such as JAVA or SQL DEVELOPER

While you can use the ALTER SESSION command to enable this sort, your JAVA connection does not have this option:

For SQLPLUS solutions includes altering the session or setting the value in the ENV for the SQLPLUS client

ALTER SESSION SET NLS_SORT=BINARY_AI   -- ( .. or CI) as well as

ALTER SESSION SET NLS_COMP=LINGUISTIC 

  orfor windows env:

set NLS_SORT=BINARY_AI

set NLS_COMP=LINGUISTIC

See also <Note 241047.1> The Priority of NLS Parameters Explained

Make sure your client can display the accented characters, that your NLS_CHARTERSET defines those characters and that you have the correct NLS_LANG set for the client you are using.

<Note 179133.1> The correct NLS_LANG in a Windows Environment
<Note 264157.1> The correct NLS_LANG setting in Unix Environments

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.