Hi,

I am having issues with nls_sort=binary_ci, nls_sort setting is not
sorting rows in case insensitive

Oracle 10g R2
Linux x86

steps I followed

alter system set nls_sort=binary_ci scope=spfile;
create pfile from spfile;
shutdown immediate;
startup open;

Here is the sample data after the setting

ACTION_ID ACTION_NAME PD_ID PD_NAME

166 Admin::AdminEditRole 773 SPrivileges
166 Admin::AdminEditRole 774 SPrivileges
166 Admin::AdminEditRole 775 sNewRoleName
166 Admin::AdminEditRole 775 sOriginalRoleName


I WANT THE DATA TO BE SORTED LIKE THIS

ACTION_ID ACTION_NAME PD_ID PD_NAME

166 Admin::AdminEditRole 775 sNewRoleName
166 Admin::AdminEditRole 775 sOriginalRoleName
166 Admin::AdminEditRole 774 SPrivileges
166 Admin::AdminEditRole 773 SPrivileges



Thank you in advance
RA

janu wrote:
> Hi,
>
> I am having issues with nls_sort=binary_ci, nls_sort setting is not
> sorting rows in case insensitive
>
> Oracle 10g R2
> Linux x86
>
> steps I followed
>
> alter system set nls_sort=binary_ci scope=spfile;
> create pfile from spfile;
> shutdown immediate;
> startup open;
>
> Here is the sample data after the setting
>
> ACTION_ID ACTION_NAME PD_ID PD_NAME
>
> 166 Admin::AdminEditRole 773 SPrivileges
> 166 Admin::AdminEditRole 774 SPrivileges
> 166 Admin::AdminEditRole 775 sNewRoleName
> 166 Admin::AdminEditRole 775 sOriginalRoleName
>
>
> I WANT THE DATA TO BE SORTED LIKE THIS
>
> ACTION_ID ACTION_NAME PD_ID PD_NAME
>
> 166 Admin::AdminEditRole 775 sNewRoleName
> 166 Admin::AdminEditRole 775 sOriginalRoleName
> 166 Admin::AdminEditRole 774 SPrivileges
> 166 Admin::AdminEditRole 773 SPrivileges
>
>
>
> Thank you in advance
> RA

Changing an NLS_SORT parameter does not sort the data in a table.
Oracle's default tables are heap tables. Nothing is sorted.
What is the SQL statement you are issuing to retrieve the data?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Here is the query

SELECT ACTION_ID
, ACTION_NAME
,pd_id
, PD_NAME
, PT_NAME
, PD_GLOBAL_JOIN.GP_VALUE
, PD_GLOBAL_JOIN.GP_NAME
, AV_VALUE
, AV_GLOBAL_JOIN.GP_VALUE AV_GLOBAL_JOIN_GP_VALUE
, AV_GLOBAL_JOIN.GP_NAME AV_GLOBAL_JOIN_GP_NAME
, AV_GLOBAL_JOIN.GP_DESCRIPTION AV_GLOBAL_JOIN_GP_DESCRIPTION
, AV_GLOBAL_JOIN.GP_PROJECTID AV_GLOBAL_JOIN_GP_PROJECTID
, AV_GLOBALPARAMETERID
FROM VMTP_ACTION
LEFT JOIN VMTP_PARAMETERDEFINITION ON
(ACTION_ID = PD_ACTIONID
AND PD_METASTATUS = 1)
LEFT JOIN VMTP_ACCEPTEDVALUE ON
(PD_ID = AV_PARAMETERDEFINITIONID)
LEFT JOIN VMTP_GLOBALPARAMETER AV_GLOBAL_JOIN ON
(AV_GLOBALPARAMETERID = AV_GLOBAL_JOIN.GP_ID)
LEFT JOIN VMTP_PARAMETERTYPE ON
(PD_PARAMETERTYPEID = PT_ID)
LEFT JOIN VMTP_GLOBALPARAMETER PD_GLOBAL_JOIN ON
(PD_DEFAULT_GLOBALPARAMETERID = PD_GLOBAL_JOIN.GP_ID)
WHERE ACTION_ID IN (166)
order by pd_name;

janu wrote:
> Here is the query
>
> SELECT ACTION_ID
> , ACTION_NAME
> ,pd_id
> , PD_NAME
> , PT_NAME
> , PD_GLOBAL_JOIN.GP_VALUE
> , PD_GLOBAL_JOIN.GP_NAME
> , AV_VALUE
> , AV_GLOBAL_JOIN.GP_VALUE AV_GLOBAL_JOIN_GP_VALUE
> , AV_GLOBAL_JOIN.GP_NAME AV_GLOBAL_JOIN_GP_NAME
> , AV_GLOBAL_JOIN.GP_DESCRIPTION AV_GLOBAL_JOIN_GP_DESCRIPTION
> , AV_GLOBAL_JOIN.GP_PROJECTID AV_GLOBAL_JOIN_GP_PROJECTID
> , AV_GLOBALPARAMETERID
> FROM VMTP_ACTION
> LEFT JOIN VMTP_PARAMETERDEFINITION ON
> (ACTION_ID = PD_ACTIONID
> AND PD_METASTATUS = 1)
> LEFT JOIN VMTP_ACCEPTEDVALUE ON
> (PD_ID = AV_PARAMETERDEFINITIONID)
> LEFT JOIN VMTP_GLOBALPARAMETER AV_GLOBAL_JOIN ON
> (AV_GLOBALPARAMETERID = AV_GLOBAL_JOIN.GP_ID)
> LEFT JOIN VMTP_PARAMETERTYPE ON
> (PD_PARAMETERTYPEID = PT_ID)
> LEFT JOIN VMTP_GLOBALPARAMETER PD_GLOBAL_JOIN ON
> (PD_DEFAULT_GLOBALPARAMETERID = PD_GLOBAL_JOIN.GP_ID)
> WHERE ACTION_ID IN (166)
> order by pd_name;
>

Consider a syntax related to this:

SELECT * FROM test
ORDER BY NLSSORT(name, 'NLS_SORT = BINARY_CI');
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks Morgan, I made the change as you recommanded.

I have one more question.

If I do a select on database_properties and v$parameter, I see a
difference. Could you please explain what might be?

select * from database_properties where property_name like '%SORT%'

NLS_SORT BINARY Linguistic definition

select name, value from v$parameter where name like '%sort%'

nls_sort BINARY_CI

Thank you
RA

janu <angani@gmail.com> wrote:
> If I do a select on database_properties and v$parameter, I see a
> difference. Could you please explain what might be?
>
> select * from database_properties where property_name like '%SORT%'
>
> NLS_SORT BINARY Linguistic definition
>
> select name, value from v$parameter where name like '%sort%'
>
> nls_sort BINARY_CI

http://download-uk.oracle.com/docs/c...ta.htm#i637232

Yours,
Laurenz Albe

Hi Laurenz A,

I set my system setting to BINARY_CI

sql>alter system set nls_sort=binary_ci scope=both;

My question is ,why there is difference between database_properties and
V$parameter.

Thank you
RA

janu <angani@gmail.com> wrote:
> I set my system setting to BINARY_CI
>
> sql>alter system set nls_sort=binary_ci scope=both;
>
> My question is ,why there is difference between database_properties and
> V$parameter.

Sorry for my previous reply, I did not understand what your problem was.

It is indeed strange that the view DATABASE_PROPERTIES and its
underlying table SYS.PROPS$ does not reflect the change of NLS_SORT.

Maybe it is a bug in Oracle.
You should open a service request if you want to find out.

By the way, the command you give to change the database parameter
NLS_SORT is not correct. I get the error:

ORA-02096: specified initialization parameter is not modifiable with this
option

You can only modify it in the spfile.

Yours,
Laurenz Albe

janu wrote:
> Hi Laurenz A,
>
> I set my system setting to BINARY_CI
>
> sql>alter system set nls_sort=binary_ci scope=both;
>
> My question is ,why there is difference between database_properties and
> V$parameter.
>
> Thank you
> RA

I'm guessing what you are seeing is the difference between how Oracle
sorts (binary) and whether it uses case insensitivity (binary_ci).
This varies based on which sql verbs you are using (see metalink
Note:263083.1). Since nls_sort is the collating sequence used, Oracle
must somehow keep track of all the derivations of it. Perhaps the bug
is both views should have both attributes. What happens when you set
your language to, say, CATALAN?

This certainly could be documented better, perhaps an SR is in order,
we shouldn't have to guess.

jg
--
@home.com is bogus.
http://www.engadget.com/2006/12/12/l...igner-glasses/