Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: Nls_sort And Rows Ordering Troubles

    My ORACLE Database is 8.1.7.0.

    Inside SQLPlus I made a SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME=NLS_SORT or NAME=NLS_COMP. Both are BINARY. The other setiings are the default ones.

    I have a table called TEST with NAME VARCHAR2(30) and 4 records:
    (
    -
    (A
    -A

    When I make a SELECT * from TEST ORDER BY NAME the ordering is not binary. I get the following one:

    (
    -
    (A
    -A

    But if I make a ALTER SESSION SET NLS_SORT=BINARY, the ORDER BY works, and I get the following list:

    (
    (A
    -
    -A

    Why this happen? Why the default values of NLS_SORT and NLS_COMP don't work?

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Check your sessions current settings with

    select * from nls_session_parameters

    There are also views for nls_database_parameters and nls_instance_parameters.

    The database parameters can be set in init<sid>.ora.

    I am not sure how the session parameters are initially determined...
    - based on the Locale of the client machine
    - based on the database parameters
    - based on the registry key HKLM/Software/Oracle

    Maybe someone on here will know?

    I believe the NLS_SORT can be affected by the NLS_LANG parameter as different languages have different sorting requirements for accented characters.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    yeah, the problem is really "where nls session parameters are defined".
    I make what you suggest and my NLS_SORT session parameter is always "WESTERN_EUROPEAN" not BINARY as I want.
    I tried to change it in INIT.ORA but SQLPlus ignore it!

    Where I could change permanently the NLS SESSION PARAMETERS?

  4. #4
    Join Date
    Mar 2004
    Posts
    16

    PROBLEM SOLVED

    The solution is:

    1) Go to the REGISTRY (run regedit)
    2) Find HKEY_LOCAL_MACHINE/Software/ORACLE/<home><n>
    3) Change NLS_LANG

    I changed it to
    AMERICAN_AMERICA.WEISO8859P15

    The WEISO8859P15 because I am in Lisbon, Portugal, and my currency symbol is the EURO!

    After the change when I login in SQLPlus my NLS_SORT is BINARY and the ORDER BY clause works as I want.

    MANY THANKS TO ALL!
    GREAT FORUM!
    I hope could help anybody in the future as you help me yesterday and today. SINCERELY, MANY THANKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •