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

    Unhappy Unanswered: Problem With Row Ordering

    I have a ORACLE 8.1.7.0 table called TEST with a column NAME VARCHAR2(30). I put on it 4 records with the following values:

    (
    -
    (A
    -A

    When I do a "SELECT * from TEST ORDER BY NAME" I get the following order:

    (
    -
    (A
    -A

    wich, in ASCII VALUES, is WRONG!
    What i want is:

    (
    (A
    -
    -A

    I only get this when I replace the "ORDER BY" by "GROUP BY NAME".
    WHY? I am really crazy with this!!!!
    Please help me!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem With Row Ordering

    I am unable to replicate your problem:

    SQL> select * from test;

    NAME
    ------------------------------
    (
    -
    (A
    -A

    SQL> select * from test order by name;

    NAME
    ------------------------------
    (
    (A
    -
    -A

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    First of all thanks for your fast reply. When a programmer has troubles its good to get answers fast.

    About my problem:

    Yes, I know that, because my customer has tried also and he get the same result as you. But in my oracle db the problem still remains.

    Could be any database parameter? "Character Set"? "Collating sequence"?
    How can I know if the database was correctly installed? The installation was well run and reports no problems...

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    What is NLS_SORT set to?

  5. #5
    Join Date
    Mar 2004
    Posts
    16
    Originally posted by SkyWriter
    What is NLS_SORT set to?
    GREAT!!! MANY THANKS!!!

    When I set it to BINARY using "ALTER SESSION SET NLS_SORT=BINARY" the "SELECT * FROM TEST ORDER BY NAME"
    worked!

    yeah!!!!! great!!!

    Now, my new and last question is:
    How can i query those NLS_ parameters?
    Where I must set it to become permanent?

  6. #6
    Join Date
    Mar 2004
    Posts
    16
    Unfortunately I was wrong...

    When I start my SQLPlus the parameters NLS_SORT and NLS_COMP are BINARY. And the ORDER BY don't work.

    Only when I issue a ALTER SESSION SET NLS_SORT=BINARY the problem disappear. Why?

    Any tip?

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    You are looking at the database settings, not the session or environment settings - try doing "show parameters nls" in your session before issuing the alter session command.

    There must be some NLS settings in your client environment which is causing the parameters to be overridden. (eg. NLS_LANG). Try setting NLS_SORT and NLS_COMP environment variables for your client session. This would avoid having to issue the alter session command.

    Not all parameters are affected by environment variables, but NLS_SORT and NLS_COMP should be.

  8. #8
    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?

  9. #9
    Join Date
    Nov 2002
    Posts
    833
    Originally posted by mfonseca
    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?
    change them on the serverside in your .profile
    on the clients side in the registry HKEY_LOCAL_MACHINE/software/oracle/home<n>

    keep in mind you then change the defaults ; an alter session stmt can always be issued

  10. #10
    Join Date
    Mar 2004
    Posts
    16

    PROBLEM SOLVED

    Now, I'm certain.
    The problem is solved!

    Many thanks for the tip HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/<home><n>

    Great forum and great members and colleagues!

Posting Permissions

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